Sequences not considered when refrenced by different tables

[sidec15]

Search first

• I searched and no similar issues were found

Description

Sequences not included in diff result.

Steps To Reproduce

Let's create two databases liq1 and liq2:

 create database liq1;
 create database liq2;

with one single table each: foo in liq1 and foos liq2. Query to create the tables:

Database liq1 table foo:

 CREATE TABLE public.foo (
    	id int4 GENERATED BY DEFAULT AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START 1 CACHE 1 NO CYCLE) NOT null,
    	CONSTRAINT pk_foo PRIMARY KEY (id)
    );

Database liq2 table foos:

CREATE TABLE public.foos (
    id int4 GENERATED BY DEFAULT AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START 1 CACHE 1 NO CYCLE) NOT null,
    CONSTRAINT pk_foos PRIMARY KEY (id)
);

If I run the liquid diff command with the following liquibase.properties file:

liquibase.command.url=jdbc:postgresql://localhost:5432/liq1
liquibase.command.username: postgres
liquibase.command.password: postgres
liquibase.command.referenceUrl=jdbc:postgresql://localhost:5432/liq2
liquibase.command.referenceUsername: postgres
liquibase.command.referencePassword: postgres

The result is the following:

Reference Database: postgres @ jdbc:postgresql://localhost:5432/liq2 (Default Schema: public)
Comparison Database: postgres @ jdbc:postgresql://localhost:5432/liq1 (Default Schema: public)
Compared Schemas: public
Product Name: EQUAL
Product Version: EQUAL
Missing Catalog(s): NONE
Unexpected Catalog(s): NONE
Changed Catalog(s): 
     liq2
          name changed from 'liq2' to 'liq1'
Missing Column(s): 
     public.foos.id
Unexpected Column(s): 
     public.foo.id
Changed Column(s): NONE
Missing Foreign Key(s): NONE
Unexpected Foreign Key(s): NONE
Changed Foreign Key(s): NONE
Missing Index(s): 
     pk_foos UNIQUE  ON public.foos(id)
Unexpected Index(s): 
     pk_foo UNIQUE  ON public.foo(id)
Changed Index(s): NONE
Missing Primary Key(s): 
     pk_foos on public.foos(id)
Unexpected Primary Key(s): 
     pk_foo on public.foo(id)
Changed Primary Key(s): NONE
Missing Schema(s): NONE
Unexpected Schema(s): NONE
Changed Schema(s): NONE
Missing Sequence(s): NONE
Unexpected Sequence(s): NONE
Changed Sequence(s): NONE
Missing Table(s): 
     foos
Unexpected Table(s): 
     foo
Changed Table(s): NONE
Missing Unique Constraint(s): NONE
Unexpected Unique Constraint(s): NONE
Changed Unique Constraint(s): NONE
Missing View(s): NONE
Unexpected View(s): NONE
Changed View(s): NONE

There's no entry for the sequences but looking at the db schema:
liq1:

liq2:

Expected/Desired Behavior

I would expect an output that include the following:

Unexpected Sequence(s):
     foos_id_seq
Changed Sequence(s):
	 foo_id_seq

Liquibase Version

4.27.0

Database Vendor & Version

PostgreSQL 16.2

Liquibase Integration

No response

Liquibase Extensions

No response

OS and/or Infrastructure Type/Provider

No response

Additional Context

If I run the following in liq1:

START TRANSACTION;
ALTER TABLE foo DROP CONSTRAINT pk_foo;
ALTER TABLE foo RENAME TO foos;
ALTER TABLE foos ADD CONSTRAINT pk_foos PRIMARY KEY (id);
COMMIT;

To rename the table foo in foos and also its primary key, the diff output doesn't still include sequences difference:

Reference Database: postgres @ jdbc:postgresql://localhost:5432/liq2 (Default Schema: public)
Comparison Database: postgres @ jdbc:postgresql://localhost:5432/liq1 (Default Schema: public)
Compared Schemas: public
Product Name: EQUAL
Product Version: EQUAL
Missing Catalog(s): NONE
Unexpected Catalog(s): NONE
Changed Catalog(s): 
     liq2
          name changed from 'liq2' to 'liq1'
Missing Column(s): NONE
Unexpected Column(s): NONE
Changed Column(s): NONE
Missing Foreign Key(s): NONE
Unexpected Foreign Key(s): NONE
Changed Foreign Key(s): NONE
Missing Index(s): NONE
Unexpected Index(s): NONE
Changed Index(s): NONE
Missing Primary Key(s): NONE
Unexpected Primary Key(s): NONE
Changed Primary Key(s): NONE
Missing Schema(s): NONE
Unexpected Schema(s): NONE
Changed Schema(s): NONE
Missing Sequence(s): NONE
Unexpected Sequence(s): NONE
Changed Sequence(s): NONE
Missing Table(s): NONE
Unexpected Table(s): NONE
Changed Table(s): NONE
Missing Unique Constraint(s): NONE
Unexpected Unique Constraint(s): NONE
Changed Unique Constraint(s): NONE
Missing View(s): NONE
Unexpected View(s): NONE
Changed View(s): NONE

log file of the first diff run:
liquibase1.log

log file of the second diff run:
liquibase2.log

Are you willing to submit a PR?

• I'm willing to submit a PR (Thank you!)

[tati-qalified]

Hi @sidec15, thank you for reporting this bug.

I have been able to replicate it using Liquibase versions 4.27.0 and 4.28.0. The same thing happens if the two tables are identical but the primary key constraint has a different name:

For the first database run

 CREATE TABLE public.foo (
    	id int4 GENERATED BY DEFAULT AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START 1 CACHE 1 NO CYCLE) NOT null,
    	CONSTRAINT pk_foo PRIMARY KEY (id)
    );

For the second one run

 CREATE TABLE public.foo (
    	id int4 GENERATED BY DEFAULT AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START 1 CACHE 1 NO CYCLE) NOT null,
    	CONSTRAINT pk_foos PRIMARY KEY (id)
    );

The sequence difference won't appear in the diff output:

Diff Results:
Reference Database: lbadminuser @ jdbc:postgresql://localhost:54322/one (Default Schema: public)
Comparison Database: lbadminuser @ jdbc:postgresql://localhost:54322/two (Default Schema: public)
Compared Schemas: public
Product Name: EQUAL
Product Version: EQUAL
Missing Catalog(s): NONE
Unexpected Catalog(s): NONE
Changed Catalog(s):
     one
          name changed from 'one' to 'two'
Missing Column(s): NONE
Unexpected Column(s): NONE
Changed Column(s): NONE
Missing Foreign Key(s): NONE
Unexpected Foreign Key(s): NONE
Changed Foreign Key(s): NONE
Missing Index(s): NONE
Unexpected Index(s): NONE
Changed Index(s): NONE
Missing Primary Key(s): NONE
Unexpected Primary Key(s): NONE
Changed Primary Key(s): NONE
Missing Schema(s): NONE
Unexpected Schema(s): NONE
Changed Schema(s): NONE
Missing Sequence(s): NONE
Unexpected Sequence(s): NONE
Changed Sequence(s): NONE
Missing Table(s): NONE
Unexpected Table(s): NONE
Changed Table(s): NONE
Missing Unique Constraint(s): NONE
Unexpected Unique Constraint(s): NONE
Changed Unique Constraint(s): NONE
Missing View(s): NONE
Unexpected View(s): NONE
Changed View(s): NONE
Liquibase command 'diff' was executed successfully.

We'll be leaving this issue open for the community to propose a fix for. Our development team will be available to provide guidance when needed.

Thank you,
Tatiana