Liquibase 3.10+/4.1+ missing UniqueConstraints in snapshot

[haster]

Environment

Liquibase Version:
4.1.0

Liquibase Integration & Version:

Liquibase Extension(s) & Version:
liquibase-hibernate5, version 4.1.0

Database Vendor & Version:
Postgres, 12 (running in a docker container based on the postgres:12 container)

Operating System Type & Version:
Linux, Ubuntu 20.04/CentOS 7+ (local/buildserver)

Description

When building a snapshot of a Postgres Database (seemingly any non-Oracle and non-MSSQL database) only the first four uniqueconstraints get loaded. All others are ignored because liquibase switches to a bulk "get-columns-for-uniqueconstraint" query but saves the results under the wrong cachekey, leading to no columns for the uniqueconstraints when the cached entry is then read, which in turn leads to the uniqueconstraint getting ignored.

This is especially noticeable if this snapshot gets compared/diffed against a snapshot that does read in all uniqueconstraints, since it will report a mismatch (provided the databases have more than 4 uniqyueconstraints).

In our specific case we use liquibase-hibernate5 to build a reference snapshot based on our JPA annotations, and compare this against a postgres database that has run all liquibase changelogs. If those database match, our liquibase changelogs is complete, if not, we need to add changesets.
However, due to this bug liquibase now incorrectly reports 33 out of 37 uniqueconstraints as missing in the postgres database.

This seems to be the caused by bebbb6e (#1199 ), where the logic to batch select the columns for uniqueconstraints gets expanded to all database (instead of just oracle and MSSQL) but the "CONSTRAINT_CONTAINER" field didn't get included in the select of the actual queries, leading to results being saved under the cachekey "null_uq_mytableconstraintname" instead of "myschema_uq_mytableconstraintname".

Upon loading the results, liquibase does look for the correct "myschema_uq_mytableconstraintname", gets a result of null despite and deduces there are no columns therefore the uniqueconstraint should not be added to the snapshot.

Steps To Reproduce

Create a DatabaseSnapshot based on a fully initialized (ie, all changelog run) postgres database, create a DatabaseSnapshot based on a hibernate/jpa annotations "database" (or a fully initilized oracle or mssql database, as long as it is guaranteed to include all uniqueconstraints). (Make sure their should be >4 uniqueconstraints present).

Run a diff on the two snapshot using the liquibase DiffGenerator.

Actual Behavior

The diff reports a difference in uniqueconstraints caused by the postgres database snapshot having fewer unique constraints than the other snapshot.

Expected/Desired Behavior

Both databasesnapshots should have the same uniqueconstraints.

Screenshots (if appropriate)

If applicable, add screenshots to help explain your problem.

Additional Context

Add any other context about the problem here.

[haster]

The actual code we use to build the snapshots and create the diff is something like:

		Liquibase liquibase = LiquibaseFactory.create(null, connection, classloader, defaultSchema);

		database = liquibase.getDatabase();
		JpaPrebuildDatabase hibernate = new JpaPrebuildDatabase(emf);
		hibernate.setConnection(new JdbcConnection(new HibernateConnection(
			"jpa:prebuild:/META-INF/persistence.xml?hibernate.dialect=" + dialect.getName(),
			liquibase.getResourceAccessor())));
		SchemaComparison[] schemaComparison = new SchemaComparison[] {
			new SchemaComparison(hibernate.getDefaultSchema(), database.getDefaultSchema())};
		CompareControl compareControl = new CompareControl(schemaComparison,
			"column,foreignkey,index,primarykey,table,view,sequence,uniqueconstraint");

		DatabaseSnapshot referenceSnapshot = SnapshotGeneratorFactory.getInstance()
			.createSnapshot(hibernate.getDefaultSchema(), hibernate,
				new SnapshotControl(hibernate));

		DatabaseSnapshot comparisonSnapshot = SnapshotGeneratorFactory.getInstance()
			.createSnapshot(database.getDefaultSchema(), database, new SnapshotControl(database));

		DiffResult diff = DiffGeneratorFactory.getInstance()
			.getGenerator(hibernate, database)
			.compare(referenceSnapshot, comparisonSnapshot, compareControl);

Where LiquibaseFactory just searches the classpath to find the correct classes for the type of database we provide it (in the connectstring) and JpaPrebuildDatabase is based upon the HibernateEjb3Database from liquibase-hibernate5 .

[sync-by-unito]

➤ Wesley Willard commented:

Tested on Postgres, MySQL 8.0, and DB2. All the unique constraints are coming back now.