public inbox for [email protected]
help / color / mirror / Atom feedFrom: vignesh C <[email protected]>
To: Amit Kapila <[email protected]>
Cc: Masahiko Sawada <[email protected]>
Cc: Peter Smith <[email protected]>
Cc: Hayato Kuroda (Fujitsu) <[email protected]>
Cc: shveta malik <[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: Sun, 15 Mar 2026 17:55:34 +0530
Message-ID: <CALDaNm2kvFahDDvdgCNo=Nv-COz_N5Xw8YmzQBN2bd3g=N81fQ@mail.gmail.com> (raw)
In-Reply-To: <CAA4eK1K1GLR7DXSABayQE+pWM=v1ODD6haPYxuDhAYwJN5gjzg@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>
On Sat, 14 Mar 2026 at 15:05, Amit Kapila <[email protected]> wrote:
>
> On Fri, Mar 13, 2026 at 11:51 PM Masahiko Sawada <[email protected]> wrote:
> >
> > On Fri, Mar 13, 2026 at 2:51 AM Amit Kapila <[email protected]> wrote:
> > >
> > > On Fri, Mar 13, 2026 at 8:55 AM Peter Smith <[email protected]> wrote:
> > > >
> > > > Hou-San (off-list) gave an interesting example of how it might look
> > > > when the example is combined with the hypothetical future ability to
> > > > exclude all schema tables from a "FOR ALL TABLES"
> > > > CREATE PUBLICATION pub FOR ALL TABLES EXCEPT (TABLE t1, t2, TABLES IN
> > > > SCHEMA s1);
> > > >
> > >
> > > So, if we follow this, in future, the syntax would look like:
> > > Create publication pub1 FOR ALL TABLES EXCEPT (TABLE t1, t2, TABLES IN
> > > SCHEMA s1, s2), ALL SEQUENCES EXCEPT (SEQUENCE s1);
> > >
> > > I think if we use the above syntax then one can also argue that why
> > > not use FOR ALL (TABLES, SEQUENCES) instead of ALL TABLES, ALL
> > > SEQUENCES.
> >
> > The same is true for the syntaxes of TABLE(t1, t2) and SCHEMA(s1, s2),
> > no? If we allow grouping the tables and the schemas by TABLE and
> > SCHEMA keywords respectively in exclusion lists, one could argue why
> > we don't allow it for the inclusion list.
> >
> > >
> > > Instead the current command would like:
> > > Create publication pub1 FOR ALL TABLES EXCEPT TABLE (t1, t2), EXCEPT
> > > TABLES IN SCHEMA (s1, s2), ALL SEQUENCES EXCEPT SEQUENCE (seq1, seq2);
> > >
> > > Though it is slightly lengthy it makes the syntax local such that the
> > > meaning of a clause is determined by what is inside it, not by
> > > something that appears ten words later.
> >
> > While it helps the syntax locality, I'm concerned that it could make
> > the context of the EXCEPT clause ambiguous. For example, IIUC the
> > above syntax rule would allow us to write:
> >
> > CREATE PUBLICATION pub1 FOR ALL TABLES EXCEPT TABLES IN SCHEMA (s1),
> > EXCEPT TABLE (t1);
> >
> > Regarding the second EXCEPT clause, while one might think it excludes
> > t1 from ALL TABLES, one might think it excludes t1 from the first
> > EXCLUDE clause (i.e., the publication actually includes t1 in the
> > publication).
> >
>
> According to me, the comma between EXCEPT clauses breaks such a chain
> of thought but others may think differently. The docs can clarify such
> a confusion if any but if multiple people interpret it the way you
> think, I am fine to go with the direction you are proposing. I am
> slightly worried that both syntaxes have some pros and cons, so even
> if we change to what you are suggesting, one can argue to go back to
> previous syntax as that also has some merits. So, I feel we can
> complete the pending patch [1] and try to get the opinion of some
> other people unless changing the code to support another alternative
> is difficult.
+1 to proceed with completing the pending work.
I implemented the suggested new syntax, and it only requires a few
lines of code changes. I evaluated the change to ensure there is no
additional complexity, and it appears straightforward. We can switch
to the new syntax once we gather a few more opinions and finalize it.
The attached 0002 patch includes the corresponding changes.
Regards,
Vignesh
Attachments:
[application/octet-stream] v63-0001-Add-support-for-EXCEPT-TABLE-in-ALTER-PUBLICATIO.patch (26.8K, 2-v63-0001-Add-support-for-EXCEPT-TABLE-in-ALTER-PUBLICATIO.patch)
download | inline diff:
From 56e3fc20f116383f6998f816dbe713bf1cd7c853 Mon Sep 17 00:00:00 2001
From: Vignesh C <[email protected]>
Date: Wed, 4 Mar 2026 16:28:32 +0530
Subject: [PATCH v63 1/2] 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 ALL TABLES EXCEPT TABLE (table_list)
ALTER PUBLICATION name SET ALL TABLES
The first form replaces the current EXCEPT TABLE list with the specified
tables. The second form clears the existing except table list. Like the
creation syntax, only root partitioned tables can be specified in the
exclusion list.
Author: vignesh C <[email protected]>
Reviewed-by: shveta malik <[email protected]>
Reviewed-by: Amit Kapila <[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 | 45 +++++++++++++++++-----
src/backend/catalog/pg_publication.c | 10 +++--
src/backend/commands/publicationcmds.c | 42 +++++++++++++++-----
src/backend/commands/tablecmds.c | 3 +-
src/backend/parser/gram.y | 16 ++++++++
src/bin/psql/tab-complete.in.c | 10 ++++-
src/include/catalog/pg_publication.h | 3 +-
src/include/nodes/parsenodes.h | 1 +
src/test/regress/expected/publication.out | 47 ++++++++++++++++++++++-
src/test/regress/sql/publication.sql | 28 +++++++++++++-
src/test/subscription/t/037_except.pl | 36 ++++++++++++++++-
11 files changed, 212 insertions(+), 29 deletions(-)
diff --git a/doc/src/sgml/ref/alter_publication.sgml b/doc/src/sgml/ref/alter_publication.sgml
index 028770f2149..c5e3dd3be77 100644
--- a/doc/src/sgml/ref/alter_publication.sgml
+++ b/doc/src/sgml/ref/alter_publication.sgml
@@ -22,6 +22,7 @@ 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 ALL TABLES <replaceable class="parameter">publication_except_tables</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_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] )
@@ -33,6 +34,10 @@ 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>where <replaceable class="parameter">publication_except_tables</replaceable> is:</phrase>
+
+ [ EXCEPT TABLE ( [ ONLY ] <replaceable class="parameter">table_name</replaceable> [, ... ] ) ]
+
<phrase>and <replaceable class="parameter">publication_drop_object</replaceable> is one of:</phrase>
TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [, ... ]
@@ -53,14 +58,22 @@ 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
+ The first four variants modify which tables/schemas are included in the
+ publication, or which tables are excluded from it. The
+ <literal>SET ALL TABLES</literal> clause is 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 except table list is replaced with the specified tables.
+ If <literal>EXCEPT TABLE</literal> is omitted, the existing except table
+ 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. 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
- <link linkend="sql-altersubscription-params-refresh-publication">
+ 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
@@ -70,7 +83,7 @@ ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RENAME TO <r
</para>
<para>
- The fourth variant of this command listed in the synopsis can change
+ The fifth variant of this command listed in the synopsis can change
all of the publication properties specified in
<xref linkend="sql-createpublication"/>. Properties not mentioned in the
command retain their previous settings.
@@ -83,8 +96,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>, and
+ <literal>SET ALL TABLES</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 +236,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>
+ Change the publication to include all tables by removing any existing
+ EXCEPT table list:
+<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 aadc7c202c6..facccbe74ea 100644
--- a/src/backend/catalog/pg_publication.c
+++ b/src/backend/catalog/pg_publication.c
@@ -440,7 +440,7 @@ attnumstoint2vector(Bitmapset *attrs)
*/
ObjectAddress
publication_add_relation(Oid pubid, PublicationRelInfo *pri,
- bool if_not_exists)
+ bool if_not_exists, bool is_alter)
{
Relation rel;
HeapTuple tup;
@@ -544,10 +544,12 @@ publication_add_relation(Oid pubid, PublicationRelInfo *pri,
/*
* 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.
+ * invalidation during CREATE PUBLICATION, as CreatePublication() function
+ * invalidates all relations as part of defining a FOR ALL TABLES
+ * publication. For ALTER PUBLICATION, explicit invalidation is still
+ * required.
*/
- if (!pri->except)
+ if (!pri->except || is_alter)
{
/*
* Invalidate relcache so that publication info is rebuilt.
diff --git a/src/backend/commands/publicationcmds.c b/src/backend/commands/publicationcmds.c
index 6a3ca4751fa..7944c957033 100644
--- a/src/backend/commands/publicationcmds.c
+++ b/src/backend/commands/publicationcmds.c
@@ -1272,15 +1272,24 @@ AlterPublicationTables(AlterPublicationStmt *stmt, HeapTuple tup,
PublicationDropTables(pubid, rels, false);
else /* AP_SetObjects */
{
- List *oldrelids = GetIncludedPublicationRelations(pubid,
- PUBLICATION_PART_ROOT);
+ bool isexcept = pubform->puballtables;
+ List *oldrelids;
List *delrels = NIL;
ListCell *oldlc;
- TransformPubWhereClauses(rels, queryString, pubform->pubviaroot);
+ if (isexcept)
+ 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 +1507,11 @@ 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 alter FOR ALL TABLES publication"));
+
/*
* Check that user is allowed to manipulate the publication tables in
* schema
@@ -1546,6 +1560,17 @@ CheckAlterPublication(AlterPublicationStmt *stmt, HeapTuple tup,
NameStr(pubform->pubname)),
errdetail("Tables or sequences cannot be added to or dropped from FOR ALL SEQUENCES publications."));
}
+
+ /*
+ * SET ALL TABLES is allowed only for publications defined as FOR ALL
+ * TABLES.
+ */
+ if (stmt->for_all_tables && !pubform->puballtables)
+ ereport(ERROR,
+ errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("publication \"%s\" does not support ALL TABLES operations",
+ NameStr(pubform->pubname)),
+ errdetail("This operation requires the publication to have been defined as FOR ALL TABLES."));
}
/*
@@ -1591,9 +1616,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,6 +1637,7 @@ 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);
@@ -1953,7 +1976,8 @@ 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 != NULL));
if (stmt)
{
EventTriggerCollectSimpleCommand(obj, InvalidObjectAddress,
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index cd6d720386f..e25010b5267 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -20600,7 +20600,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 f01f5734fe9..63b62c89b13 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -10999,6 +10999,8 @@ pub_except_obj_list: PublicationExceptObjSpec
*
* ALTER PUBLICATION name SET pub_obj [, ...]
*
+ * ALTER PUBLICATION name SET ALL TABLES [ EXCEPT TABLE ( table_name [, ...] ) ]
+ *
* pub_obj is one of:
*
* TABLE table_name [, ...]
@@ -11013,6 +11015,7 @@ AlterPublicationStmt:
n->pubname = $3;
n->options = $5;
+ n->for_all_tables = false;
$$ = (Node *) n;
}
| ALTER PUBLICATION name ADD_P pub_obj_list
@@ -11023,6 +11026,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
@@ -11033,6 +11037,17 @@ 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 ALL TABLES opt_pub_except_clause
+ {
+ AlterPublicationStmt *n = makeNode(AlterPublicationStmt);
+
+ n->pubname = $3;
+ n->pubobjects = $7;
+ n->action = AP_SetObjects;
+ n->for_all_tables = true;
$$ = (Node *) n;
}
| ALTER PUBLICATION name DROP pub_obj_list
@@ -11043,6 +11058,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 199fc64ddf5..10a6b8f4284 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -2320,7 +2320,15 @@ 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 TABLES", "TABLES IN SCHEMA", "TABLE");
+ else if (Matches("ALTER", "PUBLICATION", MatchAny, "SET", "ALL"))
+ COMPLETE_WITH("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("(");
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..f40c0fe2f95 100644
--- a/src/include/catalog/pg_publication.h
+++ b/src/include/catalog/pg_publication.h
@@ -198,7 +198,8 @@ extern bool is_schema_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,
+ bool is_alter);
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 f3d32ef0188..b0f5c603bd0 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -4386,6 +4386,7 @@ 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 SET ALL TABLES is specified */
} AlterPublicationStmt;
typedef struct CreateSubscriptionStmt
diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out
index 681d2564ed5..97a5288a728 100644
--- a/src/test/regress/expected/publication.out
+++ b/src/test/regress/expected/publication.out
@@ -126,6 +126,13 @@ ALTER PUBLICATION testpub_fortable SET TABLES IN SCHEMA pub_test;
Tables from schemas:
"pub_test"
+-- fail - SET ALL TABLES 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 have been defined as FOR ALL TABLES.
+ALTER PUBLICATION testpub_fortable SET ALL TABLES;
+ERROR: publication "testpub_fortable" does not support ALL TABLES operations
+DETAIL: This operation requires the publication to have been defined as FOR ALL TABLES.
SET client_min_messages = 'ERROR';
CREATE PUBLICATION testpub_forschema FOR TABLES IN SCHEMA pub_test;
-- should be able to create publication with schema and table of the same
@@ -213,6 +220,13 @@ Not-null constraints:
regress_publication_user | t | f | t | t | f | f | none | f |
(1 row)
+-- 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 have been defined as FOR ALL TABLES.
+ALTER PUBLICATION testpub_forschema SET ALL TABLES;
+ERROR: publication "testpub_forschema" does not support ALL TABLES operations
+DETAIL: This operation requires the publication to have been defined as FOR ALL TABLES.
---------------------------------------------
-- EXCEPT TABLE tests for normal tables
---------------------------------------------
@@ -254,6 +268,25 @@ Except Publications:
"testpub_foralltables_excepttable"
"testpub_foralltables_excepttable1"
+-- Remove all the EXCEPT 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)
+
+-- Replace the publication EXCEPT table list with a specific EXCEPT table.
+ALTER PUBLICATION testpub_foralltables_excepttable SET ALL TABLES EXCEPT TABLE (testpub_tbl1);
+\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_tbl1"
+
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;
@@ -361,6 +394,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 +1575,18 @@ 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 on a publication requires superuser privileges
+ALTER PUBLICATION testpub5 SET ALL TABLES EXCEPT TABLE (testpub_tbl1); -- fail
+ERROR: must be superuser to alter FOR ALL TABLES publication
+ALTER PUBLICATION testpub5 SET ALL TABLES; -- fail
+ERROR: must be superuser to alter FOR ALL TABLES publication
+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..56e4bfba974 100644
--- a/src/test/regress/sql/publication.sql
+++ b/src/test/regress/sql/publication.sql
@@ -74,6 +74,10 @@ ALTER PUBLICATION testpub_fortable DROP TABLES IN SCHEMA pub_test;
ALTER PUBLICATION testpub_fortable SET TABLES IN SCHEMA pub_test;
\dRp+ testpub_fortable
+-- fail - SET ALL TABLES 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;
+
SET client_min_messages = 'ERROR';
CREATE PUBLICATION testpub_forschema FOR TABLES IN SCHEMA pub_test;
-- should be able to create publication with schema and table of the same
@@ -105,6 +109,10 @@ SELECT pubname, puballtables FROM pg_publication WHERE pubname = 'testpub_forall
\d+ testpub_tbl2
\dRp+ testpub_foralltables
+-- 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;
+
---------------------------------------------
-- EXCEPT TABLE tests for normal tables
---------------------------------------------
@@ -119,6 +127,14 @@ CREATE PUBLICATION testpub_foralltables_excepttable1 FOR ALL TABLES EXCEPT TABLE
-- in the EXCEPT TABLE clause
\d testpub_tbl1
+-- Remove all the EXCEPT tables.
+ALTER PUBLICATION testpub_foralltables_excepttable SET ALL TABLES;
+\dRp+ testpub_foralltables_excepttable
+
+-- Replace the publication EXCEPT table list with a specific EXCEPT table.
+ALTER PUBLICATION testpub_foralltables_excepttable SET ALL TABLES EXCEPT TABLE (testpub_tbl1);
+\dRp+ testpub_foralltables_excepttable
+
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;
@@ -991,7 +1007,17 @@ 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 on a publication requires superuser privileges
+ALTER PUBLICATION testpub5 SET ALL TABLES EXCEPT TABLE (testpub_tbl1); -- fail
+ALTER PUBLICATION testpub5 SET ALL TABLES; -- 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..cbfe42011ef 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 occurs once tab1 is removed from the
+# EXCEPT TABLE clause via SET ALL TABLES EXCEPT TABLE.
+$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
[application/octet-stream] v63-0002-Change-syntax-of-CREATE-PUBLICATION-.-FOR-ALL-TA.patch (23.5K, 3-v63-0002-Change-syntax-of-CREATE-PUBLICATION-.-FOR-ALL-TA.patch)
download | inline diff:
From 86164bf1bbfa12b8316ec9f77daed2f0703a6bc2 Mon Sep 17 00:00:00 2001
From: Vignesh C <[email protected]>
Date: Sun, 15 Mar 2026 16:47:13 +0530
Subject: [PATCH v63 2/2] Change syntax of CREATE PUBLICATION ... FOR ALL
TABLES EXCEPT clause
Change the syntax of CREATE PUBLICATION ... FOR ALL TABLES EXCEPT to move
the TABLE keyword inside the relation list.
Old syntax:
CREATE PUBLICATION ... FOR ALL TABLES EXCEPT TABLE (t1 ...);
New syntax:
CREATE PUBLICATION ... FOR ALL TABLES EXCEPT (TABLE t1 ...);
---
doc/src/sgml/ref/alter_publication.sgml | 4 ++--
doc/src/sgml/ref/create_publication.sgml | 6 +++---
src/backend/parser/gram.y | 6 +++---
src/bin/pg_dump/pg_dump.c | 2 +-
src/bin/pg_dump/t/002_pg_dump.pl | 12 ++++++------
src/bin/psql/tab-complete.in.c | 22 +++++++++++-----------
src/test/regress/expected/publication.out | 22 +++++++++++-----------
src/test/regress/sql/publication.sql | 22 +++++++++++-----------
src/test/subscription/t/037_except.pl | 8 ++++----
9 files changed, 52 insertions(+), 52 deletions(-)
diff --git a/doc/src/sgml/ref/alter_publication.sgml b/doc/src/sgml/ref/alter_publication.sgml
index c5e3dd3be77..9e2a4fdb6e4 100644
--- a/doc/src/sgml/ref/alter_publication.sgml
+++ b/doc/src/sgml/ref/alter_publication.sgml
@@ -36,7 +36,7 @@ ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RENAME TO <r
<phrase>where <replaceable class="parameter">publication_except_tables</replaceable> is:</phrase>
- [ EXCEPT TABLE ( [ ONLY ] <replaceable class="parameter">table_name</replaceable> [, ... ] ) ]
+ [ EXCEPT (TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [, ... ] ) ]
<phrase>and <replaceable class="parameter">publication_drop_object</replaceable> is one of:</phrase>
@@ -241,7 +241,7 @@ ALTER PUBLICATION mypublication SET TABLE users (user_id, firstname, lastname),
<para>
Replace the publication's EXCEPT table list:
<programlisting>
-ALTER PUBLICATION mypublication SET ALL TABLES EXCEPT TABLE (users, departments);
+ALTER PUBLICATION mypublication SET ALL TABLES EXCEPT (TABLE users, departments);
</programlisting></para>
<para>
diff --git a/doc/src/sgml/ref/create_publication.sgml b/doc/src/sgml/ref/create_publication.sgml
index 77066ef680b..97c18e5cf2f 100644
--- a/doc/src/sgml/ref/create_publication.sgml
+++ b/doc/src/sgml/ref/create_publication.sgml
@@ -32,7 +32,7 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
<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 TABLES [ EXCEPT (TABLE <replaceable class="parameter">except_table_object</replaceable> [, ... ] ) ]
ALL SEQUENCES
<phrase>and <replaceable class="parameter">table_and_columns</replaceable> is:</phrase>
@@ -532,7 +532,7 @@ CREATE PUBLICATION all_tables_sequences FOR ALL TABLES, ALL SEQUENCES;
Create a publication that publishes all changes in all tables except
<structname>users</structname> and <structname>departments</structname>:
<programlisting>
-CREATE PUBLICATION all_tables_except FOR ALL TABLES EXCEPT TABLE (users, departments);
+CREATE PUBLICATION all_tables_except FOR ALL TABLES EXCEPT (TABLE users, departments);
</programlisting>
</para>
@@ -541,7 +541,7 @@ CREATE PUBLICATION all_tables_except FOR ALL TABLES EXCEPT TABLE (users, departm
all changes in all tables except <structname>users</structname> and
<structname>departments</structname>:
<programlisting>
-CREATE PUBLICATION all_sequences_tables_except FOR ALL SEQUENCES, ALL TABLES EXCEPT TABLE (users, departments);
+CREATE PUBLICATION all_sequences_tables_except FOR ALL SEQUENCES, ALL TABLES EXCEPT (TABLE users, departments);
</programlisting>
</para>
</refsect1>
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 63b62c89b13..85b0701de9e 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -10802,7 +10802,7 @@ AlterOwnerStmt: ALTER AGGREGATE aggregate_with_argtypes OWNER TO RoleSpec
*
* pub_all_obj_type is one of:
*
- * TABLES [EXCEPT TABLE ( table [, ...] )]
+ * TABLES [EXCEPT (TABLE table [, ...] )]
* SEQUENCES
*
* CREATE PUBLICATION FOR pub_obj [, ...] [WITH options]
@@ -10945,7 +10945,7 @@ pub_obj_list: PublicationObjSpec
;
opt_pub_except_clause:
- EXCEPT TABLE '(' pub_except_obj_list ')' { $$ = $4; }
+ EXCEPT '(' TABLE pub_except_obj_list ')' { $$ = $4; }
| /*EMPTY*/ { $$ = NIL; }
;
@@ -10999,7 +10999,7 @@ pub_except_obj_list: PublicationExceptObjSpec
*
* ALTER PUBLICATION name SET pub_obj [, ...]
*
- * ALTER PUBLICATION name SET ALL TABLES [ EXCEPT TABLE ( table_name [, ...] ) ]
+ * ALTER PUBLICATION name SET ALL TABLES [ EXCEPT ( TABLE table_name [, ...] ) ]
*
* pub_obj is one of:
*
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 137161aa5e0..76ff54ada97 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -4711,7 +4711,7 @@ dumpPublication(Archive *fout, const PublicationInfo *pubinfo)
TableInfo *tbinfo = (TableInfo *) cell->ptr;
if (++n_except == 1)
- appendPQExpBufferStr(query, " EXCEPT TABLE (");
+ appendPQExpBufferStr(query, " EXCEPT (TABLE ");
else
appendPQExpBufferStr(query, ", ");
appendPQExpBuffer(query, "ONLY %s", fmtQualifiedDumpable(tbinfo));
diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
index 6d1d38128fc..656f44eb519 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -3203,9 +3203,9 @@ my %tests = (
'CREATE PUBLICATION pub8' => {
create_order => 50,
create_sql =>
- 'CREATE PUBLICATION pub8 FOR ALL TABLES EXCEPT TABLE (dump_test.test_table);',
+ 'CREATE PUBLICATION pub8 FOR ALL TABLES EXCEPT (TABLE dump_test.test_table);',
regexp => qr/^
- \QCREATE PUBLICATION pub8 FOR ALL TABLES EXCEPT TABLE (ONLY dump_test.test_table) WITH (publish = 'insert, update, delete, truncate');\E
+ \QCREATE PUBLICATION pub8 FOR ALL TABLES EXCEPT (TABLE ONLY dump_test.test_table) WITH (publish = 'insert, update, delete, truncate');\E
/xm,
like => { %full_runs, section_post_data => 1, },
},
@@ -3213,9 +3213,9 @@ my %tests = (
'CREATE PUBLICATION pub9' => {
create_order => 50,
create_sql =>
- 'CREATE PUBLICATION pub9 FOR ALL TABLES EXCEPT TABLE (dump_test.test_table, dump_test.test_second_table);',
+ 'CREATE PUBLICATION pub9 FOR ALL TABLES EXCEPT (TABLE dump_test.test_table, dump_test.test_second_table);',
regexp => qr/^
- \QCREATE PUBLICATION pub9 FOR ALL TABLES EXCEPT TABLE (ONLY dump_test.test_table, ONLY dump_test.test_second_table) WITH (publish = 'insert, update, delete, truncate');\E
+ \QCREATE PUBLICATION pub9 FOR ALL TABLES EXCEPT (TABLE ONLY dump_test.test_table, ONLY dump_test.test_second_table) WITH (publish = 'insert, update, delete, truncate');\E
/xm,
like => { %full_runs, section_post_data => 1, },
},
@@ -3223,9 +3223,9 @@ my %tests = (
'CREATE PUBLICATION pub10' => {
create_order => 92,
create_sql =>
- 'CREATE PUBLICATION pub10 FOR ALL TABLES EXCEPT TABLE (dump_test.test_inheritance_parent);',
+ 'CREATE PUBLICATION pub10 FOR ALL TABLES EXCEPT (TABLE dump_test.test_inheritance_parent);',
regexp => qr/^
- \QCREATE PUBLICATION pub10 FOR ALL TABLES EXCEPT TABLE (ONLY dump_test.test_inheritance_parent, ONLY dump_test.test_inheritance_child) WITH (publish = 'insert, update, delete, truncate');\E
+ \QCREATE PUBLICATION pub10 FOR ALL TABLES EXCEPT (TABLE ONLY dump_test.test_inheritance_parent, ONLY dump_test.test_inheritance_child) WITH (publish = 'insert, update, delete, truncate');\E
/xm,
like => { %full_runs, section_post_data => 1, },
},
diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index 10a6b8f4284..89b9781b30c 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -2324,11 +2324,11 @@ match_previous_words(int pattern_id,
else if (Matches("ALTER", "PUBLICATION", MatchAny, "SET", "ALL"))
COMPLETE_WITH("TABLES");
else if (Matches("ALTER", "PUBLICATION", MatchAny, "SET", "ALL", "TABLES"))
- COMPLETE_WITH("EXCEPT TABLE (");
+ 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_WITH("( TABLE");
+ else if (Matches("ALTER", "PUBLICATION", MatchAny, "SET", "ALL", "TABLES", "EXCEPT", "("))
+ COMPLETE_WITH("TABLE");
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\\\\_%%'",
@@ -3689,16 +3689,16 @@ match_previous_words(int pattern_id,
else if (Matches("CREATE", "PUBLICATION", MatchAny, "FOR", "ALL"))
COMPLETE_WITH("TABLES", "SEQUENCES");
else if (Matches("CREATE", "PUBLICATION", MatchAny, "FOR", "ALL", "TABLES"))
- COMPLETE_WITH("EXCEPT TABLE (", "WITH (");
+ COMPLETE_WITH("EXCEPT ( TABLE", "WITH (");
else if (Matches("CREATE", "PUBLICATION", MatchAny, "FOR", "ALL", "TABLES", "EXCEPT"))
- COMPLETE_WITH("TABLE (");
- else if (Matches("CREATE", "PUBLICATION", MatchAny, "FOR", "ALL", "TABLES", "EXCEPT", "TABLE"))
- COMPLETE_WITH("(");
- else if (Matches("CREATE", "PUBLICATION", MatchAny, "FOR", "ALL", "TABLES", "EXCEPT", "TABLE", "("))
+ COMPLETE_WITH("( TABLE");
+ else if (Matches("CREATE", "PUBLICATION", MatchAny, "FOR", "ALL", "TABLES", "EXCEPT", "("))
+ COMPLETE_WITH("TABLE");
+ else if (Matches("CREATE", "PUBLICATION", MatchAny, "FOR", "ALL", "TABLES", "EXCEPT", "(", "TABLE"))
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables);
- else if (Matches("CREATE", "PUBLICATION", MatchAny, "FOR", "ALL", "TABLES", "EXCEPT", "TABLE", "(", MatchAnyN) && ends_with(prev_wd, ','))
+ else if (Matches("CREATE", "PUBLICATION", MatchAny, "FOR", "ALL", "TABLES", "EXCEPT", "(", "TABLE", MatchAnyN) && ends_with(prev_wd, ','))
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables);
- else if (Matches("CREATE", "PUBLICATION", MatchAny, "FOR", "ALL", "TABLES", "EXCEPT", "TABLE", "(", MatchAnyN) && !ends_with(prev_wd, ','))
+ else if (Matches("CREATE", "PUBLICATION", MatchAny, "FOR", "ALL", "TABLES", "EXCEPT", "(", "TABLE", MatchAnyN) && !ends_with(prev_wd, ','))
COMPLETE_WITH(")");
else if (Matches("CREATE", "PUBLICATION", MatchAny, "FOR", "TABLES"))
COMPLETE_WITH("IN SCHEMA");
diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out
index 97a5288a728..dec338dd774 100644
--- a/src/test/regress/expected/publication.out
+++ b/src/test/regress/expected/publication.out
@@ -127,7 +127,7 @@ Tables from schemas:
"pub_test"
-- fail - SET ALL TABLES 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 EXCEPT (TABLE testpub_tbl1);
ERROR: publication "testpub_fortable" does not support ALL TABLES operations
DETAIL: This operation requires the publication to have been defined as FOR ALL TABLES.
ALTER PUBLICATION testpub_fortable SET ALL TABLES;
@@ -221,7 +221,7 @@ Not-null constraints:
(1 row)
-- 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 EXCEPT (TABLE pub_test.testpub_nopk);
ERROR: publication "testpub_forschema" does not support ALL TABLES operations
DETAIL: This operation requires the publication to have been defined as FOR ALL TABLES.
ALTER PUBLICATION testpub_forschema SET ALL TABLES;
@@ -232,7 +232,7 @@ DETAIL: This operation requires the publication to have been defined as FOR ALL
---------------------------------------------
SET client_min_messages = 'ERROR';
-- Specify table list in the EXCEPT TABLE clause of a FOR ALL TABLES publication
-CREATE PUBLICATION testpub_foralltables_excepttable FOR ALL TABLES EXCEPT TABLE (testpub_tbl1, testpub_tbl2);
+CREATE PUBLICATION testpub_foralltables_excepttable FOR ALL TABLES EXCEPT (TABLE testpub_tbl1, testpub_tbl2);
\dRp+ testpub_foralltables_excepttable
Publication testpub_foralltables_excepttable
Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description
@@ -243,7 +243,7 @@ Except tables:
"public.testpub_tbl2"
-- Specify table in the EXCEPT TABLE clause of a FOR ALL TABLES publication
-CREATE PUBLICATION testpub_foralltables_excepttable1 FOR ALL TABLES EXCEPT TABLE (testpub_tbl1);
+CREATE PUBLICATION testpub_foralltables_excepttable1 FOR ALL TABLES EXCEPT (TABLE testpub_tbl1);
\dRp+ testpub_foralltables_excepttable1
Publication testpub_foralltables_excepttable1
Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description
@@ -278,7 +278,7 @@ ALTER PUBLICATION testpub_foralltables_excepttable SET ALL TABLES;
(1 row)
-- Replace the publication EXCEPT table list with a specific EXCEPT table.
-ALTER PUBLICATION testpub_foralltables_excepttable SET ALL TABLES EXCEPT TABLE (testpub_tbl1);
+ALTER PUBLICATION testpub_foralltables_excepttable SET ALL TABLES EXCEPT (TABLE testpub_tbl1);
\dRp+ testpub_foralltables_excepttable
Publication testpub_foralltables_excepttable
Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description
@@ -317,7 +317,7 @@ Tables:
"public.testpub_tbl_parent"
-- List the parent table in the EXCEPT TABLE clause (without ONLY or '*')
-CREATE PUBLICATION testpub5 FOR ALL TABLES EXCEPT TABLE (testpub_tbl_parent);
+CREATE PUBLICATION testpub5 FOR ALL TABLES EXCEPT (TABLE testpub_tbl_parent);
\dRp+ testpub5
Publication testpub5
Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description
@@ -328,7 +328,7 @@ Except tables:
"public.testpub_tbl_parent"
-- EXCEPT with '*': list the table and all its descendants in the EXCEPT TABLE clause
-CREATE PUBLICATION testpub6 FOR ALL TABLES EXCEPT TABLE (testpub_tbl_parent *);
+CREATE PUBLICATION testpub6 FOR ALL TABLES EXCEPT (TABLE testpub_tbl_parent *);
\dRp+ testpub6
Publication testpub6
Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description
@@ -339,7 +339,7 @@ Except tables:
"public.testpub_tbl_parent"
-- EXCEPT with ONLY: list the table in the EXCEPT TABLE clause, but not its descendants
-CREATE PUBLICATION testpub7 FOR ALL TABLES EXCEPT TABLE (ONLY testpub_tbl_parent);
+CREATE PUBLICATION testpub7 FOR ALL TABLES EXCEPT (TABLE ONLY testpub_tbl_parent);
\dRp+ testpub7
Publication testpub7
Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description
@@ -357,7 +357,7 @@ DROP PUBLICATION testpub3, testpub4, testpub5, testpub6, testpub7;
SET client_min_messages = 'ERROR';
CREATE TABLE testpub_root(a int) PARTITION BY RANGE(a);
CREATE TABLE testpub_part1 PARTITION OF testpub_root FOR VALUES FROM (0) TO (100);
-CREATE PUBLICATION testpub8 FOR ALL TABLES EXCEPT TABLE (testpub_root);
+CREATE PUBLICATION testpub8 FOR ALL TABLES EXCEPT (TABLE testpub_root);
\dRp+ testpub8;
Publication testpub8
Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description
@@ -385,7 +385,7 @@ Except Publications:
"testpub8"
Number of partitions: 1 (Use \d+ to list them.)
-CREATE PUBLICATION testpub9 FOR ALL TABLES EXCEPT TABLE (testpub_part1);
+CREATE PUBLICATION testpub9 FOR ALL TABLES EXCEPT (TABLE testpub_part1);
ERROR: cannot use publication EXCEPT clause for relation "testpub_part1"
DETAIL: This operation is not supported for individual partitions.
CREATE TABLE tab_main (a int) PARTITION BY RANGE(a);
@@ -1581,7 +1581,7 @@ RESET client_min_messages;
ALTER PUBLICATION testpub5 OWNER TO regress_publication_user3;
SET ROLE regress_publication_user3;
-- fail - SET ALL TABLES on a publication requires superuser privileges
-ALTER PUBLICATION testpub5 SET ALL TABLES EXCEPT TABLE (testpub_tbl1); -- fail
+ALTER PUBLICATION testpub5 SET ALL TABLES EXCEPT (TABLE testpub_tbl1); -- fail
ERROR: must be superuser to alter FOR ALL TABLES publication
ALTER PUBLICATION testpub5 SET ALL TABLES; -- fail
ERROR: must be superuser to alter FOR ALL TABLES publication
diff --git a/src/test/regress/sql/publication.sql b/src/test/regress/sql/publication.sql
index 56e4bfba974..154b552efaf 100644
--- a/src/test/regress/sql/publication.sql
+++ b/src/test/regress/sql/publication.sql
@@ -75,7 +75,7 @@ ALTER PUBLICATION testpub_fortable SET TABLES IN SCHEMA pub_test;
\dRp+ testpub_fortable
-- fail - SET ALL TABLES 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 EXCEPT (TABLE testpub_tbl1);
ALTER PUBLICATION testpub_fortable SET ALL TABLES;
SET client_min_messages = 'ERROR';
@@ -110,7 +110,7 @@ SELECT pubname, puballtables FROM pg_publication WHERE pubname = 'testpub_forall
\dRp+ testpub_foralltables
-- 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 EXCEPT (TABLE pub_test.testpub_nopk);
ALTER PUBLICATION testpub_forschema SET ALL TABLES;
---------------------------------------------
@@ -118,10 +118,10 @@ ALTER PUBLICATION testpub_forschema SET ALL TABLES;
---------------------------------------------
SET client_min_messages = 'ERROR';
-- Specify table list in the EXCEPT TABLE clause of a FOR ALL TABLES publication
-CREATE PUBLICATION testpub_foralltables_excepttable FOR ALL TABLES EXCEPT TABLE (testpub_tbl1, testpub_tbl2);
+CREATE PUBLICATION testpub_foralltables_excepttable FOR ALL TABLES EXCEPT (TABLE testpub_tbl1, testpub_tbl2);
\dRp+ testpub_foralltables_excepttable
-- Specify table in the EXCEPT TABLE clause of a FOR ALL TABLES publication
-CREATE PUBLICATION testpub_foralltables_excepttable1 FOR ALL TABLES EXCEPT TABLE (testpub_tbl1);
+CREATE PUBLICATION testpub_foralltables_excepttable1 FOR ALL TABLES EXCEPT (TABLE testpub_tbl1);
\dRp+ testpub_foralltables_excepttable1
-- Check that the table description shows the publications where it is listed
-- in the EXCEPT TABLE clause
@@ -132,7 +132,7 @@ ALTER PUBLICATION testpub_foralltables_excepttable SET ALL TABLES;
\dRp+ testpub_foralltables_excepttable
-- Replace the publication EXCEPT table list with a specific EXCEPT table.
-ALTER PUBLICATION testpub_foralltables_excepttable SET ALL TABLES EXCEPT TABLE (testpub_tbl1);
+ALTER PUBLICATION testpub_foralltables_excepttable SET ALL TABLES EXCEPT (TABLE testpub_tbl1);
\dRp+ testpub_foralltables_excepttable
RESET client_min_messages;
@@ -151,13 +151,13 @@ CREATE PUBLICATION testpub3 FOR TABLE testpub_tbl_parent;
CREATE PUBLICATION testpub4 FOR TABLE ONLY testpub_tbl_parent;
\dRp+ testpub4
-- List the parent table in the EXCEPT TABLE clause (without ONLY or '*')
-CREATE PUBLICATION testpub5 FOR ALL TABLES EXCEPT TABLE (testpub_tbl_parent);
+CREATE PUBLICATION testpub5 FOR ALL TABLES EXCEPT (TABLE testpub_tbl_parent);
\dRp+ testpub5
-- EXCEPT with '*': list the table and all its descendants in the EXCEPT TABLE clause
-CREATE PUBLICATION testpub6 FOR ALL TABLES EXCEPT TABLE (testpub_tbl_parent *);
+CREATE PUBLICATION testpub6 FOR ALL TABLES EXCEPT (TABLE testpub_tbl_parent *);
\dRp+ testpub6
-- EXCEPT with ONLY: list the table in the EXCEPT TABLE clause, but not its descendants
-CREATE PUBLICATION testpub7 FOR ALL TABLES EXCEPT TABLE (ONLY testpub_tbl_parent);
+CREATE PUBLICATION testpub7 FOR ALL TABLES EXCEPT (TABLE ONLY testpub_tbl_parent);
\dRp+ testpub7
RESET client_min_messages;
@@ -170,11 +170,11 @@ DROP PUBLICATION testpub3, testpub4, testpub5, testpub6, testpub7;
SET client_min_messages = 'ERROR';
CREATE TABLE testpub_root(a int) PARTITION BY RANGE(a);
CREATE TABLE testpub_part1 PARTITION OF testpub_root FOR VALUES FROM (0) TO (100);
-CREATE PUBLICATION testpub8 FOR ALL TABLES EXCEPT TABLE (testpub_root);
+CREATE PUBLICATION testpub8 FOR ALL TABLES EXCEPT (TABLE testpub_root);
\dRp+ testpub8;
\d testpub_part1
\d testpub_root
-CREATE PUBLICATION testpub9 FOR ALL TABLES EXCEPT TABLE (testpub_part1);
+CREATE PUBLICATION testpub9 FOR ALL TABLES EXCEPT (TABLE testpub_part1);
CREATE TABLE tab_main (a int) PARTITION BY RANGE(a);
-- Attaching a partition is not allowed if the partitioned table appears in a
@@ -1013,7 +1013,7 @@ RESET client_min_messages;
ALTER PUBLICATION testpub5 OWNER TO regress_publication_user3;
SET ROLE regress_publication_user3;
-- fail - SET ALL TABLES on a publication requires superuser privileges
-ALTER PUBLICATION testpub5 SET ALL TABLES EXCEPT TABLE (testpub_tbl1); -- fail
+ALTER PUBLICATION testpub5 SET ALL TABLES EXCEPT (TABLE testpub_tbl1); -- fail
ALTER PUBLICATION testpub5 SET ALL TABLES; -- fail
SET ROLE regress_publication_user;
diff --git a/src/test/subscription/t/037_except.pl b/src/test/subscription/t/037_except.pl
index cbfe42011ef..13bfa978356 100644
--- a/src/test/subscription/t/037_except.pl
+++ b/src/test/subscription/t/037_except.pl
@@ -31,7 +31,7 @@ sub test_except_root_partition
# publish_via_partition_root setting.
$node_publisher->safe_psql(
'postgres', qq(
- CREATE PUBLICATION tap_pub_part FOR ALL TABLES EXCEPT TABLE (root1) WITH (publish_via_partition_root = $pubviaroot);
+ CREATE PUBLICATION tap_pub_part FOR ALL TABLES EXCEPT (TABLE root1) WITH (publish_via_partition_root = $pubviaroot);
INSERT INTO root1 VALUES (1), (101);
));
$node_subscriber->safe_psql('postgres',
@@ -94,7 +94,7 @@ $node_subscriber->safe_psql(
# to verify exclusion behavior for inherited tables, including the effect of
# ONLY in the EXCEPT TABLE clause.
$node_publisher->safe_psql('postgres',
- "CREATE PUBLICATION tab_pub FOR ALL TABLES EXCEPT TABLE (tab1, parent, only parent1)"
+ "CREATE PUBLICATION tab_pub FOR ALL TABLES EXCEPT (TABLE tab1, parent, only parent1)"
);
# Create a logical replication slot to help with later tests.
@@ -158,7 +158,7 @@ $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)");
+ "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.
@@ -231,7 +231,7 @@ test_except_root_partition('true');
# FOR TABLE.
$node_publisher->safe_psql(
'postgres', qq(
- CREATE PUBLICATION tap_pub1 FOR ALL TABLES EXCEPT TABLE (tab1);
+ CREATE PUBLICATION tap_pub1 FOR ALL TABLES EXCEPT (TABLE tab1);
CREATE PUBLICATION tap_pub2 FOR TABLE tab1;
INSERT INTO tab1 VALUES(1);
));
--
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: <CALDaNm2kvFahDDvdgCNo=Nv-COz_N5Xw8YmzQBN2bd3g=N81fQ@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