From: Tom Lane <tgl@sss.pgh.pa.us>
To: postgresql@blakeslee.uk
cc: pgsql-bugs@lists.postgresql.org,
        Peter Eisentraut <peter.eisentraut@enterprisedb.com>
Subject: Re: BUG #18562: pg_upgrade (14.12 -> 15.7/16.3) fails when schema has
 unlogged table with logged sequence
In-reply-to: <18562-46a7ee9a7b1ee153@postgresql.org>
References: <18562-46a7ee9a7b1ee153@postgresql.org>
Comments: In-reply-to PG Bug reporting form <noreply@postgresql.org>
	message dated "Thu, 01 Aug 2024 11:35:46 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <3736989.1722522184.1@sss.pgh.pa.us>
Date: Thu, 01 Aug 2024 10:23:04 -0400
Message-ID: <3736990.1722522184@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/3736990.1722522184%40sss.pgh.pa.us>
Precedence: bulk

PG Bug reporting form <noreply@postgresql.org> writes:
> I am seeing a recurring failure when attempting to run pg_upgrade on a 14.12
> database when targeting 15.7 or 16.3.
> I've found this error to occur whenever the schema contains an unlogged
> table with a logged sequence.

Yeah, the way that pg_dump tries to set this situation up is to create
the sequence by using ALTER COLUMN ADD GENERATED, and then change
the sequence's logged-ness.  That cannot work in binary-upgrade mode.

Before we think about alternative ways to dump it though, I wonder
why we permit such a situation at all.  It doesn't seem like a
well-considered bit of database design for an identity sequence's
logged-ness to differ from the owning table's.

The combination of logged table and unlogged sequence is surely a
foot-gun, even if you think there's some value in the other way.
But we allow both.

			regards, tom lane