Two sequences associated with one identity column

On Wed, Oct 29, 2025 at 12:27:44PM +0100, Colin 't Hart wrote:

One of my clients has a database in which a single identity column
(called "id" in that table) has two sequences associated with it(!)
Both sequences display
Sequence for identity column: <schema>.<table>.id
when described with \d in psql.
Trying to drop either sequence results in

Try:

ALTER SEQUENCE some_seq_name OWNED BY none;
DROP SEQUENCE some_seq_name;

Best regards,

depesz

Thanks. But as I wrote above, trying to alter either of the two
sequences and specifying "owned by none" results in the error.

/Colin

On Wed, 29 Oct 2025 at 13:02, hubert depesz lubaczewski
<depesz@depesz.com> wrote:

On Wed, Oct 29, 2025 at 01:04:48PM +0100, Colin 't Hart wrote:

Thanks. But as I wrote above, trying to alter either of the two
sequences and specifying "owned by none" results in the error.

Sorry, missed that.

Can you please provide pg_dump output from this db, just schema, just
this one table, and both sequences?

Or, how did you arrive at this situation?

Did you try to alter table … alter column … drop identity;

Best regards,

depesz

Again as I wrote above, drop identity complains about more than one sequence.

I have no idea how this customer arrived at this situation or if it
affects other environments (this is actually a dev database that we're
trying to upgrade as the first step in an upgrade project).

I suspect the dump will just show two sequences that need to be
imported and it will fail on the second one. I'll make a dump.

/Colin

On Wed, 29 Oct 2025 at 13:07, hubert depesz lubaczewski
<depesz@depesz.com> wrote:

If this is a development database, perhaps you can do a schema-only pg_dump of it in plain text format, manually edit out the offending second sequence from the resulting SQL file, and restore it into a new database.

Yours,

Kurt Reimer
________________________________
From: Colin 't Hart <colinthart@gmail.com>
Sent: Wednesday, October 29, 2025 8:20 AM
To: PostgreSQL General <pgsql-general@lists.postgresql.org>
Subject: Re: Two sequences associated with one identity column

Again as I wrote above, drop identity complains about more than one sequence.

I have no idea how this customer arrived at this situation or if it
affects other environments (this is actually a dev database that we're
trying to upgrade as the first step in an upgrade project).

I suspect the dump will just show two sequences that need to be
imported and it will fail on the second one. I'll make a dump.

/Colin

On Wed, 29 Oct 2025 at 13:07, hubert depesz lubaczewski
<depesz@depesz.com> wrote:

On Wed, Oct 29, 2025 at 2:17 PM kurt thepw.com <kurt@thepw.com> wrote:

If this is a development database, perhaps you can do a schema-only pg_dump of it in plain text format, manually edit out the offending second sequence from the resulting SQL file, and restore it into a new database.

I'm surprised the conversation is not more about preventing this from
ever happening in the first place. Since one cannot get out of it,
apparently. --DD

On Wednesday, October 29, 2025, Dominique Devienne <ddevienne@gmail.com>
wrote:

On Wed, Oct 29, 2025 at 2:17 PM kurt thepw.com <kurt@thepw.com> wrote:

If this is a development database, perhaps you can do a schema-only

pg_dump of it in plain text format, manually edit out the offending second
sequence from the resulting SQL file, and restore it into a new database.

I'm surprised the conversation is not more about preventing this from
ever happening in the first place. Since one cannot get out of it,
apparently. --DD

If a reproducer is not offered discussions do tend to focus on fixing the
symptoms since that is what is available to consider. Not too surprised no
one volunteers to reverse-engineer a reproducer from scratch, given only
the end state.

David J.

As expected the dump contains:

CREATE TABLE <schema>.<tablename> (
<other columns>,
id bigint NOT NULL
);

<snip>

--
-- Name: <sequence1>; Type: SEQUENCE; Schema: <schema>; Owner: <owner>
--

ALTER TABLE <schema>.<tablename> ALTER COLUMN id ADD GENERATED ALWAYS
AS IDENTITY (
SEQUENCE NAME <schema>.<sequence1>
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1
);

