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.
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 |