Horton
Menu

Thread

  1. Two sequences associated with one identity column

    Colin 't Hart <colinthart@gmail.com> — 2025-10-29T11:27:44Z

    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

  2. Re: Two sequences associated with one identity column

    hubert depesz lubaczewski <depesz@depesz.com> — 2025-10-29T12:02:56Z

    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

  3. Re: Two sequences associated with one identity column

    Colin 't Hart <colinthart@gmail.com> — 2025-10-29T12:05:30Z

    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
>

  4. Re: Two sequences associated with one identity column

    hubert depesz lubaczewski <depesz@depesz.com> — 2025-10-29T12:07:08Z

    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

  5. Re: Two sequences associated with one identity column

    Colin 't Hart <colinthart@gmail.com> — 2025-10-29T12:20:05Z

    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
>

  6. Re: Two sequences associated with one identity column

    kurt thepw.com <kurt@thepw.com> — 2025-10-29T13:16:51Z

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

  7. Re: Two sequences associated with one identity column

    Dominique Devienne <ddevienne@gmail.com> — 2025-10-29T13:29:46Z

    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

  8. Re: Two sequences associated with one identity column

    David G. Johnston <david.g.johnston@gmail.com> — 2025-10-29T13:39:19Z

    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.

  9. Re: Two sequences associated with one identity column

    Colin 't Hart <colinthart@gmail.com> — 2025-10-29T13:40:03Z

    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
>

  10. Re: Two sequences associated with one identity column

    kurt thepw.com <kurt@thepw.com> — 2025-10-29T14:47:24Z

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

  11. Re: Two sequences associated with one identity column

    Adrian Klaver <adrian.klaver@aklaver.com> — 2025-10-29T14:59:37Z

    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

  12. Re: Two sequences associated with one identity column

    Ron Johnson <ronljohnsonjr@gmail.com> — 2025-10-29T15:32:14Z

    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!

  13. Re: Two sequences associated with one identity column

    Peter Eisentraut <peter@eisentraut.org> — 2025-10-29T16:01:12Z

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

  14. Re: Two sequences associated with one identity column

    Adrian Klaver <adrian.klaver@aklaver.com> — 2025-10-29T16:28:33Z

    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

  15. Re: Two sequences associated with one identity column

    Colin 't Hart <colinthart@gmail.com> — 2025-10-30T08:55:12Z

             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

  16. Re: Two sequences associated with one identity column

    Adrian Klaver <adrian.klaver@aklaver.com> — 2025-10-30T14:42:26Z

    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

  17. Re: Two sequences associated with one identity column

    Rumpi Gravenstein <rgravens@gmail.com> — 2025-10-30T15:22:31Z

    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

  18. Re: Two sequences associated with one identity column

    Adrian Klaver <adrian.klaver@aklaver.com> — 2025-10-30T16:32:57Z

    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