[Bug]: UniqueViolationError – duplicate key value violates unique constraint "pg_type_typname_nsp_index" after local Docker build

Describe the bug

After building and starting a local Docker image from the latest commit, the following error is repeatedly logged and the server fails to start correctly:

sqlalchemy.dialects.postgresql.asyncpg.AsyncAdapt_asyncpg_dbapi.IntegrityError: <class 'asyncpg.exceptions.UniqueViolationError'>: duplicate key value violates unique constraint "pg_type_typname_nsp_index"
DETAIL:  Key (typname, typnamespace)=(episode_type, 2200) already exists.

The application seems to be trying to create or register a Postgres type named 'episode_type' that already exists in the "pg_type" namespace, resulting in startup failure.

Steps to reproduce

Clone the repository and check out the latest commit.
Build a local Docker image using the build-docker.sh script
Start memmachine using the new image, e.g.:./memmachine-compose.sh start memmachine/memmachine:0.2.6.dev77_g391f1630-cpu
Observe the application logs with ./memmachine-compose.sh logs
Look for repeated error messages related to 'duplicate key value violates unique constraint "pg_type_typname_nsp_index"'.

Example log excerpt:

memmachine-app    | ERROR:    Traceback (most recent call last):
memmachine-app    |   File "/app/.venv/lib/python3.12/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 550, in _prepare_and_execute
memmachine-app    |     self._rows = deque(await prepared_stmt.fetch(*parameters))
memmachine-app    |                        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
memmachine-app    |   File "/app/.venv/lib/python3.12/site-packages/asyncpg/prepared_stmt.py", line 177, in fetch
memmachine-app    |     data = await self.__bind_execute(args, 0, timeout)
memmachine-app    | ...
memmachine-app    | asyncpg.exceptions.UniqueViolationError: duplicate key value violates unique constraint "pg_type_typname_nsp_index"
memmachine-app    | DETAIL:  Key (typname, typnamespace)=(episode_type, 2200) already exists.

Expected behavior

The application should start without encountering database integrity errors. Custom Postgres types (such as enum or composite types) should only be created/registered if they do not already exist. Multiple startup attempts or concurrency should NOT cause unique constraint violations.

Environment

OS: Ubuntu Linux 25.04
MemMachine Version: latest commit/memmachine:0.2.6.dev77_g391f1630

Additional context

See attached logs for details of the stack trace and error. This issue may affect database migration or local setup for contributors.

If you need more context, here is an extended segment of the log output:

