Postgres - can't drop constraint because objects depend on it even though it is duplicate
Laycee last edited by
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.
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);
alter table artifacts_registry add column p_artifact_name varchar(255);
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.
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.
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 start up 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.
The way PostgreSQL is implemented, a foreign key references a unique index on the target table, not a list of columns. If you have several unique constraints on a single column, a foreign key constraint referencing that column will reference one of these unique indexes. So if you created the unique constraint first, that's what the foreign key will point to.
The correct solution is to drop the foreign key along with the unique constraint and create it again, so that it points to the primary key. You could create it as
NOT VALIDand validate it later to avoid holding strong locks for a longer time.
You could also update the
pg_constraintentry and modify
conindid, but you'd also have to modify the corresponding
pg_dependentry to change the dependency. However, modifying catalog tables is not supported, and it is easy to break your database that way, so I cannot recommend this course of action.