public inbox for [email protected]
help / color / mirror / Atom feedFrom: vignesh C <[email protected]>
To: Peter Smith <[email protected]>
Cc: shveta malik <[email protected]>
Cc: Amit Kapila <[email protected]>
Cc: Masahiko Sawada <[email protected]>
Cc: Hayato Kuroda (Fujitsu) <[email protected]>
Cc: Shlok Kyal <[email protected]>
Cc: Nisha Moond <[email protected]>
Cc: Ashutosh Sharma <[email protected]>
Cc: David G. Johnston <[email protected]>
Cc: Dilip Kumar <[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: Fri, 20 Mar 2026 07:51:34 +0530
Message-ID: <CALDaNm3jpYs7ALcU6m5=Li=udidjZoW5dMpyCFs8QHGaf0S8+A@mail.gmail.com> (raw)
In-Reply-To: <CALDaNm1tKuU479T=winBqoMb3MzO3Mta2juk8W3t2R5ps0_zyg@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>
On Wed, 18 Mar 2026 at 18:51, vignesh C <[email protected]> wrote:
>
> On Wed, 18 Mar 2026 at 12:11, Peter Smith <[email protected]> wrote:
> >
> > Hi Vignesh.
> >
> > Some review comments for patch v65-0001.
> >
> > ======
> > doc/src/sgml/ref/alter_publication.sgml
> >
> > 1.
> > It seems that SET ALL TABLES is not supported if the publication
> > already has FOR TABLE.
> >
> > e.g
> > alter publication pub1 set all tables;
> > ERROR: publication "pub1" does not support ALL TABLES operations
> > DETAIL: This operation requires the publication to be defined as FOR
> > ALL TABLES/SEQUENCES or to be empty.
> >
> > e.g.
> > alter publication pub2 set table t1;
> > ERROR: publication "pub2" is defined as FOR ALL TABLES
> > DETAIL: Tables or sequences cannot be added to or dropped from FOR
> > ALL TABLES publications.
> >
> > I am not going to debate what rules are right or wrong. (Some rules do
> > seem a bit ad-hoc to me, but maybe they are just erring on the safety
> > side). But my point is that the documentation does not seem to say
> > anything much about what the rules are
> >
> > e.g. it says "Adding/Setting any schema when the publication also
> > publishes a table with a column list, and vice versa is not
> > supported.", but OTOH it says nothing about what is
> > supported/unsupported for SET ALL TABLES.
>
> Amit has already replied to this at [1].
>
> > ======
> > src/backend/catalog/pg_publication.c
> >
> > 2.
> > +/*
> > + * Returns true if the publication has explicitly included relation (i.e.,
> > + * not marked as EXCEPT).
> > + */
> > +bool
> > +is_table_publication(Oid pubid)
> >
> > To me, an "explicitly included relation" is like when you say "FOR
> > TABLE t1", where the "t1" is explicitly named.
> >
> > So it's not very clear whether you consider "FOR ALL TABLES" or a
> > "FOR TABLES IN SCHEMA" publication explicitly includes tables or not?
> >
> > The function comment needs to be clearer.
>
> Amit has already replied to this at [1].
>
> > ~~~
> >
> > publication_add_relation:
> >
> > 3.
> > + /*
> > + * Determine whether EXCEPT tables require explicit relcache invalidation.
> > + *
> > + * For CREATE PUBLICATION with EXCEPT tables, invalidation is not needed,
> > + * since it is handled when marking the publication as ALL TABLES.
> > + *
> > + * For ALTER PUBLICATION, invalidation is needed only when adding an EXCEPT
> > + * table to a publication already marked as ALL TABLES. For publications
> > + * that were originally empty or defined as ALL SEQUENCES and are being
> > + * converted to ALL TABLES, invalidation is skipped here, as it is handled
> > + * when marking the publication as ALL TABLES.
> > + */
> > + inval_except_table = (stmt != NULL) &&
> > + (stmt->for_all_tables == pub->alltables);
> >
> > 3a.
> > Why is this code done at the top of the function? Can you move it to
> > be adjacent to where it is getting used?
>
> Modified
>
> > ~
> >
> > 3b.
> > I think 'alter_stmt' or 'alter_pub_stmt' might be a more informative
> > name here, instead of the generic 'stmt'
>
> Modified
>
> > ======
> > src/backend/commands/publicationcmds.c
> >
> > 4.
> > - TransformPubWhereClauses(rels, queryString, pubform->pubviaroot);
> > + if (isexcept)
> > + oldrelids = GetExcludedPublicationTables(pubid,
> > + PUBLICATION_PART_ROOT);
> > + else
> > + {
> > + oldrelids = GetIncludedPublicationRelations(pubid,
> > + PUBLICATION_PART_ROOT);
> >
> > I felt there were some subtle things that the logic is using here:
> >
> > e.g.
> > It seems that because this function is called...
> > And because it is SET ....
> > And because it is SET ALL TABLES ...
> > Then, the tables can only be those in the EXCEPT TABLE list
> >
> > Maybe more comments about 'isexcept', and maybe an Assert(oldrelids !=
> > NIL); could help here (??)
>
> Updated comments
>
> > ~~~
> >
> > AlterPublicationAllFlags:
> >
> > 5.
> > + bool nulls[Natts_pg_publication];
> > + bool replaces[Natts_pg_publication];
> > + Datum values[Natts_pg_publication];
> > + bool dirty = false;
> > +
> > + if (!stmt->for_all_tables && !stmt->for_all_sequences)
> > + return;
> > +
> > + pubform = (Form_pg_publication) GETSTRUCT(tup);
> > +
> > + memset(values, 0, sizeof(values));
> > + memset(nulls, false, sizeof(nulls));
> > + memset(replaces, false, sizeof(replaces));
> >
> > AFAIK, these memsets are not needed if you just say "= {0}" where
> > those vars are declared.
>
> modified
>
> > AlterPublication:
> >
> > 6.
> > + relations = list_concat(relations, exceptrelations);
> > AlterPublicationTables(stmt, tup, relations, pstate->p_sourcetext,
> > schemaidlist != NIL);
> >
> > I did not quite understand this list_concat.
> >
> > Is this somehow asserting that `relations` must be empty when there
> > were `exceptrelations` and vice versa, because other combinations are
> > not supported by ALTER -- e.g. is this just a trick to so you can pass
> > the same parameter to AlterPublicationTables? This seems related to
> > the 'isexecpt' AlterPublicationTables function, which was also quite
> > subtle.
> >
> > Bottom line is, I'm unsure what the logic is here, but it appears
> > overly tricky to me. Would more comments help?
>
> Amit has already replied to this at [1].
>
> > ======
> > src/backend/parser/gram.y
> >
> > 7.
> > + * ALL TABLES [ EXCEPT TABLE ( table_name [, ...] ) ]
> >
> > The CREATE/ALTER docs synopsis says "[ ONLY ] table_name [ * ]" which
> > is different from this comment. So are ONLY and * handled properly or
> > not?
>
> It is handled, Is there any issue with the code? It is documented that
> way to keep it consistent with CreatePublicationStmt and ALTER
> PUBLICATION ADD/DROP/SET pub_obj.
>
> > ======
> > src/include/nodes/parsenodes.h
> >
> > 8.
> > + bool for_all_tables; /* True if SET ALL TABLES is specified */
> > + bool for_all_sequences; /* True if SET ALL SEQUENCES is specified */
> >
> > Maybe these comments do not need to mention the keyword "SET".
> >
> > That way, in future if/when ADD/DROP get implemented, then this code
> > won't need to churn again.
>
> Modified
>
> The attached v66 version patch has the changes for the same.
While reviewing the patch I noticed couple of small improvements that
could be done:
1) changed "may be used " to "can be used" as that is the convention followed:
In addition,
+ <literal>SET ALL TABLES</literal> may be used to update the
+ <literal>EXCEPT TABLE</literal> list of a <literal>FOR ALL TABLES</literal>
+ publication.
2) This condition was slightly difficult to understand while reading,
simplified it:
Changed:
- if (!pri->except)
+ inval_except_table = (alter_stmt != NULL) &&
+ (alter_stmt->for_all_tables == pub->alltables);
to:
- if (!pri->except)
+ inval_except_table = (alter_stmt != NULL) && pub->alltables &&
+ (alter_stmt->for_all_tables && pri->except);
The attached v67 patch has the changes for the same.
Regards,
Vignesh
Attachments:
[application/octet-stream] v67-0001-Add-support-for-EXCEPT-TABLE-in-ALTER-PUBLICATIO.patch (38.0K, 2-v67-0001-Add-support-for-EXCEPT-TABLE-in-ALTER-PUBLICATIO.patch)
download | inline diff:
From 4a31cf4bff29f8864bf298c3b6381b2075fe4774 Mon Sep 17 00:00:00 2001
From: Vignesh C <[email protected]>
Date: Wed, 4 Mar 2026 16:28:32 +0530
Subject: [PATCH v67] Add support for EXCEPT TABLE in ALTER PUBLICATION.
Following commit fd366065e0, which added EXCEPT TABLE support to
CREATE PUBLICATION, this commit extends ALTER PUBLICATION to allow
modifying the exclusion list.
New Syntax:
ALTER PUBLICATION name SET publication_all_object [, ... ]
where publication_all_object is one of:
ALL TABLES [ EXCEPT TABLE ( except_table_object [, ... ] ) ]
ALL SEQUENCES
The SET ALL TABLES clause updates pg_publication.puballtables to true.
If the EXCEPT clause is provided, the existing exclusion list in
pg_publication_rel is replaced with the specified relations. If the
EXCEPT clause is omitted, any existing exclusions for the publication
are cleared. Similarly, SET ALL SEQUENCES updates
pg_publication.puballsequences to true.
Note that because this is a SET command, specifying only one object
type (e.g., SET ALL SEQUENCES) will reset the other unspecified flags
(e.g., setting puballtables to false).
Consistent with CREATE PUBLICATION, only root partitioned tables or
standard tables can be specified in the EXCEPT list. Specifying a
partition child will result in an error.
Author: vignesh C <[email protected]>
Reviewed-by: shveta malik <[email protected]>
Reviewed-by: Amit Kapila <[email protected]>
Reviewed-by: Peter Smith <[email protected]>
Reviewed-by: Nisha Moond <[email protected]>
Discussion: https://postgr.es/m/CALDaNm3=JrucjhiiwsYQw5-PGtBHFONa6F7hhWCXMsGvh=tamA@mail.gmail.com
---
doc/src/sgml/ref/alter_publication.sgml | 79 ++++++++++++---
src/backend/catalog/pg_publication.c | 66 +++++++++++-
src/backend/commands/publicationcmds.c | 116 ++++++++++++++++++++--
src/backend/commands/tablecmds.c | 3 +-
src/backend/parser/gram.y | 23 +++++
src/bin/psql/tab-complete.in.c | 13 ++-
src/include/catalog/pg_publication.h | 4 +-
src/include/nodes/parsenodes.h | 2 +
src/test/regress/expected/publication.out | 90 ++++++++++++++++-
src/test/regress/sql/publication.sql | 54 +++++++++-
src/test/subscription/t/037_except.pl | 36 ++++++-
11 files changed, 449 insertions(+), 37 deletions(-)
diff --git a/doc/src/sgml/ref/alter_publication.sgml b/doc/src/sgml/ref/alter_publication.sgml
index 028770f2149..7f0e46380cc 100644
--- a/doc/src/sgml/ref/alter_publication.sgml
+++ b/doc/src/sgml/ref/alter_publication.sgml
@@ -22,8 +22,8 @@ PostgreSQL documentation
<refsynopsisdiv>
<synopsis>
ALTER PUBLICATION <replaceable class="parameter">name</replaceable> ADD <replaceable class="parameter">publication_object</replaceable> [, ...]
-ALTER PUBLICATION <replaceable class="parameter">name</replaceable> SET <replaceable class="parameter">publication_object</replaceable> [, ...]
ALTER PUBLICATION <replaceable class="parameter">name</replaceable> DROP <replaceable class="parameter">publication_drop_object</replaceable> [, ...]
+ALTER PUBLICATION <replaceable class="parameter">name</replaceable> SET { <replaceable class="parameter">publication_object</replaceable> [, ...] | <replaceable class="parameter">publication_all_object</replaceable> [, ... ] }
ALTER PUBLICATION <replaceable class="parameter">name</replaceable> SET ( <replaceable class="parameter">publication_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] )
ALTER PUBLICATION <replaceable class="parameter">name</replaceable> OWNER TO { <replaceable>new_owner</replaceable> | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RENAME TO <replaceable>new_name</replaceable>
@@ -33,6 +33,11 @@ ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RENAME TO <r
TABLE <replaceable class="parameter">table_and_columns</replaceable> [, ... ]
TABLES IN SCHEMA { <replaceable class="parameter">schema_name</replaceable> | CURRENT_SCHEMA } [, ... ]
+<phrase>and <replaceable class="parameter">publication_all_object</replaceable> is one of:</phrase>
+
+ ALL TABLES [ EXCEPT TABLE ( <replaceable class="parameter">except_table_object</replaceable> [, ... ] ) ]
+ ALL SEQUENCES
+
<phrase>and <replaceable class="parameter">publication_drop_object</replaceable> is one of:</phrase>
TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [, ... ]
@@ -41,6 +46,10 @@ ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RENAME TO <r
<phrase>and <replaceable class="parameter">table_and_columns</replaceable> is:</phrase>
[ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [ ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) ] [ WHERE ( <replaceable class="parameter">expression</replaceable> ) ]
+
+<phrase>and <replaceable class="parameter">except_table_object</replaceable> is:</phrase>
+
+ [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ]
</synopsis>
</refsynopsisdiv>
@@ -53,18 +62,46 @@ ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RENAME TO <r
</para>
<para>
- The first three variants change which tables/schemas are part of the
- publication. The <literal>SET</literal> clause will replace the list of
- tables/schemas in the publication with the specified list; the existing
- tables/schemas that were present in the publication will be removed. The
- <literal>ADD</literal> and <literal>DROP</literal> clauses will add and
- remove one or more tables/schemas from the publication. Note that adding
- tables/schemas to a publication that is already subscribed to will require an
+ The first two variants modify which tables/schemas are part of the
+ publication. The <literal>ADD</literal> and <literal>DROP</literal> clauses
+ will add and remove one or more tables/schemas from the publication.
+ </para>
+
+ <para>
+ The third variant either modifies the included tables/schemas
+ or marks the publication as <literal>FOR ALL SEQUENCES</literal> or
+ <literal>FOR ALL TABLES</literal>, optionally using
+ <literal>EXCEPT TABLE</literal> to exclude specific tables. The
+ <literal>SET ALL TABLES</literal> clause can transform an empty publication,
+ or one defined for <literal>ALL SEQUENCES</literal> (or both
+ <literal>ALL TABLES</literal> and <literal>ALL SEQUENCES</literal>), into
+ a publication defined for ALL TABLES. Likewise,
+ <literal>SET ALL SEQUENCES</literal> can convert an empty publication, or
+ one defined for <literal>ALL TABLES</literal> (or both
+ <literal>ALL TABLES</literal> and <literal>ALL SEQUENCES</literal>), into a
+ publication defined for <literal>ALL SEQUENCES</literal>. In addition,
+ <literal>SET ALL TABLES</literal> can be used to update the
+ <literal>EXCEPT TABLE</literal> list of a <literal>FOR ALL TABLES</literal>
+ publication. If <literal>EXCEPT TABLE</literal> is specified with a list of
+ tables, the existing exclusion list is replaced with the specified tables.
+ If <literal>EXCEPT TABLE</literal> is omitted, the existing exclusion list
+ is cleared. The <literal>SET</literal> clause, when used with a publication
+ defined with <literal>FOR TABLE</literal> or
+ <literal>FOR TABLES IN SCHEMA</literal>, replaces the list of tables/schemas
+ in the publication with the specified list; the existing tables or schemas
+ that were present in the publication will be removed.
+ </para>
+
+ <para>
+ Note that adding tables/except tables/schemas to a publication that is
+ already subscribed to will require an
<link linkend="sql-altersubscription-params-refresh-publication">
- <literal>ALTER SUBSCRIPTION ... REFRESH PUBLICATION</literal></link> action on the
- subscribing side in order to become effective. Note also that
- <literal>DROP TABLES IN SCHEMA</literal> will not drop any schema tables
- that were specified using
+ <literal>ALTER SUBSCRIPTION ... REFRESH PUBLICATION</literal></link> action
+ on the subscribing side in order to become effective. Likewise altering a
+ publication to set <literal>ALL TABLES</literal> or to set or unset
+ <literal>ALL SEQUENCES</literal> also requires the subscriber to refresh the
+ publication. Note also that <literal>DROP TABLES IN SCHEMA</literal> will
+ not drop any schema tables that were specified using
<link linkend="sql-createpublication-params-for-table"><literal>FOR TABLE</literal></link>/
<literal>ADD TABLE</literal>.
</para>
@@ -83,8 +120,9 @@ ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RENAME TO <r
<para>
You must own the publication to use <command>ALTER PUBLICATION</command>.
Adding a table to a publication additionally requires owning that table.
- The <literal>ADD TABLES IN SCHEMA</literal> and
- <literal>SET TABLES IN SCHEMA</literal> to a publication requires the
+ The <literal>ADD TABLES IN SCHEMA</literal>,
+ <literal>SET TABLES IN SCHEMA</literal>, <literal>SET ALL TABLES</literal>,
+ and <literal>SET ALL SEQUENCES</literal> to a publication requires the
invoking user to be a superuser.
To alter the owner, you must be able to <literal>SET ROLE</literal> to the
new owning role, and that role must have <literal>CREATE</literal>
@@ -222,6 +260,19 @@ ALTER PUBLICATION mypublication ADD TABLE users (user_id, firstname), department
Change the set of columns published for a table:
<programlisting>
ALTER PUBLICATION mypublication SET TABLE users (user_id, firstname, lastname), TABLE departments;
+</programlisting></para>
+
+ <para>
+ Replace the publication's EXCEPT TABLE list:
+<programlisting>
+ALTER PUBLICATION mypublication SET ALL TABLES EXCEPT TABLE (users, departments);
+</programlisting></para>
+
+ <para>
+ Reset the publication to be a FOR ALL TABLES publication with no excluded
+ tables:
+<programlisting>
+ALTER PUBLICATION mypublication SET ALL TABLES;
</programlisting></para>
<para>
diff --git a/src/backend/catalog/pg_publication.c b/src/backend/catalog/pg_publication.c
index a79157c43bf..c92ff3f51c3 100644
--- a/src/backend/catalog/pg_publication.c
+++ b/src/backend/catalog/pg_publication.c
@@ -270,6 +270,49 @@ is_schema_publication(Oid pubid)
return result;
}
+/*
+ * Returns true if the publication has explicitly included relation (i.e.,
+ * not marked as EXCEPT).
+ */
+bool
+is_table_publication(Oid pubid)
+{
+ Relation pubrelsrel;
+ ScanKeyData scankey;
+ SysScanDesc scan;
+ HeapTuple tup;
+ bool result = false;
+
+ pubrelsrel = table_open(PublicationRelRelationId, AccessShareLock);
+ ScanKeyInit(&scankey,
+ Anum_pg_publication_rel_prpubid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(pubid));
+
+ scan = systable_beginscan(pubrelsrel,
+ PublicationRelPrpubidIndexId,
+ true, NULL, 1, &scankey);
+ tup = systable_getnext(scan);
+ if (HeapTupleIsValid(tup))
+ {
+ Form_pg_publication_rel pubrel;
+
+ pubrel = (Form_pg_publication_rel) GETSTRUCT(tup);
+
+ /*
+ * For any publication, pg_publication_rel contains either only EXCEPT
+ * entries or only explicitly included tables. Therefore, examining
+ * the first tuple is sufficient to determine table inclusion.
+ */
+ result = !pubrel->prexcept;
+ }
+
+ systable_endscan(scan);
+ table_close(pubrelsrel, AccessShareLock);
+
+ return result;
+}
+
/*
* Returns true if the relation has column list associated with the
* publication, false otherwise.
@@ -440,7 +483,7 @@ attnumstoint2vector(Bitmapset *attrs)
*/
ObjectAddress
publication_add_relation(Oid pubid, PublicationRelInfo *pri,
- bool if_not_exists)
+ bool if_not_exists, AlterPublicationStmt *alter_stmt)
{
Relation rel;
HeapTuple tup;
@@ -455,6 +498,7 @@ publication_add_relation(Oid pubid, PublicationRelInfo *pri,
referenced;
List *relids = NIL;
int i;
+ bool inval_except_table;
rel = table_open(PublicationRelRelationId, RowExclusiveLock);
@@ -543,11 +587,23 @@ publication_add_relation(Oid pubid, PublicationRelInfo *pri,
table_close(rel, RowExclusiveLock);
/*
- * Relations excluded via the EXCEPT clause do not need explicit
- * invalidation as CreatePublication() function invalidates all relations
- * as part of defining a FOR ALL TABLES publication.
+ * Determine whether EXCEPT tables require explicit relcache invalidation.
+ *
+ * For CREATE PUBLICATION with EXCEPT tables, invalidation is skipped
+ * here, as CreatePublication() function invalidates all relations as part
+ * of defining a FOR ALL TABLES publication.
+ *
+ * For ALTER PUBLICATION, invalidation is needed only when adding an
+ * EXCEPT table to a publication already marked as ALL TABLES. For
+ * publications that were originally empty or defined as ALL SEQUENCES and
+ * are being converted to ALL TABLES, invalidation is skipped here, as
+ * AlterPublicationAllFlags() function invalidates all relations while
+ * marking the publication as ALL TABLES publication.
*/
- if (!pri->except)
+ inval_except_table = (alter_stmt != NULL) && pub->alltables &&
+ (alter_stmt->for_all_tables && pri->except);
+
+ if (!pri->except || inval_except_table)
{
/*
* Invalidate relcache so that publication info is rebuilt.
diff --git a/src/backend/commands/publicationcmds.c b/src/backend/commands/publicationcmds.c
index 6a3ca4751fa..9a4f70276b4 100644
--- a/src/backend/commands/publicationcmds.c
+++ b/src/backend/commands/publicationcmds.c
@@ -1272,15 +1272,37 @@ AlterPublicationTables(AlterPublicationStmt *stmt, HeapTuple tup,
PublicationDropTables(pubid, rels, false);
else /* AP_SetObjects */
{
- List *oldrelids = GetIncludedPublicationRelations(pubid,
- PUBLICATION_PART_ROOT);
+ List *oldrelids = NIL;
List *delrels = NIL;
ListCell *oldlc;
- TransformPubWhereClauses(rels, queryString, pubform->pubviaroot);
+ if (stmt->for_all_tables || stmt->for_all_sequences)
+ {
+ /*
+ * In FOR ALL TABLES mode, relations are tracked as exclusions
+ * (EXCEPT TABLES). Fetch the current excluded relations so they
+ * can be reconciled with the specified EXCEPT list.
+ *
+ * This applies only if the existing publication is already
+ * defined as FOR ALL TABLES; otherwise, there are no exclusion
+ * entries to process.
+ */
+ if (pubform->puballtables)
+ {
+ oldrelids = GetExcludedPublicationTables(pubid,
+ PUBLICATION_PART_ROOT);
+ }
+ }
+ else
+ {
+ oldrelids = GetIncludedPublicationRelations(pubid,
+ PUBLICATION_PART_ROOT);
- CheckPubRelationColumnList(stmt->pubname, rels, publish_schema,
- pubform->pubviaroot);
+ TransformPubWhereClauses(rels, queryString, pubform->pubviaroot);
+
+ CheckPubRelationColumnList(stmt->pubname, rels, publish_schema,
+ pubform->pubviaroot);
+ }
/*
* To recreate the relation list for the publication, look for
@@ -1498,6 +1520,16 @@ CheckAlterPublication(AlterPublicationStmt *stmt, HeapTuple tup,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
errmsg("must be superuser to add or set schemas")));
+ if (stmt->for_all_tables && !superuser())
+ ereport(ERROR,
+ errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("must be superuser to set ALL TABLES"));
+
+ if (stmt->for_all_sequences && !superuser())
+ ereport(ERROR,
+ errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("must be superuser to set ALL SEQUENCES"));
+
/*
* Check that user is allowed to manipulate the publication tables in
* schema
@@ -1546,6 +1578,73 @@ CheckAlterPublication(AlterPublicationStmt *stmt, HeapTuple tup,
NameStr(pubform->pubname)),
errdetail("Tables or sequences cannot be added to or dropped from FOR ALL SEQUENCES publications."));
}
+
+ if (stmt->for_all_tables || stmt->for_all_sequences)
+ {
+ /*
+ * If the publication already contains specific tables or schemas, we
+ * prevent switching to a ALL state unless the publication is empty.
+ */
+ if (is_table_publication(pubform->oid) ||
+ is_schema_publication(pubform->oid))
+ {
+ ereport(ERROR,
+ errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ stmt->for_all_tables ?
+ errmsg("publication \"%s\" does not support ALL TABLES operations", NameStr(pubform->pubname)) :
+ errmsg("publication \"%s\" does not support ALL SEQUENCES operations", NameStr(pubform->pubname)),
+ errdetail("This operation requires the publication to be defined as FOR ALL TABLES/SEQUENCES or to be empty."));
+ }
+ }
+}
+
+/*
+ * Update FOR ALL TABLES / FOR ALL SEQUENCES flags of a publication.
+ */
+static void
+AlterPublicationAllFlags(AlterPublicationStmt *stmt, Relation rel,
+ HeapTuple tup)
+{
+ Form_pg_publication pubform;
+ bool nulls[Natts_pg_publication] = {0};
+ bool replaces[Natts_pg_publication] = {0};
+ Datum values[Natts_pg_publication] = {0};
+ bool dirty = false;
+
+ if (!stmt->for_all_tables && !stmt->for_all_sequences)
+ return;
+
+ pubform = (Form_pg_publication) GETSTRUCT(tup);
+
+ /* Update FOR ALL TABLES flag if changed */
+ if (stmt->for_all_tables != pubform->puballtables)
+ {
+ values[Anum_pg_publication_puballtables - 1] =
+ BoolGetDatum(stmt->for_all_tables);
+ replaces[Anum_pg_publication_puballtables - 1] = true;
+ dirty = true;
+ }
+
+ /* Update FOR ALL SEQUENCES flag if changed */
+ if (stmt->for_all_sequences != pubform->puballsequences)
+ {
+ values[Anum_pg_publication_puballsequences - 1] =
+ BoolGetDatum(stmt->for_all_sequences);
+ replaces[Anum_pg_publication_puballsequences - 1] = true;
+ dirty = true;
+ }
+
+ if (dirty)
+ {
+ tup = heap_modify_tuple(tup, RelationGetDescr(rel), values,
+ nulls, replaces);
+ CatalogTupleUpdate(rel, &tup->t_self, tup);
+ CommandCounterIncrement();
+
+ /* For ALL TABLES, we must invalidate all relcache entries */
+ if (replaces[Anum_pg_publication_puballtables - 1])
+ CacheInvalidateRelcacheAll();
+ }
}
/*
@@ -1591,9 +1690,6 @@ AlterPublication(ParseState *pstate, AlterPublicationStmt *stmt)
ObjectsInPublicationToOids(stmt->pubobjects, pstate, &relations,
&exceptrelations, &schemaidlist);
- /* EXCEPT clause is not supported with ALTER PUBLICATION */
- Assert(exceptrelations == NIL);
-
CheckAlterPublication(stmt, tup, relations, schemaidlist);
heap_freetuple(tup);
@@ -1615,9 +1711,11 @@ AlterPublication(ParseState *pstate, AlterPublicationStmt *stmt)
errmsg("publication \"%s\" does not exist",
stmt->pubname));
+ relations = list_concat(relations, exceptrelations);
AlterPublicationTables(stmt, tup, relations, pstate->p_sourcetext,
schemaidlist != NIL);
AlterPublicationSchemas(stmt, tup, schemaidlist);
+ AlterPublicationAllFlags(stmt, rel, tup);
}
/* Cleanup. */
@@ -1953,7 +2051,7 @@ PublicationAddTables(Oid pubid, List *rels, bool if_not_exists,
aclcheck_error(ACLCHECK_NOT_OWNER, get_relkind_objtype(rel->rd_rel->relkind),
RelationGetRelationName(rel));
- obj = publication_add_relation(pubid, pub_rel, if_not_exists);
+ obj = publication_add_relation(pubid, pub_rel, if_not_exists, stmt);
if (stmt)
{
EventTriggerCollectSimpleCommand(obj, InvalidObjectAddress,
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 67e42e5df29..1415c0a243f 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -20614,7 +20614,8 @@ ATExecAttachPartition(List **wqueue, Relation rel, PartitionCmd *cmd,
list_length(exceptpuboids),
RelationGetRelationName(attachrel),
pubnames.data),
- errdetail("The publication EXCEPT clause cannot contain tables that are partitions."));
+ errdetail("The publication EXCEPT clause cannot contain tables that are partitions."),
+ errhint("Modify the publication's EXCEPT clause using ALTER PUBLICATION ... SET ALL TABLES."));
}
list_free(exceptpuboids);
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 0b4a4911370..92ded5307b3 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -11445,11 +11445,18 @@ pub_except_obj_list: PublicationExceptObjSpec
*
* ALTER PUBLICATION name SET pub_obj [, ...]
*
+ * ALTER PUBLICATION name SET pub_all_obj_type [, ...]
+ *
* pub_obj is one of:
*
* TABLE table_name [, ...]
* TABLES IN SCHEMA schema_name [, ...]
*
+ * pub_all_obj_type is one of:
+ *
+ * ALL TABLES [ EXCEPT TABLE ( table_name [, ...] ) ]
+ * ALL SEQUENCES
+ *
*****************************************************************************/
AlterPublicationStmt:
@@ -11459,6 +11466,7 @@ AlterPublicationStmt:
n->pubname = $3;
n->options = $5;
+ n->for_all_tables = false;
$$ = (Node *) n;
}
| ALTER PUBLICATION name ADD_P pub_obj_list
@@ -11469,6 +11477,7 @@ AlterPublicationStmt:
n->pubobjects = $5;
preprocess_pubobj_list(n->pubobjects, yyscanner);
n->action = AP_AddObjects;
+ n->for_all_tables = false;
$$ = (Node *) n;
}
| ALTER PUBLICATION name SET pub_obj_list
@@ -11479,6 +11488,19 @@ AlterPublicationStmt:
n->pubobjects = $5;
preprocess_pubobj_list(n->pubobjects, yyscanner);
n->action = AP_SetObjects;
+ n->for_all_tables = false;
+ $$ = (Node *) n;
+ }
+ | ALTER PUBLICATION name SET pub_all_obj_type_list
+ {
+ AlterPublicationStmt *n = makeNode(AlterPublicationStmt);
+
+ n->pubname = $3;
+ n->action = AP_SetObjects;
+ preprocess_pub_all_objtype_list($5, &n->pubobjects,
+ &n->for_all_tables,
+ &n->for_all_sequences,
+ yyscanner);
$$ = (Node *) n;
}
| ALTER PUBLICATION name DROP pub_obj_list
@@ -11489,6 +11511,7 @@ AlterPublicationStmt:
n->pubobjects = $5;
preprocess_pubobj_list(n->pubobjects, yyscanner);
n->action = AP_DropObjects;
+ n->for_all_tables = false;
$$ = (Node *) n;
}
;
diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index 5bdbf1530a2..2f674764cad 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -2329,7 +2329,18 @@ match_previous_words(int pattern_id,
COMPLETE_WITH("TABLES IN SCHEMA", "TABLE");
/* ALTER PUBLICATION <name> SET */
else if (Matches("ALTER", "PUBLICATION", MatchAny, "SET"))
- COMPLETE_WITH("(", "TABLES IN SCHEMA", "TABLE");
+ COMPLETE_WITH("(", "ALL SEQUENCES", "ALL TABLES", "TABLES IN SCHEMA", "TABLE");
+ else if (Matches("ALTER", "PUBLICATION", MatchAny, "SET", "ALL"))
+ COMPLETE_WITH("SEQUENCES", "TABLES");
+ else if (Matches("ALTER", "PUBLICATION", MatchAny, "SET", "ALL", "TABLES"))
+ COMPLETE_WITH("EXCEPT TABLE (");
+ else if (Matches("ALTER", "PUBLICATION", MatchAny, "SET", "ALL", "TABLES", "EXCEPT"))
+ COMPLETE_WITH("TABLE (");
+ else if (Matches("ALTER", "PUBLICATION", MatchAny, "SET", "ALL", "TABLES", "EXCEPT", "TABLE"))
+ COMPLETE_WITH("(");
+ /* Complete "ALTER PUBLICATION <name> FOR TABLE" with "<table>, ..." */
+ else if (Matches("ALTER", "PUBLICATION", MatchAny, "SET", "ALL", "TABLES", "EXCEPT", "TABLE", "("))
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables);
else if (Matches("ALTER", "PUBLICATION", MatchAny, "ADD|DROP|SET", "TABLES", "IN", "SCHEMA"))
COMPLETE_WITH_QUERY_PLUS(Query_for_list_of_schemas
" AND nspname NOT LIKE E'pg\\\\_%%'",
diff --git a/src/include/catalog/pg_publication.h b/src/include/catalog/pg_publication.h
index e25228713e7..89b4bb14f62 100644
--- a/src/include/catalog/pg_publication.h
+++ b/src/include/catalog/pg_publication.h
@@ -195,10 +195,12 @@ extern Oid GetTopMostAncestorInPublication(Oid puboid, List *ancestors,
extern bool is_publishable_relation(Relation rel);
extern bool is_schema_publication(Oid pubid);
+extern bool is_table_publication(Oid pubid);
extern bool check_and_fetch_column_list(Publication *pub, Oid relid,
MemoryContext mcxt, Bitmapset **cols);
extern ObjectAddress publication_add_relation(Oid pubid, PublicationRelInfo *pri,
- bool if_not_exists);
+ bool if_not_exists,
+ AlterPublicationStmt *alter_stmt);
extern Bitmapset *pub_collist_validate(Relation targetrel, List *columns);
extern ObjectAddress publication_add_schema(Oid pubid, Oid schemaid,
bool if_not_exists);
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index ffadd667167..31f9f7fff65 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -4525,6 +4525,8 @@ typedef struct AlterPublicationStmt
List *pubobjects; /* Optional list of publication objects */
AlterPublicationAction action; /* What action to perform with the given
* objects */
+ bool for_all_tables; /* True if ALL TABLES is specified */
+ bool for_all_sequences; /* True if ALL SEQUENCES is specified */
} AlterPublicationStmt;
typedef struct CreateSubscriptionStmt
diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out
index 681d2564ed5..b47626b503a 100644
--- a/src/test/regress/expected/publication.out
+++ b/src/test/regress/expected/publication.out
@@ -254,9 +254,81 @@ Except Publications:
"testpub_foralltables_excepttable"
"testpub_foralltables_excepttable1"
+---------------------------------------------
+-- SET ALL TABLES/SEQUENCES
+---------------------------------------------
+-- Replace the existing EXCEPT TABLE list (testpub_tbl1) with a new
+-- EXCEPT TABLE list containing only (testpub_tbl2).
+ALTER PUBLICATION testpub_foralltables_excepttable SET ALL TABLES EXCEPT TABLE (testpub_tbl2);
+\dRp+ testpub_foralltables_excepttable
+ Publication testpub_foralltables_excepttable
+ 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 |
+Except tables:
+ "public.testpub_tbl2"
+
+-- Clear the EXCEPT TABLE list, making the publication include all tables.
+ALTER PUBLICATION testpub_foralltables_excepttable SET ALL TABLES;
+\dRp+ testpub_foralltables_excepttable
+ Publication testpub_foralltables_excepttable
+ 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)
+
+-- Create an empty publication for subsequent tests.
+CREATE PUBLICATION testpub_forall_tbls_seqs;
+-- Enable both puballtables and puballsequences
+ALTER PUBLICATION testpub_forall_tbls_seqs SET ALL TABLES, ALL SEQUENCES;
+\dRp+ testpub_forall_tbls_seqs
+ Publication testpub_forall_tbls_seqs
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+-------------
+ regress_publication_user | t | t | t | t | t | t | none | f |
+(1 row)
+
+-- Explicitly test that SET ALL TABLES resets puballsequences to false
+-- Result should be: puballtables = true, puballsequences = false
+ALTER PUBLICATION testpub_forall_tbls_seqs SET ALL TABLES;
+\dRp+ testpub_forall_tbls_seqs
+ Publication testpub_forall_tbls_seqs
+ 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)
+
+-- Explicitly test that SET ALL SEQUENCES resets puballtables to false
+-- Result should be: puballtables = false, puballsequences = true
+ALTER PUBLICATION testpub_forall_tbls_seqs SET ALL SEQUENCES;
+\dRp+ testpub_forall_tbls_seqs
+ Publication testpub_forall_tbls_seqs
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+-------------
+ regress_publication_user | f | t | t | t | t | t | none | f |
+(1 row)
+
+-- fail - SET ALL TABLES/SEQUENCES is not allowed for a 'FOR TABLE' publication
+ALTER PUBLICATION testpub_fortable SET ALL TABLES EXCEPT TABLE (testpub_tbl1);
+ERROR: publication "testpub_fortable" does not support ALL TABLES operations
+DETAIL: This operation requires the publication to be defined as FOR ALL TABLES/SEQUENCES or to be empty.
+ALTER PUBLICATION testpub_fortable SET ALL TABLES;
+ERROR: publication "testpub_fortable" does not support ALL TABLES operations
+DETAIL: This operation requires the publication to be defined as FOR ALL TABLES/SEQUENCES or to be empty.
+ALTER PUBLICATION testpub_fortable SET ALL SEQUENCES;
+ERROR: publication "testpub_fortable" does not support ALL SEQUENCES operations
+DETAIL: This operation requires the publication to be defined as FOR ALL TABLES/SEQUENCES or to be empty.
+-- fail - SET ALL TABLES is not allowed for a schema publication
+ALTER PUBLICATION testpub_forschema SET ALL TABLES EXCEPT TABLE (pub_test.testpub_nopk);
+ERROR: publication "testpub_forschema" does not support ALL TABLES operations
+DETAIL: This operation requires the publication to be defined as FOR ALL TABLES/SEQUENCES or to be empty.
+ALTER PUBLICATION testpub_forschema SET ALL TABLES;
+ERROR: publication "testpub_forschema" does not support ALL TABLES operations
+DETAIL: This operation requires the publication to be defined as FOR ALL TABLES/SEQUENCES or to be empty.
RESET client_min_messages;
DROP TABLE testpub_tbl2;
-DROP PUBLICATION testpub_foralltables, testpub_fortable, testpub_forschema, testpub_for_tbl_schema, testpub_foralltables_excepttable, testpub_foralltables_excepttable1;
+DROP PUBLICATION testpub_foralltables, testpub_fortable, testpub_forschema, testpub_for_tbl_schema;
+DROP PUBLICATION testpub_forall_tbls_seqs, testpub_foralltables_excepttable, testpub_foralltables_excepttable1;
---------------------------------------------
-- Tests for inherited tables, and
-- EXCEPT TABLE tests for inherited tables
@@ -361,6 +433,7 @@ CREATE TABLE tab_main (a int) PARTITION BY RANGE(a);
ALTER TABLE tab_main ATTACH PARTITION testpub_root FOR VALUES FROM (0) TO (200);
ERROR: cannot attach table "testpub_root" as partition because it is referenced in publication "testpub8" EXCEPT clause
DETAIL: The publication EXCEPT clause cannot contain tables that are partitions.
+HINT: Modify the publication's EXCEPT clause using ALTER PUBLICATION ... SET ALL TABLES.
RESET client_min_messages;
DROP TABLE testpub_root, testpub_part1, tab_main;
DROP PUBLICATION testpub8;
@@ -1541,7 +1614,20 @@ ERROR: permission denied to change owner of publication "testpub4"
HINT: The owner of a FOR ALL TABLES or ALL SEQUENCES or TABLES IN SCHEMA publication must be a superuser.
ALTER PUBLICATION testpub4 owner to regress_publication_user; -- ok
SET ROLE regress_publication_user;
-DROP PUBLICATION testpub4;
+SET client_min_messages = 'ERROR';
+CREATE PUBLICATION testpub5 FOR ALL TABLES;
+RESET client_min_messages;
+ALTER PUBLICATION testpub5 OWNER TO regress_publication_user3;
+SET ROLE regress_publication_user3;
+-- fail - SET ALL TABLES/SEQUENCES on a publication requires superuser privileges
+ALTER PUBLICATION testpub5 SET ALL TABLES EXCEPT TABLE (testpub_tbl1); -- fail
+ERROR: must be superuser to set ALL TABLES
+ALTER PUBLICATION testpub5 SET ALL TABLES; -- fail
+ERROR: must be superuser to set ALL TABLES
+ALTER PUBLICATION testpub5 SET ALL SEQUENCES; -- fail
+ERROR: must be superuser to set ALL SEQUENCES
+SET ROLE regress_publication_user;
+DROP PUBLICATION testpub4, testpub5;
DROP ROLE regress_publication_user3;
REVOKE CREATE ON DATABASE regression FROM regress_publication_user2;
DROP TABLE testpub_parted;
diff --git a/src/test/regress/sql/publication.sql b/src/test/regress/sql/publication.sql
index 405579dad52..f8c5932c908 100644
--- a/src/test/regress/sql/publication.sql
+++ b/src/test/regress/sql/publication.sql
@@ -119,9 +119,48 @@ CREATE PUBLICATION testpub_foralltables_excepttable1 FOR ALL TABLES EXCEPT TABLE
-- in the EXCEPT TABLE clause
\d testpub_tbl1
+---------------------------------------------
+-- SET ALL TABLES/SEQUENCES
+---------------------------------------------
+-- Replace the existing EXCEPT TABLE list (testpub_tbl1) with a new
+-- EXCEPT TABLE list containing only (testpub_tbl2).
+ALTER PUBLICATION testpub_foralltables_excepttable SET ALL TABLES EXCEPT TABLE (testpub_tbl2);
+\dRp+ testpub_foralltables_excepttable
+
+-- Clear the EXCEPT TABLE list, making the publication include all tables.
+ALTER PUBLICATION testpub_foralltables_excepttable SET ALL TABLES;
+\dRp+ testpub_foralltables_excepttable
+
+-- Create an empty publication for subsequent tests.
+CREATE PUBLICATION testpub_forall_tbls_seqs;
+
+-- Enable both puballtables and puballsequences
+ALTER PUBLICATION testpub_forall_tbls_seqs SET ALL TABLES, ALL SEQUENCES;
+\dRp+ testpub_forall_tbls_seqs
+
+-- Explicitly test that SET ALL TABLES resets puballsequences to false
+-- Result should be: puballtables = true, puballsequences = false
+ALTER PUBLICATION testpub_forall_tbls_seqs SET ALL TABLES;
+\dRp+ testpub_forall_tbls_seqs
+
+-- Explicitly test that SET ALL SEQUENCES resets puballtables to false
+-- Result should be: puballtables = false, puballsequences = true
+ALTER PUBLICATION testpub_forall_tbls_seqs SET ALL SEQUENCES;
+\dRp+ testpub_forall_tbls_seqs
+
+-- fail - SET ALL TABLES/SEQUENCES is not allowed for a 'FOR TABLE' publication
+ALTER PUBLICATION testpub_fortable SET ALL TABLES EXCEPT TABLE (testpub_tbl1);
+ALTER PUBLICATION testpub_fortable SET ALL TABLES;
+ALTER PUBLICATION testpub_fortable SET ALL SEQUENCES;
+
+-- fail - SET ALL TABLES is not allowed for a schema publication
+ALTER PUBLICATION testpub_forschema SET ALL TABLES EXCEPT TABLE (pub_test.testpub_nopk);
+ALTER PUBLICATION testpub_forschema SET ALL TABLES;
+
RESET client_min_messages;
DROP TABLE testpub_tbl2;
-DROP PUBLICATION testpub_foralltables, testpub_fortable, testpub_forschema, testpub_for_tbl_schema, testpub_foralltables_excepttable, testpub_foralltables_excepttable1;
+DROP PUBLICATION testpub_foralltables, testpub_fortable, testpub_forschema, testpub_for_tbl_schema;
+DROP PUBLICATION testpub_forall_tbls_seqs, testpub_foralltables_excepttable, testpub_foralltables_excepttable1;
---------------------------------------------
-- Tests for inherited tables, and
@@ -991,7 +1030,18 @@ ALTER PUBLICATION testpub4 owner to regress_publication_user2; -- fail
ALTER PUBLICATION testpub4 owner to regress_publication_user; -- ok
SET ROLE regress_publication_user;
-DROP PUBLICATION testpub4;
+SET client_min_messages = 'ERROR';
+CREATE PUBLICATION testpub5 FOR ALL TABLES;
+RESET client_min_messages;
+ALTER PUBLICATION testpub5 OWNER TO regress_publication_user3;
+SET ROLE regress_publication_user3;
+-- fail - SET ALL TABLES/SEQUENCES on a publication requires superuser privileges
+ALTER PUBLICATION testpub5 SET ALL TABLES EXCEPT TABLE (testpub_tbl1); -- fail
+ALTER PUBLICATION testpub5 SET ALL TABLES; -- fail
+ALTER PUBLICATION testpub5 SET ALL SEQUENCES; -- fail
+
+SET ROLE regress_publication_user;
+DROP PUBLICATION testpub4, testpub5;
DROP ROLE regress_publication_user3;
REVOKE CREATE ON DATABASE regression FROM regress_publication_user2;
diff --git a/src/test/subscription/t/037_except.pl b/src/test/subscription/t/037_except.pl
index 2729df4d5c0..13b99eda258 100644
--- a/src/test/subscription/t/037_except.pl
+++ b/src/test/subscription/t/037_except.pl
@@ -152,18 +152,50 @@ $result =
$node_subscriber->safe_psql('postgres', "SELECT count(*) FROM child1");
is($result, qq(10), 'check replicated inserts on subscriber');
+$node_publisher->safe_psql('postgres',
+ "CREATE TABLE tab2 AS SELECT generate_series(1,10) AS a");
+$node_subscriber->safe_psql('postgres', "CREATE TABLE tab2 (a int)");
+
+# Replace the EXCEPT TABLE list so that only tab2 is excluded.
+$node_publisher->safe_psql('postgres',
+ "ALTER PUBLICATION tab_pub SET ALL TABLES EXCEPT TABLE (tab2)");
+
+# Refresh the subscription so the subscriber picks up the updated
+# publication definition and initiates table synchronization.
+$node_subscriber->safe_psql('postgres',
+ "ALTER SUBSCRIPTION tab_sub REFRESH PUBLICATION");
+
+# Wait for initial table sync to finish
+$node_subscriber->wait_for_subscription_sync($node_publisher, 'tab_sub');
+
+# Verify that initial table synchronization does not occur for tables
+# listed in the EXCEPT TABLE clause.
+$result =
+ $node_subscriber->safe_psql('postgres', "SELECT count(*) FROM tab2");
+is($result, qq(0),
+ 'check there is no initial data copied for the tables specified in the EXCEPT TABLE clause'
+);
+
+# Verify that table synchronization now happens for tab1. Table tab1 is
+# included now since the EXCEPT TABLE list is only (tab2).
+$result =
+ $node_subscriber->safe_psql('postgres', "SELECT count(*) FROM tab1");
+is($result, qq(20),
+ 'check that the data is copied as the tab1 is removed from EXCEPT TABLE clause'
+);
+
# cleanup
$node_subscriber->safe_psql(
'postgres', qq(
DROP SUBSCRIPTION tab_sub;
TRUNCATE TABLE tab1;
- DROP TABLE parent, parent1, child, child1;
+ DROP TABLE parent, parent1, child, child1, tab2;
));
$node_publisher->safe_psql(
'postgres', qq(
DROP PUBLICATION tab_pub;
TRUNCATE TABLE tab1;
- DROP TABLE parent, parent1, child, child1;
+ DROP TABLE parent, parent1, child, child1, tab2;
));
# ============================================
--
2.43.0
view thread (377+ 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: <CALDaNm3jpYs7ALcU6m5=Li=udidjZoW5dMpyCFs8QHGaf0S8+A@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