Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sZtft-004zpB-Tt for pgsql-general@arkaria.postgresql.org; Fri, 02 Aug 2024 14:54:34 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sZtfq-000O0b-LR for pgsql-general@arkaria.postgresql.org; Fri, 02 Aug 2024 14:54:30 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sZjMD-00EqPe-2n for pgsql-general@lists.postgresql.org; Fri, 02 Aug 2024 03:53:33 +0000 Received: from eu-smtp-delivery-161.mimecast.com ([185.58.86.161]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sZjM5-002eLm-Mk for pgsql-general@lists.postgresql.org; Fri, 02 Aug 2024 03:53:31 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=starlingbank.com; s=mimecast20190321; t=1722570803; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:cc:mime-version:mime-version:content-type:content-type; bh=v7T4MemRQg9SsswK5HMMEjdybUotw5t5aoI4120EVNo=; b=BD2n68Uv3rNW4wFZkJ6E4IZ7ZxZczscyqgU7x77jhssFHPfqXF8jfGACKsbyzp1brqfJ3N ELqNJ7txU9zMRhwcefUVsPEGHqaXHYUGl/8F+cM5jmrhn3VBaDnCh7RVFlk2NTdEav9qvi JzqFhdC6+g9mrOWieXx4HxY7unSirgExNfWhhARi312ATI/9GMPe0bA8qatx5+mtail8NI 0IIohFkGl5zEtjN8zqj2oAOrIQAJHbwPjJU1oSpTIzNhJZj/yNoBLHsx84OGOJgttTavpv TjHCyLWY4CxIIrHd6GHsl8OsN59lxaPzTSXWfYBhdJSOkZJkcdJOoU/qhiTEvw== Received: from mail-yw1-f197.google.com (mail-yw1-f197.google.com [209.85.128.197]) by relay.mimecast.com with ESMTP with STARTTLS (version=TLSv1.3, cipher=TLS_AES_256_GCM_SHA384) id uk-mta-319-iVYWEhsyP3ipWHY0eyp2lQ-1; Fri, 02 Aug 2024 04:53:21 +0100 X-MC-Unique: iVYWEhsyP3ipWHY0eyp2lQ-1 Received: by mail-yw1-f197.google.com with SMTP id 00721157ae682-68a8460c971so3220797b3.3 for ; Thu, 01 Aug 2024 20:53:21 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1722570800; x=1723175600; h=cc:to:subject:message-id:date:from:mime-version:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=oW49UYV/4C+uWu10Mf3WCZOoC+ayLFNJOM3hLBhQBFQ=; b=sQohfAsJgP4IeOLbcDDII6HtXBjT8dRzc2zAzOfyuS7z6gLu3B7DkLwjxcyneBICxB epU6RRrDSRoYDtiDqT6orxBqSDUWIjugMZdqWvqs1HMwn83aIGlV9Ia2PHPkHsvOfpTI EIil/h8GzFyfG4AYwBr9fUEvGM/141WOs9luZMJv+cQv/FrB/6Hs+wl//605XCUwnGUQ 9ifp2F8GOFJZS7g3FHWhfz5a/RGhU2S8kHefuBdhMu+hwIE3ZOGWQvdsHMhKB81y0Oco B+RTr2r5Oq2/+CgF33be/W/xDcnxjfaP5E9Vvb4w65B+B8YJ4s6SD+tvTJiX2FTHB+nZ 1M1A== X-Gm-Message-State: AOJu0YxXvHlC3JqRqY0u+WjQueO/1uHzWg0MPaP481KdJbqfDA4jmAEE ASpn0YkPVTdVX/qE4DfSEOUuqaddqsdSOdFDI8x/wDhxl2bY/vCOZXwdbzJ74VfIwE3n8xl15m2 TgPhgwJOg+RfWf/XdJrkaLnCncbyruqjI60aI1p++mE9T6LrtxCcudVrjgsX/HtODFkYaGu47dz qMB76Okj5i/+m8YrxedlnORIt7K9lNgO8X0/ljn0QlsRaCoCDTzHe0pgSo4dD3iG133qNT80DT5 ycnyWgNo7I0pxkZ5IAIXH55lKqPlkyfH5P8zx89uhpnO87e8nUScZTS96XkV7aG0duCaQ== X-Received: by 2002:a81:88c5:0:b0:664:e8aa:26dd with SMTP id 00721157ae682-6896151092emr24946307b3.24.1722570800388; Thu, 01 Aug 2024 20:53:20 -0700 (PDT) X-Google-Smtp-Source: AGHT+IEP7tRHHXTj6ZhmCZmZ8yzSDh5Ucjcj/dNds4dX6Sr+04Vbtj/VOxoBUv7YMyv7P/4qO5fNO6lObs43BNuwKDw= X-Received: by 2002:a81:88c5:0:b0:664:e8aa:26dd with SMTP id 00721157ae682-6896151092emr24946217b3.24.1722570800073; Thu, 01 Aug 2024 20:53:20 -0700 (PDT) MIME-Version: 1.0 From: Sindhu Selvaraj Date: Fri, 2 Aug 2024 04:53:09 +0100 Message-ID: Subject: Subscribe to mailing list - General Question To: pgsql-general@lists.postgresql.org Cc: Gurmokh Sangha X-Mimecast-Spam-Score: 0 X-Mimecast-Originator: starlingbank.com Content-Type: multipart/alternative; boundary="000000000000a3b195061eab4458" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000a3b195061eab4458 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable 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 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(''::pg_catalog= .oid); SELECT pg_catalog.binary_upgrade_set_next_heap_relfilenode(''::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 [image: SB Logo] SINDHU SELVARAJ POSTGRESQL DBA STARLING BANK --000000000000a3b195061eab4458 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
In 15.7 and 16.3 Release Notes, I found a change to an unl= ogged table's sequence.

Make=C2=A0ALTER TABLE ... ADD C= OLUMN=C2=A0create identity/serial sequences with the same persistence as th= eir owning tables (Peter Eisentraut)

CREATE UN= LOGGED TABLE=C2=A0will make any owned sequences be unlogged too.=C2=A0ALTER= TABLE=C2=A0missed that consideration, so that an added identity column wou= ld have a logged sequence, which seems pointless.

Major version upgrade to 15.7 or 16.3 is failing on pg_restore step wit= h following error,

pg_re= store: creating TABLE "public.queue_context"

pg_re= store: creating SEQUENCE "public.queue_context_id_seq"

pg_re= store: while PROCESSING TOC:

pg_re= store: from TOC entry 200; 1259 <NUMBER> SEQUENCE queue_context_id_se= q sindhu.selvaraj

pg_re= store: error: could not execute query: ERROR:=C2=A0 unexpected request for new relfilenode in binar= y upgrade mode

Comma= nd was:=C2=A0

-- Fo= r binary upgrade, must preserve pg_class oids and relfilenodes

SELEC= T pg_catalog.binary_upgrade_set_next_heap_pg_class_oid('<NUMBER>&= #39;::pg_catalog.oid);

SELEC= T pg_catalog.binary_upgrade_set_next_heap_relfilenode('<NUMBER>&#= 39;::pg_catalog.oid);

ALTER= TABLE "public"."queue_context" ALTER COLUMN "id&q= uot; ADD GENERATED BY DEFAULT AS IDENTITY (

=C2=A0 =C2=A0 SEQUENCE NAME &= quot;public"."queue_context_id_seq"

=C2=A0 =C2=A0 START WITH 1

=C2=A0 =C2=A0 INCREMENT BY 1<= /p>

=C2=A0 =C2=A0 NO MINVALUE

=C2=A0 =C2=A0 NO MAXVALUE

=C2=A0 =C2=A0 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.=C2=A0

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


Regards,

Sindhu

3D"SB=C2=A0

SINDHU SELVA= RAJ

POSTGRESQL DBA

STARL= ING BANK


--000000000000a3b195061eab4458--