Re: BUG #18562: pg_upgrade (14.12 -> 15.7/16.3) fails when schema has unlogged table with logged sequence

PG Bug reporting form <noreply(at)postgresql(dot)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