public inbox for [email protected]
help / color / mirror / Atom feedRe: Two sequences associated with one identity column
2+ messages / 2 participants
[nested] [flat]
* Re: Two sequences associated with one identity column
@ 2025-10-30 15:22 Rumpi Gravenstein <[email protected]>
2025-10-30 16:32 ` Re: Two sequences associated with one identity column Adrian Klaver <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Rumpi Gravenstein @ 2025-10-30 15:22 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; +Cc: Colin 't Hart <[email protected]>; PostgreSQL General <[email protected]>
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 protected]>
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 protected]
>
>
>
--
Rumpi Gravenstein
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Two sequences associated with one identity column
2025-10-30 15:22 Re: Two sequences associated with one identity column Rumpi Gravenstein <[email protected]>
@ 2025-10-30 16:32 ` Adrian Klaver <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Adrian Klaver @ 2025-10-30 16:32 UTC (permalink / raw)
To: Rumpi Gravenstein <[email protected]>; +Cc: Colin 't Hart <[email protected]>; PostgreSQL General <[email protected]>
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.g...
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 protected] <mailto:[email protected]>> 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 protected] <mailto:[email protected]>
>
>
>
>
> --
> Rumpi Gravenstein
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2025-10-30 16:32 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-10-30 15:22 Re: Two sequences associated with one identity column Rumpi Gravenstein <[email protected]>
2025-10-30 16:32 ` Adrian Klaver <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox