public inbox for [email protected]
help / color / mirror / Atom feedFrom: vignesh C <[email protected]>
To: Amit Kapila <[email protected]>
Cc: Dilip Kumar <[email protected]>
Cc: shveta malik <[email protected]>
Cc: Peter Smith <[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: 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: Thu, 26 Mar 2026 20:54:37 +0530
Message-ID: <CALDaNm3cdoT58E3QtYCwBbzyxYJjoS2k7Q0EgzR9ta6fyDGHSg@mail.gmail.com> (raw)
In-Reply-To: <CAA4eK1LBf5asit18HcqcFinOkdCjD6Lk2Eid9PDhtH6acwYb8w@mail.gmail.com>
References: <CAJpy0uB20MhJJEaPJdm31t4fykJ+fChA_76jU2P9HX5knbJvAA@mail.gmail.com>
<CAD21AoCC8XuwfX62qKBSfHUAoww_XB3_84HjswgL9jxQy696yw@mail.gmail.com>
<OS9PR01MB12149EA0C749BC29C7C949E32F544A@OS9PR01MB12149.jpnprd01.prod.outlook.com>
<CAD21AoBbZEshyaK0PeiF_J4_S75EfF=Gcs=C+X-osoVoUnawuQ@mail.gmail.com>
<CAHut+PssG+sHeV+Xo0g=S7xBb9FgDPjHYDR4iSuOdYXDq-Psng@mail.gmail.com>
<CAA4eK1LaSfAG7UAuy1xpnkWKM_YtrPuhbgAxYBFY3Sp_v_KqoQ@mail.gmail.com>
<CAD21AoAb8E8krN63cY_U7RQs9v-zkqUZyKT_UVKDwKfExtvTBg@mail.gmail.com>
<CAA4eK1K1GLR7DXSABayQE+pWM=v1ODD6haPYxuDhAYwJN5gjzg@mail.gmail.com>
<CALDaNm2kvFahDDvdgCNo=Nv-COz_N5Xw8YmzQBN2bd3g=N81fQ@mail.gmail.com>
<CAHut+PsCqTR_kQu5M1TqBjnE6KM5cO22aH8boHfpMa_gSJBmWg@mail.gmail.com>
<CALDaNm2OOgmNOPpABUU+AXzHhfrLG9HMfSd3jfNe=t3dc-kp1Q@mail.gmail.com>
<CAJpy0uCN4gfP7fSt__KdW5wYQ82650Z6L4YLnjRHZTQ1yir1mg@mail.gmail.com>
<CALDaNm32+c6RTE5xR6sJ=MZGgwEtzjkxpov_Hu70MXfbvmN+=Q@mail.gmail.com>
<CAHut+PtQbK9USLepyzArXFoNuLok1MsBu_Jg4UT=koZocombFw@mail.gmail.com>
<CALDaNm1tKuU479T=winBqoMb3MzO3Mta2juk8W3t2R5ps0_zyg@mail.gmail.com>
<CALDaNm3jpYs7ALcU6m5=Li=udidjZoW5dMpyCFs8QHGaf0S8+A@mail.gmail.com>
<CAJpy0uCWS=ybBKG-kRAfdWEe1VBNj+VqpAUUoT8MPaNS7EggiA@mail.gmail.com>
<CAA4eK1LMM-P4NatbkjG-96B7hHC7KYrJ8XTsCZQy0jLO9Qj4Bw@mail.gmail.com>
<CAJpy0uAyf71QSYitBf4WbCYq22HDR6LPdxB12TpTgTRpczwphw@mail.gmail.com>
<CAFiTN-s5PW121mBGKin20YEQpZkWefMehmP=v+0onzEaMQpwdw@mail.gmail.com>
<CAA4eK1LBf5asit18HcqcFinOkdCjD6Lk2Eid9PDhtH6acwYb8w@mail.gmail.com>
On Thu, 26 Mar 2026 at 11:41, Amit Kapila <[email protected]> wrote:
>
> On Tue, Mar 24, 2026 at 7:50 PM Dilip Kumar <[email protected]> wrote:
> >
> > On Mon, Mar 23, 2026 at 12:35 PM shveta malik <[email protected]> wrote:
> > >
> > > I would like to summarize the discussion/feedback for the EXCEPT
> > > syntax implemented in [1].
> > >
> > > 1)
> > > The currently implemented syntax is ([1]):
> > >
> > > CREATE PUBLICATION pub FOR ALL TABLES EXCEPT TABLE (a, b, c);
> > >
> > > There were concerns about why the TABLE keyword and the parentheses
> > > '()' are required. These have been answered in [2].
> > >
> > > Please review the discussion there.
> > >
> > > 2)
> > > Another feedback on current syntax was to move the TABLE keyword
> > > inside the parentheses:
> > >
> > > CREATE PUBLICATION pub FOR ALL TABLES EXCEPT (TABLE t1, TABLE t2,
> > > TABLES IN SCHEMA s1);
> > > CREATE PUBLICATION pub FOR TABLES IN SCHEMA s1 EXCEPT (TABLE t1, TABLE
> > > t2), TABLE t3;
> > >
> > > While this approach is workable, a downside is the repeated use of the
> > > TABLE keyword inside the parentheses, which can become verbose. But it
> > > can then be optimized to have:
> > >
> > > CREATE PUBLICATION pub FOR ALL TABLES EXCEPT (TABLE t1, t2, t3);
> > >
> > > This could be extended further in the future:
> > >
> > > CREATE PUBLICATION pub FOR ALL TABLES EXCEPT (TABLE t1, t2, TABLES IN
> > > SCHEMA s1, s2);
> > >
> > > This approach gives users flexibility to mix styles, for example:
> > >
> > > EXCEPT (TABLE t1, TABLE t2, TABLE t3)
> > > EXCEPT (TABLE t1, t2, t3)
> > > EXCEPT (TABLE t1, t2, TABLE t3)
> > > EXCEPT (TABLE t1, TABLES IN SCHEMA s1, s2, TABLE t2, t3)
> > >
> > > While flexible, this can reduce clarity due to mixed styles, making
> > > the statement harder to read. If extended further, the syntax could
> > > evolve into something like:
> > >
> > > CREATE PUBLICATION pub1 FOR
> > > ALL TABLES
> > > EXCEPT (TABLE t1, t2, TABLES IN SCHEMA s1, s2),
> > > ALL SEQUENCES
> > > EXCEPT (SEQUENCE s1);
> > >
> > > At this point, one might also question why not allow something like:
> > > FOR ALL (TABLES, SEQUENCES).
> > >
> > > Additionally, this shows a potential drift toward less structured
> > > syntax. Instead, with the syntax already implemented in [1], its
> > > future extension would look like:
> > >
> > > CREATE PUBLICATION pub1 FOR
> > > ALL TABLES
> > > EXCEPT TABLE (t1, t2),
> > > EXCEPT TABLES IN SCHEMA (s1, s2),
> > > ALL SEQUENCES
> > > EXCEPT SEQUENCE (seq1, seq2);
> > >
> > > Although slightly more verbose, this approach keeps each clause
> > > self-contained and explicit. The meaning of each part is determined
> > > locally, rather than depending on elements appearing far in the
> > > statement.
> > >
> > > The current syntax in [1] is simple and easy to follow. We have
> > > retained the current implementation for now, while remaining open to
> > > further discussion and suggestions.
> > >
> > > [1]:
> > > https://www.postgresql.org/message-id/CALDaNm2-Ob9qPR%2BvqUSVMkxYO8RW4LQ_S1XiB0Y7xa54U%3DDqbA%40mail...
> > > https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=fd366065e06ae953c4f2d973d5c5f0474...
> > >
> > > [2]: https://www.postgresql.org/message-id/CAJpy0uB20MhJJEaPJdm31t4fykJ%2BfChA_76jU2P9HX5knbJvAA%40mail.g...
> >
> >
> > Thanks for the summary. While I find the current implementation
> > simpler and more intuitive, and would prefer it if we were designing
> > this from scratch, we must consider the existing patterns for table
> > inclusion. Since the inclusion syntax already supports a mixed
> > approach, users will likely expect the same flexibility for
> > exclusions. For the sake of consistency across the features, I believe
> > we should move toward the mixed approach, despite my preference for
> > the current structured style.
> >
>
> IIUC, you think that we should change the current syntax "CREATE
> PUBLICATION pub FOR ALL TABLES EXCEPT TABLE (a, b, c);" to "CREATE
> PUBLICATION pub FOR ALL TABLES EXCEPT (TABLE a, b, c);".
>
> By now multiple people (Dilip Kumar, Peter Smith, Sawada Masahiko)
> have preferred the alternate syntax, to move TABLE inside () to make
> specifying inclusion and exclusion list in a similar way. Unless we
> have more feedback, I think we can change it now.
Attached patch has the changes for the same.
Regards,
Vignesh
Attachments:
[application/octet-stream] 0001-Change-syntax-of-EXCEPT-TABLE-clause-in-publication-.patch (24.3K, 2-0001-Change-syntax-of-EXCEPT-TABLE-clause-in-publication-.patch)
download | inline diff:
From 8d6a5f0ba31e2821ef0174a0875b4afdd67a3c55 Mon Sep 17 00:00:00 2001
From: Vignesh C <[email protected]>
Date: Thu, 26 Mar 2026 20:30:14 +0530
Subject: [PATCH] Change syntax of EXCEPT TABLE clause in publication commands
Adjust the syntax of the EXCEPT clause in CREATE/ALTER PUBLICATION
to move the TABLE keyword inside the relation list.
Old syntax:
CREATE PUBLICATION ... FOR ALL TABLES EXCEPT TABLE (t1, ...);
ALTER PUBLICATION ... SET ALL TABLES EXCEPT TABLE (t1, ...);
New syntax:
CREATE PUBLICATION ... FOR ALL TABLES EXCEPT (TABLE t1, ...);
ALTER PUBLICATION ... SET ALL TABLES EXCEPT (TABLE t1, ...);
This change is purely syntactic and does not alter behavior.
---
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 | 24 +++++++++++------------
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, 53 insertions(+), 53 deletions(-)
diff --git a/doc/src/sgml/ref/alter_publication.sgml b/doc/src/sgml/ref/alter_publication.sgml
index 7f0e46380cc..1416e98d793 100644
--- a/doc/src/sgml/ref/alter_publication.sgml
+++ b/doc/src/sgml/ref/alter_publication.sgml
@@ -35,7 +35,7 @@ ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RENAME TO <r
<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">publication_drop_object</replaceable> is one of:</phrase>
@@ -265,7 +265,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..186fb582085 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 0fea726cdd5..0562dde68a3 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -11256,7 +11256,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]
@@ -11399,7 +11399,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; }
;
@@ -11462,7 +11462,7 @@ pub_except_obj_list: PublicationExceptObjSpec
*
* pub_all_obj_type is one of:
*
- * ALL TABLES [ EXCEPT TABLE ( table_name [, ...] ) ]
+ * ALL TABLES [ EXCEPT ( TABLE table_name [, ...] ) ]
* ALL SEQUENCES
*
*****************************************************************************/
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 5d1f7682f11..76558fe50b2 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -4714,7 +4714,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 051a3d8ea3d..b4b8efed513 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -3215,9 +3215,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, },
},
@@ -3225,9 +3225,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, },
},
@@ -3235,9 +3235,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 523d3f39fc5..cbdd02cd01b 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -2333,13 +2333,13 @@ match_previous_words(int pattern_id,
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 (");
+ 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");
/* Complete "ALTER PUBLICATION <name> FOR TABLE" with "<table>, ..." */
- else if (Matches("ALTER", "PUBLICATION", MatchAny, "SET", "ALL", "TABLES", "EXCEPT", "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
@@ -3738,16 +3738,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 a220f48b285..f117c75f2af 100644
--- a/src/test/regress/expected/publication.out
+++ b/src/test/regress/expected/publication.out
@@ -218,7 +218,7 @@ Not-null constraints:
---------------------------------------------
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
@@ -229,7 +229,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
@@ -259,7 +259,7 @@ Except Publications:
---------------------------------------------
-- 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);
+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
@@ -309,7 +309,7 @@ ALTER PUBLICATION testpub_forall_tbls_seqs SET ALL SEQUENCES;
(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);
+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;
@@ -319,7 +319,7 @@ 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/SEQUENCES 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 be defined as FOR ALL TABLES/SEQUENCES or to be empty.
ALTER PUBLICATION testpub_forschema SET ALL TABLES;
@@ -359,7 +359,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
@@ -370,7 +370,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
@@ -381,7 +381,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
@@ -399,7 +399,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
@@ -427,7 +427,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);
@@ -1623,7 +1623,7 @@ 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 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
diff --git a/src/test/regress/sql/publication.sql b/src/test/regress/sql/publication.sql
index 22e0a30b5c7..99a4868eaec 100644
--- a/src/test/regress/sql/publication.sql
+++ b/src/test/regress/sql/publication.sql
@@ -110,10 +110,10 @@ SELECT pubname, puballtables FROM pg_publication WHERE pubname = 'testpub_forall
---------------------------------------------
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
@@ -124,7 +124,7 @@ CREATE PUBLICATION testpub_foralltables_excepttable1 FOR ALL TABLES EXCEPT TABLE
---------------------------------------------
-- 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);
+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.
@@ -149,12 +149,12 @@ 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 EXCEPT (TABLE testpub_tbl1);
ALTER PUBLICATION testpub_fortable SET ALL TABLES;
ALTER PUBLICATION testpub_fortable SET ALL SEQUENCES;
-- fail - SET ALL TABLES/SEQUENCES 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;
ALTER PUBLICATION testpub_forschema SET ALL SEQUENCES;
@@ -175,13 +175,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;
@@ -194,11 +194,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
@@ -1037,7 +1037,7 @@ 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 EXCEPT (TABLE testpub_tbl1); -- fail
ALTER PUBLICATION testpub5 SET ALL TABLES; -- fail
ALTER PUBLICATION testpub5 SET ALL SEQUENCES; -- fail
diff --git a/src/test/subscription/t/037_except.pl b/src/test/subscription/t/037_except.pl
index 13b99eda258..9878d4eefd0 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: <CALDaNm3cdoT58E3QtYCwBbzyxYJjoS2k7Q0EgzR9ta6fyDGHSg@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