Re: Report Postgres Bug - Unlogged table sequence

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(at)gmail(dot)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
>