Обсуждение: 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@blakeslee.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

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