Postgres Pro
Downloads
Home   >   mailing lists

Re: Report Postgres Bug - Unlogged table sequence - Mailing list pgsql-bugs

From Sindhu S
Subject Re: Report Postgres Bug - Unlogged table sequence
Date
Msg-id CALRZP9h+gY8eGusTCjAjFw4_sFntp5DeHy5jMYmC=tL8wvKdtg@mail.gmail.com
Whole thread Raw
In response to Re: Report Postgres Bug - Unlogged table sequence  (Zaid Shabbir <zaidshabbir@gmail.com>)
List pgsql-bugs
Tree view
Hi Zaid,

I had the same error while moving from 15.x to 16.3 in RDS database.

This change in latest 15 and 16 release could be the reason

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


In local, I tried from 13.15 to 15.7
Here are my commands,

 ./initdb --locale=C -E UTF-8 $HOMEBREW_PREFIX/var/postgresql@13
 
 
 ./pg_ctl -D '/opt/homebrew/var/postgresql@13' -l logfile start
 
 
 psql -h localhost -p 5432 -d postgres
 
 CREATE UNLOGGED TABLE IF NOT EXISTS queue_context
 (
     queue_name character varying(255) COLLATE pg_catalog."default" NOT NULL,
     correlation_id character varying(32) COLLATE pg_catalog."default" NOT NULL,
     trace_id character varying(32) COLLATE pg_catalog."default" NOT NULL,
     span_id character varying(16) COLLATE pg_catalog."default" NOT NULL,
     enqueued_at timestamp without time zone NOT NULL,
     id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
     item_uid uuid NOT NULL,
     CONSTRAINT pk_queue_context PRIMARY KEY (id),
     CONSTRAINT uk_queue_context_queue_name_item_uuid UNIQUE (queue_name, item_uid)
 );
 
 ./pg_ctl stop -D /opt/homebrew/var/postgresql@13 -m fast
 
 
 cd /opt/homebrew/Cellar/postgresql@15/15.7/bin
 
 
 ./pg_upgrade --old-datadir /opt/homebrew/var/postgresql@13/ --new-datadir /opt/homebrew/var/postgresql@15/ --old-bindir /opt/homebrew/Cellar/postgresql@13/13.15/bin --new-bindir /opt/homebrew/Cellar/postgresql@15/15.7/bin
 
 
 *failure*
 Consult the last few lines of "/opt/homebrew/var/postgresql@15/pg_upgrade_output.d/20240801T072509.333/log/pg_upgrade_dump_13776.log" for
 the probable cause of the failure.
 
 
 
 
 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 16384 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('16384'::pg_catalog.oid);
 SELECT pg_catalog.binary_upgrade_set_next_heap_relfilenode('16384'::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;

Regards,
Sindhu 


On Mon, 19 Aug 2024, 7:11 pm Zaid Shabbir, <zaidshabbir@gmail.com> wrote:
Hello Sindhu,

Regarding the issue you reported for Unlogged table restore, do you also face a similar issue while performing pg_upgrade from 15.x to 16.x.

Can you please explain your scenario like the older postgresql version you are using and complete the command for upgradation ?

Thanks & Regards,
Zaid





On Mon, Aug 19, 2024 at 10:48 AM Sindhu S <sindhusanti@gmail.com> wrote:

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.X"
pg_restore: creating SEQUENCE "public.X_id_seq"
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 200; 1259 <NUMBER> SEQUENCE X_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"."X" ALTER COLUMN "id" ADD GENERATED BY DEFAULT AS IDENTITY (
   SEQUENCE NAME "public"."X_id_seq"
   START WITH 1
   INCREMENT BY 1
   NO MINVALUE
   NO MAXVALUE
   CACHE 1
);
ALTER SEQUENCE "public"."X_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

pgsql-bugs by date:

Previous
From: Matthew Clark
Date:
Subject: server crash on raspberry pi for large queries
Next
From: Peter Eisentraut
Date:
Subject: CREATE CAST allows creation of binary-coercible cast to range over domain