Feedback-ID: i76984098:Fastmail
Message-ID: <77e6127d-2d1f-440e-b9fe-e1a523ff1329@aklaver.com>
Date: Thu, 30 Oct 2025 09:32:57 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Two sequences associated with one identity column
To: Rumpi Gravenstein <rgravens@gmail.com>
Cc: Colin 't Hart <colinthart@gmail.com>,
 PostgreSQL General <pgsql-general@lists.postgresql.org>
References: <CAMon-aTbZC5k2pLpijk5x+h__LV08DzpoJtHBgZGkOKJNJ91pw@mail.gmail.com>
 <aQICcMvkp96kenL8@depesz.com>
 <CAMon-aTMgbwC9j-wY8iX5kZ4=KXzCXc-Wpwx3viid8hQ_bJkoQ@mail.gmail.com>
 <aQIDbBO0nJTiMd5C@depesz.com>
 <CAMon-aQ0Zs-Otkp1=zk3WYt7Am-_oQHKEmD_rZ1CNGBAgM_jzw@mail.gmail.com>
 <e1513246-9b04-4beb-ac8e-16a1c2f1ebd5@aklaver.com>
 <CAMon-aQNPQ86-swjmpiryy3s_yKaWsZbzysrbn7e1xPc3JM3fg@mail.gmail.com>
 <4c41582a-83ee-4500-abde-e44a0f3d3895@aklaver.com>
 <CAEpg1wBP7U6CiaJFz1LeH3A01jRgg6iZsyrmFk_jEHKfWgesdg@mail.gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CAEpg1wBP7U6CiaJFz1LeH3A01jRgg6iZsyrmFk_jEHKfWgesdg@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/77e6127d-2d1f-440e-b9fe-e1a523ff1329%40aklaver.com>
Precedence: bulk

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