Обсуждение: Two sequences associated with one identity column

Hi,

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.


This is in a Postgres 12.22 database that we're trying to upgrade to Postgres 17


Thanks,

Colin

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 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
>

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:
>
> 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
>

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
editout 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 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

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 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
>

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

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 Wed, 29 Oct 2025 at 17:28, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>
> 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

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

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:

https://www.postgresql.org/message-id/CAMon-aQ0Zs-Otkp1%3Dzk3WYt7Am-_oQHKEmD_rZ1CNGBAgM_jzw%40mail.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