public inbox for [email protected]
help / color / mirror / Atom feedFrom: vignesh C <[email protected]>
To: Peter Smith <[email protected]>
Cc: Amit Kapila <[email protected]>
Cc: Shlok Kyal <[email protected]>
Cc: Dilip Kumar <[email protected]>
Cc: shveta malik <[email protected]>
Cc: Masahiko Sawada <[email protected]>
Cc: Hayato Kuroda (Fujitsu) <[email protected]>
Cc: Nisha Moond <[email protected]>
Cc: Ashutosh Sharma <[email protected]>
Cc: David G. Johnston <[email protected]>
Cc: Zhijie Hou (Fujitsu) <[email protected]>
Cc: YeXiu <[email protected]>
Cc: Ian Lawrence Barwick <[email protected]>
Cc: Bharath Rupireddy <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: Skipping schema changes in publication
Date: Mon, 20 Apr 2026 17:43:15 +0530
Message-ID: <CALDaNm2VghC-CSyS6jwyMMcpwSWjQ09t9AkkL0Sz7dsb1VGF3A@mail.gmail.com> (raw)
In-Reply-To: <CAHut+PsXKQbyAhQLRHX0aANkJZPWsYpz4TQQba1WFM7dvSwXNg@mail.gmail.com>
References: <CAJpy0uB20MhJJEaPJdm31t4fykJ+fChA_76jU2P9HX5knbJvAA@mail.gmail.com>
<CAD21AoCC8XuwfX62qKBSfHUAoww_XB3_84HjswgL9jxQy696yw@mail.gmail.com>
<OS9PR01MB12149EA0C749BC29C7C949E32F544A@OS9PR01MB12149.jpnprd01.prod.outlook.com>
<CAD21AoBbZEshyaK0PeiF_J4_S75EfF=Gcs=C+X-osoVoUnawuQ@mail.gmail.com>
<CAHut+PssG+sHeV+Xo0g=S7xBb9FgDPjHYDR4iSuOdYXDq-Psng@mail.gmail.com>
<CAA4eK1LaSfAG7UAuy1xpnkWKM_YtrPuhbgAxYBFY3Sp_v_KqoQ@mail.gmail.com>
<CAD21AoAb8E8krN63cY_U7RQs9v-zkqUZyKT_UVKDwKfExtvTBg@mail.gmail.com>
<CAA4eK1K1GLR7DXSABayQE+pWM=v1ODD6haPYxuDhAYwJN5gjzg@mail.gmail.com>
<CALDaNm2kvFahDDvdgCNo=Nv-COz_N5Xw8YmzQBN2bd3g=N81fQ@mail.gmail.com>
<CAHut+PsCqTR_kQu5M1TqBjnE6KM5cO22aH8boHfpMa_gSJBmWg@mail.gmail.com>
<CALDaNm2OOgmNOPpABUU+AXzHhfrLG9HMfSd3jfNe=t3dc-kp1Q@mail.gmail.com>
<CAJpy0uCN4gfP7fSt__KdW5wYQ82650Z6L4YLnjRHZTQ1yir1mg@mail.gmail.com>
<CALDaNm32+c6RTE5xR6sJ=MZGgwEtzjkxpov_Hu70MXfbvmN+=Q@mail.gmail.com>
<CAHut+PtQbK9USLepyzArXFoNuLok1MsBu_Jg4UT=koZocombFw@mail.gmail.com>
<CALDaNm1tKuU479T=winBqoMb3MzO3Mta2juk8W3t2R5ps0_zyg@mail.gmail.com>
<CALDaNm3jpYs7ALcU6m5=Li=udidjZoW5dMpyCFs8QHGaf0S8+A@mail.gmail.com>
<CAJpy0uCWS=ybBKG-kRAfdWEe1VBNj+VqpAUUoT8MPaNS7EggiA@mail.gmail.com>
<CAA4eK1LMM-P4NatbkjG-96B7hHC7KYrJ8XTsCZQy0jLO9Qj4Bw@mail.gmail.com>
<CAJpy0uAyf71QSYitBf4WbCYq22HDR6LPdxB12TpTgTRpczwphw@mail.gmail.com>
<CAFiTN-s5PW121mBGKin20YEQpZkWefMehmP=v+0onzEaMQpwdw@mail.gmail.com>
<CAA4eK1LBf5asit18HcqcFinOkdCjD6Lk2Eid9PDhtH6acwYb8w@mail.gmail.com>
<CALDaNm3cdoT58E3QtYCwBbzyxYJjoS2k7Q0EgzR9ta6fyDGHSg@mail.gmail.com>
<CAHut+PthJx_gZJNgF=mWSpkWjQJ58KyhrZ7D7CkX_TVq12wv7A@mail.gmail.com>
<CANhcyEVLp5kbaVR4=nh1jR4YWqv7YpVx_SnYoshbnOrnY79_fg@mail.gmail.com>
<CAA4eK1+4ZNF-MGheeTtYF9TdfNBnKKJ8DivWZsXBnuJVkqfa0g@mail.gmail.com>
<CANhcyEUQvEK+HOH6Y8Fy30fNvC631ZopWKhwgskXjKnuXiGV5Q@mail.gmail.com>
<CAHut+PuUnDZ4ki8nCK6SkHOn8iP6N1Vm24jzWtEqRG9a_GMxrw@mail.gmail.com>
<CALDaNm0wV2Jd558jWG2EWVAjCiuaAEUrP4i2FWBKqob=1Y9-2A@mail.gmail.com>
<CAHut+Pv+sE82few1Chv4wBGnTR548n_FrzGyabL0w_1TOG6GCA@mail.gmail.com>
<CALDaNm1CiBYcteE_jjPA4BPHfX30dg9eTTTkJgkjY5tgE7t=bQ@mail.gmail.com>
<CAHut+PuzgDiBcD6rp_31RzqbGLpMwqGrNKznFUA_fpBpZYPe9Q@mail.gmail.com>
<CAA4eK1LG9ezz2QHMfaAKeWqCaRLRaDtu6-kBgrCRq14UaB3ECA@mail.gmail.com>
<CAHut+PsXKQbyAhQLRHX0aANkJZPWsYpz4TQQba1WFM7dvSwXNg@mail.gmail.com>
Hi,
When changing a table to UNLOGGED, tables that appear in publications
via EXCEPT clauses (prexcept = true) are currently allowed, but their
entries remain in pg_publication_rel.
For example:
postgres=# create table t1(c1 int);
CREATE TABLE
postgres=# create publication pub1 for all tables except (table t1);
CREATE PUBLICATION
postgres=# alter table t1 set unlogged;
ALTER TABLE
postgres=# \d t1
Unlogged table "public.t1"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
c1 | integer | | |
Except publications:
"pub1"
Since UNLOGGED tables are not supported in publications, this leaves
stale catalog entries. This patch removes such entries from
pg_publication_rel when the table is changed to UNLOGGED, and emits a
NOTICE to inform the user.
Another option considered was to throw an error when setting such
tables to UNLOGGED. However, allowing the operation was preferred,
since UNLOGGED tables do not generate WAL and are not replicated
anyway, so blocking the operation would be unnecessarily restrictive.
Attached patch has the changes for the same.
Thoughts?
Regards,
Vignesh
Attachments:
[application/octet-stream] v1-0001-Handle-EXCEPT-publications-when-changing-table-to.patch (6.2K, 2-v1-0001-Handle-EXCEPT-publications-when-changing-table-to.patch)
download | inline diff:
From 8c9b04f003d4989e09149efd3c875edc31a3a5bd Mon Sep 17 00:00:00 2001
From: Vignesh C <[email protected]>
Date: Mon, 20 Apr 2026 15:02:31 +0530
Subject: [PATCH v1] Handle EXCEPT publications when changing table to UNLOGGED
When changing a table to UNLOGGED, tables that appear in publications
only via EXCEPT clauses (prexcept = true) were allowed, but left stale
entries in pg_publication_rel.
Since UNLOGGED tables are not supported in publications, remove such
entries when the table is changed to UNLOGGED, and emit a NOTICE to
inform the user.
---
src/backend/commands/tablecmds.c | 46 +++++++++++++++++++----
src/test/regress/expected/publication.out | 26 +++++++++++++
src/test/regress/sql/publication.sql | 16 ++++++++
3 files changed, 81 insertions(+), 7 deletions(-)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index eec09ba1ded..3cb4b51489a 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -98,6 +98,7 @@
#include "tcop/utility.h"
#include "utils/acl.h"
#include "utils/builtins.h"
+#include "utils/catcache.h"
#include "utils/fmgroids.h"
#include "utils/inval.h"
#include "utils/lsyscache.h"
@@ -19093,13 +19094,44 @@ ATPrepChangePersistence(AlteredTableInfo *tab, Relation rel, bool toLogged)
* Check that the table is not part of any publication when changing to
* UNLOGGED, as UNLOGGED tables can't be published.
*/
- if (!toLogged &&
- GetRelationIncludedPublications(RelationGetRelid(rel)) != NIL)
- ereport(ERROR,
- (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
- errmsg("cannot change table \"%s\" to unlogged because it is part of a publication",
- RelationGetRelationName(rel)),
- errdetail("Unlogged relations cannot be replicated.")));
+ if (!toLogged)
+ {
+ CatCList *pubrellist;
+
+ /* Find all publications associated with the relation. */
+ pubrellist = SearchSysCacheList1(PUBLICATIONRELMAP,
+ ObjectIdGetDatum(RelationGetRelid(rel)));
+ for (int i = 0; i < pubrellist->n_members; i++)
+ {
+ HeapTuple tup = &pubrellist->members[i]->tuple;
+ Form_pg_publication_rel pubrel = (Form_pg_publication_rel) GETSTRUCT(tup);
+
+ if (!pubrel->prexcept)
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("cannot change table \"%s\" to unlogged because it is part of a publication",
+ RelationGetRelationName(rel)),
+ errdetail("Unlogged relations cannot be replicated.")));
+ }
+ else
+ {
+ ObjectAddress obj;
+ char *pubname;
+
+ pubname = get_publication_name(pubrel->prpubid, false);
+
+ ObjectAddressSet(obj, PublicationRelRelationId, pubrel->oid);
+ performDeletion(&obj, DROP_CASCADE, 0);
+
+ ereport(NOTICE,
+ errmsg("relation \"%s\" removed from publication \"%s\" due to being changed to UNLOGGED",
+ RelationGetRelationName(rel), pubname));
+ }
+ }
+
+ ReleaseSysCacheList(pubrellist);
+ }
/*
* Check existing foreign key constraints to preserve the invariant that
diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out
index d028e9be866..dec7f5b24fb 100644
--- a/src/test/regress/expected/publication.out
+++ b/src/test/regress/expected/publication.out
@@ -256,6 +256,32 @@ Except publications:
"testpub_foralltables_excepttable"
"testpub_foralltables_excepttable1"
+SET client_min_messages = 'NOTICE';
+-- Change testpub_tbl1 to UNLOGGED. This should remove the relation from the
+-- publication (and effectively from the EXCEPT clause), since UNLOGGED tables
+-- are not supported in publications.
+ALTER TABLE testpub_tbl1 SET UNLOGGED;
+NOTICE: relation "testpub_tbl1" removed from publication "testpub_foralltables_excepttable" due to being changed to UNLOGGED
+NOTICE: relation "testpub_tbl1" removed from publication "testpub_foralltables_excepttable1" due to being changed to UNLOGGED
+\dRp+ testpub_foralltables_excepttable1
+ Publication testpub_foralltables_excepttable1
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+-------------
+ regress_publication_user | t | f | t | t | t | t | none | f |
+(1 row)
+
+\d testpub_tbl1
+ Unlogged table "public.testpub_tbl1"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+------------------------------------------
+ id | integer | | not null | nextval('testpub_tbl1_id_seq'::regclass)
+ data | text | | |
+Indexes:
+ "testpub_tbl1_pkey" PRIMARY KEY, btree (id)
+
+-- Restore the table to LOGGED.
+ALTER TABLE testpub_tbl1 SET LOGGED;
+SET client_min_messages = 'ERROR';
-- fail - first table in the EXCEPT list should use TABLE keyword
CREATE PUBLICATION testpub_foralltables_excepttable2 FOR ALL TABLES EXCEPT (testpub_tbl1, testpub_tbl2);
ERROR: syntax error at or near "testpub_tbl1"
diff --git a/src/test/regress/sql/publication.sql b/src/test/regress/sql/publication.sql
index 642e32fa098..3c8b5a3a980 100644
--- a/src/test/regress/sql/publication.sql
+++ b/src/test/regress/sql/publication.sql
@@ -119,6 +119,22 @@ CREATE PUBLICATION testpub_foralltables_excepttable1 FOR ALL TABLES EXCEPT (TABL
-- Check that the table description shows the publications where it is listed
-- in the EXCEPT clause
\d testpub_tbl1
+
+SET client_min_messages = 'NOTICE';
+
+-- Change testpub_tbl1 to UNLOGGED. This should remove the relation from the
+-- publication (and effectively from the EXCEPT clause), since UNLOGGED tables
+-- are not supported in publications.
+ALTER TABLE testpub_tbl1 SET UNLOGGED;
+\dRp+ testpub_foralltables_excepttable1
+\d testpub_tbl1
+
+-- Restore the table to LOGGED.
+ALTER TABLE testpub_tbl1 SET LOGGED;
+
+SET client_min_messages = 'ERROR';
+
+
-- fail - first table in the EXCEPT list should use TABLE keyword
CREATE PUBLICATION testpub_foralltables_excepttable2 FOR ALL TABLES EXCEPT (testpub_tbl1, testpub_tbl2);
--
2.43.0
view thread (259+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Skipping schema changes in publication
In-Reply-To: <CALDaNm2VghC-CSyS6jwyMMcpwSWjQ09t9AkkL0Sz7dsb1VGF3A@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox