Subject: Re: Two sequences associated with one identity column

From: Rumpi Gravenstein <[email-masked]>
Date: Thu, 30 Oct 2025 11:22:31 -0400

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 <[email-masked]>
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
> [email-masked]
>
>
>

-- 
Rumpi Gravenstein

---

From: Adrian Klaver <[email-masked]>
Date: Thu, 30 Oct 2025 09:32:57 -0700

On 10/30/25 08:22, Rumpi Gravenstein wrote:
> 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 
> <[email-masked] <mailto:[email-masked]>> wrote:
> 
>     [email-masked] <mailto:[email-masked]>
> 
> 
> 
> 
> -- 
> Rumpi Gravenstein