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

The following bug has been logged on the website:

Bug reference: 18562
Logged by: Stephan Blakeslee
Email address: postgresql(at)blakeslee(dot)uk
PostgreSQL version: 16.3
Operating system: Flatcar Linux 3815.2.5 / macOS 14.5
Description:

Hello all,
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.

For example, given the creation of an unlogged table with a sequence on an
otherwise vanilla database cluster running 14.12:

CREATE UNLOGGED TABLE foo (
n INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY
);

Where:
SELECT r.relname AS table_name,
r.relpersistence AS table_persistence,
s.relname AS sequence_name,
s.relpersistence AS sequence_persistence
FROM pg_depend d
JOIN pg_class r ON d.refobjid = r.oid
JOIN pg_class s ON d.objid = s.oid
WHERE r.relname = 'foo';

Returns:
table_name | table_persistence | sequence_name | sequence_persistence
------------+-------------------+---------------+----------------------
foo | u | foo_n_seq | p

Which shows the sequence is logged while the table is unlogged, as I believe
is what you would expect for any PostgreSQL instance prior to 15.0 (when
unlogged sequences were introduced)

Then, when running pg_upgrade, the process fails during the "Restoring
database schemas in the new cluster" phase:

2024-07-31 10:04:41.106 UTC [1199] ERROR: 22023: unexpected request for new
relfilenode in binary upgrade mode
2024-07-31 10:04:41.106 UTC [1199] LOCATION: RelationSetNewRelfilenode,
relcache.c:3760
2024-07-31 10:04:41.106 UTC [1199] STATEMENT:
-- For binary upgrade, must preserve pg_class oids and relfilenodes
SELECT
pg_catalog.binary_upgrade_set_next_heap_pg_class_oid('16388'::pg_catalog.oid);
SELECT
pg_catalog.binary_upgrade_set_next_heap_relfilenode('16388'::pg_catalog.oid);

ALTER TABLE "public"."foo" ALTER COLUMN "n" ADD GENERATED BY DEFAULT AS
IDENTITY (
SEQUENCE NAME "public"."foo_n_seq"
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1
);
ALTER SEQUENCE "public"."foo_n_seq" SET LOGGED;

*failure*
There were problems executing ""/usr/postgresql-15.7/bin/pg_restore" --host
/var/lib/postgresql/data --port 50432 --username postgres --clean --create
--exit-on-error --verbose --dbname template1
"/var/lib/postgresql/data/new/pg_upgrade_output.d/20240731T100439.477/dump/pg_upgrade_dump_14127.custom"
>>
"/var/lib/postgresql/data/new/pg_upgrade_output.d/20240731T100439.477/log/pg_upgrade_dump_14127.log"
2>&1"

Consult the last few lines of
"/var/lib/postgresql/data/new/pg_upgrade_output.d/20240731T100439.477/log/pg_upgrade_dump_14127.log"
for
the probable cause of the failure.
Failure, exiting

Where the last few lines of the referenced log file are:

$ cat
/var/lib/postgresql/data/new/pg_upgrade_output.d/20240731T092548.046/log/pg_upgrade_dump_14127.log
...
pg_restore: creating TABLE "public.foo"
pg_restore: creating SEQUENCE "public.foo_n_seq"
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 211; 1259 16388 SEQUENCE foo_n_seq postgres
pg_restore: error: could not execute query: ERROR: unexpected request for
new relfilenode in binary upgrade mode
Command was:
-- For binary upgrade, must preserve pg_class oids and relfilenodes
SELECT
pg_catalog.binary_upgrade_set_next_heap_pg_class_oid('16388'::pg_catalog.oid);
SELECT
pg_catalog.binary_upgrade_set_next_heap_relfilenode('16388'::pg_catalog.oid);

ALTER TABLE "public"."foo" ALTER COLUMN "n" ADD GENERATED BY DEFAULT AS
IDENTITY (
SEQUENCE NAME "public"."foo_n_seq"
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1
);
ALTER SEQUENCE "public"."foo_n_seq" SET LOGGED;

I've observed this both when upgrading 14.12 -> 15.6 and 14.12 -> 16.3.
However, attempts to upgrade from 14.12 -> 15.6/16.2 for the same schema
have been successful.

Below I have included the steps used to reproduce this issue as well as the
versions involved in both the environments where the issue initially
occurred (Linux) and where I reproduced it (macOS).

If there is any additional information / logs etc that would be useful, let
me know.

