MIME-Version: 1.0
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>
In-Reply-To: 
 <CABdArM7mJsqFCGe2AyaS-E6n7rk4SoDTSKD+D3hUNO=UaDPStA@mail.gmail.com>
From: Peter Smith <smithpb2250@gmail.com>
Date: Mon, 6 Jul 2026 17:55:19 +1000
Message-ID: 
 <CAHut+Pt-+6Q4-kmhqCZ2_FpaNRR=haJk-GK1eEyTXcTTDnRnXA@mail.gmail.com>
Subject: Re: Support EXCEPT for TABLES IN SCHEMA publications
To: Nisha Moond <nisha.moond412@gmail.com>
Cc: shveta malik <shveta.malik@gmail.com>,
 Amit Kapila <amit.kapila16@gmail.com>,
	Zsolt Parragi <zsolt.parragi@percona.com>, pgsql-hackers@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Archived-At: 
 <https://www.postgresql.org/message-id/CAHut%2BPt-%2B6Q4-kmhqCZ2_FpaNRR%3DhaJk-GK1eEyTXcTTDnRnXA%40mail.gmail.com>
Precedence: bulk

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