3

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 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.

2 Answers 2

3

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 VALID and validate it later to avoid holding strong locks for a longer time.

You could also update the pg_constraint entry and modify conindid, but you'd also have to modify the corresponding pg_depend entry 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.

1

Indeed the only good way to solve this issue 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.

1
  • Hi, and welcome to dba.se! Is there a particular reason that you don't give meaningful names to your CONSTRAINTs? They are a help for end-users when reporting errors - a long meaningless string like uk_t5rk0b5yybufi8pkkxato2e11 is not easy to read, whereas something like parent_table_child_table_fk at least gives (maybe) the user and (hopefully) the developer a place to start looking!
    – Vérace
    Jun 4, 2022 at 8:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.