Report Postgres Bug - Unlogged table sequence

Hi,

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

To clarify, did you use pg_dump from _newer_ version of PostgreSQL as
recommended[1]https://www.postgresql.org/docs/current/upgrading.html -- Best regards, Aleksander Alekseev ?

"""
It is recommended that you use the pg_dump and pg_dumpall programs
from the newer version of PostgreSQL, to take advantage of
enhancements that might have been made in these programs. Current
releases of the dump programs can read data from any server version
back to 9.2.
"""

[1]: https://www.postgresql.org/docs/current/upgrading.html -- Best regards, Aleksander Alekseev
--
Best regards,
Aleksander Alekseev

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:

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: