BUG-HUNT: [migration] a5_006 re-creates constraints already defined in a5_003 — breaks PostgreSQL upgrade and causes duplicate index on SQLite #6647

New issue

Open

opened 2026-04-09 22:44:16 +00:00 by HAL9000 · 0 comments

HAL9000 commented

2026-04-09 22:44:16 +00:00

Owner

Bug Report: [migration] Duplicate Constraint Creation in `a5_006_action_invariants_unique_constraint`

Severity Assessment

Impact: On PostgreSQL, migration a5_006 will fail with DuplicateObject: constraint already exists, blocking all future Alembic upgrades on PostgreSQL databases. On SQLite, batch_alter_table reflects the existing unique index and then attempts to create it again, potentially resulting in a duplicate named constraint in the reconstructed table.
Likelihood: Certain — any PostgreSQL deployment attempting alembic upgrade head that passes through a5_006 will fail. SQLite may silently produce a malformed schema or raise an integrity error depending on SQLAlchemy/SQLite version.
Priority: High

Location

File: alembic/versions/a5_006_action_invariants_unique_constraint.py
Function: upgrade()
Lines: ~55–75 (the action_arguments batch alter block)

Description

a5_003_spec_aligned_actions.py already creates the action_arguments table with all three constraints fully declared:

# In a5_003_spec_aligned_actions.py, upgrade()
op.create_table(
    "action_arguments",
    ...
    sa.UniqueConstraint("action_name", "name", name="uq_action_arguments_name"),   # ← already created
    sa.CheckConstraint(
        "arg_type IN ('string', 'integer', 'float', 'boolean', 'list')",
        name="ck_action_arguments_type",                                             # ← already created
    ),
    sa.CheckConstraint(
        "requirement IN ('required', 'optional')",
        name="ck_action_arguments_requirement",                                      # ← already created
    ),
)

a5_006 then unconditionally attempts to create those same three constraints again via batch_alter_table:

# In a5_006_action_invariants_unique_constraint.py, upgrade()
with op.batch_alter_table("action_arguments") as batch:
    batch.create_unique_constraint(
        "uq_action_arguments_name",          # ← DUPLICATE of a5_003
        ["action_name", "name"],
    )
    batch.create_check_constraint(
        "ck_action_arguments_type",          # ← DUPLICATE of a5_003
        "arg_type IN ('string', 'integer', 'float', 'boolean', 'list')",
    )
    batch.create_check_constraint(
        "ck_action_arguments_requirement",   # ← DUPLICATE of a5_003
        "requirement IN ('required', 'optional')",
    )

No migration between a5_003 and a5_006 drops or rebuilds action_arguments (verified by checking a5_004, a5_005, and all intermediate migrations — none touch action_arguments). The migration chain from a5_003 through a5_006 is:

a5_003 → a5_004 → b1_001_resource_registry → a5_005 → b0_001_projects → c1_001 → a6_001/a7_001 → ... → m9_002 → a5_006

None of these drop or rebuild action_arguments.

Expected Behavior

a5_006 should only add the new constraint uq_action_invariants_position on action_invariants (which correctly did not exist in a5_003). It should not attempt to re-create constraints already present on action_arguments.

Actual Behavior

On PostgreSQL: alembic upgrade head fails with:

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.DuplicateObject) constraint "uq_action_arguments_name" for relation "action_arguments" already exists

On SQLite: batch_alter_table reflects the existing named unique index, then the migration attempts to create it again — depending on SQLAlchemy's batch mode, this either raises or silently produces a schema with a duplicated constraint definition.

Suggested Fix

Remove the three duplicate constraint creations for action_arguments from a5_006. Keep only the uq_action_invariants_position constraint (which is genuinely new):

def upgrade() -> None:
    """Add the uq_action_invariants_position unique constraint."""
    _deduplicate_action_invariants()
    with op.batch_alter_table("action_invariants") as batch:
        batch.create_unique_constraint(
            "uq_action_invariants_position",
            ["action_name", "position"],
        )
    # NOTE: uq_action_arguments_name, ck_action_arguments_type, and
    # ck_action_arguments_requirement were already created in a5_003.
    # Do NOT re-create them here.

If the intent is to bring already-running databases into parity, a guard is needed:

from sqlalchemy import inspect
insp = inspect(op.get_bind())
existing = {c["name"] for c in insp.get_unique_constraints("action_arguments")}
if "uq_action_arguments_name" not in existing:
    batch.create_unique_constraint(...)

TDD Note

After this bug issue is verified, a corresponding Type/Testing issue will be created for TDD. The test will use tags: @tdd_issue, @tdd_issue_<this-issue-number>, and @tdd_expected_fail to prove the bug exists before fixing it.

Automated by CleverAgents Bot
Supervisor: Bug Hunting | Agent: bug-hunter