--
-- Name: <sequence2>; Type: SEQUENCE; Schema: <schema>; Owner: <owner>
--

ALTER TABLE <schema>.<tablename> ALTER COLUMN id ADD GENERATED ALWAYS
AS IDENTITY (
SEQUENCE NAME <schema>.<sequence2>
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1
);

Two things are interesting:
1. The id column is last, so quite possibly added later (instead of
the original PK which was dropped?)
2. The two sequences are just dumped -- which causes an error when importing.

A third thing that is interesting is that I can drop the table just
fine -- and both sequences get dropped along with it.

The table seems to be relatively small -- and has no foreign keys --
so I think the solution will be to recreate the table (create table as
select), drop the original table and finally rename the new table the
same as the old one.

Also checking to see if the problem extends to the other environments.

/Colin

On Wed, 29 Oct 2025 at 13:07, hubert depesz lubaczewski
<depesz@depesz.com> wrote:

<
< CREATE TABLE <schema>.<tablename> (
< <other columns>,
< id bigint NOT NULL
< );
<

I've never seen a plaintext pg_dump output where the sequence associated with a column in a table was not mentioned in s "DEFAULT nextval(..." modifier in that column's line of the CREATE TABLE statement, ex:

<
< CREATE TABLE <schema>.<tbl> (
< id integer DEFAULT nextval('<schema>.<seqname>'::regclass) NOT NULL,
< <next column>...,
< . . . . .
< );

With the sequence already created earlier in the dump file. But then, I've never before seen a table column with two associated sequences. Maybe that is what makes pg_dump generate the

"ALTER TABLE <schema>.<tablename> ALTER COLUMN id ADD GENERATED..."

Statements.

<
< 1. The id column is last, so quite possibly added later (instead of
< the original PK which was dropped?)
<
That seems likely, and probably the 2nd sequence was added in by someone who didn't know (or forgot) about the first one.

<
< 2. The two sequences are just dumped -- which causes an error when importing.
<
I'd be curious to know if simple editing out the 2nd "ALTER TABLE...ADD GENERATED.." statement would allow a restore of the database to succeed. pg_restore dorsn't work with plaintext files, you have to cat them into psql or use the '-f' switch.

<
< A third thing that is interesting is that I can drop the table just
< fine -- and both sequences get dropped along with it.
< The table seems to be relatively small -- and has no foreign keys --
< so I think the solution will be to recreate the table (create table as
< select), drop the original table and finally rename the new table the
< same as the old one.
<

That's probably the quickest way to fix it, though if you are "create table as select.."-ing from the old table you might get the two sequences again. I've never used "create table as select" .

An alternative might be to pg_dump just that table, edit the .sql file, drop the table, and then restore.

Kurt

________________________________
From: Colin 't Hart <colinthart@gmail.com>
Sent: Wednesday, October 29, 2025 9:40 AM
To: PostgreSQL General <pgsql-general@lists.postgresql.org>
Subject: Re: Two sequences associated with one identity column

As expected the dump contains:

CREATE TABLE <schema>.<tablename> (
<other columns>,
id bigint NOT NULL
);

<snip>

--
-- Name: <sequence1>; Type: SEQUENCE; Schema: <schema>; Owner: <owner>
--

ALTER TABLE <schema>.<tablename> ALTER COLUMN id ADD GENERATED ALWAYS
AS IDENTITY (
SEQUENCE NAME <schema>.<sequence1>
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1
);

--
-- Name: <sequence2>; Type: SEQUENCE; Schema: <schema>; Owner: <owner>
--

ALTER TABLE <schema>.<tablename> ALTER COLUMN id ADD GENERATED ALWAYS
AS IDENTITY (
SEQUENCE NAME <schema>.<sequence2>
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1
);

Two things are interesting:
1. The id column is last, so quite possibly added later (instead of
the original PK which was dropped?)
2. The two sequences are just dumped -- which causes an error when importing.

