I am trying to generatechangelog all my sequences but i am not possible to do it.
I have created a postgres database via SQL files and i am trying to export that DB to XML.
I have created 1 sequence in SQL via
“create sequence test if not exists”
and there are other 9 sequences which has been auto created by the creation of table by having Serial datatype for ID’s.
So when i am trying to export all those 10 sequences I m able to have only the one which has been created by the “create sequence” command and nothing else.
Is there any way to get all those 10 sequences to XML?
Hi @michey Thanks you for joining the community. Liquibase should be able to retrieve the sequences. I will test to see what is different in your environment. Do you have the scripts that were used to create your tables? Of course, abstracting any proprietary or customer data.
@michey I am curious if this also reproduces if you create the table and id column without autoIncrement=“true” property set inline but introduce a new, standalone changeset, after the createTable. This changeset is of type: addAutoIncrement | Liquibase Docs
Hi @jitender I did not get to see it because I didn’t get the scripts that we used to create the table with the 10 sequences to see what is special or different about that table.
Do you have the scripts used to create the table minus any proprietary info or a sample DDL that creates the table in order to pull out the info with a generateChangeLog to recreate the issue?
Further insight:
Can you provide the scripts that were used to create the sequences? This may give a clue to which ones are missing and if they are created in a proprietary PostgreSQL way that does not reference back to the original tables that refer to those sequences.
In the absence of sequence creation scripts, I got a pg_dump from the source database and loaded that locally. I noticed that the dump specified:
CREATE SEQUENCE {schema}.{seqname} START WITH 1 INCREMENT BY 1 NO MINVALUE MAXVALUE 281474976710655 CACHE 1;
This created a ‘bigint’ sequence. The only sequence that Liquibase recognised was one that specified “AS integer” in the dump and had a max value of 65535.
I created a two sequences, both with “maxvalue 1048576” one with “AS integer”. Liquibase recognised both of these and exported these two new ones alongside the one it had always noticed.
This, to me, pointed to a problem with reading the “MAXVALUE” but on further tests, I was able to create a SEQUENCE with such a MAXVALUE but if I specified explicitly “AS bigint” Liquibase recognised it.
I adjusted the MAXVALUE of one of the bigint sequences to 65535 and explicitly adjusted it to ‘int’ and back to ‘bigint’ but Liquibase still did not recognise it. This is unfortunate as it points away from a nice, easy workaround.
Update: The problem appears to relate to the connection with the Apache/AGE extension:
create role lbtest_u with login password 'demo';
create database lbtest with owner = lbtest_u;
grant all privileges on database lbtest to lbtest_u;
create schema seqs authorization lbtest_u;
CREATE SEQUENCE seqs."thing"
START WITH 1
INCREMENT BY 1
NO MINVALUE
MAXVALUE 65535
CACHE 1;
CREATE SEQUENCE seqs."anotherthing"
START WITH 1
INCREMENT BY 1
NO MINVALUE
MAXVALUE 65535
CACHE 1;
CREATE EXTENSION IF NOT EXISTS age WITH SCHEMA ag_catalog;
create table seqs.things (id ag_catalog.graphid);
ALTER SEQUENCE seqs."thing" OWNED BY seqs."things".id;
-- liquibase finds both sequences
ALTER TABLE ONLY seqs."things"
ALTER COLUMN id
SET DEFAULT ag_catalog._graphid((ag_catalog._label_id('thing_graph'::name, 'NAME'::name))::integer, nextval('seqs."thing"'::regclass));
-- liquibase fails to find "thing"
ALTER TABLE ONLY seqs."things"
ALTER COLUMN id
SET DEFAULT null;
-- liquibase finds both sequences
I believe the above steps reproduce the problem consistently.
With the help of some AI, SQL tracing, experimentation, and simplification of the tests case, I got:
CREATE SEQUENCE seqs.“seqOne”
START WITH 1
INCREMENT BY 1
NO MINVALUE
MAXVALUE 65535
CACHE 1;
CREATE SEQUENCE seqs.“seqTwo”
START WITH 1
INCREMENT BY 1
NO MINVALUE
MAXVALUE 65535
CACHE 1;
create table seqs.things (id integer);
ALTER SEQUENCE seqs.“seqOne” OWNED BY seqs.“things”.id;
ALTER TABLE ONLY seqs.“things”
ALTER COLUMN id
SET DEFAULT nextval(‘seqs.“seqOne”’);
Root Cause: Liquibase Sequence Detection Logic Issue with OWNED BY
The issue is in how Liquibase’s query handles sequences with both OWNED BY and DEFAULT relationships:
WHERE c.relkind = 'S'
AND ns.nspname = 'seqs'
AND (c.oid not in (select ds.objid FROM pg_depend ds where ds.refobjsubid > 0) OR
(d.deptype = 'a' AND EXISTS (...NOT (pattern matching)...)))
The Logic:
First condition: Exclude sequences that have dependencies with refobjsubid > 0 (this catches OWNED BY relationships)
Second condition: BUT include them back if they have deptype = 'a' (auto dependency) AND the pattern matching FAILS
The Problem:
When you set both OWNED BY and a DEFAULT expression:
The sequence gets excluded by the first condition (has OWNED BY dependency)
The pattern matching DOES find the sequence name in the default expression
Because the pattern matches, the NOT (pattern matching) evaluates to FALSE
So the sequence stays excluded
Test Results:
seqOne: Has OWNED BY + DEFAULT → excluded (pattern DOES match, so NOT(match) = FALSE)
seqTwo: Has DEFAULT only → included (no ownership dependency to exclude it)
After adding OWNED BY to seqTwo: Both excluded (both have ownership + successful pattern matches)
Root Issue:
Liquibase assumes that if a sequence name appears in a default expression AND the sequence is owned by a column, it shouldn’t be managed separately. However, this logic fails when sequences are explicitly created and then assigned ownership, rather than being auto-generated by SERIAL columns.
The pattern matching is working correctly - that’s precisely why the sequences are being filtered out.
—-
Note that the test case is likely not the best practice for using a sequence to populate an ID value, but it reproduces the problem at the heart of the Apache/Age work without requiring that extension.
