public inbox for [email protected]
help / color / mirror / Atom feedFrom: Peter Smith <[email protected]>
To: Nisha Moond <[email protected]>
Cc: shveta malik <[email protected]>
Cc: Amit Kapila <[email protected]>
Cc: Zsolt Parragi <[email protected]>
Cc: [email protected]
Subject: Re: Support EXCEPT for TABLES IN SCHEMA publications
Date: Mon, 6 Jul 2026 17:55:19 +1000
Message-ID: <CAHut+Pt-+6Q4-kmhqCZ2_FpaNRR=haJk-GK1eEyTXcTTDnRnXA@mail.gmail.com> (raw)
In-Reply-To: <CABdArM7mJsqFCGe2AyaS-E6n7rk4SoDTSKD+D3hUNO=UaDPStA@mail.gmail.com>
References: <CABdArM5sw4Q1ZU8HGdo4BSc1A_+8xtUNq17j6wcir=yMUy19Cg@mail.gmail.com>
<CAHut+PvnH8QHa035Skoh1e9jm_H08DO9fQ=F-NAMsEpYf0RZ2Q@mail.gmail.com>
<CAJpy0uDu0LcNXcZCP0cR_LHqo+sau33KwPFHemmGVYf_JTxRBQ@mail.gmail.com>
<CAA4eK1KbCWBmEXH-rhQjKgNwq=onZp8vRR-QkRhPpbKwL-kQdw@mail.gmail.com>
<CAHut+Pvj4=GWoJEd4EBdp4pi6KxXQ46ioW=PV+=UktiXr2gCvg@mail.gmail.com>
<CABdArM75F0A+DGP8AOt-_b_XREX40rvFid1jRjnr_+S5b51t8Q@mail.gmail.com>
<CAJpy0uDTshb243L5yEYWB3uO-JrwSoRqQDNovh03K2GZuuR3Pg@mail.gmail.com>
<CAJpy0uDy97ULmJUwPacAzc5u2seuPK6RXgCS1rnsW2MfR4eeSw@mail.gmail.com>
<CABdArM6oXXXSAxxXFktTTfBf4kyxJCvdNtTbUZtSwJ=CepN+Xw@mail.gmail.com>
<CAJpy0uBqM+fq7+g1ZRATuY16H10MFP9i25wfFCYCE5MGu+PE0Q@mail.gmail.com>
<CABdArM4uKaS1coCQj6rAwMmHqU_cCJyEWNic-PFF1_ZjDDM82Q@mail.gmail.com>
<CAHut+Pu5VNakf5JAhKM7T-P_q37eN1Qgv5nvZUe+8RAAT41y4g@mail.gmail.com>
<CABdArM6WTm2gP4pcjVdHT1Nx6zdLKTq7nLPUkzZOhprc95a6Rw@mail.gmail.com>
<CAHut+Ptthc1X-UA8-6zG-iFeCDuoNd+oJRBZ1eCnJ9RNOXjfBQ@mail.gmail.com>
<CABdArM79m7-CTf6KGGGU2QBydFtuonGgfxRSqk-vhwTsH8z1ow@mail.gmail.com>
<CAHut+PuiK_Pa=BkSgBxYzqf1PYh+mcUcUQCr8r1e69-y1r+hhw@mail.gmail.com>
<CABdArM7rH+3GekRgufEOwrJxUeQk=LB182CQwJD35e0oN7q8ZA@mail.gmail.com>
<CAHut+PtP1mbQT==xo=G-37dV9Lt3q7YO2eMEAqSbZuszy93LcQ@mail.gmail.com>
<CABdArM6XRpUR86a-daYMXFqhH-spJQiQAVfJ2+GFiAqeup2jyA@mail.gmail.com>
<CAHut+PuDB=doKUSf94cs8hOo2d5mOc+GxxPOf57xGhdE6e-Aog@mail.gmail.com>
<CABdArM76MZ4dYC87zCv67fVY9t5aFLd2nD5tNBxFFizESyBOSg@mail.gmail.com>
<CAN4CZFM9xXkw8FvHOTan+m4rASSoxbJTWXMbZ_-v2M-84w5Wzg@mail.gmail.com>
<CABdArM5wj0dtieSVMvzvJDh5fJ-GGwh3PxV+E_MbPEntCk4KRg@mail.gmail.com>
<CAN4CZFMaB1a89NZjRT9bWFZ8-XN02amye_fnYAg0NM_23DAwQw@mail.gmail.com>
<CABdArM5qHR=Zmyfqs6AuCn-Crj=_w3CXA-1K9rQ53_zismXCNQ@mail.gmail.com>
<CAHut+Pu0-A92MyRxFCLNQFYgAKGrKbYChG_p4ARogEfbAtMm8A@mail.gmail.com>
<CABdArM4fUnAZ10uyT61tNYu-WWHBiBOz75at9L90jBKLH5YR8g@mail.gmail.com>
<CAHut+Pv-GA1oGa6+nwn_5AVhBg8NuJThQVUzqhQPXJge49jnew@mail.gmail.com>
<CAA4eK1LMooRmK3_w_Zo-g3ftB7mDDkLitXjiEjgZY=0LTeZRoQ@mail.gmail.com>
<CABdArM5j8gX_xYDU6_aPEOuPAiCGtF8QyrpHeqZXQDsQ_wpJKA@mail.gmail.com>
<CAHut+PvAzsQA7BPKFC2dKWAr_kpAdHhLAaGOJQmZ_7=0paZCZA@mail.gmail.com>
<CABdArM4peXa8W9AYw6OHWmBuP4kkN3e01TcrUq_arWQLacoiNw@mail.gmail.com>
<CAHut+PucwGJ4Tyr9kRF4zNstAM_GGheU+-55u3JrKW8epKGSEQ@mail.gmail.com>
<CABdArM48_Dm3cEcrzb0MRuaMoKhCtzbQE7mX7GyaoVOLc9fNXQ@mail.gmail.com>
<CAHut+PsN+_2Me8khvk4bQ1+U8B73YykHv7qnQ7V2qtucaf2c4g@mail.gmail.com>
<CABdArM6Vv9Z4Dyj3+Wvdcq_8AAL2PshoHObZhpi5aBRkRn1eQA@mail.gmail.com>
<CAJpy0uCBkS2MyRzz0WSxg6Ch6Vi9-vNLCWLmEtcu_WwHWP7oiw@mail.gmail.com>
<CABdArM6Ycpng+SYh733ZfZaFFDUkZmjgxh35+6utx03HOTvH2A@mail.gmail.com>
<CAJpy0uD=deQJxbEuug-fbhvG6K47w1k_dwkZvbhAaBbBAgUPeg@mail.gmail.com>
<CABdArM4PzvWHyqG-tdZpf9U+0_-23n8NXJKAPW+_NADOqU42xA@mail.gmail.com>
<CAJpy0uAs2a2R+P=7cPo3iW4SgMUQVMf-1WwmPgJ5ttZErgzZvw@mail.gmail.com>
<CABdArM7mJsqFCGe2AyaS-E6n7rk4SoDTSKD+D3hUNO=UaDPStA@mail.gmail.com>
Hi Nisha.
Some review comments for v18.
//////////
Patch v18-0001
//////////
src/backend/catalog/pg_publication.c
1.
+ /*
+ * A partition cannot be independently included when any of its partition
+ * ancestors is in this publication's EXCEPT list. Doing so would leave
+ * the catalog inconsistent (root excluded, partition explicitly included)
+ * and the ancestor-cascade rule would silently override the include at
+ * replication time. Individual partitions cannot appear in EXCEPT
+ * (rejected above), so this check only applies to non-EXCEPT adds.
+ */
+ if (!pri->except && targetrel->rd_rel->relispartition)
It might be tidier to deal with partitions in one place.
SUGGESTION
if (targetrel->rd_rel->relispartition)
{
if (pri->except)
{
/* If in EXCEPT clause, must be root partitioned table */
...
} else {
/* If not in EXCEPT clause, check ancestors are not excluded */
...
}
}
~~~
2.
+ errmsg("cannot add table \"%s\" to publication \"%s\"",
+ RelationGetQualifiedRelationName(targetrel),
+ get_publication_name(pubid, false)),
+ errdetail("Partition ancestor \"%s\" of table \"%s\" is currently
listed in the EXCEPT clause of the publication.",
"Partition ancestor .. of table" sounded odd.
How about:
errmsg: cannot add partition \"%s\" to ...
errdetail: Ancestor \"%s\" of partition \"%s\" is currently ...
~~~
publication_add_relation:
3.
+ ereport(ERROR,
+ (errcode(ERRCODE_DUPLICATE_OBJECT),
+ errmsg("cannot add table \"%s\" to publication \"%s\"",
+ RelationGetQualifiedRelationName(targetrel),
+ pub->name),
+ errdetail("Table \"%s\" is currently listed in the EXCEPT clause of
the publication.",
The table was already named in the errmsg. It seemed redundant to name
it again in the errdetail.
SUGGESTION
errdetail("The table is currently listed in the EXCEPT clause of the
publication.")
======
src/backend/commands/publicationcmds.c
4.
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("table \"%s\" cannot appear in both the table list and the
EXCEPT clause",
+ RelationGetQualifiedRelationName(pri->relation)));
Do we need to name the clauses in this errmsg? Users can see what they typed.
SUGGESTION
errmsg("table \"%s\" cannot be both published and excluded")
//////////
Patch v18-0003
//////////
src/backend/commands/publicationcmds.c
PublicationDropTables:
1.
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("cannot drop table \"%s\" from publication \"%s\"",
+ RelationGetQualifiedRelationName(rel),
+ get_publication_name(pubid, false)),
+ errdetail("Table \"%s\" is currently listed in the EXCEPT clause of
the publication.",
+ RelationGetQualifiedRelationName(rel)),
+ errhint("Change EXCEPT list using ALTER PUBLICATION ... SET TABLES
IN SCHEMA ... EXCEPT.")));
1a.
(Same as above review comment for patch 0001).
The table was already named in the errmsg. It seemed redundant to name
it again in the errdetail.
SUGGESTION
errdetail("The table is currently listed in the EXCEPT clause of the
publication.")
~
1b.
Why hint to change the except list -- who says the EXCEPT list is wrong?
It seemed more like a user error to me if they are trying to drop
something from a publication when it is already excluded. Why does
this need any hint at all?
======
src/test/regress/sql/publication.sql
2.
I did not recognise the distinction in the test comments between
"fail:" and "error:".
~~~
3.
+-- error: ALTER PUBLICATION ... DROP TABLE on an excluded table is rejected
+-- with an EXCEPT-specific error; an EXCEPT entry is not a member.
I don't think you need to say "an EXCEPT entry is not a member." --
what does that mean, other than to redundantly say an excluded table
is a table that is excluded?
//////////
Patch v18-0004
//////////
doc/src/sgml/ref/create_publication.sgml
1.
<para>
For partitioned tables, only the root partitioned table may be specified
in <literal>EXCEPT</literal>. Doing so excludes the root table and
- all of its partitions from replication. The optional
+ all of its partitions from replication, even any partition that lives
+ in a schema which is itself included in the publication.
BEFORE
For partitioned tables, only the root partitioned table may be
specified in EXCEPT. Doing so excludes the root table and all of its
partitions from replication, even any partition that lives in a schema
which is itself included in the publication.
SUGGESTION #1
For partitioned tables, only the root partitioned table may be
specified in EXCEPT. Excluding the root table automatically excludes
all of its partitions from replication, including those in schemas
that are otherwise included in the publication.
SUGGESTION #2 (TI doubt you even needed to mention about other schemas)
For partitioned tables, only the root partitioned table may be
specified in EXCEPT. Excluding the root table automatically excludes
all of its partitions from replication, including those that would
otherwise be included in the publication.
======
Kind Regards,
Peter Smith.
Fujitsu Australia
view thread (68+ 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]
Subject: Re: Support EXCEPT for TABLES IN SCHEMA publications
In-Reply-To: <CAHut+Pt-+6Q4-kmhqCZ2_FpaNRR=haJk-GK1eEyTXcTTDnRnXA@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