A third thing that is interesting is that I can drop the table just
fine -- and both sequences get dropped along with it.

The table seems to be relatively small -- and has no foreign keys --
so I think the solution will be to recreate the table (create table as
select), drop the original table and finally rename the new table the
same as the old one.

Also checking to see if the problem extends to the other environments.

/Colin

On Wed, 29 Oct 2025 at 13:07, hubert depesz lubaczewski
<depesz@depesz.com> wrote:

On 10/29/25 07:47, kurt thepw.com wrote:

<
< CREATE TABLE <schema>.<tablename> (
< <other columns>,
<   id bigint NOT NULL
< );
<

I've never seen a plaintext pg_dump  output where the sequence
associated with a column in a table was not mentioned in s "DEFAULT
nextval(..." modifier in that column's line of the CREATE TABLE
statement, ex:

<
< CREATE TABLE <schema>.<tbl> (
<    id integer DEFAULT nextval('<schema>.<seqname>'::regclass) NOT NULL,
<    <next column>...,
<   .  .  .  .  .
<  );

That is for case where someone manually creates DEFAULT:

create table manual_seq_test(id integer default nextval('test_seq'),
fld_1 varchar, fld_2 boolean);

pg_dump -d test -U postgres -p 5432 -t manual_seq_test

CREATE TABLE public.manual_seq_test (
id integer DEFAULT nextval('public.test_seq'::regclass),
fld_1 character varying,
fld_2 boolean
);

Otherwise for system generated sequences you get:

create table seq_test(id serial, fld_1 varchar, fld_2 boolean);

CREATE TABLE public.seq_test (
id integer NOT NULL,
fld_1 character varying,
fld_2 boolean
);

CREATE SEQUENCE public.seq_test_id_seq
AS integer
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;

ALTER SEQUENCE public.seq_test_id_seq OWNER TO postgres;

--
-- Name: seq_test_id_seq; Type: SEQUENCE OWNED BY; Schema: public;
Owner: postgres
--

ALTER SEQUENCE public.seq_test_id_seq OWNED BY public.seq_test.id;

--
-- Name: seq_test id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.seq_test ALTER COLUMN id SET DEFAULT
nextval('public.seq_test_id_seq'::regclass);

OR

create table id_test(id integer generated always as identity, fld_1
varchar, fld_2 boolean);

CREATE TABLE public.id_test (
id integer NOT NULL,
fld_1 character varying,
fld_2 boolean
);

ALTER TABLE public.id_test OWNER TO postgres;

--
-- Name: id_test_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

ALTER TABLE public.id_test ALTER COLUMN id ADD GENERATED ALWAYS AS
IDENTITY (
SEQUENCE NAME public.id_test_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1
);

With the sequence already created earlier in the dump file. But then,
I've never before seen a table column with two associated sequences.
Maybe that is what makes pg_dump generate the

"ALTER TABLE <schema>.<tablename> ALTER COLUMN id ADD GENERATED..."

Statements.

--
Adrian Klaver
adrian.klaver@aklaver.com

I'd have expected the CREATE SEQUENCE and ALTER TABLE to be separate that
can go in the post-data section, and be there even in schema-only dumps
because it was easier for whoever added sections to pg_dump. After all,
what really matters is the destination, not the journey.

On Wed, Oct 29, 2025 at 10:59 AM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 10/29/25 07:47, kurt thepw.com wrote:

<
< CREATE TABLE <schema>.<tablename> (
< <other columns>,
< id bigint NOT NULL
< );
<

I've never seen a plaintext pg_dump output where the sequence
associated with a column in a table was not mentioned in s "DEFAULT
nextval(..." modifier in that column's line of the CREATE TABLE
statement, ex:

<
< CREATE TABLE <schema>.<tbl> (
< id integer DEFAULT nextval('<schema>.<seqname>'::regclass) NOT NULL,
< <next column>...,
< . . . . .
< );

That is for case where someone manually creates DEFAULT:

create table manual_seq_test(id integer default nextval('test_seq'),
fld_1 varchar, fld_2 boolean);

pg_dump -d test -U postgres -p 5432 -t manual_seq_test

CREATE TABLE public.manual_seq_test (
id integer DEFAULT nextval('public.test_seq'::regclass),
fld_1 character varying,
fld_2 boolean
);

Otherwise for system generated sequences you get:

create table seq_test(id serial, fld_1 varchar, fld_2 boolean);

CREATE TABLE public.seq_test (
id integer NOT NULL,
fld_1 character varying,
fld_2 boolean
);

CREATE SEQUENCE public.seq_test_id_seq
AS integer
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;

ALTER SEQUENCE public.seq_test_id_seq OWNER TO postgres;

--
-- Name: seq_test_id_seq; Type: SEQUENCE OWNED BY; Schema: public;
Owner: postgres
--

ALTER SEQUENCE public.seq_test_id_seq OWNED BY public.seq_test.id;

--
-- Name: seq_test id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.seq_test ALTER COLUMN id SET DEFAULT
nextval('public.seq_test_id_seq'::regclass);

OR

create table id_test(id integer generated always as identity, fld_1
varchar, fld_2 boolean);

CREATE TABLE public.id_test (
id integer NOT NULL,
fld_1 character varying,
fld_2 boolean
);

ALTER TABLE public.id_test OWNER TO postgres;

--
-- Name: id_test_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

ALTER TABLE public.id_test ALTER COLUMN id ADD GENERATED ALWAYS AS
IDENTITY (
SEQUENCE NAME public.id_test_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1
);

With the sequence already created earlier in the dump file. But then,
I've never before seen a table column with two associated sequences.
Maybe that is what makes pg_dump generate the

"ALTER TABLE <schema>.<tablename> ALTER COLUMN id ADD GENERATED..."

Statements.

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

On 29.10.25 12:27, Colin 't Hart wrote:

One of my clients has a database in which a single identity column
(called "id" in that table) has two sequences associated with it(!)

Both sequences display

Sequence for identity column: <schema>.<table>.id

when described with \d in psql.

Inserting fails with "ERROR: more than one owned sequence found", as
does trying to alter the table to drop the identity on that column.

Trying to drop either sequence results in

ERROR: cannot drop sequence <name> because column id of table <name>
requires it
HINT: You can drop column id of table <name> instead.

while trying to alter either sequence "owned by none" results in

ERROR: cannot change ownership of identity sequence
DETAIL: Sequence "<name>" is linked to table "<name>".

How do we fix this? I presume we need to update the catalog directly
to dissociate one of the sequences and after that drop the orphaned
sequence.

I don't know how one would get into this situation, but I can fake it
like this:

create table t1 (a int, b int generated always as identity);

select * from pg_depend where refclassid = 'pg_class'::regclass and
refobjid = 't1'::regclass;
classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptype
---------+-------+----------+------------+----------+-------------+---------
1247 | 16388 | 0 | 1259 | 16386 | 0 | i
1259 | 16384 | 0 | 1259 | 16386 | 2 | i
(2 rows)

The second entry is the dependency between the sequence and the table.
1259 is pg_class, the numbers 16384 and 16386 are the OIDs of the
sequence and the table, and 2 is the column number.

Now create another sequence and manually insert a dependency record:

create sequence sx;

insert into pg_depend values (1259, 'sx'::regclass, 0, 1259, 16386, 2, 'i');

Now you have the same breakage:

insert into t1 (a) values (1);
ERROR: more than one owned sequence found

To fix this, remove the extra dependency record:

delete from pg_depend where (classid, objid, objsubid) =
('pg_class'::regclass, 'sx'::regclass, 0) and (refclassid, refobjid,
refobjsubid) = ('pg_class'::regclass, 't1'::regclass, 2) and deptype = 'i';

On 10/29/25 06:40, Colin 't Hart wrote:

As expected the dump contains:

CREATE TABLE <schema>.<tablename> (
<other columns>,
id bigint NOT NULL
);

<snip>

--
-- Name: <sequence1>; Type: SEQUENCE; Schema: <schema>; Owner: <owner>
--

ALTER TABLE <schema>.<tablename> ALTER COLUMN id ADD GENERATED ALWAYS
AS IDENTITY (
SEQUENCE NAME <schema>.<sequence1>
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1
);

--
-- Name: <sequence2>; Type: SEQUENCE; Schema: <schema>; Owner: <owner>
--

ALTER TABLE <schema>.<tablename> ALTER COLUMN id ADD GENERATED ALWAYS
AS IDENTITY (
SEQUENCE NAME <schema>.<sequence2>
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1
);

Just a wild guess. Assuming <schema> is the same, what is the output of:

SELECT
relname,
relnamespace,
relpersistence
FROM
pg_class
WHERE
relname IN ('<sequence1>', '<sequence2> ')
AND relnamespace = '<schema>'::regnamespace;

/Colin

--
Adrian Klaver
adrian.klaver@aklaver.com

relname | relnamespace | relpersistence
--------------------------+--------------+----------------
<sequence1> | 524799410 | p
<sequence2> | 524799410 | p
(2 rows)

On 10/30/25 01:55, Colin 't Hart wrote:

relname | relnamespace | relpersistence
--------------------------+--------------+----------------o
<sequence1> | 524799410 | p
<sequence2> | 524799410 | p
(2 rows)

Well so much for that guess. I was exploring the idea that the sequence
may have been unlogged at some point and you had both a logged(p) and
unlogged(u) instance of each.

--
Adrian Klaver
adrian.klaver@aklaver.com

I've seen two indexes created on the same table/column when you create a
primary key as part of table create ddl and then also run a separate create
index statement for the same table/column.

On Thu, Oct 30, 2025 at 10:42 AM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 10/30/25 01:55, Colin 't Hart wrote:

relname | relnamespace | relpersistence
--------------------------+--------------+----------------o
<sequence1> | 524799410 | p
<sequence2> | 524799410 | p
(2 rows)

Well so much for that guess. I was exploring the idea that the sequence
may have been unlogged at some point and you had both a logged(p) and
unlogged(u) instance of each.

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Rumpi Gravenstein

On 10/30/25 08:22, Rumpi Gravenstein wrote:

I've seen two indexes created on the same table/column when you create a
primary key as part of table create ddl and then also run a separate
create index statement for the same table/column.

Yes it is possible to create two indexes on a given table column, the
issue here though is, from this post:

/messages/by-id/CAMon-aQ0Zs-Otkp1=zk3WYt7Am-_oQHKEmD_rZ1CNGBAgM_jzw@mail.gmail.com

The two indexes are coming from:

--
-- Name: <sequence1>; Type: SEQUENCE; Schema: <schema>; Owner: <owner>
--

ALTER TABLE <schema>.<tablename> ALTER COLUMN id ADD GENERATED ALWAYS
AS IDENTITY (
SEQUENCE NAME <schema>.<sequence1>
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1
);

--
-- Name: <sequence2>; Type: SEQUENCE; Schema: <schema>; Owner: <owner>
--

ALTER TABLE <schema>.<tablename> ALTER COLUMN id ADD GENERATED ALWAYS
AS IDENTITY (
SEQUENCE NAME <schema>.<sequence2>
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1
);

That is two GENERATED ALWAYS AS IDENTITY sequences being created for the
PK. That should not happen.

On Thu, Oct 30, 2025 at 10:42 AM Adrian Klaver
<adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:

On 10/30/25 01:55, Colin 't Hart wrote:

           relname          | relnamespace | relpersistence
--------------------------+--------------+----------------o
   <sequence1>  |    524799410 | p
   <sequence2>  |    524799410 | p
(2 rows)

Well so much for that guess. I was exploring the idea that the sequence
may have been unlogged at some point and you had both a logged(p) and
unlogged(u) instance of each.

--
Adrian Klaver
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

--
Rumpi Gravenstein

--
Adrian Klaver
adrian.klaver@aklaver.com