public inbox for [email protected]
help / color / mirror / Atom feedRe: Two sequences associated with one identity column
6+ messages / 4 participants
[nested] [flat]
* Re: Two sequences associated with one identity column
@ 2025-10-29 12:02 hubert depesz lubaczewski <[email protected]>
2025-10-29 12:05 ` Re: Two sequences associated with one identity column Colin 't Hart <[email protected]>
2025-10-29 12:07 ` Re: Two sequences associated with one identity column hubert depesz lubaczewski <[email protected]>
0 siblings, 2 replies; 6+ messages in thread
From: hubert depesz lubaczewski @ 2025-10-29 12:02 UTC (permalink / raw)
To: Colin 't Hart <[email protected]>; +Cc: [email protected]
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Two sequences associated with one identity column
2025-10-29 12:02 Re: Two sequences associated with one identity column hubert depesz lubaczewski <[email protected]>
@ 2025-10-29 12:05 ` Colin 't Hart <[email protected]>
1 sibling, 0 replies; 6+ messages in thread
From: Colin 't Hart @ 2025-10-29 12:05 UTC (permalink / raw)
To: [email protected]
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
<[email protected]> 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
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Two sequences associated with one identity column
2025-10-29 12:02 Re: Two sequences associated with one identity column hubert depesz lubaczewski <[email protected]>
@ 2025-10-29 12:07 ` hubert depesz lubaczewski <[email protected]>
2025-10-29 13:40 ` Re: Two sequences associated with one identity column Colin 't Hart <[email protected]>
1 sibling, 1 reply; 6+ messages in thread
From: hubert depesz lubaczewski @ 2025-10-29 12:07 UTC (permalink / raw)
To: Colin 't Hart <[email protected]>; +Cc: PostgreSQL General <[email protected]>
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Two sequences associated with one identity column
2025-10-29 12:02 Re: Two sequences associated with one identity column hubert depesz lubaczewski <[email protected]>
2025-10-29 12:07 ` Re: Two sequences associated with one identity column hubert depesz lubaczewski <[email protected]>
@ 2025-10-29 13:40 ` Colin 't Hart <[email protected]>
2025-10-29 14:47 ` Re: Two sequences associated with one identity column kurt thepw.com <[email protected]>
2025-10-29 16:28 ` Re: Two sequences associated with one identity column Adrian Klaver <[email protected]>
0 siblings, 2 replies; 6+ messages in thread
From: Colin 't Hart @ 2025-10-29 13:40 UTC (permalink / raw)
To: PostgreSQL General <[email protected]>
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
<[email protected]> 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
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Two sequences associated with one identity column
2025-10-29 12:02 Re: Two sequences associated with one identity column hubert depesz lubaczewski <[email protected]>
2025-10-29 12:07 ` Re: Two sequences associated with one identity column hubert depesz lubaczewski <[email protected]>
2025-10-29 13:40 ` Re: Two sequences associated with one identity column Colin 't Hart <[email protected]>
@ 2025-10-29 14:47 ` kurt thepw.com <[email protected]>
1 sibling, 0 replies; 6+ messages in thread
From: kurt thepw.com @ 2025-10-29 14:47 UTC (permalink / raw)
To: Colin 't Hart <[email protected]>; PostgreSQL General <[email protected]>
<
< 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 <[email protected]>
Sent: Wednesday, October 29, 2025 9:40 AM
To: PostgreSQL General <[email protected]>
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
<[email protected]> 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
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Two sequences associated with one identity column
2025-10-29 12:02 Re: Two sequences associated with one identity column hubert depesz lubaczewski <[email protected]>
2025-10-29 12:07 ` Re: Two sequences associated with one identity column hubert depesz lubaczewski <[email protected]>
2025-10-29 13:40 ` Re: Two sequences associated with one identity column Colin 't Hart <[email protected]>
@ 2025-10-29 16:28 ` Adrian Klaver <[email protected]>
1 sibling, 0 replies; 6+ messages in thread
From: Adrian Klaver @ 2025-10-29 16:28 UTC (permalink / raw)
To: Colin 't Hart <[email protected]>; PostgreSQL General <[email protected]>
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
[email protected]
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2025-10-29 16:28 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-10-29 12:02 Re: Two sequences associated with one identity column hubert depesz lubaczewski <[email protected]>
2025-10-29 12:05 ` Colin 't Hart <[email protected]>
2025-10-29 12:07 ` hubert depesz lubaczewski <[email protected]>
2025-10-29 13:40 ` Colin 't Hart <[email protected]>
2025-10-29 14:47 ` kurt thepw.com <[email protected]>
2025-10-29 16:28 ` 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