Subject: Re: Two sequences associated with one identity column

From: kurt thepw.com <[email-masked]>
Date: Wed, 29 Oct 2025 13:16:51 +0000

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 <[email-masked]>
Sent: Wednesday, October 29, 2025 8:20 AM
To: PostgreSQL General <[email-masked]>
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
<[email-masked]> 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
>

---

From: Dominique Devienne <[email-masked]>
Date: Wed, 29 Oct 2025 14:29:46 +0100

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

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

---

From: "David G. Johnston" <[email-masked]>
Date: Wed, 29 Oct 2025 09:39:19 -0400

On Wednesday, October 29, 2025, Dominique Devienne <[email-masked]>
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.
>
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.