Explain psql drop constraint?

244 Asked by DavidEdmunds in SQL Server , Asked on Mar 13, 2023

 I have a situation that I am running into after upgrading my Postgres from 10.7 to 13.6. I have flyway scripts that date over several years that build up the history of the database. The changes to the schema shuffled around PKs, and while doing so it had to create some constraints.

V1__PLM.sql
create table artifacts_registry (
    artifact_id varchar(36) not null,
    artifact_name varchar(255) not null,
    ...);
alter table artifacts_registry 
    add constraint UK_t5rk0b5yybufi8pkkxato2e11 unique (artifact_name);
V17__PLM.sql
alter table artifacts_registry
    add column p_artifact_name varchar(255);
V19___PLM.sql
alter table artifacts_registry
    add constraint artifacts_registry_pkey primary key (artifact_name);
At this point I noticed that I had a duplicate index on a single column - artifacts_registry_pkey and uk_t5rk0b5yybufi8pkkxato2e11.

My flyway script tries to drop this extra constraint.

V78__PLM.sql
ALTER TABLE artifacts_registry 
    DROP CONSTRAINT uk_t5rk0b5yybufi8pkkxato2e11;
and this fails on the new Postgres 13.6 version.
Migration V78__PLM.sql failed
-----------------------------
SQL State  : 2BP01
Error Code : 0
Message    : ERROR: cannot drop constraint uk_t5rk0b5yybufi8pkkxato2e11 on table artifacts_registry because other objects depend on it
  Detail: constraint fkhg85thdq13hf91f7h6mbd5dza on table artifacts_registry depends on index uk_t5rk0b5yybufi8pkkxato2e11
constraint fk1afmjb153f2dgxs4roj2pzez9 on table artifacts_relation depends on index uk_t5rk0b5yybufi8pkkxato2e11
constraint fkafh3xt7w1oyg7sz2r6c7jo8nk on table artifacts_relation depends on index uk_t5rk0b5yybufi8pkkxato2e11
constraint fk89pg0m2cqgnoidk3uxuxex76x on table execution_artifacts depends on index uk_t5rk0b5yybufi8pkkxato2e11
  Hint: Use DROP ... CASCADE to drop the dependent objects too.
Location   : db/migration/schema/V78__.sql (/Users/dobrim1/dev/P-PLM/target/classes/db/migration/schema/V78__PLM.sql)
Line       : 1
Statement  : ALTER TABLE artifacts_registry DROP CONSTRAINT uk_t5rk0b5yybufi8pkkxato2e11
Confusingly, this all used to pass on previous versions of Postgres and AWS Aurora 10.7, 11.x, and 12.x.

It tries to give me the hint to use DROP CASCADE. However, I don't really want to do this because those indexes are useful and referencing a valid relationship. The problem here is that Postgres has two identical unique constraints, but it is choosing uk_t5rk0b5yybufi8pkkxato2e11 as the "main" one, against which it is enforcing deletion constraints.

Question

How can I mark the p_artifact_name index as the main index, such that I can delete uk_t5rk0b5yybufi8pkkxato2e11?

Related info -

These changes are already deployed in Production against AWS Aurora 13.6. Each time the containers startup they validate the migration history and don't have any issues. So the production server did not choose the main index in the same way as Postgres does locally.

The issue arises locally when I spin up a Postgres 13.6 container and start my application, which then has to run the full migration history starting from V1__PLM.sql.

Baseline and undo migrations are not an option due to paid nature.

Answered by Aashna Saito

Indeed the only good way to solve this issue of psql drop constraint seems to be to drop the constraint anyway by adding CASCADE to the SQL. Then you have to add back the constraints that were dropped. When the migration runs, flyway will see that some constraints were dropped and log.

  DB: drop cascades to 4 other objects

So make sure that you are adding back the same constraints. After the constraints are added back, they will be associated with the remaining correct constraint. All of this can be done in the same migration script that was failing before.

V78__PLM.sql

ALTER TABLE artifacts_registry DROP CONSTRAINT uk_t5rk0b5yybufi8pkkxato2e11 CASCADE;

-- add back constraints dropped by CASCADE operation above

ALTER TABLE artifacts_registry ADD CONSTRAINT fkhg85thdq13hf91f7h6mbd5dza ...

ALTER TABLE artifacts_relation ADD CONSTRAINT fk1afmjb153f2dgxs4roj2pzez9 ...

ALTER TABLE artifacts_relation ADD CONSTRAINT fkafh3xt7w1oyg7sz2r6c7jo8nk ...

ALTER TABLE execution_artifacts ADD CONSTRAINT fk89pg0m2cqgnoidk3uxuxex76x ...

But of course with this approach you will have to repair the flyway checksum for this version.

Explain psql drop constraint?

Your Answer