Thanks,
Stephan

# Reproduction Steps

# The binaries used are those bundled with Postgres.app on macOS downloaded
from here:
https://github.com/PostgresApp/PostgresApp/releases/download/v2.7.3/Postgres-2.7.3-12-13-14-15-16.dmg

# Prepare folder structure and binaries
> mkdir -p /tmp/postgresql/socket /tmp/postgresql/14.12/data
/tmp/postgresql/15.7/data \
&& cp -r /Applications/Postgres.app/Contents/Versions/14/*
/tmp/postgresql/14.12 \
&& cp -r /Applications/Postgres.app/Contents/Versions/15/*
/tmp/postgresql/15.7

# Init 14.12 database
> /tmp/postgresql/14.12/bin/initdb -D /tmp/postgresql/14.12/data -U
postgres

# Start 14.12 database
> /tmp/postgresql/14.12/bin/pg_ctl -D /tmp/postgresql/14.12/data start

# Create problematic schema
> /tmp/postgresql/14.12/bin/psql \
--no-psqlrc \
-U postgres \
-c 'CREATE UNLOGGED TABLE foo (n INTEGER NOT NULL GENERATED BY DEFAULT
AS IDENTITY);'

# Stop 14.12 database ready for upgrade
> /tmp/postgresql/14.12/bin/pg_ctl -D /tmp/postgresql/14.12/data stop

# Init 15.7 database
> /tmp/postgresql/15.7/bin/initdb -D /tmp/postgresql/15.7/data -U postgres

# Run pg_upgrade, which should fail
> /tmp/postgresql/15.7/bin/pg_upgrade \
--old-bindir="/tmp/postgresql/14.12/bin" \
--old-datadir="/tmp/postgresql/14.12/data" \
--new-bindir="/tmp/postgresql/15.7/bin" \
--new-datadir="/tmp/postgresql/15.7/data" \
--socketdir="/tmp/postgresql/socket" \
--username="postgres" \
--verbose

# Inspect pg_upgrade log file referenced by the output
...
pg_restore: creating TABLE "public.foo"
pg_restore: creating SEQUENCE "public.foo_n_seq"
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 209; 1259 16384 SEQUENCE foo_n_seq
stephan.blakeslee
pg_restore: error: could not execute query: ERROR: unexpected request for
new relfilenode in binary upgrade mode
Command was:
-- For binary upgrade, must preserve pg_class oids and relfilenodes
SELECT
pg_catalog.binary_upgrade_set_next_heap_pg_class_oid('16384'::pg_catalog.oid);
SELECT
pg_catalog.binary_upgrade_set_next_heap_relfilenode('16384'::pg_catalog.oid);

ALTER TABLE "public"."foo" ALTER COLUMN "n" ADD GENERATED BY DEFAULT AS
IDENTITY (
SEQUENCE NAME "public"."foo_n_seq"
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1
);
ALTER SEQUENCE "public"."foo_n_seq" SET LOGGED;

# Clean up
> rm -rf /tmp/postgresql

# Versions

postgres=# SELECT version() AS linux_14_version;
linux_14_version

---------------------------------------------------------------------------------------------------------------------
PostgreSQL 14.12 on aarch64-unknown-linux-musl, compiled by gcc (Alpine
13.2.1_git20240309) 13.2.1 20240309, 64-bit

postgres=# SELECT version() AS linux_15_version;
linux_15_version

---------------------------------------------------------------------------------------------------------------------
PostgreSQL 15.7 on aarch64-unknown-linux-musl, compiled by gcc (Alpine
13.2.1_git20240309) 13.2.1 20240309, 64-bit

postgres=# SELECT version() AS linux_16_version;
linux_16_version

---------------------------------------------------------------------------------------------------------------------
PostgreSQL 16.3 on aarch64-unknown-linux-musl, compiled by gcc (Alpine
13.2.1_git20240309) 13.2.1 20240309, 64-bit

postgres=# SELECT version() AS mac_14_version;
mac_14_version

-----------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 14.12 (Postgres.app) on aarch64-apple-darwin20.6.0, compiled by
Apple clang version 12.0.5 (clang-1205.0.22.9), 64-bit

postgres=# SELECT version() AS mac_15_version;
mac_15_version

------------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 15.7 (Postgres.app) on aarch64-apple-darwin21.6.0, compiled by
Apple clang version 14.0.0 (clang-1400.0.29.102), 64-bit