## Bug Report: [migration] Duplicate Constraint Creation in `a5_006_action_invariants_unique_constraint` ### Severity Assessment - **Impact**: On PostgreSQL, migration `a5_006` will fail with `DuplicateObject: constraint already exists`, blocking all future Alembic upgrades on PostgreSQL databases. On SQLite, `batch_alter_table` reflects the existing unique index and then attempts to create it again, potentially resulting in a duplicate named constraint in the reconstructed table. - **Likelihood**: Certain — any PostgreSQL deployment attempting `alembic upgrade head` that passes through `a5_006` will fail. SQLite may silently produce a malformed schema or raise an integrity error depending on SQLAlchemy/SQLite version. - **Priority**: High ### Location - **File**: `alembic/versions/a5_006_action_invariants_unique_constraint.py` - **Function**: `upgrade()` - **Lines**: ~55–75 (the `action_arguments` batch alter block) ### Description `a5_003_spec_aligned_actions.py` already creates the `action_arguments` table **with all three constraints** fully declared: ```python # In a5_003_spec_aligned_actions.py, upgrade() op.create_table( "action_arguments", ... sa.UniqueConstraint("action_name", "name", name="uq_action_arguments_name"), # ← already created sa.CheckConstraint( "arg_type IN ('string', 'integer', 'float', 'boolean', 'list')", name="ck_action_arguments_type", # ← already created ), sa.CheckConstraint( "requirement IN ('required', 'optional')", name="ck_action_arguments_requirement", # ← already created ), ) ``` `a5_006` then unconditionally attempts to create those **same three constraints again** via `batch_alter_table`: ```python # In a5_006_action_invariants_unique_constraint.py, upgrade() with op.batch_alter_table("action_arguments") as batch: batch.create_unique_constraint( "uq_action_arguments_name", # ← DUPLICATE of a5_003 ["action_name", "name"], ) batch.create_check_constraint( "ck_action_arguments_type", # ← DUPLICATE of a5_003 "arg_type IN ('string', 'integer', 'float', 'boolean', 'list')", ) batch.create_check_constraint( "ck_action_arguments_requirement", # ← DUPLICATE of a5_003 "requirement IN ('required', 'optional')", ) ``` No migration between `a5_003` and `a5_006` drops or rebuilds `action_arguments` (verified by checking `a5_004`, `a5_005`, and all intermediate migrations — none touch `action_arguments`). The migration chain from `a5_003` through `a5_006` is: ``` a5_003 → a5_004 → b1_001_resource_registry → a5_005 → b0_001_projects → c1_001 → a6_001/a7_001 → ... → m9_002 → a5_006 ``` None of these drop or rebuild `action_arguments`. ### Expected Behavior `a5_006` should only add the **new** constraint `uq_action_invariants_position` on `action_invariants` (which correctly did not exist in `a5_003`). It should **not** attempt to re-create constraints already present on `action_arguments`. ### Actual Behavior On PostgreSQL: `alembic upgrade head` fails with: ``` sqlalchemy.exc.ProgrammingError: (psycopg2.errors.DuplicateObject) constraint "uq_action_arguments_name" for relation "action_arguments" already exists ``` On SQLite: `batch_alter_table` reflects the existing named unique index, then the migration attempts to create it again — depending on SQLAlchemy's batch mode, this either raises or silently produces a schema with a duplicated constraint definition. ### Suggested Fix Remove the three duplicate constraint creations for `action_arguments` from `a5_006`. Keep only the `uq_action_invariants_position` constraint (which is genuinely new): ```python def upgrade() -> None: """Add the uq_action_invariants_position unique constraint.""" _deduplicate_action_invariants() with op.batch_alter_table("action_invariants") as batch: batch.create_unique_constraint( "uq_action_invariants_position", ["action_name", "position"], ) # NOTE: uq_action_arguments_name, ck_action_arguments_type, and # ck_action_arguments_requirement were already created in a5_003. # Do NOT re-create them here. ``` If the intent is to bring already-running databases into parity, a guard is needed: ```python from sqlalchemy import inspect insp = inspect(op.get_bind()) existing = {c["name"] for c in insp.get_unique_constraints("action_arguments")} if "uq_action_arguments_name" not in existing: batch.create_unique_constraint(...) ``` ### Category `migration` / `boundary` ### TDD Note After this bug issue is verified, a corresponding Type/Testing issue will be created for TDD. The test will use tags: `@tdd_issue`, `@tdd_issue_<this-issue-number>`, and `@tdd_expected_fail` to prove the bug exists before fixing it. --- **Automated by CleverAgents Bot** Supervisor: Bug Hunting | Agent: bug-hunter

HAL9000 added the

labels

2026-04-09 22:45:14 +00:00

HAL9000 added this to the v3.2.0 milestone

2026-04-09 22:47:13 +00:00

HAL9000 referenced this issue