Repository navigation
[GH-1174] Fix episode_type enum UniqueViolationError during concurrent startup - #1175
Merged
sscargal merged 1 commit intoMar 4, 2026
Merged
Conversation
Prevent UniqueViolationError on pg_type_typname_nsp_index when multiple uvicorn workers call metadata.create_all concurrently. SQLAlchemy's checkfirst queries pg_type then issues CREATE TYPE in separate steps, creating a TOCTOU window where a concurrent process can create the type between check and create. Disable automatic enum creation (create_type=False) and instead create the type explicitly inside a SAVEPOINT so a duplicate-type IntegrityError rolls back only the savepoint, keeping the outer transaction healthy for subsequent CREATE TABLE statements. Closes #1174
Contributor
There was a problem hiding this comment.
Pull request overview
This PR addresses a PostgreSQL startup race where multiple concurrent workers can collide while creating the episode_type enum, causing UniqueViolationError and aborting schema initialization.
Changes:
- Disable SQLAlchemy’s automatic enum type creation (
create_type=False) forEpisode.episode_type. - Add explicit, PostgreSQL-only enum creation guarded by a SAVEPOINT (
begin_nested()) duringSqlAlchemyEpisodeStore.startup(). - Add unit + integration tests covering the enum creation behavior and error handling.
Reviewed changes
Copilot reviewed 2 out of 2 changed files in this pull request and generated 1 comment.
| File | Description |
|---|---|
| packages/server/src/memmachine_server/common/episode_store/episode_sqlalchemy_store.py | Adds explicit PG enum creation during startup and disables automatic enum creation on the column. |
| packages/server/server_tests/memmachine_server/common/episode_store/test_sqlalchemy_episode_store_startup.py | Adds tests for startup wrapping, enum idempotency behavior, and PostgreSQL integration verification. |
Comments suppressed due to low confidence (1)
packages/server/src/memmachine_server/common/episode_store/episode_sqlalchemy_store.py:164
- The enum-creation block catches IntegrityError and ProgrammingError unconditionally and assumes they mean "enum already exists". ProgrammingError can also represent real startup failures (e.g. permission denied, wrong schema/search_path), and swallowing it can lead to harder-to-diagnose errors later in create_all. Consider only suppressing the specific duplicate/unique-violation cases (e.g. by checking SQLSTATE such as 42710/23505 or matching the underlying driver exception/message for episode_type) and re-raising otherwise.
except (IntegrityError, ProgrammingError):
logger.debug(
"episode_type enum already exists (concurrent creation); continuing"
)
💡 Add Copilot custom instructions for smarter, more guided reviews. Learn how to get started.
You can also share your feedback on Copilot code review. Take the survey.
jealous
approved these changes
Mar 4, 2026
sscargal
approved these changes
Mar 4, 2026
sscargal
left a comment
sscargal
left a comment
Contributor
There was a problem hiding this comment.
LGTM, thanks for the quick fix. Tested ok. No errors seen on the console when spawning multiple workers.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Add this suggestion to a batch that can be applied as a single commit.This suggestion is invalid because no changes were made to the code.Suggestions cannot be applied while the pull request is closed.Suggestions cannot be applied while viewing a subset of changes.Only one suggestion per line can be applied in a batch.Add this suggestion to a batch that can be applied as a single commit.Applying suggestions on deleted lines is not supported.You must change the existing code in this line in order to create a valid suggestion.Outdated suggestions cannot be applied.This suggestion has been applied or marked resolved.Suggestions cannot be applied from pending reviews.Suggestions cannot be applied on multi-line comments.Suggestions cannot be applied while the pull request is queued to merge.Suggestion cannot be applied right now. Please check back later.
Purpose of the change
Fix
UniqueViolationErroronpg_type_typname_nsp_indexfor theepisode_typePostgreSQL enum when multiple uvicorn workers start concurrently.Description
SQLAlchemy's
metadata.create_all(checkfirst=True)queriespg_catalog.pg_typebefore issuingCREATE TYPE, but this is a Python-level TOCTOU (time-of-check-to-time-of-use) check — not an atomic SQL operation. When multiple uvicorn workers (MEMMACHINE_WORKERS > 1) run the startup lifespan concurrently, a race window exists where two workers both find the type missing and both attemptCREATE TYPE episode_type, causing the second to fail withUniqueViolationError.Since PostgreSQL aborts the entire transaction on any error, the failed
CREATE TYPEalso prevents subsequentCREATE TABLEstatements, leaving the database partially initialized and the container in a restart loop.Changes:
create_type=Falseon theSAEnumcolumn to preventcreate_allfrom attempting automaticCREATE TYPEpg_dialect.ENUMwhose values are derived from the PythonEpisodeTypeenum (stays in sync automatically)startup(), explicitly create the enum type inside abegin_nested()(SAVEPOINT) beforecreate_all. If a concurrent worker already created the type, theIntegrityErrorrolls back only the savepoint — not the outer transaction — socreate_allcan proceed normallycreate_type=False)Fixes/Closes
Fixes #1174
Type of change
How Has This Been Tested?
Checklist
Screenshots/Gifs
N/A