BUG #18618: pg_upgrade from 14 to 15+ fails for unlogged table with identity column

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: erwaman(at)gmail(dot)com
Subject: BUG #18618: pg_upgrade from 14 to 15+ fails for unlogged table with identity column
Date: 2024-09-15 22:17:33
Message-ID: 18618-d4eb26d669ed110a@postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 18618
Logged by: Anthony Hsu
Email address: erwaman(at)gmail(dot)com
PostgreSQL version: 15.7
Operating system: Linux
Description:

If I create an unlogged table with an identity column as follows

CREATE UNLOGGED TABLE test (a INT GENERATED BY DEFAULT AS IDENTITY);

in PG14 and then run pg_upgrade to PG15+, it fails due to

```
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"."test" ALTER COLUMN "a" ADD GENERATED BY DEFAULT AS
IDENTITY (
SEQUENCE NAME "public"."test_a_seq"
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1
);
ALTER SEQUENCE "public"."test_a_seq" SET LOGGED;
```

If I use pg_upgrade from 15.6, it succeeds; if I use pg_upgrade from 15.7 or
15.8, it fails with the above. Upon further testing, I found that if I
revert
https://github.com/postgres/postgres/commit/d17a3a4c6a34f61a3d4d9faa7a70c14d8d0c0ffb,
then pg_upgrade succeeds. This bug also does not happen if I try upgrading
from 15 -> 15 (same version), as the "ALTER SEQUENCE "public"."test_a_seq"
SET LOGGED;" line is not generated by pg_dump in this case.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message PG Bug reporting form 2024-09-16 07:29:54 BUG #18619: uppercase column with quotation marks, gets an error without quotation marks
Previous Message Tom Lane 2024-09-14 22:33:54 Re: Case clause doesn't report syntactic error