Skip to content

Fix bug in postgres migration from v13 to v14

There is a bug in the postgres 13.0to14.0.sql migration script.

Instead of:

UPDATE CTA_CATALOGUE SET STATUS='PRODUCTION';
UPDATE CTA_CATALOGUE SET SCHEMA_VERSION_MAJOR=14;
UPDATE CTA_CATALOGUE SET SCHEMA_VERSION_MINOR=0;
UPDATE CTA_CATALOGUE SET NEXT_SCHEMA_VERSION_MAJOR=NULL;
UPDATE CTA_CATALOGUE SET NEXT_SCHEMA_VERSION_MINOR=NULL;

It should be:

UPDATE CTA_CATALOGUE SET SCHEMA_VERSION_MAJOR=14;
UPDATE CTA_CATALOGUE SET SCHEMA_VERSION_MINOR=0;
UPDATE CTA_CATALOGUE SET NEXT_SCHEMA_VERSION_MAJOR=NULL;
UPDATE CTA_CATALOGUE SET NEXT_SCHEMA_VERSION_MINOR=NULL;
UPDATE CTA_CATALOGUE SET STATUS='PRODUCTION';

Otherwise, this will cause the constraint catalogue_status_content_ck to fail:

Running Changeset: /CTA/catalogue/cta-catalogue-schema/migrations/liquibase/postgres/13.0to14.0.sql::4::lwardena
[2023-08-03 09:24:57] FINE [liquibase.changelog] Running Changeset: /CTA/catalogue/cta-catalogue-schema/migrations/liquibase/postgres/13.0to14.0.sql::4::lwardena
[2023-08-03 09:24:57] FINE [liquibase.executor] Changeset /CTA/catalogue/cta-catalogue-schema/migrations/liquibase/postgres/13.0to14.0.sql::4::lwardena
[2023-08-03 09:24:57] FINE [liquibase.executor] Executing with the 'jdbc' executor
[2023-08-03 09:24:57] FINE [liquibase.changelog] Reading ChangeSet: /CTA/catalogue/cta-catalogue-schema/migrations/liquibase/postgres/13.0to14.0.sql::4::lwardena
[2023-08-03 09:24:57] FINE [liquibase.database] Executing Statement: UPDATE CTA_CATALOGUE SET STATUS='PRODUCTION'
[2023-08-03 09:24:57] FINE [liquibase.executor] Executing with the 'jdbc' executor
[2023-08-03 09:24:57] FINE [liquibase.executor] UPDATE CTA_CATALOGUE SET STATUS='PRODUCTION'
[2023-08-03 09:24:57] FINE [liquibase.executor] Release Database Lock
[2023-08-03 09:24:57] FINE [org.postgresql.jdbc.PgConnection rollback] Rollback requested but no transaction in progress
[2023-08-03 09:24:57] FINE [liquibase.executor] Executing with the 'jdbc' executor
[2023-08-03 09:24:57] INFO [liquibase.lockservice] Successfully released change log lock
[2023-08-03 09:24:57] FINE [org.postgresql.jdbc.PgConnection rollback] Rollback requested but no transaction in progress
[2023-08-03 09:24:57] FINE [org.postgresql.jdbc.PgConnection rollback] Rollback requested but no transaction in progress
[2023-08-03 09:24:57] FINE [org.postgresql.jdbc.PgConnection setAutoCommit]   setAutoCommit = true
[2023-08-03 09:24:57] SEVERE [liquibase.integration] ERROR: new row for relation "cta_catalogue" violates check constraint "catalogue_status_content_ck"
  Detail: Failing row contains (13, 0, 14, 0, PRODUCTION, 0). [Failed SQL: (0) UPDATE CTA_CATALOGUE SET STATUS='PRODUCTION']
liquibase.exception.CommandExecutionException: liquibase.exception.LiquibaseException: Unexpected error running Liquibase: Migration failed for change set /CTA/catalogue/cta-catalogue-schema/migrations/liquibase/postgres/13.0to14.0.sql::4::lwardena:
     Reason: liquibase.exception.DatabaseException: ERROR: new row for relation "cta_catalogue" violates check constraint "catalogue_status_content_ck"
  Detail: Failing row contains (13, 0, 14, 0, PRODUCTION, 0). [Failed SQL: (0) UPDATE CTA_CATALOGUE SET STATUS='PRODUCTION']

Not that this error does not happen in Oracle DBs, because its constraint catalogue_status_content_ck contains the attribute INITIALLY DEFERRED. This allows STATUS='PRODUCTION' to be set in a different order.

After this fix, we will need to release it on top of 4.10.0-1, as 4.10.0-2.

Edited by Joao Afonso