Subscribe to mailing list - General Question

[Sindhu Selvaraj <sindhu.selvaraj@xxxxxxxxxxxxxxxx>]

In 15.7 and 16.3 Release Notes, I found a change to an unlogged table's sequence.

Make ALTER TABLE ... ADD COLUMN create identity/serial sequences with the same persistence as their owning tables (Peter Eisentraut)

CREATE UNLOGGED TABLE will make any owned sequences be unlogged too. ALTER TABLE missed that consideration, so that an added identity column would have a logged sequence, which seems pointless.

Major version upgrade to 15.7 or 16.3 is failing on pg_restore step with following error,

pg_restore: creating TABLE "public.queue_context"

pg_restore: creating SEQUENCE "public.queue_context_id_seq"

pg_restore: while PROCESSING TOC:

pg_restore: from TOC entry 200; 1259 <NUMBER> SEQUENCE queue_context_id_seq sindhu.selvaraj

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('<NUMBER>'::pg_catalog.oid);

SELECT pg_catalog.binary_upgrade_set_next_heap_relfilenode('<NUMBER>'::pg_catalog.oid);

ALTER TABLE "public"."queue_context" ALTER COLUMN "id" ADD GENERATED BY DEFAULT AS IDENTITY (

    SEQUENCE NAME "public"."queue_context_id_seq"

    START WITH 1

    INCREMENT BY 1

    NO MINVALUE

    NO MAXVALUE

    CACHE 1

);

ALTER SEQUENCE "public"."queue_context_id_seq" SET LOGGED;

This has been spotted in local postgres installed in MAC as well as in AWS RDS.

We had to change the unlogged table to logged and then upgrade. 

I am reporting this as a bug. Please keep us updated.

Regards,

Sindhu

 

SINDHU SELVARAJ

POSTGRESQL DBA

STARLING BANK