Oracle to PostgreSQL, unique index and unique constraints

General Discussion
1

Greetings everyone,
We are porting some Oracle Database DDL to PostgreSQL and intending to use Liquibase 4.17.2 for the database schema change management (of course!). The problem I am having is related to Unique Indexes and Unique Constraints existing together; we have 133 pairs of these in this case. My hope is there is already a way to address this that I am not yet aware of, as my bet is someone else must have run into this but I don’t find anything from searches.

I connected Liquibase to the Oracle DB and executed ‘generate-changelog’ to produce changelogs in both YAML and *.postgresql.sql formats for comparison.

An example of this circumstance in the YAML changelog:

- changeSet:
    id: 1669850401274-399
    author: terryp (generated)
    changes:
    - createIndex:
        columns:
        - column:
            name: MVM_PLAN_ID
        - column:
            name: MVM_OPTION_ID
        indexName: MBM_PO_UK
        tableName: MVM_PLAN_OPTION
        unique: true
- changeSet:
    id: 1669850401274-400
    author: terryp (generated)
    changes:
    - addUniqueConstraint:
        columnNames: MVM_PLAN_ID, MVM_OPTION_ID
        constraintName: MBM_PO_UK
        forIndexName: MBM_PO_UK
        tableName: MVM_PLAN_OPTION

When this changelog is executed against PostgreSQL, it fails as ‘forIndexName’ is not supported for Postgres. That makes sense, as in the Liquibase docs for ‘forIndexName’, the ‘Supports’ column only lists ‘oracle’ (though perhaps it should also support postgresql, more on that below). Once I remove the ‘forIndexName’ entries, I get this error:

[2022-12-06 13:39:58] SEVERE [liquibase.integration] Migration failed for changeset changelog.yaml::1669850401274-400::terryp (generated):
     Reason: liquibase.exception.DatabaseException: ERROR: relation "mbm_po_uk" already exists [Failed SQL: (0) ALTER TABLE fd.MVM_PLAN_OPTION ADD CONSTRAINT MBM_PO_UK UNIQUE (MVM_PLAN_ID, MVM_OPTION_ID)]
liquibase.exception.CommandExecutionException: liquibase.exception.LiquibaseException: liquibase.exception.MigrationFailedException: Migration failed for changeset changelog.yaml::1669850401274-400::terryp (generated):
     Reason: liquibase.exception.DatabaseException: ERROR: relation "mbm_po_uk" already exists [Failed SQL: (0) ALTER TABLE fd.MVM_PLAN_OPTION ADD CONSTRAINT MBM_PO_UK UNIQUE (MVM_PLAN_ID, MVM_OPTION_ID)]

Interestingly though, when one looks at the *.postgresql.sql changelog for this same unique index and unique constraint, we see the following:

-- changeset terryp:1669850071015-399
CREATE UNIQUE INDEX MBM_PO_UK ON MVM_PLAN_OPTION(MVM_PLAN_ID, MVM_OPTION_ID);

-- changeset terryp:1669850071015-400
ALTER TABLE MVM_PLAN_OPTION ADD CONSTRAINT MBM_PO_UK UNIQUE (MVM_PLAN_ID, MVM_OPTION_ID) USING INDEX MBM_PO_UK;

Note that in this case the DDL produced by generate-changelog does not work in PostgreSQL:

fd=> ALTER TABLE MVM_PLAN_OPTION ADD CONSTRAINT MBM_PO_UK UNIQUE (MVM_PLAN_ID, MVM_OPTION_ID) USING INDEX MBM_PO_UK;
ERROR:  syntax error at or near "MBM_PO_UK"
LINE 1: ...MVM_PLAN_ID, MVM_OPTION_ID) USING INDEX MBM_PO_UK...

However, PostgreSQL does support adding a constraint with ‘USING INDEX ’ syntax, similar to how Oracle does but one cannot specify the columns. Thus the following modified SQL from the -400 changeset works fine in a PostgreSQL database that has the -399 changeset already which created the unique index MBM_BPBO_UK:

   fd=> ALTER TABLE MVM_PLAN_OPTION ADD CONSTRAINT MBM_PO_UK UNIQUE USING INDEX MBM_PO_UK;
   ALTER TABLE

So my questions are:

  1. Has anyone hit this before and have a clever way around it in the changelog?

I could programmatically parse the generated YAML changelog and remove Unique Index creations wherever Unique Constraints are found, but I’m hoping there is a better way. My changelogs are using native change types only, and I’d like to stick to that if possible.

  1. Should/could the ‘addUniqueConstraint’ change type be expanded/enhanced to support PostgreSQL as well? I would think so given PostgreSQL supports adding a unique constraint while using an existing unique index, very close in syntax to what Oracle supports. Having support like that would make this situation a complete non-issue, and given plenty of Oracle systems I have worked on use Unique Indexes and Unique Constraints together, others that are porting such systems to PostgreSQL would hit this same issue as I have.

Thanks in advance to all.

2

Going over this with a co-worker I just found I could exclude unique constraints from the generated changelogs using the --difftypes attribute. So I could specify a reduced set of objects such as:

--difftypes=tables,columns,indexes,foreignkeys,primarykeys,sequences

I cannot exclude indexes from the generation (that would omit necessary secondary indexes), but excluding the uniqueconstraints should be “safe enough” so I can press forward as I’ve verified in the oracle database that every unique constraint has a corresponding unique index.

Still curious if others have hit this when porting from oracle to postgresql. And maybe I’ll find that most folks don’t create unique indexes AND unique constraints explicitly as this system did! (I did not design it :wink: )