memmachine-app    | ERROR:    Traceback (most recent call last):
memmachine-app    |   File "/app/.venv/lib/python3.12/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 550, in _prepare_and_execute
memmachine-app    |     self._rows = deque(await prepared_stmt.fetch(*parameters))
memmachine-app    |                        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
memmachine-app    |   File "/app/.venv/lib/python3.12/site-packages/asyncpg/prepared_stmt.py", line 177, in fetch
memmachine-app    |     data = await self.__bind_execute(args, 0, timeout)
memmachine-app    |            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
memmachine-app    |   File "/app/.venv/lib/python3.12/site-packages/asyncpg/prepared_stmt.py", line 268, in __bind_execute
memmachine-app    |     data, status, _ = await self.__do_execute(
memmachine-app    |                       ^^^^^^^^^^^^^^^^^^^^^^^^
memmachine-app    |   File "/app/.venv/lib/python3.12/site-packages/asyncpg/prepared_stmt.py", line 257, in __do_execute
memmachine-app    |     return await executor(protocol)
memmachine-app    |            ^^^^^^^^^^^^^^^^^^^^^^^^
memmachine-app    |   File "asyncpg/protocol/protocol.pyx", line 205, in bind_execute
memmachine-app    | asyncpg.exceptions.UniqueViolationError: duplicate key value violates unique constraint "pg_type_typname_nsp_index"
memmachine-app    | DETAIL:  Key (typname, typnamespace)=(episode_type, 2200) already exists.

memmachine-app    | The above exception was the direct cause of the following exception:

memmachine-app    | Traceback (most recent call last):
memmachine-app    |   File "/app/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
memmachine-app    |     self.dialect.do_execute(
memmachine-app    |   File "/app/.venv/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 952, in do_execute
memmachine-app    |     cursor.execute(statement, parameters)
memmachine-app    |   File "/app/.venv/lib/python3.12/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 585, in execute
memmachine-app    |     self._adapt_connection.await_(
memmachine-app    |   File "/app/.venv/lib/python3.12/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 132, in await_only
memmachine-app    |     return current.parent.switch(awaitable)  # type: ignore[no-any-return,attr-defined] # noqa: E501
memmachine-app    |            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
memmachine-app    |   File "/app/.venv/lib/python3.12/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 196, in greenlet_spawn
memmachine-app    |     value = await result
memmachine-app    |             ^^^^^^^^^^^^
memmachine-app    |   File "/app/.venv/lib/python3.12/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 563, in _prepare_and_execute
memmachine-app    |     self._handle_exception(error)
memmachine-app    |   File "/app/.venv/lib/python3.12/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 513, in _handle_exception
memmachine-app    |     self._adapt_connection._handle_exception(error)
memmachine-app    |   File "/app/.venv/lib/python3.12/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 797, in _handle_exception
memmachine-app    |     raise translated_error from error
memmachine-app    | sqlalchemy.dialects.postgresql.asyncpg.AsyncAdapt_asyncpg_dbapi.IntegrityError: <class 'asyncpg.exceptions.UniqueViolationError'>: duplicate key value violates unique constraint "pg_type_typname_nsp_index"
memmachine-app    | DETAIL:  Key (typname, typnamespace)=(episode_type, 2200) already exists.

Suggested Fix

Here's the recommended code fix for episode_sqlalchemy_store.py.

The root cause is that SAEnum(EpisodeType, name="episode_type") tells SQLAlchemy to CREATE TYPE episode_type in PostgreSQL every time create_all is called. The fix is to pass create_type=False so SQLAlchemy assumes the type already exists, and instead manage its creation explicitly with an IF NOT EXISTS guard inside startup():

# Change the column definition to suppress auto-creation of the enum type:
    episode_type = mapped_column(
        SAEnum(EpisodeType, name="episode_type", create_type=False),
        default=EpisodeType.MESSAGE,
    )

And update startup() to create the enum type idempotently before running create_all:

    async def startup(self) -> None:
        try:
            async with self._engine.begin() as conn:
                # Create the enum type only if it doesn't already exist.
                # PostgreSQL doesn't support CREATE TYPE ... IF NOT EXISTS natively,
                # so we guard with a DO block.
                await conn.execute(
                    sa.text(
                        """
                        DO $$
                        BEGIN
                            IF NOT EXISTS (
                                SELECT 1 FROM pg_type WHERE typname = 'episode_type'
                            ) THEN
                                CREATE TYPE episode_type AS ENUM (
                                    'message', 'text'
                                );
                            END IF;
                        END$$;
                        """
                    )
                )
                await conn.run_sync(BaseEpisodeStore.metadata.create_all)
        except (OperationalError, socket.gaierror) as err:
            raise ConfigurationError(
                "Failed to connect to the database during startup, please check your configuration."
            ) from err

Note: The enum values in the CREATE TYPE statement should match exactly the values in your EpisodeType Python enum. You can verify these in episode_model.py. Also, for SQLite (used in tests), SAEnum doesn't create a PostgreSQL type, so create_type=False has no effect there — tests will continue to pass unchanged.

[MemMachineGH-1174] Fix episode_type enum UniqueViolationError during…

Navigation Menu

Repository navigation

[Bug]: UniqueViolationError – duplicate key value violates unique constraint "pg_type_typname_nsp_index" after local Docker build #1174

Describe the bug

Steps to reproduce

Expected behavior

Environment

Additional context

Suggested Fix

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

[Bug]: UniqueViolationError – duplicate key value violates unique constraint "pg_type_typname_nsp_index" after local Docker build #1174

Description

Describe the bug

Steps to reproduce

Expected behavior

Environment

Additional context

Activity

sscargal commented on Mar 4, 2026

Suggested Fix

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions