public inbox for [email protected]
help / color / mirror / Atom feedFrom: Shlok Kyal <[email protected]>
To: Peter Smith <[email protected]>
Cc: vignesh C <[email protected]>
Cc: Amit Kapila <[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, 6 Nov 2025 16:46:48 +0530
Message-ID: <CANhcyEUh9ki36VTXyYf8UqUrfLX9ZhfP_f2LjpvvycgqWLQqqQ@mail.gmail.com> (raw)
In-Reply-To: <CAHut+PtGu2j72yV_as_TVKfWr3ctd18svReGEx3xa=q5BtKyKA@mail.gmail.com>
References: <CALDaNm3=JrucjhiiwsYQw5-PGtBHFONa6F7hhWCXMsGvh=tamA@mail.gmail.com>
<CALj2ACVOzhs+BD+abFV2x4oKJdsDNd6SgsE7r8UjnZDCKGEckA@mail.gmail.com>
<CAA4eK1K6Kr88d2S0zFdHRMyuoaZeNh+ktU+oigmCuD09_x_-+g@mail.gmail.com>
<CAHut+PsvC-NezO3MJkdyEz=G1QRje2LntjwhQiEeVbmhOQuBMA@mail.gmail.com>
<CALDaNm18VH2j8cTqfELHQ=0ZNognbGBhbHPteJenWQC6C2dueQ@mail.gmail.com>
<CALDaNm0k_0Ccj47wzJzzPFwgQB7w=R5+Q2_nSqYrmMmjhmcRUw@mail.gmail.com>
<CAHut+Pv_0DwyWoGQNMF+G2AGqMuJTzWQKRtmxaC+=zLTPL-Zkw@mail.gmail.com>
<CALDaNm2-GJt2HsYTkLqQ=ecm=R-vOBw1=aM_d2EiYbz39x_cTQ@mail.gmail.com>
<TYCPR01MB8373C3120C2B3112001ED6F1EDCF9@TYCPR01MB8373.jpnprd01.prod.outlook.com>
<CALDaNm0iZZDB300Dez_97S8G6_RW5QpQ8ef6X3wq8tyK-8wnXQ@mail.gmail.com>
<CAHut+PtiomM+iyAZHvb2dzfsPvRru266KuBe49hKy2n2h+m_zA@mail.gmail.com>
<CALDaNm30KDnwX4Czi29fqLb8JBkuwqjbpj9ixwNXXox574NZqQ@mail.gmail.com>
<CALDaNm1PfKRJsEzbKpyt=v4p3bw+_SzE+LFPsMhR5X+qs+0pPw@mail.gmail.com>
<TYCPR01MB83730A2F1D6A5303E9C1416AEDD99@TYCPR01MB8373.jpnprd01.prod.outlook.com>
<CALDaNm0sAU4s1KTLOEWv=rYo5dQK6uFTJn_0FKj3XG1Nv4D-qw@mail.gmail.com>
<CALDaNm3CLRa95tpas6tEj8x58MUNDShxBNoYS+P8Uq5cryoAOw@mail.gmail.com>
<CALDaNm0EKC3o=v+F7GneGibuCULGKkBWXmNaVB4GR9HoqD066A@mail.gmail.com>
<CALDaNm1Z1Rmqj9s6P9ZzmrVA9F_vZ_DwwhYAJmsjqmY6dS3-hA@mail.gmail.com>
<CAB8KJ=jJGuW=ozKmXZzKDUHZ_-J2ZYGOtJo=i2cnNbSu6=KuYg@mail.gmail.com>
<CALDaNm1mbFP8fxHU_H1Ex4cT2Aq3n8FE79tq0TO5ThvFnDUYMA@mail.gmail.com>
<CAB8KJ=jq4RwTs8K7pokmXQwQppP2ChVJLMSAdXaxAX+c1r+mdg@mail.gmail.com>
<CALDaNm1mJvLni8GODebKBmyegXuZ18bLoG-Pz6H1MCX=vphCYA@mail.gmail.com>
<CALDaNm3dWZCYDih55qTNAYsjCvYXMFv=46UsDWmfCnXMt3kPCg@mail.gmail.com>
<CALDaNm1AQZYgT0tALRrkvpP1Q+8+e7vkGCUjQ-jim1C0q3e=zA@mail.gmail.com>
<CAA4eK1KRdAPC=5=7tQ1GW0cRwD=zaDMi+T4u_k4GxPhPY6e8BQ@mail.gmail.com>
<OS3PR01MB5718C8BE84B862E7E0CEC29B94BD2@OS3PR01MB5718.jpnprd01.prod.outlook.com>
<CAA4eK1KYQz7cf46_D=6VkZ4J6Y8vJ88MMi=6zm2TJXDP+V1mLg@mail.gmail.com>
<CANhcyEXZq4mP5dNgg7u=sMPwvxA4_ZN9U92uZEuzs=0xTu+8Yg@mail.gmail.com>
<CANhcyEXspT3v5-Tdop9uqQV2HWBvZoN5P0BxXQ6Md6Mr7GXK9A@mail.gmail.com>
<CAHut+PuiaLOCkiAx9nPnjk6wTbPFvnm9T5svTuKbgwJwTdea8w@mail.gmail.com>
<CANhcyEV_MePxgftHY65et1WdOAk70M0C7PZ1STPUO8PXHVB1YA@mail.gmail.com>
<CAHut+Ps0hSNqrjv_jT1AuXxO-CrZue3ixE0jKsxVhtArMrkujQ@mail.gmail.com>
<CANhcyEXX3viVpYcGHD_fzhf_f6CDQWr2+VBywrJf5zm_XiB4tg@mail.gmail.com>
<CAHut+PsXP_61ZXuVOx5u9FZGK3oH4taaA59oOzgqyygZx8ezWw@mail.gmail.com>
<CANhcyEU+aPu6iAH2cTA0cDtn3pd6c_njBONCt3FubYZoEEnm8Q@mail.gmail.com>
<CAHut+Pv2P6dJ7hZj_fmzN+=xzjvpOpgkAJvDZg3TD2xpvmY1NQ@mail.gmail.com>
<CANhcyEW2LK4diNeCG862DE40yQoV3VAgf59kXUq2TuR8fnw5vQ@mail.gmail.com>
<CAHut+PuSHScrODVGCM7P53Mv1HE2N6ThzkH4+gQ1eFXVeD-OCA@mail.gmail.com>
<CANhcyEUtYV-9ujtxLasnxN_peT+3LuZjcRx1xUECh1CCmANB8w@mail.gmail.com>
<CAHut+PuviFA6C7qps=+kDYfe3P99as8NCjbR=SYxoi0o96ipoA@mail.gmail.com>
<CANhcyEXkeg3sjkS3DS9yU1ckz4ozUBNZ+RmrWaRNSSVCR8RquA@mail.gmail.com>
<CAHut+PsHavMy_KJ0MwR9J6q0BTTty54TxS-KBZc7X6tb4u7rfA@mail.gmail.com>
<CANhcyEU=k4+0BqOu25N76g738XKUwfLGGdf8e+ssGiRKHC4RwQ@mail.gmail.com>
<CAHut+Ptdi8txJbMc+5Sp2uB4QLGOp-rZdkBbXyhAkxim0iAhBQ@mail.gmail.com>
<CANhcyEW+uJB_bvQLEaZCgoRTc1=i+QnrPPHxZ2=0SBSCyj9pkg@mail.gmail.com>
<CAHut+Ptq0-tMYUOvG3yR34AvuEzR9vUH=muqV_=uEO3zCuA6rA@mail.gmail.com>
<CANhcyEUEMWSkTfGc7Q3B+UiOzSiOmOGLgK-+C5DXwtCGOnDBhg@mail.gmail.com>
<CAHut+PuAgSDr3kbSxPMYEyCeGiJ5hgaT1JUvuiYPRT=Q+--O-Q@mail.gmail.com>
<CANhcyEU_uuiKMRrd_E_DeYsyCvwY_u995E-Do3d66J7tQnzdzw@mail.gmail.com>
<CANhcyEWK9sj9UY4uaV36Q8qxUv=8Joch0o98RCN5U3xLjUxAag@mail.gmail.com>
<CALDaNm32XQDR4qsOhPQeophVbZ8r+ShJSSssoVfdPcwG6joPHQ@mail.gmail.com>
<CANhcyEVt2CBnG7MOktaPPV4rYapHR-VHe5=qoziTZh1L9SVc6w@mail.gmail.com>
<CAHut+PtGu2j72yV_as_TVKfWr3ctd18svReGEx3xa=q5BtKyKA@mail.gmail.com>
On Thu, 30 Oct 2025 at 11:34, Peter Smith <[email protected]> wrote:
>
> Hi Vignesh
>
> Here are some review comments for the patch v24-0002.
>
> These comments are just for the SGML docs. The patch needs a rebase so
> I was unable to review the code.
>
> ======
> Commit message
>
> 1.
> A new column "prexcept" is added to table "pg_publication_rel", to maintain
> the relations that the user wants to exclude from the publications.
>
> ~
>
> /to maintain/to flag/
>
> ======
> doc/src/sgml/logical-replication.sgml
>
> 2.
> <para>
> - To add tables to a publication, the user must have ownership rights on the
> - table. To add all tables in schema to a publication, the user must be a
> - superuser. To create a publication that publishes all tables or
> all tables in
> - schema automatically, the user must be a superuser.
> + To create a publication using FOR ALL TABLES or FOR ALL TABLES IN SCHEMA,
> + the user must be a superuser. To add ALL TABLES or ALL TABLES IN SCHEMA to a
> + publication, the user must be a superuser. To add tables to a publication,
> + the user must have ownership rights on the table.
> </para>
>
> Those "FOR ALL TABLES" etc are missing SGML markup.
>
> ======
> doc/src/sgml/ref/alter_publication.sgml
>
> 3.
> +ALTER PUBLICATION <replaceable class="parameter">name</replaceable>
> ADD ALL TABLES [ EXCEPT [ TABLE ] <replaceable
> class="parameter">exception_object</replaceable> [, ... ] ]
>
> and
>
> +
> +<phrase>where <replaceable
> class="parameter">exception_object</replaceable> is:</phrase>
> +
> + [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ]
> +
>
> It is not clear from the syntax which of these is possible.
>
> ... ADD ALL TABLES EXCEPT TABLE t1,t2,t3
> ... ADD ALL TABLES EXCEPT TABLE t1, TABLE t2, TABLES t3
>
> IMO it is best put the "[TABLE]" within the exception_object:
> [ TABLE ] [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ]
>
> Then both are possible, which is consistent with how "FOR TABLE" syntax works.
>
> Furthermore, you might want later to say EXCLUDE SEQUENCE, so doing it
> this way makes that possible.
>
> ~~~
>
> 4.
> - Adding a table to a publication additionally requires owning that table.
> - The <literal>ADD TABLES IN SCHEMA</literal>,
> + Adding a table to or excluding a table from a publication additionally
> + requires owning that table. The <literal>ADD ALL TABLES</literal>,
>
> This wording seems a bit awkward. How are re-phrasing like:
>
> SUGGESTION
> Adding or excluding a table from a publication requires ownership of that table.
>
> ~~~
>
> 5.
> - name to explicitly indicate that descendant tables are included.
> + name to explicitly indicate that descendant tables are affected. For
> + partitioned tables, <literal>ONLY</literal> donot have any effect.
>
> typo: /donot/does not/
>
> ======
> doc/src/sgml/ref/create_publication.sgml
>
> 6.
> - [ FOR ALL TABLES
> + [ FOR ALL TABLES [ EXCEPT [ TABLE ] <replaceable
> class="parameter">exception_object</replaceable> [, ... ] ]
> | FOR <replaceable
> class="parameter">publication_object</replaceable> [, ... ] ]
> [ WITH ( <replaceable
> class="parameter">publication_parameter</replaceable> [= <replaceable
> class="parameter">value</replaceable>] [, ... ] ) ]
>
> @@ -30,6 +30,10 @@ CREATE PUBLICATION <replaceable
> class="parameter">name</replaceable>
>
> TABLE [ ONLY ] <replaceable
> class="parameter">table_name</replaceable> [ * ] [ ( <replaceable
> class="parameter">column_name</replaceable> [, ... ] ) ] [ WHERE (
> <replaceable class="parameter">expression</replaceable> ) ] [, ... ]
> TABLES IN SCHEMA { <replaceable
> class="parameter">schema_name</replaceable> | CURRENT_SCHEMA } [, ...
> ]
> +
> +<phrase>where <replaceable
> class="parameter">exception_object</replaceable> is:</phrase>
> +
> + [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ]
>
> Same review comment as #3 before.
>
> I think it is clearer (and more flexible) to change the
> exception_object to include [TABLE].
> [ TABLE ] [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ]
>
> It also helps pave the way for any future EXCLUDE SEQUENCE feature.
>
> ~~~
>
> 7.
> + <para>
> + This clause specifies a list of tables to be excluded from the
> + publication. It can only be used with <literal>FOR ALL TABLES</literal>.
> + If <literal>ONLY</literal> is specified before the table name, only
> + that table is excluded from the publication. If
> <literal>ONLY</literal> is
> + not specified, the table and all its descendant tables (if any) are
> + excluded. Optionally, <literal>*</literal> can be specified after the
> + table name to explicitly indicate that descendant tables are excluded.
> + This does not apply to a partitioned table, however. The partitioned
> + table or its partitions are excluded from the publication based on the
> + parameter <literal>publish_via_partition_root</literal>.
> + </para>
> + <para>
> + When <literal>publish_via_partition_root</literal> is set to
> + <literal>true</literal>, specifying a root partitioned table in
> + <literal>EXCEPT TABLE</literal> excludes it and all its partitions from
> + replication. Specifying a leaf partition has no effect, as its
> changes are
> + still replicated via the root partitioned table. When
> + <literal>publish_via_partition_root</literal> is set to
> + <literal>false</literal>, specifying a partitioned table or non-leaf
> + partition has no effect, as changes are replicated via the leaf
> + partitions. Specifying a leaf partition excludes only that partition from
> + replication.
> + </para>
>
> I felt that the second paragraph should be started with the sentence
> "The partitioned table or its partitions are excluded...", so then
> everything related to "publish_via_partition_root" is kept together.
>
> ~~~
>
> 8.
> + <para>
> + Create a publication that publishes all changes in all the tables except for
> + the changes of <structname>users</structname> and
> + <structname>departments</structname>:
> +<programlisting>
> +CREATE PUBLICATION mypublication FOR ALL TABLES EXCEPT users, departments;
> +</programlisting>
> + </para>
>
> The words "the changes of" are not needed, and you did not use that
> wording in the ALTER PUBLICATION example.
>
> ======
> doc/src/sgml/ref/psql-ref.sgml
>
> 9.
> If <literal>x</literal> is appended to the command name, the results
> are displayed in expanded mode.
> - If <literal>+</literal> is appended to the command name, the tables and
> - schemas associated with each publication are shown as well.
> + If <literal>+</literal> is appended to the command name, the tables,
> + excluded tables and schemas associated with each publication
> are shown as
> + well.
> </para>
>
> /excluded tables and schemas/excluded tables, and schemas/
>
Hi Peter, Vignesh
Thanks for reviewing the patches.
I have rebased the patches. I have modified the syntax for EXCEPT
TABLE (002) patch.
For example, now to exclude a table we need to specify like:
CREATE PUBLICATION pub1 FOR ALL TABLE EXCEPT TABLE (t1, t2);
We need to specify '()' around the table list.
This patchset is the only rebased version. I will address all the
comments in the next version of patch.
Thanks,
Shlok Kyal
Attachments:
[application/octet-stream] v25-0001-Add-RESET-clause-to-Alter-Publication-which-will.patch (20.7K, 2-v25-0001-Add-RESET-clause-to-Alter-Publication-which-will.patch)
download | inline diff:
From 632a1f6bab7305b55821d7b3c6e42cd2bb11cd85 Mon Sep 17 00:00:00 2001
From: Shlok Kyal <[email protected]>
Date: Thu, 30 Oct 2025 10:52:56 +0530
Subject: [PATCH v25 1/3] Add RESET clause to Alter Publication which will
reset the publication with default values.
This patch adds a new RESET clause to ALTER PUBLICATION which will reset
the publication to the default state which includes resetting the publication
parameters, setting ALL TABLES flag to false and dropping the relations and
schemas that are associated with the publication.
Usage:
ALTER PUBLICATION pub1 RESET;
---
doc/src/sgml/ref/alter_publication.sgml | 34 ++++--
src/backend/commands/publicationcmds.c | 124 ++++++++++++++++++++--
src/backend/parser/gram.y | 9 ++
src/bin/psql/tab-complete.in.c | 2 +-
src/include/nodes/parsenodes.h | 1 +
src/test/regress/expected/publication.out | 118 ++++++++++++++++++++
src/test/regress/sql/publication.sql | 57 ++++++++++
7 files changed, 331 insertions(+), 14 deletions(-)
diff --git a/doc/src/sgml/ref/alter_publication.sgml b/doc/src/sgml/ref/alter_publication.sgml
index c36e754f887..85c79c1a9cb 100644
--- a/doc/src/sgml/ref/alter_publication.sgml
+++ b/doc/src/sgml/ref/alter_publication.sgml
@@ -27,6 +27,7 @@ ALTER PUBLICATION <replaceable class="parameter">name</replaceable> DROP <replac
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>
+ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RESET
<phrase>where <replaceable class="parameter">publication_object</replaceable> is one of:</phrase>
@@ -69,18 +70,31 @@ ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RENAME TO <r
</para>
<para>
- The remaining variants change the owner and the name of the publication.
+ The <literal>OWNER</literal> clause will change the owner of the
+ publication.
+ </para>
+
+ <para>
+ The <literal>RENAME</literal> clause will change the name of the
+ publication.
+ </para>
+
+ <para>
+ The <literal>RESET</literal> clause will reset the publication to the default
+ state. This includes resetting all publication parameters, setting the
+ <literal>ALL TABLES</literal> flag to <literal>false</literal>, and
+ removing all associated tables and schemas from the publication.
</para>
<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
- 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>
- privilege on the database.
+ The <literal>ADD TABLES IN SCHEMA</literal>,
+ <literal>SET TABLES IN SCHEMA</literal> to a publication and
+ <literal>RESET</literal> of 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> privilege on the database.
Also, the new owner of a
<link linkend="sql-createpublication-params-for-tables-in-schema"><literal>FOR TABLES IN SCHEMA</literal></link>
or <link linkend="sql-createpublication-params-for-all-tables"><literal>FOR ALL TABLES</literal></link>
@@ -232,6 +246,12 @@ ALTER PUBLICATION sales_publication ADD TABLES IN SCHEMA marketing, sales;
<structname>production_publication</structname>:
<programlisting>
ALTER PUBLICATION production_publication ADD TABLE users, departments, TABLES IN SCHEMA production;
+</programlisting></para>
+
+ <para>
+ Reset the publication <structname>production_publication</structname>:
+<programlisting>
+ALTER PUBLICATION production_publication RESET;
</programlisting></para>
</refsect1>
diff --git a/src/backend/commands/publicationcmds.c b/src/backend/commands/publicationcmds.c
index 1faf3a8c372..ed88c306677 100644
--- a/src/backend/commands/publicationcmds.c
+++ b/src/backend/commands/publicationcmds.c
@@ -48,6 +48,16 @@
#include "utils/varlena.h"
+/* CREATE PUBLICATION default values for flags and publication parameters */
+#define PUB_DEFAULT_ACTION_INSERT true
+#define PUB_DEFAULT_ACTION_UPDATE true
+#define PUB_DEFAULT_ACTION_DELETE true
+#define PUB_DEFAULT_ACTION_TRUNCATE true
+#define PUB_DEFAULT_VIA_ROOT false
+#define PUB_DEFAULT_ALL_TABLES false
+#define PUB_DEFAULT_ALL_SEQUENCES false
+#define PUB_DEFAULT_GENCOLS PUBLISH_GENCOLS_NONE
+
/*
* Information used to validate the columns in the row filter expression. See
* contain_invalid_rfcolumn_walker for details.
@@ -90,12 +100,12 @@ parse_publication_options(ParseState *pstate,
*publish_generated_columns_given = false;
/* defaults */
- pubactions->pubinsert = true;
- pubactions->pubupdate = true;
- pubactions->pubdelete = true;
- pubactions->pubtruncate = true;
- *publish_via_partition_root = false;
- *publish_generated_columns = PUBLISH_GENCOLS_NONE;
+ pubactions->pubinsert = PUB_DEFAULT_ACTION_INSERT;
+ pubactions->pubupdate = PUB_DEFAULT_ACTION_UPDATE;
+ pubactions->pubdelete = PUB_DEFAULT_ACTION_DELETE;
+ pubactions->pubtruncate = PUB_DEFAULT_ACTION_TRUNCATE;
+ *publish_via_partition_root = PUB_DEFAULT_VIA_ROOT;
+ *publish_generated_columns = PUB_DEFAULT_GENCOLS;
/* Parse options */
foreach(lc, options)
@@ -1209,6 +1219,106 @@ InvalidatePublicationRels(List *relids)
CacheInvalidateRelcacheAll();
}
+/*
+ * Reset the publication.
+ *
+ * Reset the publication parameters, setting ALL TABLES flag to false and drop
+ * all relations and schemas that are associated with the publication.
+ */
+static void
+AlterPublicationReset(ParseState *pstate, AlterPublicationStmt *stmt,
+ Relation rel, HeapTuple tup)
+{
+ Form_pg_publication pubform = (Form_pg_publication) GETSTRUCT(tup);
+ Oid pubid = pubform->oid;
+ List *schemaids = NIL;
+ List *rels = NIL;
+ List *relids = NIL;
+ bool nulls[Natts_pg_publication];
+ bool replaces[Natts_pg_publication];
+ Datum values[Natts_pg_publication];
+
+ /* RESET publication requires superuser */
+ if (!superuser())
+ ereport(ERROR,
+ errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("must be superuser to RESET publication"));
+
+ memset(values, 0, sizeof(values));
+ memset(nulls, false, sizeof(nulls));
+ memset(replaces, false, sizeof(replaces));
+
+ /* Reset the publication parameters */
+ values[Anum_pg_publication_pubinsert - 1] = BoolGetDatum(PUB_DEFAULT_ACTION_INSERT);
+ replaces[Anum_pg_publication_pubinsert - 1] = true;
+
+ values[Anum_pg_publication_pubupdate - 1] = BoolGetDatum(PUB_DEFAULT_ACTION_UPDATE);
+ replaces[Anum_pg_publication_pubupdate - 1] = true;
+
+ values[Anum_pg_publication_pubdelete - 1] = BoolGetDatum(PUB_DEFAULT_ACTION_DELETE);
+ replaces[Anum_pg_publication_pubdelete - 1] = true;
+
+ values[Anum_pg_publication_pubtruncate - 1] = BoolGetDatum(PUB_DEFAULT_ACTION_TRUNCATE);
+ replaces[Anum_pg_publication_pubtruncate - 1] = true;
+
+ values[Anum_pg_publication_pubviaroot - 1] = BoolGetDatum(PUB_DEFAULT_VIA_ROOT);
+ replaces[Anum_pg_publication_pubviaroot - 1] = true;
+
+ values[Anum_pg_publication_pubgencols - 1] = CharGetDatum(PUB_DEFAULT_GENCOLS);
+ replaces[Anum_pg_publication_pubgencols - 1] = true;
+
+ /* Set ALL TABLES flag to false */
+ if (pubform->puballtables)
+ {
+ values[Anum_pg_publication_puballtables - 1] = BoolGetDatum(PUB_DEFAULT_ALL_TABLES);
+ replaces[Anum_pg_publication_puballtables - 1] = true;
+ CacheInvalidateRelcacheAll();
+ }
+
+ /* Set ALL TABLES flag to false */
+ if (pubform->puballsequences)
+ {
+ values[Anum_pg_publication_puballsequences - 1] = BoolGetDatum(PUB_DEFAULT_ALL_SEQUENCES);
+ replaces[Anum_pg_publication_puballsequences - 1] = true;
+ }
+
+ tup = heap_modify_tuple(tup, RelationGetDescr(rel), values, nulls,
+ replaces);
+
+ /* Update the catalog. */
+ CatalogTupleUpdate(rel, &tup->t_self, tup);
+
+ /* Remove the associated schemas from the publication */
+ schemaids = GetPublicationSchemas(pubid);
+
+ /*
+ * Schema lock is held until the publication is altered to prevent
+ * concurrent schema deletion.
+ */
+ LockSchemaList(schemaids);
+
+ /* Remove Schemas */
+ PublicationDropSchemas(pubid, schemaids, true);
+
+ /* Get all relations associated with the publication */
+ relids = GetPublicationRelations(pubid, PUBLICATION_PART_ROOT);
+
+ foreach_oid(relid, relids)
+ {
+ PublicationRelInfo *rel;
+
+ rel = palloc(sizeof(PublicationRelInfo));
+ rel->whereClause = NULL;
+ rel->columns = NIL;
+ rel->relation = table_open(relid, ShareUpdateExclusiveLock);
+ rels = lappend(rels, rel);
+ }
+
+ /* Remove the associated relations from the publication */
+ PublicationDropTables(pubid, rels, true);
+ CloseTableList(rels);
+}
+
/*
* Add or remove table to/from publication.
*/
@@ -1553,6 +1663,8 @@ AlterPublication(ParseState *pstate, AlterPublicationStmt *stmt)
if (stmt->options)
AlterPublicationOptions(pstate, stmt, rel, tup);
+ else if (stmt->action == AP_ResetPublication)
+ AlterPublicationReset(pstate, stmt, rel, tup);
else
{
List *relations = NIL;
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 57fe0186547..2b88c74a319 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -10909,6 +10909,8 @@ pub_obj_type_list: PublicationAllObjSpec
*
* ALTER PUBLICATION name SET pub_obj [, ...]
*
+ * ALTER PUBLICATION name RESET
+ *
* pub_obj is one of:
*
* TABLE table_name [, ...]
@@ -10955,6 +10957,13 @@ AlterPublicationStmt:
n->action = AP_DropObjects;
$$ = (Node *) n;
}
+ | ALTER PUBLICATION name RESET
+ {
+ AlterPublicationStmt *n = makeNode(AlterPublicationStmt);
+ n->pubname = $3;
+ n->action = AP_ResetPublication;
+ $$ = (Node *)n;
+ }
;
/*****************************************************************************
diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index 316a2dafbf1..d16181bc115 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -2288,7 +2288,7 @@ match_previous_words(int pattern_id,
/* ALTER PUBLICATION <name> */
else if (Matches("ALTER", "PUBLICATION", MatchAny))
- COMPLETE_WITH("ADD", "DROP", "OWNER TO", "RENAME TO", "SET");
+ COMPLETE_WITH("ADD", "DROP", "OWNER TO", "RENAME TO", "RESET", "SET");
/* ALTER PUBLICATION <name> ADD */
else if (Matches("ALTER", "PUBLICATION", MatchAny, "ADD"))
COMPLETE_WITH("TABLES IN SCHEMA", "TABLE");
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index d14294a4ece..778ef039d86 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -4326,6 +4326,7 @@ typedef enum AlterPublicationAction
AP_AddObjects, /* add objects to publication */
AP_DropObjects, /* remove objects from publication */
AP_SetObjects, /* set list of objects */
+ AP_ResetPublication, /* reset the publication */
} AlterPublicationAction;
typedef struct AlterPublicationStmt
diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out
index e72d1308967..bb614ba5d0a 100644
--- a/src/test/regress/expected/publication.out
+++ b/src/test/regress/expected/publication.out
@@ -2009,6 +2009,124 @@ Tables:
DROP PUBLICATION pub1;
DROP PUBLICATION pub2;
DROP TABLE gencols;
+-- Tests for ALTER PUBLICATION ... RESET
+CREATE SCHEMA pub_sch1;
+CREATE TABLE pub_sch1.tbl1 (a int);
+SET client_min_messages = 'ERROR';
+CREATE PUBLICATION testpub_reset FOR ALL TABLES;
+RESET client_min_messages;
+-- Verify that 'ALL TABLES' flag is reset
+\dRp+ testpub_reset
+ Publication testpub_reset
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | t | t | t | t | t | none | f
+(1 row)
+
+ALTER PUBLICATION testpub_reset RESET;
+\dRp+ testpub_reset
+ Publication testpub_reset
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | t | t | t | t | none | f
+(1 row)
+
+ALTER PUBLICATION testpub_reset ADD TABLE pub_sch1.tbl1;
+-- Verify that associated tables are removed from the publication after RESET
+\dRp+ testpub_reset
+ Publication testpub_reset
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | t | t | t | t | none | f
+Tables:
+ "pub_sch1.tbl1"
+
+ALTER PUBLICATION testpub_reset RESET;
+\dRp+ testpub_reset
+ Publication testpub_reset
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | t | t | t | t | none | f
+(1 row)
+
+ALTER PUBLICATION testpub_reset ADD TABLES IN SCHEMA public;
+-- Verify that associated schemas are reomved from the publication after RESET
+\dRp+ testpub_reset
+ Publication testpub_reset
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | t | t | t | t | none | f
+Tables from schemas:
+ "public"
+
+ALTER PUBLICATION testpub_reset RESET;
+\dRp+ testpub_reset
+ Publication testpub_reset
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | t | t | t | t | none | f
+(1 row)
+
+ALTER PUBLICATION testpub_reset SET (PUBLISH = '');
+-- Verify that 'PUBLISH' parameter is reset
+\dRp+ testpub_reset
+ Publication testpub_reset
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | f | f | f | f | none | f
+(1 row)
+
+ALTER PUBLICATION testpub_reset RESET;
+\dRp+ testpub_reset
+ Publication testpub_reset
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | t | t | t | t | none | f
+(1 row)
+
+ALTER PUBLICATION testpub_reset SET (PUBLISH_VIA_PARTITION_ROOT = 'true');
+-- Verify that 'PUBLISH_VIA_PARTITION_ROOT' parameter is reset
+\dRp+ testpub_reset
+ Publication testpub_reset
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | t | t | t | t | none | t
+(1 row)
+
+ALTER PUBLICATION testpub_reset RESET;
+\dRp+ testpub_reset
+ Publication testpub_reset
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | t | t | t | t | none | f
+(1 row)
+
+ALTER PUBLICATION testpub_reset SET (PUBLISH_GENERATED_COLUMNS = stored);
+-- Verify that 'PUBLISH_GENERATED_COLUMNS' parameter is reset
+\dRp+ testpub_reset
+ Publication testpub_reset
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | t | t | t | t | stored | f
+(1 row)
+
+ALTER PUBLICATION testpub_reset RESET;
+\dRp+ testpub_reset
+ Publication testpub_reset
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | t | t | t | t | none | f
+(1 row)
+
+-- Verify that only superuser can reset a publication
+ALTER PUBLICATION testpub_reset OWNER TO regress_publication_user2;
+SET ROLE regress_publication_user2;
+ALTER PUBLICATION testpub_reset RESET; -- fail - must be superuser
+ERROR: must be superuser to RESET publication
+SET ROLE regress_publication_user;
+DROP PUBLICATION testpub_reset;
+DROP TABLE pub_sch1.tbl1;
+DROP SCHEMA pub_sch1;
RESET client_min_messages;
-- Test that the INSERT ON CONFLICT command correctly checks REPLICA IDENTITY
-- when the target table is published.
diff --git a/src/test/regress/sql/publication.sql b/src/test/regress/sql/publication.sql
index 00390aecd47..829466d8de0 100644
--- a/src/test/regress/sql/publication.sql
+++ b/src/test/regress/sql/publication.sql
@@ -1268,6 +1268,63 @@ DROP PUBLICATION pub1;
DROP PUBLICATION pub2;
DROP TABLE gencols;
+-- Tests for ALTER PUBLICATION ... RESET
+CREATE SCHEMA pub_sch1;
+CREATE TABLE pub_sch1.tbl1 (a int);
+SET client_min_messages = 'ERROR';
+CREATE PUBLICATION testpub_reset FOR ALL TABLES;
+RESET client_min_messages;
+
+-- Verify that 'ALL TABLES' flag is reset
+\dRp+ testpub_reset
+ALTER PUBLICATION testpub_reset RESET;
+\dRp+ testpub_reset
+
+ALTER PUBLICATION testpub_reset ADD TABLE pub_sch1.tbl1;
+
+-- Verify that associated tables are removed from the publication after RESET
+\dRp+ testpub_reset
+ALTER PUBLICATION testpub_reset RESET;
+\dRp+ testpub_reset
+
+ALTER PUBLICATION testpub_reset ADD TABLES IN SCHEMA public;
+
+-- Verify that associated schemas are reomved from the publication after RESET
+\dRp+ testpub_reset
+ALTER PUBLICATION testpub_reset RESET;
+\dRp+ testpub_reset
+
+ALTER PUBLICATION testpub_reset SET (PUBLISH = '');
+
+-- Verify that 'PUBLISH' parameter is reset
+\dRp+ testpub_reset
+ALTER PUBLICATION testpub_reset RESET;
+\dRp+ testpub_reset
+
+ALTER PUBLICATION testpub_reset SET (PUBLISH_VIA_PARTITION_ROOT = 'true');
+
+-- Verify that 'PUBLISH_VIA_PARTITION_ROOT' parameter is reset
+\dRp+ testpub_reset
+ALTER PUBLICATION testpub_reset RESET;
+\dRp+ testpub_reset
+
+ALTER PUBLICATION testpub_reset SET (PUBLISH_GENERATED_COLUMNS = stored);
+
+-- Verify that 'PUBLISH_GENERATED_COLUMNS' parameter is reset
+\dRp+ testpub_reset
+ALTER PUBLICATION testpub_reset RESET;
+\dRp+ testpub_reset
+
+-- Verify that only superuser can reset a publication
+ALTER PUBLICATION testpub_reset OWNER TO regress_publication_user2;
+SET ROLE regress_publication_user2;
+ALTER PUBLICATION testpub_reset RESET; -- fail - must be superuser
+SET ROLE regress_publication_user;
+
+DROP PUBLICATION testpub_reset;
+DROP TABLE pub_sch1.tbl1;
+DROP SCHEMA pub_sch1;
+
RESET client_min_messages;
-- Test that the INSERT ON CONFLICT command correctly checks REPLICA IDENTITY
--
2.34.1
[application/octet-stream] v25-0003-Skip-publishing-the-columns-specified-in-FOR-TAB.patch (85.5K, 3-v25-0003-Skip-publishing-the-columns-specified-in-FOR-TAB.patch)
download | inline diff:
From fc872b2fb74037de4f88a6476e644f00e229a56c Mon Sep 17 00:00:00 2001
From: Shlok Kyal <[email protected]>
Date: Thu, 6 Nov 2025 15:29:40 +0530
Subject: [PATCH v25 3/3] Skip publishing the columns specified in FOR TABLE
EXCEPT
A new "TABLE table_name EXCEPT (column_list)" clause for CREATE/ALTER
PUBLICATION allows one or more columns to be excluded. The publisher
will not send the data of excluded columns to the subscriber.
The new syntax allows specifying excluded column list when creating or
altering a publication. For example:
CREATE PUBLICATION pubname FOR TABLE tabname EXCEPT (col1, col2, col3)
or
ALTER PUBLICATION pubname ADD TABLE tabname EXCEPT (col1, col2, col3)
When column "prexcept" of system catalog "pg_publication_rel" is set
to "true", and column "prattrs" of system catalog "pg_publication_rel"
is not NULL, that means the publication was created with "EXCEPT
(column-list)", and the columns in "prattrs" will be excluded from
being published.
pg_dump is updated to identify and dump the excluded column list of the
publication.
The psql \d family of commands can now display excluded column list. e.g.
psql \dRp+ variant will now display associated "EXCEPT (column_list)" if
any.
---
doc/src/sgml/catalogs.sgml | 5 +-
doc/src/sgml/logical-replication.sgml | 106 +++++--
doc/src/sgml/ref/alter_publication.sgml | 10 +-
doc/src/sgml/ref/create_publication.sgml | 52 +++-
doc/src/sgml/system-views.sgml | 10 +
src/backend/catalog/pg_publication.c | 86 +++++-
src/backend/catalog/system_views.sql | 3 +-
src/backend/commands/publicationcmds.c | 52 ++--
src/backend/parser/gram.y | 44 ++-
src/backend/replication/logical/tablesync.c | 41 ++-
src/backend/replication/pgoutput/pgoutput.c | 70 ++++-
src/bin/pg_dump/pg_dump.c | 45 +--
src/bin/pg_dump/pg_dump.h | 1 +
src/bin/psql/describe.c | 262 +++++++++++-------
src/bin/psql/tab-complete.in.c | 13 +-
src/include/catalog/pg_proc.dat | 6 +-
src/include/catalog/pg_publication.h | 6 +-
src/include/catalog/pg_publication_rel.h | 5 +-
src/test/regress/expected/publication.out | 130 +++++++--
src/test/regress/expected/rules.out | 5 +-
src/test/regress/sql/publication.sql | 55 ++++
src/test/subscription/meson.build | 1 +
.../t/038_rep_changes_except_collist.pl | 193 +++++++++++++
23 files changed, 956 insertions(+), 245 deletions(-)
create mode 100644 src/test/subscription/t/038_rep_changes_except_collist.pl
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 144f3fbdef2..c5e97d1575f 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -6586,7 +6586,10 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l
<structfield>prexcept</structfield> <type>bool</type>
</para>
<para>
- True if the relation must be excluded
+ True if the column list or relation must be excluded from publication.
+ If a column list is specified in <literal>prattrs</literal>, then
+ exclude only those columns. If <literal>prattrs</literal> is null,
+ then exclude the entire relation.
</para></entry>
</row>
diff --git a/doc/src/sgml/logical-replication.sgml b/doc/src/sgml/logical-replication.sgml
index 7359bd77569..42405c95687 100644
--- a/doc/src/sgml/logical-replication.sgml
+++ b/doc/src/sgml/logical-replication.sgml
@@ -1374,10 +1374,10 @@ Publications:
<title>Column Lists</title>
<para>
- Each publication can optionally specify which columns of each table are
- replicated to subscribers. The table on the subscriber side must have at
- least all the columns that are published. If no column list is specified,
- then all columns on the publisher are replicated.
+ Each publication can optionally specify which columns of each table should be
+ replicated or excluded from replication. The table on the subscriber side
+ must have at least all the columns that are published. If no column list is
+ specified, then all columns on the publisher are replicated.
See <xref linkend="sql-createpublication"/> for details on the syntax.
</para>
@@ -1391,8 +1391,11 @@ Publications:
<para>
If no column list is specified, any columns added to the table later are
- automatically replicated. This means that having a column list which names
- all columns is not the same as having no column list at all.
+ automatically replicated. However, a normal column list (without
+ <literal>EXCEPT</literal>) only replicates the specified columns and no more.
+ Therefore, having a column list that names all columns is not the same as
+ having no column list at all, as more columns may be added to the table
+ later.
</para>
<para>
@@ -1404,6 +1407,14 @@ Publications:
Generated columns can also be specified in a column list. This allows
generated columns to be published, regardless of the publication parameter
<link linkend="sql-createpublication-params-with-publish-generated-columns">
+ <literal>publish_generated_columns</literal></link>. Specifying generated
+ columns using the <literal>EXCEPT</literal> clause excludes those columns
+ from being published, regardless of the
+ <link linkend="sql-createpublication-params-with-publish-generated-columns">
+ <literal>publish_generated_columns</literal></link> setting. However, for
+ generated columns that are not listed in the <literal>EXCEPT</literal>
+ clause, whether they are published or not still depends on the value of
+ <link linkend="sql-createpublication-params-with-publish-generated-columns">
<literal>publish_generated_columns</literal></link>. See
<xref linkend="logical-replication-gencols"/> for details.
</para>
@@ -1425,11 +1436,14 @@ Publications:
<para>
If a publication publishes <command>UPDATE</command> or
- <command>DELETE</command> operations, any column list must include the
- table's replica identity columns (see
- <xref linkend="sql-altertable-replica-identity"/>).
+ <command>DELETE</command> operations, any column list must include table's
+ replica identity columns and any column list specified with
+ <literal>EXCEPT</literal> clause must not include the table's replica
+ identity columns (see <xref linkend="sql-altertable-replica-identity"/>).
If a publication publishes only <command>INSERT</command> operations, then
- the column list may omit replica identity columns.
+ the column list may omit replica identity columns and the column list
+ specified with <literal>EXCEPT</literal> clause may include replica identity
+ columns.
</para>
<para>
@@ -1474,18 +1488,21 @@ Publications:
<title>Examples</title>
<para>
- Create a table <literal>t1</literal> to be used in the following example.
+ Create tables <literal>t1</literal> and <literal>t2</literal> to be used in
+ the following example.
<programlisting>
/* pub # */ CREATE TABLE t1(id int, a text, b text, c text, d text, e text, PRIMARY KEY(id));
+/* pub # */ CREATE TABLE t2(id int, a text, b text, c text, d text, e text, PRIMARY KEY(id));
</programlisting></para>
<para>
Create a publication <literal>p1</literal>. A column list is defined for
- table <literal>t1</literal> to reduce the number of columns that will be
- replicated. Notice that the order of column names in the column list does
- not matter.
+ table <literal>t1</literal>, and another column list is defined for table
+ <literal>t2</literal> using the <literal>EXCEPT</literal> clause to reduce
+ the number of columns that will be replicated. Note that the order of column
+ names in the column lists does not matter.
<programlisting>
-/* pub # */ CREATE PUBLICATION p1 FOR TABLE t1 (id, b, a, d);
+/* pub # */ CREATE PUBLICATION p1 FOR TABLE t1 (id, b, a, d), t2 EXCEPT (d, a);
</programlisting></para>
<para>
@@ -1499,6 +1516,7 @@ Publications:
postgres | f | f | t | t | t | t | none | f
Tables:
"public.t1" (id, a, b, d)
+ "public.t2" EXCEPT (a, d)
</programlisting></para>
<para>
@@ -1519,23 +1537,41 @@ Indexes:
"t1_pkey" PRIMARY KEY, btree (id)
Publications:
"p1" (id, a, b, d)
+
+/* pub # */ \d t2
+ Table "public.t2"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ id | integer | | not null |
+ a | text | | |
+ b | text | | |
+ c | text | | |
+ d | text | | |
+ e | text | | |
+Indexes:
+ "t2_pkey" PRIMARY KEY, btree (id)
+Publications:
+ "p1" EXCEPT (a, d)
</programlisting></para>
<para>
- On the subscriber node, create a table <literal>t1</literal> which now
- only needs a subset of the columns that were on the publisher table
- <literal>t1</literal>, and also create the subscription
+ On the subscriber node, create tables <literal>t1</literal> and
+ <literal>t2</literal> which now only needs a subset of the columns that
+ were on the publisher tables <literal>t1</literal> and
+ <literal>t2</literal>, and also create the subscription
<literal>s1</literal> that subscribes to the publication
<literal>p1</literal>.
<programlisting>
/* sub # */ CREATE TABLE t1(id int, b text, a text, d text, PRIMARY KEY(id));
+/* sub # */ CREATE TABLE t2(id int, b text, c text, e text, PRIMARY KEY(id));
/* sub # */ CREATE SUBSCRIPTION s1
/* sub - */ CONNECTION 'host=localhost dbname=test_pub application_name=s1'
/* sub - */ PUBLICATION p1;
</programlisting></para>
<para>
- On the publisher node, insert some rows to table <literal>t1</literal>.
+ On the publisher node, insert some rows to tables <literal>t1</literal>
+ and <literal>t2</literal>
<programlisting>
/* pub # */ INSERT INTO t1 VALUES(1, 'a-1', 'b-1', 'c-1', 'd-1', 'e-1');
/* pub # */ INSERT INTO t1 VALUES(2, 'a-2', 'b-2', 'c-2', 'd-2', 'e-2');
@@ -1547,11 +1583,21 @@ Publications:
2 | a-2 | b-2 | c-2 | d-2 | e-2
3 | a-3 | b-3 | c-3 | d-3 | e-3
(3 rows)
+/* pub # */ INSERT INTO t2 VALUES(1, 'a-1', 'b-1', 'c-1', 'd-1', 'e-1');
+/* pub # */ INSERT INTO t2 VALUES(2, 'a-2', 'b-2', 'c-2', 'd-2', 'e-2');
+/* pub # */ INSERT INTO t2 VALUES(3, 'a-3', 'b-3', 'c-3', 'd-3', 'e-3');
+/* pub # */ SELECT * FROM t2 ORDER BY id;
+ id | a | b | c | d | e
+----+-----+-----+-----+-----+-----
+ 1 | a-1 | b-1 | c-1 | d-1 | e-1
+ 2 | a-2 | b-2 | c-2 | d-2 | e-2
+ 3 | a-3 | b-3 | c-3 | d-3 | e-3
+(3 rows)
</programlisting></para>
<para>
- Only data from the column list of publication <literal>p1</literal> is
- replicated.
+ Only data specified by the column lists of publication
+ <literal>p1</literal> is replicated.
<programlisting>
/* sub # */ SELECT * FROM t1 ORDER BY id;
id | b | a | d
@@ -1560,6 +1606,13 @@ Publications:
2 | b-2 | a-2 | d-2
3 | b-3 | a-3 | d-3
(3 rows)
+/* sub # */ SELECT * FROM t2 ORDER BY id;
+ id | b | c | e
+----+-----+-----+-----
+ 1 | b-1 | c-1 | e-1
+ 2 | b-2 | c-2 | e-2
+ 3 | b-3 | c-3 | e-3
+(3 rows)
</programlisting></para>
</sect2>
@@ -1656,6 +1709,17 @@ Publications:
</itemizedlist>
</para>
+ <para>
+ Generated columns specified in the column list with the
+ <literal>EXCEPT</literal> clause are not published, regardless of the value
+ of the <link linkend="sql-createpublication-params-with-publish-generated-columns">
+ <literal>publish_generated_columns</literal></link> parameter. However,
+ generated columns that are not part of the column list with the
+ <literal>EXCEPT</literal> clause are published according to the value of the
+ <link linkend="sql-createpublication-params-with-publish-generated-columns">
+ <literal>publish_generated_columns</literal></link> parameter.
+ </para>
+
<para>
The following table summarizes behavior when there are generated columns
involved in the logical replication. Results are shown for when
diff --git a/doc/src/sgml/ref/alter_publication.sgml b/doc/src/sgml/ref/alter_publication.sgml
index 4afe62b7cfd..62204b4a416 100644
--- a/doc/src/sgml/ref/alter_publication.sgml
+++ b/doc/src/sgml/ref/alter_publication.sgml
@@ -32,7 +32,7 @@ ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RESET
<phrase>where <replaceable class="parameter">publication_object</replaceable> is one of:</phrase>
- TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [ ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) ] [ WHERE ( <replaceable class="parameter">expression</replaceable> ) ] [, ... ]
+ TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [ [ EXCEPT ] ( column_name [, ... ] ) ] [ WHERE ( <replaceable class="parameter">expression</replaceable> ) ] [, ... ]
TABLES IN SCHEMA { <replaceable class="parameter">schema_name</replaceable> | CURRENT_SCHEMA } [, ... ]
<phrase>where <replaceable class="parameter">exception_object</replaceable> is:</phrase>
@@ -262,6 +262,14 @@ ALTER PUBLICATION production_publication ADD ALL TABLES EXCEPT (users, departmen
<structname>production_publication</structname>:
<programlisting>
ALTER PUBLICATION production_publication ADD TABLE users, departments, TABLES IN SCHEMA production;
+</programlisting></para>
+
+ <para>
+ Alter publication <structname>mypublication</structname> to add table
+ <structname>users</structname> except column
+ <structname>security_pin</structname>:
+<programlisting>
+ALTER PUBLICATION production_publication ADD TABLE users EXCEPT (security_pin);
</programlisting></para>
<para>
diff --git a/doc/src/sgml/ref/create_publication.sgml b/doc/src/sgml/ref/create_publication.sgml
index d28a9a10c86..21aec0ae1c7 100644
--- a/doc/src/sgml/ref/create_publication.sgml
+++ b/doc/src/sgml/ref/create_publication.sgml
@@ -27,7 +27,7 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
<phrase>where <replaceable class="parameter">publication_object</replaceable> is one of:</phrase>
- TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [ ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) ] [ WHERE ( <replaceable class="parameter">expression</replaceable> ) ] [, ... ]
+ TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [ [ EXCEPT ] ( column_name [, ... ] ) ] [ WHERE ( <replaceable class="parameter">expression</replaceable> ) ] [, ... ]
TABLES IN SCHEMA { <replaceable class="parameter">schema_name</replaceable> | CURRENT_SCHEMA } [, ... ]
<phrase>where <replaceable class="parameter">all_publication_object</replaceable> is one of:</phrase>
@@ -96,17 +96,24 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
</para>
<para>
- When a column list is specified, only the named columns are replicated.
- The column list can contain stored generated columns as well. If the
- column list is omitted, the publication will replicate all non-generated
- columns (including any added in the future) by default. Stored generated
- columns can also be replicated if <literal>publish_generated_columns</literal>
- is set to <literal>stored</literal>. Specifying a column list has no
- effect on <literal>TRUNCATE</literal> commands. See
+ When a column list without <literal>EXCEPT</literal> is specified, only
+ the named columns are replicated. The column list can contain stored
+ generated columns as well. If the column list is omitted, the publication
+ will replicate all non-generated columns (including any added in the
+ future) by default. Stored generated columns can also be replicated if
+ <literal>publish_generated_columns</literal> is set to
+ <literal>stored</literal>. Specifying a column list has no effect on
+ <literal>TRUNCATE</literal> commands. See
<xref linkend="logical-replication-col-lists"/> for details about column
lists.
</para>
+ <para>
+ When a column list is specified with <literal>EXCEPT</literal>, the named
+ columns are not replicated. Specifying a column list has no effect on
+ <literal>TRUNCATE</literal> commands.
+ </para>
+
<para>
Only persistent base tables and partitioned tables can be part of a
publication. Temporary tables, unlogged tables, foreign tables,
@@ -368,10 +375,12 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
</para>
<para>
- Any column list must include the <literal>REPLICA IDENTITY</literal> columns
- in order for <command>UPDATE</command> or <command>DELETE</command>
- operations to be published. There are no column list restrictions if the
- publication publishes only <command>INSERT</command> operations.
+ In order for <command>UPDATE</command> or <command>DELETE</command>
+ operations to work, all the <literal>REPLICA IDENTITY</literal> columns must
+ be published. So, any column list must name all
+ <literal>REPLICA IDENTITY</literal> columns, and any
+ <literal>EXCEPT</literal> column list must not name any
+ <literal>REPLICA IDENTITY</literal> columns.
</para>
<para>
@@ -394,6 +403,16 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
to be published.
</para>
+ <para>
+ The generated columns that are part of the column list specified with the
+ <literal>EXCEPT</literal> clause are not published, regardless of the
+ <literal>publish_generated_columns</literal> option. However, generated
+ columns that are not part of the column list specified with the
+ <literal>EXCEPT</literal> clause are published according to the value of the
+ <literal>publish_generated_columns</literal> option. See
+ <xref linkend="logical-replication-gencols"/> for details.
+ </para>
+
<para>
The row filter on a table becomes redundant if
<literal>FOR TABLES IN SCHEMA</literal> is specified and the table
@@ -516,6 +535,15 @@ CREATE PUBLICATION mypublication FOR ALL TABLES EXCEPT (users, departments);
CREATE PUBLICATION users_filtered FOR TABLE users (user_id, firstname);
</programlisting></para>
+ <para>
+ Create a publication that publishes all changes for table
+ <structname>users</structname> except changes for column
+ <structname>security_pin</structname>:
+<programlisting>
+CREATE PUBLICATION users_safe FOR TABLE users EXCEPT (security_pin);
+</programlisting>
+ </para>
+
<para>
Create a publication that publishes all sequences for synchronization:
<programlisting>
diff --git a/doc/src/sgml/system-views.sgml b/doc/src/sgml/system-views.sgml
index 7971498fe75..243697d4d8c 100644
--- a/doc/src/sgml/system-views.sgml
+++ b/doc/src/sgml/system-views.sgml
@@ -2698,6 +2698,16 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx
Expression for the table's publication qualifying condition
</para></entry>
</row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>exceptcol</structfield> <type>bool</type>
+ </para>
+ <para>
+ True if a column list with <literal>EXCEPT</literal> clause is specified
+ for the table in the publication.
+ </para></entry>
+ </row>
</tbody>
</tgroup>
</table>
diff --git a/src/backend/catalog/pg_publication.c b/src/backend/catalog/pg_publication.c
index bec3a34e48f..02bba6b08cf 100644
--- a/src/backend/catalog/pg_publication.c
+++ b/src/backend/catalog/pg_publication.c
@@ -266,14 +266,19 @@ is_schema_publication(Oid pubid)
* If a column list is found, the corresponding bitmap is returned through the
* cols parameter, if provided. The bitmap is constructed within the given
* memory context (mcxt).
+ *
+ * If a column list is found specified with EXCEPT clause, except_columns is set
+ * to true.
*/
bool
check_and_fetch_column_list(Publication *pub, Oid relid, MemoryContext mcxt,
- Bitmapset **cols)
+ Bitmapset **cols, bool *except_columns)
{
HeapTuple cftuple;
bool found = false;
+ *except_columns = false;
+
if (pub->alltables)
return false;
@@ -299,6 +304,16 @@ check_and_fetch_column_list(Publication *pub, Oid relid, MemoryContext mcxt,
found = true;
}
+ /* Lookup the except attribute */
+ cfdatum = SysCacheGetAttr(PUBLICATIONRELMAP, cftuple,
+ Anum_pg_publication_rel_prexcept, &isnull);
+
+ if (!isnull)
+ {
+ Assert(!pub->alltables);
+ *except_columns = DatumGetBool(cfdatum);
+ }
+
ReleaseSysCache(cftuple);
}
@@ -660,10 +675,12 @@ pub_collist_to_bitmapset(Bitmapset *columns, Datum pubcols, MemoryContext mcxt)
* Returns a bitmap representing the columns of the specified table.
*
* Generated columns are included if include_gencols_type is
- * PUBLISH_GENCOLS_STORED.
+ * PUBLISH_GENCOLS_STORED. Columns that are in the except_cols are excluded from
+ * the column list.
*/
Bitmapset *
-pub_form_cols_map(Relation relation, PublishGencolsType include_gencols_type)
+pub_form_cols_map(Relation relation, PublishGencolsType include_gencols_type,
+ Bitmapset *except_cols)
{
Bitmapset *result = NULL;
TupleDesc desc = RelationGetDescr(relation);
@@ -686,6 +703,9 @@ pub_form_cols_map(Relation relation, PublishGencolsType include_gencols_type)
continue;
}
+ if (except_cols && bms_is_member(att->attnum, except_cols))
+ continue;
+
result = bms_add_member(result, att->attnum);
}
@@ -790,8 +810,10 @@ GetRelationPublications(Oid relid, bool except_flag)
{
HeapTuple tup = &pubrellist->members[i]->tuple;
Oid pubid = ((Form_pg_publication_rel) GETSTRUCT(tup))->prpubid;
+ bool is_except_table = ((Form_pg_publication_rel) GETSTRUCT(tup))->prexcept &&
+ heap_attisnull(tup, Anum_pg_publication_rel_prattrs, NULL);
- if (except_flag == ((Form_pg_publication_rel) GETSTRUCT(tup))->prexcept)
+ if (except_flag == is_except_table)
result = lappend_oid(result, pubid);
}
@@ -831,10 +853,12 @@ GetPublicationRelations(Oid pubid, PublicationPartOpt pub_partopt,
while (HeapTupleIsValid(tup = systable_getnext(scan)))
{
Form_pg_publication_rel pubrel;
+ bool has_collist = false;
pubrel = (Form_pg_publication_rel) GETSTRUCT(tup);
+ has_collist = !heap_attisnull(tup, Anum_pg_publication_rel_prattrs, NULL);
- if (except_flag == pubrel->prexcept)
+ if (except_flag == (pubrel->prexcept && !has_collist))
result = GetPubPartitionOptionRelations(result, pub_partopt,
pubrel->prrelid);
@@ -1163,7 +1187,7 @@ GetPublicationByName(const char *pubname, bool missing_ok)
Datum
pg_get_publication_tables(PG_FUNCTION_ARGS)
{
-#define NUM_PUBLICATION_TABLES_ELEM 4
+#define NUM_PUBLICATION_TABLES_ELEM 5
FuncCallContext *funcctx;
List *table_infos = NIL;
@@ -1270,6 +1294,8 @@ pg_get_publication_tables(PG_FUNCTION_ARGS)
INT2VECTOROID, -1, 0);
TupleDescInitEntry(tupdesc, (AttrNumber) 4, "qual",
PG_NODE_TREEOID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 5, "exceptcol",
+ BOOLOID, -1, 0);
funcctx->tuple_desc = BlessTupleDesc(tupdesc);
funcctx->user_fctx = table_infos;
@@ -1291,6 +1317,7 @@ pg_get_publication_tables(PG_FUNCTION_ARGS)
Oid schemaid = get_rel_namespace(relid);
Datum values[NUM_PUBLICATION_TABLES_ELEM] = {0};
bool nulls[NUM_PUBLICATION_TABLES_ELEM] = {0};
+ Bitmapset *except_columns = NULL;
/*
* Form tuple with appropriate data.
@@ -1315,11 +1342,36 @@ pg_get_publication_tables(PG_FUNCTION_ARGS)
if (HeapTupleIsValid(pubtuple))
{
- /* Lookup the column list attribute. */
values[2] = SysCacheGetAttr(PUBLICATIONRELMAP, pubtuple,
Anum_pg_publication_rel_prattrs,
&(nulls[2]));
+ if (!nulls[2])
+ {
+ Datum exceptDatum;
+ bool isnull;
+
+ /*
+ * We fetch pubtuple if publication is not FOR ALL TABLES and
+ * not FOR TABLES IN SCHEMA. So if prexcept is true, it
+ * indicates that prattrs contains columns to be excluded for
+ * replication.
+ */
+ exceptDatum = SysCacheGetAttr(PUBLICATIONRELMAP, pubtuple,
+ Anum_pg_publication_rel_prexcept,
+ &isnull);
+
+ if (!isnull && DatumGetBool(exceptDatum))
+ {
+ except_columns = pub_collist_to_bitmapset(NULL, values[2], NULL);
+
+ if (except_columns)
+ values[4] = BoolGetDatum(true);
+ else
+ values[4] = BoolGetDatum(false);
+ }
+ }
+
/* Null indicates no filter. */
values[3] = SysCacheGetAttr(PUBLICATIONRELMAP, pubtuple,
Anum_pg_publication_rel_prqual,
@@ -1331,8 +1383,12 @@ pg_get_publication_tables(PG_FUNCTION_ARGS)
nulls[3] = true;
}
- /* Show all columns when the column list is not specified. */
- if (nulls[2])
+ /*
+ * Construct column list to show all columns when no column list is
+ * specified or to show remaining columns when a column list is
+ * provided with EXCEPT.
+ */
+ if (except_columns || nulls[2])
{
Relation rel = table_open(relid, AccessShareLock);
int nattnums = 0;
@@ -1363,6 +1419,13 @@ pg_get_publication_tables(PG_FUNCTION_ARGS)
continue;
}
+ /*
+ * Skip columns that are part of column list specified with
+ * EXCEPT.
+ */
+ if (except_columns && bms_is_member(att->attnum, except_columns))
+ continue;
+
attnums[nattnums++] = att->attnum;
}
@@ -1371,6 +1434,11 @@ pg_get_publication_tables(PG_FUNCTION_ARGS)
values[2] = PointerGetDatum(buildint2vector(attnums, nattnums));
nulls[2] = false;
}
+ else
+ {
+ values[2] = (Datum) 0;
+ nulls[2] = true;
+ }
table_close(rel, AccessShareLock);
}
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index dec8df4f8ee..9003f25fac4 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -388,7 +388,8 @@ CREATE VIEW pg_publication_tables AS
WHERE a.attrelid = GPT.relid AND
a.attnum = ANY(GPT.attrs)
) AS attnames,
- pg_get_expr(GPT.qual, GPT.relid) AS rowfilter
+ pg_get_expr(GPT.qual, GPT.relid) AS rowfilter,
+ gpt.exceptcol AS exceptcol
FROM pg_publication P,
LATERAL pg_get_publication_tables(P.pubname) GPT,
pg_class C JOIN pg_namespace N ON (N.oid = C.relnamespace)
diff --git a/src/backend/commands/publicationcmds.c b/src/backend/commands/publicationcmds.c
index e70fdefbc90..11ec4e2818b 100644
--- a/src/backend/commands/publicationcmds.c
+++ b/src/backend/commands/publicationcmds.c
@@ -237,7 +237,6 @@ ObjectsInPublicationToOids(List *pubobjspec_list, ParseState *pstate,
switch (pubobj->pubobjtype)
{
case PUBLICATIONOBJ_TABLE:
- pubobj->pubtable->except = false;
*rels = lappend(*rels, pubobj->pubtable);
break;
case PUBLICATIONOBJ_EXCEPT_TABLE:
@@ -391,8 +390,8 @@ pub_rf_contains_invalid_column(Oid pubid, Relation relation, List *ancestors,
* This function evaluates two conditions:
*
* 1. Ensures that all columns referenced in the REPLICA IDENTITY are covered
- * by the column list. If any column is missing, *invalid_column_list is set
- * to true.
+ * by the column list and are not part of the column list specified with
+ * EXCEPT. If any column is missing, *invalid_column_list is set to true.
* 2. Ensures that all the generated columns referenced in the REPLICA IDENTITY
* are published, either by being explicitly named in the column list or, if
* no column list is specified, by setting the option
@@ -414,6 +413,7 @@ pub_contains_invalid_column(Oid pubid, Relation relation, List *ancestors,
TupleDesc desc = RelationGetDescr(relation);
Publication *pub;
int x;
+ bool except_columns = false;
*invalid_column_list = false;
*invalid_gen_col = false;
@@ -437,7 +437,8 @@ pub_contains_invalid_column(Oid pubid, Relation relation, List *ancestors,
/* Fetch the column list */
pub = GetPublication(pubid);
- check_and_fetch_column_list(pub, publish_as_relid, NULL, &columns);
+ check_and_fetch_column_list(pub, publish_as_relid, NULL, &columns,
+ &except_columns);
if (relation->rd_rel->relreplident == REPLICA_IDENTITY_FULL)
{
@@ -527,8 +528,14 @@ pub_contains_invalid_column(Oid pubid, Relation relation, List *ancestors,
attnum = get_attnum(publish_as_relid, colname);
}
- /* replica identity column, not covered by the column list */
- *invalid_column_list |= !bms_is_member(attnum, columns);
+ /*
+ * Replica identity column, not covered by the column list or is part
+ * of column list specified with EXCEPT.
+ */
+ if (except_columns)
+ *invalid_column_list |= bms_is_member(attnum, columns);
+ else
+ *invalid_column_list |= !bms_is_member(attnum, columns);
if (*invalid_column_list && *invalid_gen_col)
break;
@@ -1515,6 +1522,7 @@ AlterPublicationTables(AlterPublicationStmt *stmt, HeapTuple tup,
HeapTuple rftuple;
Node *oldrelwhereclause = NULL;
Bitmapset *oldcolumns = NULL;
+ bool oldexcept = false;
/* look up the cache for the old relmap */
rftuple = SearchSysCache2(PUBLICATIONRELMAP,
@@ -1528,23 +1536,28 @@ AlterPublicationTables(AlterPublicationStmt *stmt, HeapTuple tup,
if (HeapTupleIsValid(rftuple))
{
bool isnull = true;
- Datum whereClauseDatum;
- Datum columnListDatum;
+ Datum datum;
/* Load the WHERE clause for this table. */
- whereClauseDatum = SysCacheGetAttr(PUBLICATIONRELMAP, rftuple,
- Anum_pg_publication_rel_prqual,
- &isnull);
+ datum = SysCacheGetAttr(PUBLICATIONRELMAP, rftuple,
+ Anum_pg_publication_rel_prqual,
+ &isnull);
if (!isnull)
- oldrelwhereclause = stringToNode(TextDatumGetCString(whereClauseDatum));
+ oldrelwhereclause = stringToNode(TextDatumGetCString(datum));
/* Transform the int2vector column list to a bitmap. */
- columnListDatum = SysCacheGetAttr(PUBLICATIONRELMAP, rftuple,
- Anum_pg_publication_rel_prattrs,
- &isnull);
+ datum = SysCacheGetAttr(PUBLICATIONRELMAP, rftuple,
+ Anum_pg_publication_rel_prattrs,
+ &isnull);
+ if (!isnull)
+ oldcolumns = pub_collist_to_bitmapset(NULL, datum, NULL);
+ /* Load the prexcept flag for this table. */
+ datum = SysCacheGetAttr(PUBLICATIONRELMAP, rftuple,
+ Anum_pg_publication_rel_prexcept,
+ &isnull);
if (!isnull)
- oldcolumns = pub_collist_to_bitmapset(NULL, columnListDatum, NULL);
+ oldexcept = DatumGetBool(datum);
ReleaseSysCache(rftuple);
}
@@ -1571,13 +1584,14 @@ AlterPublicationTables(AlterPublicationStmt *stmt, HeapTuple tup,
* Check if any of the new set of relations matches with the
* existing relations in the publication. Additionally, if the
* relation has an associated WHERE clause, check the WHERE
- * expressions also match. Same for the column list. Drop the
- * rest.
+ * expressions also match. Same for the column list and except
+ * flag. Drop the rest.
*/
if (newrelid == oldrelid)
{
if (equal(oldrelwhereclause, newpubrel->whereClause) &&
- bms_equal(oldcolumns, newcolumns))
+ bms_equal(oldcolumns, newcolumns) &&
+ oldexcept == newpubrel->except)
{
found = true;
break;
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 87e78f13d94..94f2e654551 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -535,7 +535,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
OptWhereClause operator_def_arg
%type <list> opt_column_and_period_list
%type <list> rowsfrom_item rowsfrom_list opt_col_def_list
-%type <boolean> opt_ordinality opt_without_overlaps
+%type <boolean> opt_ordinality opt_without_overlaps opt_except
%type <list> ExclusionConstraintList ExclusionConstraintElem
%type <list> func_arg_list func_arg_list_opt
%type <node> func_arg_expr
@@ -4480,6 +4480,11 @@ opt_without_overlaps:
| /*EMPTY*/ { $$ = false; }
;
+opt_except:
+ EXCEPT { $$ = true; }
+ | /*EMPTY*/ { $$ = false; }
+ ;
+
opt_column_list:
'(' columnList ')' { $$ = $2; }
| /*EMPTY*/ { $$ = NIL; }
@@ -10796,14 +10801,15 @@ CreatePublicationStmt:
* relation_expr here.
*/
PublicationObjSpec:
- TABLE relation_expr opt_column_list OptWhereClause
+ TABLE relation_expr opt_except opt_column_list OptWhereClause
{
$$ = makeNode(PublicationObjSpec);
$$->pubobjtype = PUBLICATIONOBJ_TABLE;
$$->pubtable = makeNode(PublicationTable);
$$->pubtable->relation = $2;
- $$->pubtable->columns = $3;
- $$->pubtable->whereClause = $4;
+ $$->pubtable->except = $3;
+ $$->pubtable->columns = $4;
+ $$->pubtable->whereClause = $5;
$$->location = @1;
}
| TABLES IN_P SCHEMA ColId
@@ -10819,7 +10825,7 @@ PublicationObjSpec:
$$->pubobjtype = PUBLICATIONOBJ_TABLES_IN_CUR_SCHEMA;
$$->location = @4;
}
- | ColId opt_column_list OptWhereClause
+ | ColId opt_except opt_column_list OptWhereClause
{
$$ = makeNode(PublicationObjSpec);
$$->pubobjtype = PUBLICATIONOBJ_CONTINUATION;
@@ -10827,7 +10833,7 @@ PublicationObjSpec:
* If either a row filter or column list is specified, create
* a PublicationTable object.
*/
- if ($2 || $3)
+ if ($2 || $3 || $4)
{
/*
* The OptWhereClause must be stored here but it is
@@ -10837,8 +10843,9 @@ PublicationObjSpec:
*/
$$->pubtable = makeNode(PublicationTable);
$$->pubtable->relation = makeRangeVar(NULL, $1, @1);
- $$->pubtable->columns = $2;
- $$->pubtable->whereClause = $3;
+ $$->pubtable->except = $2;
+ $$->pubtable->columns = $3;
+ $$->pubtable->whereClause = $4;
}
else
{
@@ -10846,25 +10853,27 @@ PublicationObjSpec:
}
$$->location = @1;
}
- | ColId indirection opt_column_list OptWhereClause
+ | ColId indirection opt_except opt_column_list OptWhereClause
{
$$ = makeNode(PublicationObjSpec);
$$->pubobjtype = PUBLICATIONOBJ_CONTINUATION;
$$->pubtable = makeNode(PublicationTable);
$$->pubtable->relation = makeRangeVarFromQualifiedName($1, $2, @1, yyscanner);
- $$->pubtable->columns = $3;
- $$->pubtable->whereClause = $4;
+ $$->pubtable->except = $3;
+ $$->pubtable->columns = $4;
+ $$->pubtable->whereClause = $5;
$$->location = @1;
}
/* grammar like tablename * , ONLY tablename, ONLY ( tablename ) */
- | extended_relation_expr opt_column_list OptWhereClause
+ | extended_relation_expr opt_except opt_column_list OptWhereClause
{
$$ = makeNode(PublicationObjSpec);
$$->pubobjtype = PUBLICATIONOBJ_CONTINUATION;
$$->pubtable = makeNode(PublicationTable);
$$->pubtable->relation = $1;
- $$->pubtable->columns = $2;
- $$->pubtable->whereClause = $3;
+ $$->pubtable->except = $2;
+ $$->pubtable->columns = $3;
+ $$->pubtable->whereClause = $4;
}
| CURRENT_SCHEMA
{
@@ -19856,6 +19865,13 @@ preprocess_pubobj_list(List *pubobjspec_list, core_yyscan_t yyscanner)
errmsg("invalid table name"),
parser_errposition(pubobj->location));
+ if (pubobj->pubtable && pubobj->pubtable->except &&
+ pubobj->pubtable->columns == NULL)
+ ereport(ERROR,
+ errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("table without column list cannot use EXCEPT clause"),
+ parser_errposition(pubobj->location));
+
if (pubobj->name)
{
/* convert it to PublicationTable */
diff --git a/src/backend/replication/logical/tablesync.c b/src/backend/replication/logical/tablesync.c
index e5a2856fd17..6ca32fd85bc 100644
--- a/src/backend/replication/logical/tablesync.c
+++ b/src/backend/replication/logical/tablesync.c
@@ -720,10 +720,18 @@ copy_read_data(void *outbuf, int minread, int maxread)
* This function also returns (a) the relation qualifications to be used in
* the COPY command, and (b) whether the remote relation has published any
* generated column.
+ *
+ * With the introduction of the EXCEPT qualifier in column lists, it is now
+ * possible to define a publication that excludes all columns of a table. When
+ * the column list is fetched from the remote server and is NULL, it normally
+ * indicates that all columns are included. To distinguish this from the case
+ * where all columns are explicitly excluded, the 'all_cols_excluded' flag has
+ * been introduced.
*/
static void
fetch_remote_table_info(char *nspname, char *relname, LogicalRepRelation *lrel,
- List **qual, bool *gencol_published)
+ List **qual, bool *gencol_published,
+ bool *all_cols_excluded)
{
WalRcvExecResult *res;
StringInfoData cmd;
@@ -737,6 +745,9 @@ fetch_remote_table_info(char *nspname, char *relname, LogicalRepRelation *lrel,
Bitmapset *included_cols = NULL;
int server_version = walrcv_server_version(LogRepWorkerWalRcvConn);
+ Assert(*gencol_published == false);
+ Assert(*all_cols_excluded == false);
+
lrel->nspname = nspname;
lrel->relname = relname;
@@ -787,7 +798,7 @@ fetch_remote_table_info(char *nspname, char *relname, LogicalRepRelation *lrel,
{
WalRcvExecResult *pubres;
TupleTableSlot *tslot;
- Oid attrsRow[] = {INT2VECTOROID};
+ Oid attrsRow[] = {INT2VECTOROID, BOOLOID};
/* Build the pub_names comma-separated string. */
pub_names = makeStringInfo();
@@ -801,7 +812,17 @@ fetch_remote_table_info(char *nspname, char *relname, LogicalRepRelation *lrel,
appendStringInfo(&cmd,
"SELECT DISTINCT"
" (CASE WHEN (array_length(gpt.attrs, 1) = c.relnatts)"
- " THEN NULL ELSE gpt.attrs END)"
+ " THEN NULL ELSE gpt.attrs END)");
+
+ /*
+ * When publication is created with EXCEPT (column-list) and all
+ * columns are specified, gpt.attrs will be NULL and no columns are
+ * published in this case.
+ */
+ if (server_version >= 190000)
+ appendStringInfo(&cmd, ", gpt.attrs IS NULL AND c.relnatts > 0");
+
+ appendStringInfo(&cmd,
" FROM pg_publication p,"
" LATERAL pg_get_publication_tables(p.pubname) gpt,"
" pg_class c"
@@ -811,7 +832,7 @@ fetch_remote_table_info(char *nspname, char *relname, LogicalRepRelation *lrel,
pub_names->data);
pubres = walrcv_exec(LogRepWorkerWalRcvConn, cmd.data,
- lengthof(attrsRow), attrsRow);
+ server_version >= 190000 ? 2 : 1, attrsRow);
if (pubres->status != WALRCV_OK_TUPLES)
ereport(ERROR,
@@ -858,6 +879,9 @@ fetch_remote_table_info(char *nspname, char *relname, LogicalRepRelation *lrel,
included_cols = bms_add_member(included_cols, elems[natt]);
}
+ if (server_version >= 190000)
+ *all_cols_excluded = DatumGetBool(slot_getattr(tslot, 2, &isnull));
+
ExecClearTuple(tslot);
}
ExecDropSingleTupleTableSlot(tslot);
@@ -920,7 +944,8 @@ fetch_remote_table_info(char *nspname, char *relname, LogicalRepRelation *lrel,
Assert(!isnull);
/* If the column is not in the column list, skip it. */
- if (included_cols != NULL && !bms_is_member(attnum, included_cols))
+ if (*all_cols_excluded ||
+ (included_cols != NULL && !bms_is_member(attnum, included_cols)))
{
ExecClearTuple(slot);
continue;
@@ -1052,11 +1077,15 @@ copy_table(Relation rel)
ParseState *pstate;
List *options = NIL;
bool gencol_published = false;
+ bool all_cols_excluded = false;
/* Get the publisher relation info. */
fetch_remote_table_info(get_namespace_name(RelationGetNamespace(rel)),
RelationGetRelationName(rel), &lrel, &qual,
- &gencol_published);
+ &gencol_published, &all_cols_excluded);
+
+ if (all_cols_excluded)
+ return;
/* Put the relation into relmap. */
logicalrep_relmap_update(&lrel);
diff --git a/src/backend/replication/pgoutput/pgoutput.c b/src/backend/replication/pgoutput/pgoutput.c
index a9593c5d9da..7f534618cf4 100644
--- a/src/backend/replication/pgoutput/pgoutput.c
+++ b/src/backend/replication/pgoutput/pgoutput.c
@@ -185,6 +185,16 @@ typedef struct RelationSyncEntry
* row filter expressions, column list, etc.
*/
MemoryContext entry_cxt;
+
+ /*
+ * Indicates whether no columns are published for a given relation. With
+ * the introduction of the EXCEPT qualifier in column lists, it is now
+ * possible to define a publication that excludes all columns of a table.
+ * However, the 'columns' attribute cannot represent this case, since a
+ * NULL value implies that all columns are published. To distinguish this
+ * scenario, the 'all_cols_excluded' flag is introduced.
+ */
+ bool all_cols_excluded;
} RelationSyncEntry;
/*
@@ -1091,12 +1101,21 @@ check_and_init_gencol(PGOutputData *data, List *publications,
*/
foreach_ptr(Publication, pub, publications)
{
+ bool has_column_list = false;
+ bool except_columns = false;
+
+ has_column_list = check_and_fetch_column_list(pub,
+ entry->publish_as_relid,
+ NULL, NULL,
+ &except_columns);
+
/*
* The column list takes precedence over the
* 'publish_generated_columns' parameter. Those will be checked later,
- * see pgoutput_column_list_init.
+ * see pgoutput_column_list_init. But when a column list is specified
+ * with EXCEPT, it should be checked.
*/
- if (check_and_fetch_column_list(pub, entry->publish_as_relid, NULL, NULL))
+ if (has_column_list && !except_columns)
continue;
if (first)
@@ -1145,19 +1164,41 @@ pgoutput_column_list_init(PGOutputData *data, List *publications,
{
Publication *pub = lfirst(lc);
Bitmapset *cols = NULL;
+ bool except_columns = false;
+ bool all_cols_excluded = false;
/* Retrieve the bitmap of columns for a column list publication. */
found_pub_collist |= check_and_fetch_column_list(pub,
entry->publish_as_relid,
- entry->entry_cxt, &cols);
+ entry->entry_cxt, &cols,
+ &except_columns);
+
+ /*
+ * If column list is specified with EXCEPT retrieve bitmap of columns
+ * which are not part of this column list.
+ */
+ if (except_columns)
+ {
+ MemoryContext oldcxt = MemoryContextSwitchTo(entry->entry_cxt);
+
+ cols = pub_form_cols_map(relation,
+ entry->include_gencols_type, cols);
+ MemoryContextSwitchTo(oldcxt);
+
+ if (!cols)
+ all_cols_excluded = true;
+ }
/*
- * For non-column list publications — e.g. TABLE (without a column
- * list), ALL TABLES, or ALL TABLES IN SCHEMA, we consider all columns
- * of the table (including generated columns when
+ * If 'cols' is null, it indicates that the publication is either a
+ * non-column list publication or one where all columns are excluded.
+ * When 'all_cols_excluded' is true, it explicitly means all columns
+ * have been excluded. For non-column list publications — e.g. TABLE
+ * (without a column list), ALL TABLES, or ALL TABLES IN SCHEMA, we
+ * consider all columns of the table (including generated columns when
* 'publish_generated_columns' parameter is true).
*/
- if (!cols)
+ if (!all_cols_excluded && !cols)
{
/*
* Cache the table columns for the first publication with no
@@ -1169,7 +1210,7 @@ pgoutput_column_list_init(PGOutputData *data, List *publications,
MemoryContext oldcxt = MemoryContextSwitchTo(entry->entry_cxt);
relcols = pub_form_cols_map(relation,
- entry->include_gencols_type);
+ entry->include_gencols_type, NULL);
MemoryContextSwitchTo(oldcxt);
}
@@ -1179,9 +1220,11 @@ pgoutput_column_list_init(PGOutputData *data, List *publications,
if (first)
{
entry->columns = cols;
+ entry->all_cols_excluded = all_cols_excluded;
first = false;
}
- else if (!bms_equal(entry->columns, cols))
+ else if ((entry->all_cols_excluded != all_cols_excluded) ||
+ !bms_equal(entry->columns, cols))
ereport(ERROR,
errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("cannot use different column lists for table \"%s.%s\" in different publications",
@@ -1505,6 +1548,13 @@ pgoutput_change(LogicalDecodingContext *ctx, ReorderBufferTXN *txn,
relentry = get_rel_sync_entry(data, relation);
+ /*
+ * If all columns of a table are present in column list specified with
+ * EXCEPT, skip publishing the changes.
+ */
+ if (relentry->all_cols_excluded)
+ return;
+
/* First check the table filter */
switch (action)
{
@@ -2078,6 +2128,7 @@ get_rel_sync_entry(PGOutputData *data, Relation relation)
entry->publish_as_relid = InvalidOid;
entry->columns = NULL;
entry->attrmap = NULL;
+ entry->all_cols_excluded = false;
}
/* Validate the entry */
@@ -2127,6 +2178,7 @@ get_rel_sync_entry(PGOutputData *data, Relation relation)
entry->pubactions.pubupdate = false;
entry->pubactions.pubdelete = false;
entry->pubactions.pubtruncate = false;
+ entry->all_cols_excluded = false;
/*
* Tuple slots cleanups. (Will be rebuilt later if needed).
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index e34aaba7937..1fdb90f6482 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -4933,24 +4933,7 @@ getPublicationTables(Archive *fout, TableInfo tblinfo[], int numTables)
if (tbinfo == NULL)
continue;
- /* OK, make a DumpableObject for this relationship */
- if (strcmp(prexcept, "f") == 0)
- pubrinfo[j].dobj.objType = DO_PUBLICATION_REL;
- else
- pubrinfo[j].dobj.objType = DO_PUBLICATION_EXCEPT_REL;
-
- pubrinfo[j].dobj.catId.tableoid =
- atooid(PQgetvalue(res, i, i_tableoid));
- pubrinfo[j].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
- AssignDumpId(&pubrinfo[j].dobj);
- pubrinfo[j].dobj.namespace = tbinfo->dobj.namespace;
- pubrinfo[j].dobj.name = tbinfo->dobj.name;
- pubrinfo[j].publication = pubinfo;
- pubrinfo[j].pubtable = tbinfo;
- if (PQgetisnull(res, i, i_prrelqual))
- pubrinfo[j].pubrelqual = NULL;
- else
- pubrinfo[j].pubrelqual = pg_strdup(PQgetvalue(res, i, i_prrelqual));
+ pubrinfo[j].pubexcept = (strcmp(prexcept, "t") == 0);
if (!PQgetisnull(res, i, i_prattrs))
{
@@ -4976,10 +4959,29 @@ getPublicationTables(Archive *fout, TableInfo tblinfo[], int numTables)
else
pubrinfo[j].pubrattrs = NULL;
+ /* OK, make a DumpableObject for this relationship */
+ if (pubrinfo[j].pubexcept && !pubrinfo[j].pubrattrs)
+ pubrinfo[j].dobj.objType = DO_PUBLICATION_EXCEPT_REL;
+ else
+ pubrinfo[j].dobj.objType = DO_PUBLICATION_REL;
+
+ pubrinfo[j].dobj.catId.tableoid =
+ atooid(PQgetvalue(res, i, i_tableoid));
+ pubrinfo[j].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
+ AssignDumpId(&pubrinfo[j].dobj);
+ pubrinfo[j].dobj.namespace = tbinfo->dobj.namespace;
+ pubrinfo[j].dobj.name = tbinfo->dobj.name;
+ pubrinfo[j].publication = pubinfo;
+ pubrinfo[j].pubtable = tbinfo;
+ if (PQgetisnull(res, i, i_prrelqual))
+ pubrinfo[j].pubrelqual = NULL;
+ else
+ pubrinfo[j].pubrelqual = pg_strdup(PQgetvalue(res, i, i_prrelqual));
+
/* Decide whether we want to dump it */
selectDumpablePublicationObject(&(pubrinfo[j].dobj), fout);
- if (strcmp(prexcept, "t") == 0)
+ if (pubrinfo[j].pubexcept && !pubrinfo[j].pubrattrs)
simple_ptr_list_append(&exceptinfo, &pubrinfo[j]);
j++;
@@ -5059,7 +5061,12 @@ dumpPublicationTable(Archive *fout, const PublicationRelInfo *pubrinfo)
fmtQualifiedDumpable(tbinfo));
if (pubrinfo->pubrattrs)
+ {
+ if (pubrinfo->pubexcept)
+ appendPQExpBufferStr(query, " EXCEPT");
+
appendPQExpBuffer(query, " (%s)", pubrinfo->pubrattrs);
+ }
if (pubrinfo->pubrelqual)
{
diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h
index 723b5575c53..ca2d356f72a 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -690,6 +690,7 @@ typedef struct _PublicationRelInfo
TableInfo *pubtable;
char *pubrelqual;
char *pubrattrs;
+ bool pubexcept;
} PublicationRelInfo;
/*
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 50b1d435359..6ceb108a35b 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -1560,6 +1560,91 @@ describeTableDetails(const char *pattern, bool verbose, bool showSystem)
return true;
}
+/*
+ * Add a footer to a publication description or a table description.
+ *
+ * 'is_pub_desc' - true for a pub desc; false for a table desc
+ * 'pub_schemas' - true if the pub_desc only shows schemas, otherwise false
+ */
+static bool
+addFooterToPublicationOrTableDesc(PQExpBuffer buf,
+ printTableContent *const cont,
+ const char *footermsg,
+ bool is_pub_desc, bool pub_schemas)
+{
+ PGresult *res;
+ int count;
+ int col = is_pub_desc ? 1 : 0;
+
+ res = PSQLexec(buf->data);
+ if (!res)
+ return false;
+
+ count = PQntuples(res);
+ if (count > 0)
+ printTableAddFooter(cont, footermsg);
+
+ /*--------------------------------------------------------------
+ * Description columns for:
+ *
+ * PUB TBL
+ * [0] - : schema name (nspname)
+ * [col] - : table name (relname)
+ * - [col] : publication name (pubname)
+ * [col+1] [col+1]: row filter expression (prqual), may be NULL
+ * [col+2] [col+2]: column list (comma-separated), may be NULL
+ * [col+3] [col+3]: except flag ("t" if EXCEPT, else "f")
+ *--------------------------------------------------------------
+ */
+ for (int i = 0; i < count; i++)
+ {
+ printfPQExpBuffer(buf, " "); /* indent */
+
+ /*
+ * Footer entries for a publication description or a table
+ * description
+ */
+ if (is_pub_desc)
+ {
+ if (pub_schemas)
+ {
+ /* Schemas of the publication... */
+ appendPQExpBuffer(buf, "\"%s\"", PQgetvalue(res, i, 0));
+ }
+ else
+ {
+ /* Tables of the publication... */
+ appendPQExpBuffer(buf, "\"%s.%s\"", PQgetvalue(res, i, 0),
+ PQgetvalue(res, i, col));
+ }
+ }
+ else
+ {
+ /* Publications of the table... */
+ appendPQExpBuffer(buf, "\"%s\"", PQgetvalue(res, i, col));
+ }
+
+ /* Common footer output for column list and/or row filter */
+ if (!pub_schemas)
+ {
+ if (!PQgetisnull(res, i, col + 2))
+ {
+ if (strcmp(PQgetvalue(res, i, col + 3), "t") == 0)
+ appendPQExpBuffer(buf, " EXCEPT");
+ appendPQExpBuffer(buf, " (%s)", PQgetvalue(res, i, col + 2));
+ }
+
+ if (!PQgetisnull(res, i, col + 1))
+ appendPQExpBuffer(buf, " WHERE %s", PQgetvalue(res, i, col + 1));
+ }
+
+ printTableAddFooter(cont, buf->data);
+ }
+
+ PQclear(res);
+ return true;
+}
+
/*
* describeOneTableDetails (for \d)
*
@@ -3053,16 +3138,27 @@ describeOneTableDetails(const char *schemaname,
/* print any publications */
if (pset.sversion >= 100000)
{
- if (pset.sversion >= 150000)
+ if (pset.sversion >= 190000)
{
printfPQExpBuffer(&buf,
+
+ /*
+ * Get all publications for the schema that this relation is
+ * part of
+ */
"SELECT pubname\n"
" , NULL\n"
" , NULL\n"
+ " , NULL\n"
"FROM pg_catalog.pg_publication p\n"
" JOIN pg_catalog.pg_publication_namespace pn ON p.oid = pn.pnpubid\n"
" JOIN pg_catalog.pg_class pc ON pc.relnamespace = pn.pnnspid\n"
"WHERE pc.oid ='%s' and pg_catalog.pg_relation_is_publishable('%s')\n"
+
+ /*
+ * Get all publications for this relation created using FOR
+ * TABLE
+ */
"UNION\n"
"SELECT pubname\n"
" , pg_get_expr(pr.prqual, c.oid)\n"
@@ -3072,35 +3168,67 @@ describeOneTableDetails(const char *schemaname,
" pg_catalog.pg_attribute\n"
" WHERE attrelid = pr.prrelid AND attnum = prattrs[s])\n"
" ELSE NULL END) "
+ " , prexcept "
"FROM pg_catalog.pg_publication p\n"
" JOIN pg_catalog.pg_publication_rel pr ON p.oid = pr.prpubid\n"
" JOIN pg_catalog.pg_class c ON c.oid = pr.prrelid\n"
- "WHERE pr.prrelid = '%s'\n",
- oid, oid, oid);
-
- if (pset.sversion >= 190000)
- appendPQExpBufferStr(&buf, " AND NOT pr.prexcept\n");
+ "WHERE pr.prrelid = '%s' "
+ "AND p.puballtables = false\n"
+ "AND c.relnamespace NOT IN (\n "
+ " SELECT pnnspid FROM\n"
+ " pg_catalog.pg_publication_namespace)\n"
- appendPQExpBuffer(&buf,
+ /*
+ * Get all FOR ALL TABLES publications that include this
+ * relation
+ */
"UNION\n"
"SELECT pubname\n"
" , NULL\n"
" , NULL\n"
+ " , NULL\n"
"FROM pg_catalog.pg_publication p\n"
- "WHERE p.puballtables AND pg_catalog.pg_relation_is_publishable('%s')\n",
- oid);
-
- if (pset.sversion >= 190000)
- appendPQExpBuffer(&buf,
- " AND NOT EXISTS (\n"
- " SELECT 1\n"
- " FROM pg_catalog.pg_publication_rel pr\n"
- " JOIN pg_catalog.pg_class pc\n"
- " ON pr.prrelid = pc.oid\n"
- " WHERE pr.prrelid = '%s' AND pr.prpubid = p.oid)\n",
- oid);
-
- appendPQExpBufferStr(&buf, "ORDER BY 1;");
+ "WHERE p.puballtables AND pg_catalog.pg_relation_is_publishable('%s')\n"
+ " AND NOT EXISTS (\n"
+ " SELECT 1\n"
+ " FROM pg_catalog.pg_publication_rel pr\n"
+ " JOIN pg_catalog.pg_class pc\n"
+ " ON pr.prrelid = pc.oid\n"
+ " WHERE pr.prrelid = '%s' AND pr.prpubid = p.oid)\n"
+ "ORDER BY 1;",
+ oid, oid, oid, oid, oid);
+ }
+ else if (pset.sversion >= 150000)
+ {
+ printfPQExpBuffer(&buf,
+ "SELECT pubname\n"
+ " , NULL\n"
+ " , NULL\n"
+ "FROM pg_catalog.pg_publication p\n"
+ " JOIN pg_catalog.pg_publication_namespace pn ON p.oid = pn.pnpubid\n"
+ " JOIN pg_catalog.pg_class pc ON pc.relnamespace = pn.pnnspid\n"
+ "WHERE pc.oid ='%s' and pg_catalog.pg_relation_is_publishable('%s')\n"
+ "UNION\n"
+ "SELECT pubname\n"
+ " , pg_get_expr(pr.prqual, c.oid)\n"
+ " , (CASE WHEN pr.prattrs IS NOT NULL THEN\n"
+ " (SELECT string_agg(attname, ', ')\n"
+ " FROM pg_catalog.generate_series(0, pg_catalog.array_upper(pr.prattrs::pg_catalog.int2[], 1)) s,\n"
+ " pg_catalog.pg_attribute\n"
+ " WHERE attrelid = pr.prrelid AND attnum = prattrs[s])\n"
+ " ELSE NULL END) "
+ "FROM pg_catalog.pg_publication p\n"
+ " JOIN pg_catalog.pg_publication_rel pr ON p.oid = pr.prpubid\n"
+ " JOIN pg_catalog.pg_class c ON c.oid = pr.prrelid\n"
+ "WHERE pr.prrelid = '%s'\n"
+ "UNION\n"
+ "SELECT pubname\n"
+ " , NULL\n"
+ " , NULL\n"
+ "FROM pg_catalog.pg_publication p\n"
+ "WHERE p.puballtables AND pg_catalog.pg_relation_is_publishable('%s')\n"
+ "ORDER BY 1;",
+ oid, oid, oid, oid);
}
else
{
@@ -3121,34 +3249,8 @@ describeOneTableDetails(const char *schemaname,
oid, oid);
}
- result = PSQLexec(buf.data);
- if (!result)
+ if (!addFooterToPublicationOrTableDesc(&buf, &cont, _("Publications:"), false, false))
goto error_return;
- else
- tuples = PQntuples(result);
-
- if (tuples > 0)
- printTableAddFooter(&cont, _("Publications:"));
-
- /* Might be an empty set - that's ok */
- for (i = 0; i < tuples; i++)
- {
- printfPQExpBuffer(&buf, " \"%s\"",
- PQgetvalue(result, i, 0));
-
- /* column list (if any) */
- if (!PQgetisnull(result, i, 2))
- appendPQExpBuffer(&buf, " (%s)",
- PQgetvalue(result, i, 2));
-
- /* row filter (if any) */
- if (!PQgetisnull(result, i, 1))
- appendPQExpBuffer(&buf, " WHERE %s",
- PQgetvalue(result, i, 1));
-
- printTableAddFooter(&cont, buf.data);
- }
- PQclear(result);
}
/*
@@ -6532,49 +6634,6 @@ listPublications(const char *pattern)
return true;
}
-/*
- * Add footer to publication description.
- */
-static bool
-addFooterToPublicationDesc(PQExpBuffer buf, const char *footermsg,
- bool as_schema, printTableContent *const cont)
-{
- PGresult *res;
- int count = 0;
- int i = 0;
-
- res = PSQLexec(buf->data);
- if (!res)
- return false;
- else
- count = PQntuples(res);
-
- if (count > 0)
- printTableAddFooter(cont, footermsg);
-
- for (i = 0; i < count; i++)
- {
- if (as_schema)
- printfPQExpBuffer(buf, " \"%s\"", PQgetvalue(res, i, 0));
- else
- {
- printfPQExpBuffer(buf, " \"%s.%s\"", PQgetvalue(res, i, 0),
- PQgetvalue(res, i, 1));
-
- if (!PQgetisnull(res, i, 3))
- appendPQExpBuffer(buf, " (%s)", PQgetvalue(res, i, 3));
-
- if (!PQgetisnull(res, i, 2))
- appendPQExpBuffer(buf, " WHERE %s", PQgetvalue(res, i, 2));
- }
-
- printTableAddFooter(cont, buf->data);
- }
-
- PQclear(res);
- return true;
-}
-
/*
* \dRp+
* Describes publications including the contents.
@@ -6764,6 +6823,12 @@ describePublications(const char *pattern)
else
appendPQExpBufferStr(&buf,
", NULL, NULL");
+
+ if (pset.sversion >= 190000)
+ appendPQExpBufferStr(&buf, ", prexcept");
+ else
+ appendPQExpBufferStr(&buf, ", NULL");
+
appendPQExpBuffer(&buf,
"\nFROM pg_catalog.pg_class c,\n"
" pg_catalog.pg_namespace n,\n"
@@ -6772,11 +6837,8 @@ describePublications(const char *pattern)
" AND c.oid = pr.prrelid\n"
" AND pr.prpubid = '%s'\n", pubid);
- if (pset.sversion >= 190000)
- appendPQExpBuffer(&buf, " AND NOT pr.prexcept\n");
-
appendPQExpBuffer(&buf, "ORDER BY 1,2");
- if (!addFooterToPublicationDesc(&buf, _("Tables:"), false, &cont))
+ if (!addFooterToPublicationOrTableDesc(&buf, &cont, _("Tables:"), true, false))
goto error_return;
if (pset.sversion >= 150000)
@@ -6788,8 +6850,8 @@ describePublications(const char *pattern)
" JOIN pg_catalog.pg_publication_namespace pn ON n.oid = pn.pnnspid\n"
"WHERE pn.pnpubid = '%s'\n"
"ORDER BY 1", pubid);
- if (!addFooterToPublicationDesc(&buf, _("Tables from schemas:"),
- true, &cont))
+ if (!addFooterToPublicationOrTableDesc(&buf, &cont,
+ _("Tables from schemas:"), true, true))
goto error_return;
}
}
@@ -6799,14 +6861,14 @@ describePublications(const char *pattern)
{
/* Get the excluded tables for the specified publication */
printfPQExpBuffer(&buf,
- "SELECT concat(c.relnamespace::regnamespace, '.', c.relname)\n"
+ "SELECT c.relnamespace::regnamespace, c.relname, NULL, NULL\n"
"FROM pg_catalog.pg_class c\n"
" JOIN pg_catalog.pg_publication_rel pr ON c.oid = pr.prrelid\n"
"WHERE pr.prpubid = '%s'\n"
" AND pr.prexcept\n"
"ORDER BY 1", pubid);
- if (!addFooterToPublicationDesc(&buf, _("Except tables:"),
- true, &cont))
+ if (!addFooterToPublicationOrTableDesc(&buf, &cont,
+ _("Except tables:"), true, false))
goto error_return;
}
}
diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index c5c8e6e8534..f72be93d942 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -2294,6 +2294,10 @@ match_previous_words(int pattern_id,
COMPLETE_WITH("ALL TABLES", "TABLES IN SCHEMA", "TABLE");
else if (Matches("ALTER", "PUBLICATION", MatchAny, "ADD", "ALL", "TABLES"))
COMPLETE_WITH("EXCEPT TABLE");
+ else if (Matches("ALTER", "PUBLICATION", MatchAny, "ADD|SET", "TABLE", MatchAny) && !ends_with(prev_wd, ','))
+ COMPLETE_WITH("EXCEPT (");
+ else if (Matches("ALTER", "PUBLICATION", MatchAny, MatchAnyN, "EXCEPT"))
+ COMPLETE_WITH("(");
else if (Matches("ALTER", "PUBLICATION", MatchAny, "ADD|SET", "TABLE"))
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables);
else if (Matches("ALTER", "PUBLICATION", MatchAny, "ADD", "ALL", "TABLES", "EXCEPT", "TABLE"))
@@ -2314,10 +2318,13 @@ match_previous_words(int pattern_id,
COMPLETE_WITH("(");
else if (Matches("ALTER", "PUBLICATION", MatchAny, MatchAnyN, "WHERE", "("))
COMPLETE_WITH_ATTR(prev3_wd);
+ else if (Matches("ALTER", "PUBLICATION", MatchAny, MatchAnyN, "EXCEPT", "("))
+ COMPLETE_WITH_ATTR(prev3_wd);
else if (HeadMatches("ALTER", "PUBLICATION", MatchAny, "ADD|SET", "TABLE") &&
- !TailMatches("WHERE", "(*)"))
+ !TailMatches("WHERE", "(*)") && !TailMatches("EXCEPT", "("))
COMPLETE_WITH(",", "WHERE (");
- else if (HeadMatches("ALTER", "PUBLICATION", MatchAny, "ADD|SET", "TABLE"))
+ else if (HeadMatches("ALTER", "PUBLICATION", MatchAny, "ADD|SET", "TABLE") &&
+ !ends_with(prev_wd, '('))
COMPLETE_WITH(",");
/* ALTER PUBLICATION <name> DROP */
else if (Matches("ALTER", "PUBLICATION", MatchAny, "DROP"))
@@ -3635,7 +3642,7 @@ match_previous_words(int pattern_id,
else if (Matches("CREATE", "PUBLICATION", MatchAny, "FOR", "TABLES"))
COMPLETE_WITH("IN SCHEMA");
else if (Matches("CREATE", "PUBLICATION", MatchAny, "FOR", "TABLE", MatchAny) && !ends_with(prev_wd, ','))
- COMPLETE_WITH("WHERE (", "WITH (");
+ COMPLETE_WITH("EXCEPT (", "WHERE (", "WITH (");
/* Complete "CREATE PUBLICATION <name> FOR TABLE" with "<table>, ..." */
else if (Matches("CREATE", "PUBLICATION", MatchAny, "FOR", "TABLE"))
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables);
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 34b7fddb0e7..6068abf3afa 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -12298,9 +12298,9 @@
proname => 'pg_get_publication_tables', prorows => '1000',
provariadic => 'text', proretset => 't', provolatile => 's',
prorettype => 'record', proargtypes => '_text',
- proallargtypes => '{_text,oid,oid,int2vector,pg_node_tree}',
- proargmodes => '{v,o,o,o,o}',
- proargnames => '{pubname,pubid,relid,attrs,qual}',
+ proallargtypes => '{_text,oid,oid,int2vector,pg_node_tree,bool}',
+ proargmodes => '{v,o,o,o,o,o}',
+ proargnames => '{pubname,pubid,relid,attrs,qual,exceptcol}',
prosrc => 'pg_get_publication_tables' },
{ oid => '8052', descr => 'get OIDs of sequences in a publication',
proname => 'pg_get_publication_sequences', prorows => '1000', proretset => 't',
diff --git a/src/include/catalog/pg_publication.h b/src/include/catalog/pg_publication.h
index 9a07215ae30..0ab8a008dc4 100644
--- a/src/include/catalog/pg_publication.h
+++ b/src/include/catalog/pg_publication.h
@@ -188,7 +188,8 @@ extern Oid GetTopMostAncestorInPublication(Oid puboid, List *ancestors,
extern bool is_publishable_relation(Relation rel);
extern bool is_schema_publication(Oid pubid);
extern bool check_and_fetch_column_list(Publication *pub, Oid relid,
- MemoryContext mcxt, Bitmapset **cols);
+ MemoryContext mcxt, Bitmapset **cols,
+ bool *except_columns);
extern ObjectAddress publication_add_relation(Oid pubid, PublicationRelInfo *pri,
bool if_not_exists);
extern Bitmapset *pub_collist_validate(Relation targetrel, List *columns);
@@ -198,6 +199,7 @@ extern ObjectAddress publication_add_schema(Oid pubid, Oid schemaid,
extern Bitmapset *pub_collist_to_bitmapset(Bitmapset *columns, Datum pubcols,
MemoryContext mcxt);
extern Bitmapset *pub_form_cols_map(Relation relation,
- PublishGencolsType include_gencols_type);
+ PublishGencolsType include_gencols_type,
+ Bitmapset *except_cols);
#endif /* PG_PUBLICATION_H */
diff --git a/src/include/catalog/pg_publication_rel.h b/src/include/catalog/pg_publication_rel.h
index e7d7f3ba85c..6a2168fc32c 100644
--- a/src/include/catalog/pg_publication_rel.h
+++ b/src/include/catalog/pg_publication_rel.h
@@ -31,11 +31,12 @@ CATALOG(pg_publication_rel,6106,PublicationRelRelationId)
Oid oid; /* oid */
Oid prpubid BKI_LOOKUP(pg_publication); /* Oid of the publication */
Oid prrelid BKI_LOOKUP(pg_class); /* Oid of the relation */
- bool prexcept BKI_DEFAULT(f); /* exclude the relation */
+ bool prexcept BKI_DEFAULT(f); /* exclude the relation or columns */
#ifdef CATALOG_VARLEN /* variable-length fields start here */
pg_node_tree prqual; /* qualifications */
- int2vector prattrs; /* columns to replicate */
+ int2vector prattrs; /* columns to replicate or exclude to
+ * replicate */
#endif
} FormData_pg_publication_rel;
diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out
index 062469d7220..1747c22b2bc 100644
--- a/src/test/regress/expected/publication.out
+++ b/src/test/regress/expected/publication.out
@@ -1904,52 +1904,52 @@ CREATE TABLE sch2.tbl1_part1 PARTITION OF sch1.tbl1 FOR VALUES FROM (1) to (10);
-- Schema publication that does not include the schema that has the parent table
CREATE PUBLICATION pub FOR TABLES IN SCHEMA sch2 WITH (PUBLISH_VIA_PARTITION_ROOT=1);
SELECT * FROM pg_publication_tables;
- pubname | schemaname | tablename | attnames | rowfilter
----------+------------+------------+----------+-----------
- pub | sch2 | tbl1_part1 | {a} |
+ pubname | schemaname | tablename | attnames | rowfilter | exceptcol
+---------+------------+------------+----------+-----------+-----------
+ pub | sch2 | tbl1_part1 | {a} | | f
(1 row)
DROP PUBLICATION pub;
-- Table publication that does not include the parent table
CREATE PUBLICATION pub FOR TABLE sch2.tbl1_part1 WITH (PUBLISH_VIA_PARTITION_ROOT=1);
SELECT * FROM pg_publication_tables;
- pubname | schemaname | tablename | attnames | rowfilter
----------+------------+------------+----------+-----------
- pub | sch2 | tbl1_part1 | {a} |
+ pubname | schemaname | tablename | attnames | rowfilter | exceptcol
+---------+------------+------------+----------+-----------+-----------
+ pub | sch2 | tbl1_part1 | {a} | | f
(1 row)
-- Table publication that includes both the parent table and the child table
ALTER PUBLICATION pub ADD TABLE sch1.tbl1;
SELECT * FROM pg_publication_tables;
- pubname | schemaname | tablename | attnames | rowfilter
----------+------------+-----------+----------+-----------
- pub | sch1 | tbl1 | {a} |
+ pubname | schemaname | tablename | attnames | rowfilter | exceptcol
+---------+------------+-----------+----------+-----------+-----------
+ pub | sch1 | tbl1 | {a} | | f
(1 row)
DROP PUBLICATION pub;
-- Schema publication that does not include the schema that has the parent table
CREATE PUBLICATION pub FOR TABLES IN SCHEMA sch2 WITH (PUBLISH_VIA_PARTITION_ROOT=0);
SELECT * FROM pg_publication_tables;
- pubname | schemaname | tablename | attnames | rowfilter
----------+------------+------------+----------+-----------
- pub | sch2 | tbl1_part1 | {a} |
+ pubname | schemaname | tablename | attnames | rowfilter | exceptcol
+---------+------------+------------+----------+-----------+-----------
+ pub | sch2 | tbl1_part1 | {a} | | f
(1 row)
DROP PUBLICATION pub;
-- Table publication that does not include the parent table
CREATE PUBLICATION pub FOR TABLE sch2.tbl1_part1 WITH (PUBLISH_VIA_PARTITION_ROOT=0);
SELECT * FROM pg_publication_tables;
- pubname | schemaname | tablename | attnames | rowfilter
----------+------------+------------+----------+-----------
- pub | sch2 | tbl1_part1 | {a} |
+ pubname | schemaname | tablename | attnames | rowfilter | exceptcol
+---------+------------+------------+----------+-----------+-----------
+ pub | sch2 | tbl1_part1 | {a} | | f
(1 row)
-- Table publication that includes both the parent table and the child table
ALTER PUBLICATION pub ADD TABLE sch1.tbl1;
SELECT * FROM pg_publication_tables;
- pubname | schemaname | tablename | attnames | rowfilter
----------+------------+------------+----------+-----------
- pub | sch2 | tbl1_part1 | {a} |
+ pubname | schemaname | tablename | attnames | rowfilter | exceptcol
+---------+------------+------------+----------+-----------+-----------
+ pub | sch2 | tbl1_part1 | {a} | | f
(1 row)
DROP PUBLICATION pub;
@@ -1962,9 +1962,9 @@ CREATE TABLE sch1.tbl1_part3 (a int) PARTITION BY RANGE(a);
ALTER TABLE sch1.tbl1 ATTACH PARTITION sch1.tbl1_part3 FOR VALUES FROM (20) to (30);
CREATE PUBLICATION pub FOR TABLES IN SCHEMA sch1 WITH (PUBLISH_VIA_PARTITION_ROOT=1);
SELECT * FROM pg_publication_tables;
- pubname | schemaname | tablename | attnames | rowfilter
----------+------------+-----------+----------+-----------
- pub | sch1 | tbl1 | {a} |
+ pubname | schemaname | tablename | attnames | rowfilter | exceptcol
+---------+------------+-----------+----------+-----------+-----------
+ pub | sch1 | tbl1 | {a} | | f
(1 row)
RESET client_min_messages;
@@ -2211,6 +2211,94 @@ SET ROLE regress_publication_user;
DROP PUBLICATION testpub_reset;
DROP TABLE pub_sch1.tbl1;
DROP TABLE pub_sch1.tbl2;
+-- ======================================================
+-- Test EXCEPT columns for CREATE PUBLICATION
+SET client_min_messages = 'ERROR';
+CREATE TABLE pub_test_except1 (a int NOT NULL, b int, c int NOT NULL, d int);
+CREATE TABLE pub_sch1.pub_test_except2 (a int, b int, c int, d int);
+-- Verify that publication is created with EXCEPT
+CREATE PUBLICATION testpub_except FOR TABLE pub_test_except1, pub_sch1.pub_test_except2 EXCEPT (b, c);
+SELECT * FROM pg_publication_tables WHERE pubname = 'testpub_except';
+ pubname | schemaname | tablename | attnames | rowfilter | exceptcol
+----------------+------------+------------------+-----------+-----------+-----------
+ testpub_except | public | pub_test_except1 | {a,b,c,d} | | f
+ testpub_except | pub_sch1 | pub_test_except2 | {a,d} | | t
+(2 rows)
+
+-- Cannot use EXCEPT col-lists combined with TABLES IN SCHEMA
+CREATE PUBLICATION testpub_except2 FOR TABLES IN SCHEMA pub_sch1, TABLE pub_test_except1 EXCEPT (b, c);
+ERROR: cannot use column list for relation "public.pub_test_except1" in publication "testpub_except2"
+DETAIL: Column lists cannot be specified in publications containing FOR TABLES IN SCHEMA elements.
+-- Syntax error EXCEPT without a col-list
+CREATE PUBLICATION testpub_except2 FOR TABLE pub_test_except1 EXCEPT;
+ERROR: table without column list cannot use EXCEPT clause
+LINE 1: CREATE PUBLICATION testpub_except2 FOR TABLE pub_test_except...
+ ^
+-- Verify ok - ALTER PUBLICATION ... SET ... EXCEPT (col-list)
+ALTER PUBLICATION testpub_except SET TABLE pub_test_except1 EXCEPT (a, b), pub_sch1.pub_test_except2;
+\dRp+ testpub_except
+ Publication testpub_except
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | f | t | t | t | t | none | f
+Tables:
+ "pub_sch1.pub_test_except2"
+ "public.pub_test_except1" EXCEPT (a, b)
+
+-- Verify fails - ALTER PUBLICATION ... DROP ... EXCEPT (col-list)
+ALTER PUBLICATION testpub_except DROP TABLE pub_test_except1 EXCEPT (a, b);
+ERROR: column list must not be specified in ALTER PUBLICATION ... DROP
+-- Verify ok - ALTER PUBLICATION ... DROP
+ALTER PUBLICATION testpub_except DROP TABLE pub_test_except1;
+-- Verify ok - ALTER PUBLICATION ... ADD ... EXCEPT (col-list)
+ALTER PUBLICATION testpub_except ADD TABLE pub_test_except1 EXCEPT (c, d);
+\dRp+ testpub_except
+ Publication testpub_except
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | f | t | t | t | t | none | f
+Tables:
+ "pub_sch1.pub_test_except2"
+ "public.pub_test_except1" EXCEPT (c, d)
+
+-- Verify fails - EXCEPT col-list cannot contain RI cols (when using RI FULL)
+ALTER TABLE pub_test_except1 REPLICA IDENTITY FULL;
+UPDATE pub_test_except1 SET a = 3 WHERE a = 1;
+ERROR: cannot update table "pub_test_except1"
+DETAIL: Column list used by the publication does not cover the replica identity.
+-- Verify fails - EXCEPT col-list cannot contain RI cols (when using INDEX)
+CREATE UNIQUE INDEX pub_test_except1_ac_idx ON pub_test_except1 (a, c);
+ALTER TABLE pub_test_except1 REPLICA IDENTITY USING INDEX pub_test_except1_ac_idx;
+UPDATE pub_test_except1 SET a = 3 WHERE a = 1;
+ERROR: cannot update table "pub_test_except1"
+DETAIL: Column list used by the publication does not cover the replica identity.
+DROP INDEX pub_test_except1_ac_idx;
+-- Verify ok - no clash between RI cols and the EXCEPT col-list
+CREATE UNIQUE INDEX pub_test_except1_a_idx ON pub_test_except1 (a);
+ALTER TABLE pub_test_except1 REPLICA IDENTITY USING INDEX pub_test_except1_a_idx;
+UPDATE pub_test_except1 SET a = 3 WHERE a = 1;
+-- Verify description of a table with publication with EXCEPT col-list
+\d+ pub_test_except1
+ Table "public.pub_test_except1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ a | integer | | not null | | plain | |
+ b | integer | | | | plain | |
+ c | integer | | not null | | plain | |
+ d | integer | | | | plain | |
+Indexes:
+ "pub_test_except1_a_idx" UNIQUE, btree (a) REPLICA IDENTITY
+Publications:
+ "testpub_except" EXCEPT (c, d)
+Not-null constraints:
+ "pub_test_except1_a_not_null" NOT NULL "a"
+ "pub_test_except1_c_not_null" NOT NULL "c"
+
+-- cleanup
+DROP INDEX pub_test_except1_a_idx;
+DROP PUBLICATION testpub_except;
+DROP TABLE pub_test_except1;
+DROP TABLE pub_sch1.pub_test_except2;
DROP SCHEMA pub_sch1;
RESET client_min_messages;
-- Test that the INSERT ON CONFLICT command correctly checks REPLICA IDENTITY
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 2bf968ae3d3..7a030f30155 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1476,9 +1476,10 @@ pg_publication_tables| SELECT p.pubname,
( SELECT array_agg(a.attname ORDER BY a.attnum) AS array_agg
FROM pg_attribute a
WHERE ((a.attrelid = gpt.relid) AND (a.attnum = ANY ((gpt.attrs)::smallint[])))) AS attnames,
- pg_get_expr(gpt.qual, gpt.relid) AS rowfilter
+ pg_get_expr(gpt.qual, gpt.relid) AS rowfilter,
+ gpt.exceptcol
FROM pg_publication p,
- LATERAL pg_get_publication_tables(VARIADIC ARRAY[(p.pubname)::text]) gpt(pubid, relid, attrs, qual),
+ LATERAL pg_get_publication_tables(VARIADIC ARRAY[(p.pubname)::text]) gpt(pubid, relid, attrs, qual, exceptcol),
(pg_class c
JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
WHERE (c.oid = gpt.relid);
diff --git a/src/test/regress/sql/publication.sql b/src/test/regress/sql/publication.sql
index f5c62338e78..9266071d618 100644
--- a/src/test/regress/sql/publication.sql
+++ b/src/test/regress/sql/publication.sql
@@ -1364,6 +1364,61 @@ SET ROLE regress_publication_user;
DROP PUBLICATION testpub_reset;
DROP TABLE pub_sch1.tbl1;
DROP TABLE pub_sch1.tbl2;
+
+-- ======================================================
+-- Test EXCEPT columns for CREATE PUBLICATION
+
+SET client_min_messages = 'ERROR';
+CREATE TABLE pub_test_except1 (a int NOT NULL, b int, c int NOT NULL, d int);
+CREATE TABLE pub_sch1.pub_test_except2 (a int, b int, c int, d int);
+
+-- Verify that publication is created with EXCEPT
+CREATE PUBLICATION testpub_except FOR TABLE pub_test_except1, pub_sch1.pub_test_except2 EXCEPT (b, c);
+SELECT * FROM pg_publication_tables WHERE pubname = 'testpub_except';
+
+-- Cannot use EXCEPT col-lists combined with TABLES IN SCHEMA
+CREATE PUBLICATION testpub_except2 FOR TABLES IN SCHEMA pub_sch1, TABLE pub_test_except1 EXCEPT (b, c);
+
+-- Syntax error EXCEPT without a col-list
+CREATE PUBLICATION testpub_except2 FOR TABLE pub_test_except1 EXCEPT;
+
+-- Verify ok - ALTER PUBLICATION ... SET ... EXCEPT (col-list)
+ALTER PUBLICATION testpub_except SET TABLE pub_test_except1 EXCEPT (a, b), pub_sch1.pub_test_except2;
+\dRp+ testpub_except
+
+-- Verify fails - ALTER PUBLICATION ... DROP ... EXCEPT (col-list)
+ALTER PUBLICATION testpub_except DROP TABLE pub_test_except1 EXCEPT (a, b);
+
+-- Verify ok - ALTER PUBLICATION ... DROP
+ALTER PUBLICATION testpub_except DROP TABLE pub_test_except1;
+
+-- Verify ok - ALTER PUBLICATION ... ADD ... EXCEPT (col-list)
+ALTER PUBLICATION testpub_except ADD TABLE pub_test_except1 EXCEPT (c, d);
+\dRp+ testpub_except
+
+-- Verify fails - EXCEPT col-list cannot contain RI cols (when using RI FULL)
+ALTER TABLE pub_test_except1 REPLICA IDENTITY FULL;
+UPDATE pub_test_except1 SET a = 3 WHERE a = 1;
+
+-- Verify fails - EXCEPT col-list cannot contain RI cols (when using INDEX)
+CREATE UNIQUE INDEX pub_test_except1_ac_idx ON pub_test_except1 (a, c);
+ALTER TABLE pub_test_except1 REPLICA IDENTITY USING INDEX pub_test_except1_ac_idx;
+UPDATE pub_test_except1 SET a = 3 WHERE a = 1;
+DROP INDEX pub_test_except1_ac_idx;
+
+-- Verify ok - no clash between RI cols and the EXCEPT col-list
+CREATE UNIQUE INDEX pub_test_except1_a_idx ON pub_test_except1 (a);
+ALTER TABLE pub_test_except1 REPLICA IDENTITY USING INDEX pub_test_except1_a_idx;
+UPDATE pub_test_except1 SET a = 3 WHERE a = 1;
+
+-- Verify description of a table with publication with EXCEPT col-list
+\d+ pub_test_except1
+
+-- cleanup
+DROP INDEX pub_test_except1_a_idx;
+DROP PUBLICATION testpub_except;
+DROP TABLE pub_test_except1;
+DROP TABLE pub_sch1.pub_test_except2;
DROP SCHEMA pub_sch1;
RESET client_min_messages;
diff --git a/src/test/subscription/meson.build b/src/test/subscription/meson.build
index b8e5c54c314..e8e69f7443d 100644
--- a/src/test/subscription/meson.build
+++ b/src/test/subscription/meson.build
@@ -47,6 +47,7 @@ tests += {
't/035_conflicts.pl',
't/036_sequences.pl',
't/037_rep_changes_except_table.pl',
+ 't/038_rep_changes_except_collist.pl',
't/100_bugs.pl',
],
},
diff --git a/src/test/subscription/t/038_rep_changes_except_collist.pl b/src/test/subscription/t/038_rep_changes_except_collist.pl
new file mode 100644
index 00000000000..3dfd266bc3d
--- /dev/null
+++ b/src/test/subscription/t/038_rep_changes_except_collist.pl
@@ -0,0 +1,193 @@
+# Copyright (c) 2021-2025, PostgreSQL Global Development Group
+
+# Logical replication tests for EXCEPT (column-list) publications
+use strict;
+use warnings;
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+# Initialize publisher node
+my $node_publisher = PostgreSQL::Test::Cluster->new('publisher');
+$node_publisher->init(allows_streaming => 'logical');
+$node_publisher->start;
+
+# Create subscriber node
+my $node_subscriber = PostgreSQL::Test::Cluster->new('subscriber');
+$node_subscriber->init(allows_streaming => 'logical');
+$node_subscriber->start;
+
+# Initial setup
+$node_publisher->safe_psql(
+ 'postgres', qq (
+ CREATE SCHEMA sch1;
+ CREATE TABLE tab1 (a int, b int NOT NULL, c int);
+ CREATE TABLE sch1.tab1 (a int, b int, c int);
+ CREATE TABLE tab2 (a int, b int, c int);
+ CREATE TABLE tab3 (a int, bgen int GENERATED ALWAYS AS (a * 2) STORED, cgen int GENERATED ALWAYS AS (a * 3) STORED);
+ CREATE TABLE tab4 (a int, bgen int GENERATED ALWAYS AS (a * 2) STORED, cgen int GENERATED ALWAYS AS (a * 3) STORED);
+ CREATE TABLE tab5 (a int, b int, c int);
+ INSERT INTO tab1 VALUES (1, 2, 3);
+ INSERT INTO sch1.tab1 VALUES (1, 2, 3);
+ CREATE PUBLICATION tap_pub_col FOR TABLE tab1 EXCEPT (a), sch1.tab1 EXCEPT (b, c);
+));
+
+$node_subscriber->safe_psql(
+ 'postgres', qq (
+ CREATE SCHEMA sch1;
+ CREATE TABLE tab1 (a int, b int NOT NULL, c int);
+ CREATE TABLE sch1.tab1 (a int, b int, c int);
+ CREATE TABLE tab2 (a int, b int, c int);
+ CREATE TABLE tab3 (a int, bgen int, cgen int);
+ CREATE TABLE tab4 (a int, bgen int, cgen int);
+ CREATE TABLE tab5 (a int, b int, c int, d int);
+));
+
+# Setup logical replication
+my $publisher_connstr = $node_publisher->connstr . ' dbname=postgres';
+$node_subscriber->safe_psql('postgres',
+ "CREATE SUBSCRIPTION tap_sub_col CONNECTION '$publisher_connstr' PUBLICATION tap_pub_col"
+);
+$node_subscriber->wait_for_subscription_sync($node_publisher, 'tap_sub_col');
+
+# Test initial sync
+my $result = $node_subscriber->safe_psql('postgres', "SELECT * FROM tab1");
+is($result, qq(|2|3),
+ 'Verify initial sync of tab1 in a publication using EXCEPT (column-list)'
+);
+$result = $node_subscriber->safe_psql('postgres', "SELECT * FROM sch1.tab1");
+is($result, qq(1||),
+ 'Verify initial sync of sch1.tab1 in a publication using EXCEPT (column-list)'
+);
+
+# Test incremental changes
+$node_publisher->safe_psql(
+ 'postgres', qq (
+ INSERT INTO tab1 VALUES (4, 5, 6);
+ INSERT INTO sch1.tab1 VALUES (4, 5, 6);
+));
+$node_publisher->wait_for_catchup('tap_sub_col');
+$result =
+ $node_subscriber->safe_psql('postgres', "SELECT * FROM tab1 ORDER BY a");
+is( $result, qq(|2|3
+|5|6),
+ 'Verify incremental inserts on tab1 in a publication using EXCEPT (column-list)'
+);
+$result = $node_subscriber->safe_psql('postgres',
+ "SELECT * FROM sch1.tab1 ORDER BY a");
+is( $result, qq(1||
+4||),
+ 'Verify incremental inserts on sch1.tab1 in a publication using EXCEPT (column-list)'
+);
+
+# Test for update
+$node_subscriber->safe_psql(
+ 'postgres', qq(
+ CREATE UNIQUE INDEX b_idx ON tab1 (b);
+ ALTER TABLE tab1 REPLICA IDENTITY USING INDEX b_idx;
+));
+$node_publisher->safe_psql(
+ 'postgres', qq(
+ CREATE UNIQUE INDEX b_idx ON tab1 (b);
+ ALTER TABLE tab1 REPLICA IDENTITY USING INDEX b_idx;
+ UPDATE tab1 SET a = 991, b = 992, c = 993 WHERE a = 1;
+));
+$node_publisher->wait_for_catchup('tap_sub_col');
+$result =
+ $node_subscriber->safe_psql('postgres', "SELECT * FROM tab1 ORDER BY a");
+is( $result, qq(|5|6
+|992|993),
+ 'check update for EXCEPT (column-list) publication');
+
+# Test ALTER PUBLICATION for EXCEPT (column-list)
+$node_publisher->safe_psql('postgres',
+ "ALTER PUBLICATION tap_pub_col ADD TABLE tab2 EXCEPT(b)");
+$node_subscriber->safe_psql('postgres',
+ "ALTER SUBSCRIPTION tap_sub_col REFRESH PUBLICATION");
+$node_subscriber->wait_for_subscription_sync($node_publisher, 'tap_sub_col');
+
+$node_publisher->safe_psql('postgres', "INSERT INTO tab2 VALUES (1, 2, 3)");
+$node_publisher->wait_for_catchup('tap_sub_col');
+$result = $node_subscriber->safe_psql('postgres', "SELECT * FROM tab2");
+is($result, qq(1||3), 'check alter publication with EXCEPT (column-list)');
+
+# Test for publication created with 'publish_generated_columns' as 'stored' on
+# table 'tab3' and with column 'bgen' in column list with EXCEPT clause.
+$node_publisher->safe_psql(
+ 'postgres', qq(INSERT INTO tab3 VALUES (1);
+ ALTER PUBLICATION tap_pub_col SET (publish_generated_columns = stored);
+ ALTER PUBLICATION tap_pub_col SET TABLE tab3 EXCEPT(bgen);
+));
+$node_subscriber->safe_psql('postgres',
+ "ALTER SUBSCRIPTION tap_sub_col REFRESH PUBLICATION");
+$node_subscriber->wait_for_subscription_sync($node_publisher, 'tap_sub_col');
+
+$node_publisher->safe_psql('postgres', "INSERT INTO tab3 VALUES (2)");
+$node_publisher->wait_for_catchup('tap_sub_col');
+$result =
+ $node_subscriber->safe_psql('postgres', "SELECT * FROM tab3 ORDER BY a");
+
+# column 'bgen' is specified in EXCEPT (columm-list). So data corresponding to
+# 'bgen' is not replicated. Parameter 'publish_generated_columns' is set as
+# 'stored', so data corresponding to column 'cgen' is replicated.
+is( $result, qq(1||3
+2||6),
+ 'check publication(publish_generated_columns as stored) with generated columns and EXCEPT (column-list)'
+);
+
+# Test for publication created with 'publish_generated_columns' as 'none' on
+# table with generated columns and column list specified with EXCEPT clause.
+$node_publisher->safe_psql(
+ 'postgres', qq(
+ INSERT INTO tab4 VALUES (1);
+ ALTER PUBLICATION tap_pub_col SET (publish_generated_columns = none);
+ ALTER PUBLICATION tap_pub_col SET TABLE tab4 EXCEPT(bgen);
+));
+$node_subscriber->safe_psql('postgres',
+ "ALTER SUBSCRIPTION tap_sub_col REFRESH PUBLICATION");
+$node_subscriber->wait_for_subscription_sync($node_publisher, 'tap_sub_col');
+
+$node_publisher->safe_psql('postgres', "INSERT INTO tab4 VALUES (2)");
+$node_publisher->wait_for_catchup('tap_sub_col');
+$result =
+ $node_subscriber->safe_psql('postgres', "SELECT * FROM tab4 ORDER BY a");
+
+# column 'bgen' is specified in EXCEPT (columm-list). So data corresponding to
+# 'bgen' is not replicated. Parameter 'publish_generated_columns' is set as
+# 'none', so data corresponding to column 'cgen' is not replicated.
+is( $result, qq(1||
+2||),
+ 'check publication(publish_generated_columns as none) with generated columns and EXCEPT (column-list)'
+);
+
+# All columns are present in EXCEPT (column-list)
+$node_publisher->safe_psql(
+ 'postgres', qq(
+ ALTER PUBLICATION tap_pub_col SET TABLE tab5 EXCEPT(a, b, c);
+ INSERT INTO tab5 VALUES (1, 2, 3);
+));
+$node_subscriber->safe_psql('postgres',
+ "ALTER SUBSCRIPTION tap_sub_col REFRESH PUBLICATION");
+$node_subscriber->wait_for_subscription_sync($node_publisher, 'tap_sub_col');
+
+$node_publisher->safe_psql('postgres', "INSERT INTO tab5 VALUES (4, 5, 6)");
+$node_publisher->wait_for_catchup('tap_sub_col');
+$result = $node_subscriber->safe_psql('postgres', "SELECT * FROM tab5");
+is($result, qq(), 'all columns are specified in EXCEPT (column-list)');
+
+# Add a new column and check that it is replicated
+$node_publisher->safe_psql(
+ 'postgres', qq(
+ ALTER TABLE tab5 ADD COLUMN d int;
+));
+$node_publisher->safe_psql('postgres',
+ "INSERT INTO tab5 VALUES (7, 8, 9, 10)");
+$node_publisher->wait_for_catchup('tap_sub_col');
+
+$result = $node_subscriber->safe_psql('postgres', "SELECT * FROM tab5");
+is($result, qq(|||10), 'newly added column is replicated');
+
+$node_subscriber->stop('fast');
+$node_publisher->stop('fast');
+
+done_testing();
--
2.34.1
[application/octet-stream] v25-0002-Skip-publishing-the-tables-specified-in-EXCEPT-T.patch (91.0K, 4-v25-0002-Skip-publishing-the-tables-specified-in-EXCEPT-T.patch)
download | inline diff:
From f495af76ef8ea7b3fc4a779423b94da1090707cb Mon Sep 17 00:00:00 2001
From: Shlok Kyal <[email protected]>
Date: Thu, 6 Nov 2025 14:19:22 +0530
Subject: [PATCH v25 2/3] Skip publishing the tables specified in EXCEPT TABLE.
A new "EXCEPT TABLE" clause for CREATE/ALTER PUBLICATION allows one or
more tables to be excluded. The publisher will not send the data of
excluded tables to the subscriber.
The new syntax allows specifying excluded relations when creating or altering
a publication. For example:
CREATE PUBLICATION pub1 FOR ALL TABLES EXCEPT TABLE (t1,t2);
or
ALTER PUBLICATION pub1 ADD ALL TABLES EXCEPT TABLE (t1,t2);
A new column "prexcept" is added to table "pg_publication_rel", to maintain
the relations that the user wants to exclude from the publications.
pg_dump is updated to identify and dump the excluded tables of the publications.
The psql \d family of commands can now display excluded tables. e.g. psql
\dRp+ variant will now display associated "except tables" if any.
Bump catalog version.
---
doc/src/sgml/catalogs.sgml | 9 +
doc/src/sgml/logical-replication.sgml | 8 +-
doc/src/sgml/ref/alter_publication.sgml | 22 +-
doc/src/sgml/ref/create_publication.sgml | 49 +++-
doc/src/sgml/ref/psql-ref.sgml | 5 +-
src/backend/catalog/pg_publication.c | 99 ++++---
src/backend/commands/publicationcmds.c | 246 ++++++++++++++----
src/backend/commands/tablecmds.c | 4 +-
src/backend/parser/gram.y | 41 ++-
src/backend/replication/pgoutput/pgoutput.c | 25 +-
src/backend/utils/cache/relcache.c | 17 +-
src/bin/pg_dump/pg_dump.c | 56 +++-
src/bin/pg_dump/pg_dump.h | 1 +
src/bin/pg_dump/pg_dump_sort.c | 18 ++
src/bin/pg_dump/t/002_pg_dump.pl | 22 +-
src/bin/psql/describe.c | 58 ++++-
src/bin/psql/tab-complete.in.c | 15 +-
src/include/catalog/pg_publication.h | 10 +-
src/include/catalog/pg_publication_rel.h | 1 +
src/include/commands/publicationcmds.h | 5 +-
src/include/nodes/parsenodes.h | 4 +
src/test/regress/expected/publication.out | 185 +++++++++----
src/test/regress/sql/publication.sql | 45 +++-
src/test/subscription/meson.build | 1 +
.../t/037_rep_changes_except_table.pl | 186 +++++++++++++
25 files changed, 947 insertions(+), 185 deletions(-)
create mode 100644 src/test/subscription/t/037_rep_changes_except_table.pl
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 6c8a0f173c9..144f3fbdef2 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -6581,6 +6581,15 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l
if there is no publication qualifying condition.</para></entry>
</row>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>prexcept</structfield> <type>bool</type>
+ </para>
+ <para>
+ True if the relation must be excluded
+ </para></entry>
+ </row>
+
<row>
<entry role="catalog_table_entry"><para role="column_definition">
<structfield>prattrs</structfield> <type>int2vector</type>
diff --git a/doc/src/sgml/logical-replication.sgml b/doc/src/sgml/logical-replication.sgml
index daab2cae989..7359bd77569 100644
--- a/doc/src/sgml/logical-replication.sgml
+++ b/doc/src/sgml/logical-replication.sgml
@@ -2301,10 +2301,10 @@ CONTEXT: processing remote data for replication origin "pg_16395" during "INSER
</para>
<para>
- To add tables to a publication, the user must have ownership rights on the
- table. To add all tables in schema to a publication, the user must be a
- superuser. To create a publication that publishes all tables or all tables in
- schema automatically, the user must be a superuser.
+ To create a publication using FOR ALL TABLES or FOR ALL TABLES IN SCHEMA,
+ the user must be a superuser. To add ALL TABLES or ALL TABLES IN SCHEMA to a
+ publication, the user must be a superuser. To add tables to a publication,
+ the user must have ownership rights on the table.
</para>
<para>
diff --git a/doc/src/sgml/ref/alter_publication.sgml b/doc/src/sgml/ref/alter_publication.sgml
index 85c79c1a9cb..4afe62b7cfd 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> ADD ALL TABLES [ EXCEPT [ TABLE ] ( <replaceable class="parameter">exception_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_object</replaceable> [, ...]
ALTER PUBLICATION <replaceable class="parameter">name</replaceable> SET ( <replaceable class="parameter">publication_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] )
@@ -33,6 +34,11 @@ ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RESET
TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [ ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) ] [ WHERE ( <replaceable class="parameter">expression</replaceable> ) ] [, ... ]
TABLES IN SCHEMA { <replaceable class="parameter">schema_name</replaceable> | CURRENT_SCHEMA } [, ... ]
+
+<phrase>where <replaceable class="parameter">exception_object</replaceable> is:</phrase>
+
+ [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ]
+
</synopsis>
</refsynopsisdiv>
@@ -88,8 +94,9 @@ ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RESET
<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>,
+ Adding a table to or excluding a table from a publication additionally
+ requires owning that table. The <literal>ADD ALL TABLES</literal>,
+ <literal>ADD ALL TABLES IN SCHEMA</literal>,
<literal>SET TABLES IN SCHEMA</literal> to a publication and
<literal>RESET</literal> of publication requires the invoking user to be a
superuser. To alter the owner, you must be able to
@@ -130,7 +137,8 @@ ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RESET
table name, only that table is affected. If <literal>ONLY</literal> is not
specified, the table and all its descendant tables (if any) are
affected. Optionally, <literal>*</literal> can be specified after the table
- name to explicitly indicate that descendant tables are included.
+ name to explicitly indicate that descendant tables are affected. For
+ partitioned tables, <literal>ONLY</literal> donot have any effect.
</para>
<para>
@@ -239,6 +247,14 @@ ALTER PUBLICATION sales_publication ADD TABLES IN SCHEMA marketing, sales;
</programlisting>
</para>
+ <para>
+ Alter publication <structname>production_publication</structname> to publish
+ all tables except <structname>users</structname> and
+ <structname>departments</structname>:
+<programlisting>
+ALTER PUBLICATION production_publication ADD ALL TABLES EXCEPT (users, departments);
+</programlisting></para>
+
<para>
Add tables <structname>users</structname>,
<structname>departments</structname> and schema
diff --git a/doc/src/sgml/ref/create_publication.sgml b/doc/src/sgml/ref/create_publication.sgml
index 66a70e5c5b5..d28a9a10c86 100644
--- a/doc/src/sgml/ref/create_publication.sgml
+++ b/doc/src/sgml/ref/create_publication.sgml
@@ -32,8 +32,12 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
<phrase>where <replaceable class="parameter">all_publication_object</replaceable> is one of:</phrase>
- ALL TABLES
+ ALL TABLES [ EXCEPT [ TABLE ] ( <replaceable class="parameter">exception_object</replaceable> [, ... ] ) ]
ALL SEQUENCES
+
+<phrase>where <replaceable class="parameter">exception_object</replaceable> is:</phrase>
+
+ [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ]
</synopsis>
</refsynopsisdiv>
@@ -160,7 +164,9 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
<listitem>
<para>
Marks the publication as one that replicates changes for all tables in
- the database, including tables created in the future.
+ the database, including tables created in the future. If
+ <literal>EXCEPT TABLE</literal> is specified, then exclude replicating
+ the changes for the specified tables.
</para>
</listitem>
</varlistentry>
@@ -180,6 +186,36 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
</listitem>
</varlistentry>
+ <varlistentry id="sql-createpublication-params-for-except-table">
+ <term><literal>EXCEPT TABLE</literal></term>
+ <listitem>
+ <para>
+ This clause specifies a list of tables to be excluded from the
+ publication. It can only be used with <literal>FOR ALL TABLES</literal>.
+ If <literal>ONLY</literal> is specified before the table name, only
+ that table is excluded from the publication. If <literal>ONLY</literal> is
+ not specified, the table and all its descendant tables (if any) are
+ excluded. Optionally, <literal>*</literal> can be specified after the
+ table name to explicitly indicate that descendant tables are excluded.
+ This does not apply to a partitioned table, however. The partitioned
+ table or its partitions are excluded from the publication based on the
+ parameter <literal>publish_via_partition_root</literal>.
+ </para>
+ <para>
+ When <literal>publish_via_partition_root</literal> is set to
+ <literal>true</literal>, specifying a root partitioned table in
+ <literal>EXCEPT TABLE</literal> excludes it and all its partitions from
+ replication. Specifying a leaf partition has no effect, as its changes are
+ still replicated via the root partitioned table. When
+ <literal>publish_via_partition_root</literal> is set to
+ <literal>false</literal>, specifying a partitioned table or non-leaf
+ partition has no effect, as changes are replicated via the leaf
+ partitions. Specifying a leaf partition excludes only that partition from
+ replication.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="sql-createpublication-params-with">
<term><literal>WITH ( <replaceable class="parameter">publication_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] )</literal></term>
<listitem>
@@ -463,6 +499,15 @@ CREATE PUBLICATION production_publication FOR TABLE users, departments, TABLES I
CREATE PUBLICATION sales_publication FOR TABLES IN SCHEMA marketing, sales;
</programlisting></para>
+ <para>
+ Create a publication that publishes all changes in all the tables except for
+ the changes of <structname>users</structname> and
+ <structname>departments</structname>:
+<programlisting>
+CREATE PUBLICATION mypublication FOR ALL TABLES EXCEPT (users, departments);
+</programlisting>
+ </para>
+
<para>
Create a publication that publishes all changes for table <structname>users</structname>,
but replicates only columns <structname>user_id</structname> and
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 1ab427d18af..9998ab32565 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -2103,8 +2103,9 @@ SELECT $1 \parse stmt1
listed.
If <literal>x</literal> is appended to the command name, the results
are displayed in expanded mode.
- If <literal>+</literal> is appended to the command name, the tables and
- schemas associated with each publication are shown as well.
+ If <literal>+</literal> is appended to the command name, the tables,
+ excluded tables and schemas associated with each publication are shown as
+ well.
</para>
</listitem>
</varlistentry>
diff --git a/src/backend/catalog/pg_publication.c b/src/backend/catalog/pg_publication.c
index ac2f4ee3561..bec3a34e48f 100644
--- a/src/backend/catalog/pg_publication.c
+++ b/src/backend/catalog/pg_publication.c
@@ -354,7 +354,8 @@ GetPubPartitionOptionRelations(List *result, PublicationPartOpt pub_partopt,
* ancestor is at the end of the list.
*/
Oid
-GetTopMostAncestorInPublication(Oid puboid, List *ancestors, int *ancestor_level)
+GetTopMostAncestorInPublication(Oid puboid, List *ancestors,
+ int *ancestor_level, bool puballtables)
{
ListCell *lc;
Oid topmost_relid = InvalidOid;
@@ -366,32 +367,44 @@ GetTopMostAncestorInPublication(Oid puboid, List *ancestors, int *ancestor_level
foreach(lc, ancestors)
{
Oid ancestor = lfirst_oid(lc);
- List *apubids = GetRelationPublications(ancestor);
- List *aschemaPubids = NIL;
+ List *apubids = GetRelationPublications(ancestor, false);
+ List *aschemapubids = NIL;
+ List *aexceptpubids = NIL;
+ bool set_top = false;
level++;
- if (list_member_oid(apubids, puboid))
+ /* check if member of table publications */
+ set_top = list_member_oid(apubids, puboid);
+ if (!set_top)
{
- topmost_relid = ancestor;
+ aschemapubids = GetSchemaPublications(get_rel_namespace(ancestor));
- if (ancestor_level)
- *ancestor_level = level;
+ /* check if member of schema publications */
+ set_top = list_member_oid(aschemapubids, puboid);
+
+ /*
+ * If the publication is all tables publication and the table is
+ * not part of exception tables.
+ */
+ if (!set_top && puballtables)
+ {
+ aexceptpubids = GetRelationPublications(ancestor, true);
+ set_top = !list_member_oid(aexceptpubids, puboid);
+ }
}
- else
+
+ if (set_top)
{
- aschemaPubids = GetSchemaPublications(get_rel_namespace(ancestor));
- if (list_member_oid(aschemaPubids, puboid))
- {
- topmost_relid = ancestor;
+ topmost_relid = ancestor;
- if (ancestor_level)
- *ancestor_level = level;
- }
+ if (ancestor_level)
+ *ancestor_level = level;
}
list_free(apubids);
- list_free(aschemaPubids);
+ list_free(aschemapubids);
+ list_free(aexceptpubids);
}
return topmost_relid;
@@ -466,6 +479,17 @@ publication_add_relation(Oid pubid, PublicationRelInfo *pri,
RelationGetRelationName(targetrel), pub->name)));
}
+ /*
+ * Check for partitions of partitioned table which are specified with
+ * EXCEPT clause and partitioned table is published with
+ * publish_via_partition_root = true.
+ */
+ if (pub->alltables && pri->except && targetrel->rd_rel->relispartition &&
+ pub->pubviaroot)
+ ereport(WARNING,
+ (errmsg("partition \"%s\" will be replicated as publish_via_partition_root is \"%s\"",
+ RelationGetRelationName(targetrel), "true")));
+
check_publication_add_relation(targetrel);
/* Validate and translate column names into a Bitmapset of attnums. */
@@ -482,6 +506,8 @@ publication_add_relation(Oid pubid, PublicationRelInfo *pri,
ObjectIdGetDatum(pubid);
values[Anum_pg_publication_rel_prrelid - 1] =
ObjectIdGetDatum(relid);
+ values[Anum_pg_publication_rel_prexcept - 1] =
+ BoolGetDatum(pri->except);
/* Add qualifications, if available */
if (pri->whereClause != NULL)
@@ -749,9 +775,9 @@ publication_add_schema(Oid pubid, Oid schemaid, bool if_not_exists)
return myself;
}
-/* Gets list of publication oids for a relation */
+/* Gets list of publication oids for a relation that matches the except_flag */
List *
-GetRelationPublications(Oid relid)
+GetRelationPublications(Oid relid, bool except_flag)
{
List *result = NIL;
CatCList *pubrellist;
@@ -765,7 +791,8 @@ GetRelationPublications(Oid relid)
HeapTuple tup = &pubrellist->members[i]->tuple;
Oid pubid = ((Form_pg_publication_rel) GETSTRUCT(tup))->prpubid;
- result = lappend_oid(result, pubid);
+ if (except_flag == ((Form_pg_publication_rel) GETSTRUCT(tup))->prexcept)
+ result = lappend_oid(result, pubid);
}
ReleaseSysCacheList(pubrellist);
@@ -774,13 +801,14 @@ GetRelationPublications(Oid relid)
}
/*
- * Gets list of relation oids for a publication.
+ * Gets list of relation oids for a publication that matches the except_flag.
*
* This should only be used FOR TABLE publications, the FOR ALL TABLES/SEQUENCES
* should use GetAllPublicationRelations().
*/
List *
-GetPublicationRelations(Oid pubid, PublicationPartOpt pub_partopt)
+GetPublicationRelations(Oid pubid, PublicationPartOpt pub_partopt,
+ bool except_flag)
{
List *result;
Relation pubrelsrel;
@@ -805,8 +833,11 @@ GetPublicationRelations(Oid pubid, PublicationPartOpt pub_partopt)
Form_pg_publication_rel pubrel;
pubrel = (Form_pg_publication_rel) GETSTRUCT(tup);
- result = GetPubPartitionOptionRelations(result, pub_partopt,
- pubrel->prrelid);
+
+ if (except_flag == pubrel->prexcept)
+ result = GetPubPartitionOptionRelations(result, pub_partopt,
+ pubrel->prrelid);
+
}
systable_endscan(scan);
@@ -866,13 +897,19 @@ GetAllTablesPublications(void)
* publication.
*/
List *
-GetAllPublicationRelations(char relkind, bool pubviaroot)
+GetAllPublicationRelations(Oid pubid, char relkind, bool pubviaroot)
{
Relation classRel;
ScanKeyData key[1];
TableScanDesc scan;
HeapTuple tuple;
List *result = NIL;
+ List *exceptlist;
+
+ exceptlist = GetPublicationRelations(pubid, pubviaroot ?
+ PUBLICATION_PART_ALL :
+ PUBLICATION_PART_ROOT,
+ true);
Assert(!(relkind == RELKIND_SEQUENCE && pubviaroot));
@@ -891,7 +928,8 @@ GetAllPublicationRelations(char relkind, bool pubviaroot)
Oid relid = relForm->oid;
if (is_publishable_class(relid, relForm) &&
- !(relForm->relispartition && pubviaroot))
+ !(relForm->relispartition && pubviaroot) &&
+ !list_member_oid(exceptlist, relid))
result = lappend_oid(result, relid);
}
@@ -912,7 +950,8 @@ GetAllPublicationRelations(char relkind, bool pubviaroot)
Oid relid = relForm->oid;
if (is_publishable_class(relid, relForm) &&
- !relForm->relispartition)
+ !relForm->relispartition &&
+ !list_member_oid(exceptlist, relid))
result = lappend_oid(result, relid);
}
@@ -1168,7 +1207,8 @@ pg_get_publication_tables(PG_FUNCTION_ARGS)
* those. Otherwise, get the partitioned table itself.
*/
if (pub_elem->alltables)
- pub_elem_tables = GetAllPublicationRelations(RELKIND_RELATION,
+ pub_elem_tables = GetAllPublicationRelations(pub_elem->oid,
+ RELKIND_RELATION,
pub_elem->pubviaroot);
else
{
@@ -1178,7 +1218,8 @@ pg_get_publication_tables(PG_FUNCTION_ARGS)
relids = GetPublicationRelations(pub_elem->oid,
pub_elem->pubviaroot ?
PUBLICATION_PART_ROOT :
- PUBLICATION_PART_LEAF);
+ PUBLICATION_PART_LEAF,
+ false);
schemarelids = GetAllSchemaPublicationRelations(pub_elem->oid,
pub_elem->pubviaroot ?
PUBLICATION_PART_ROOT :
@@ -1367,7 +1408,7 @@ pg_get_publication_sequences(PG_FUNCTION_ARGS)
publication = GetPublicationByName(pubname, false);
if (publication->allsequences)
- sequences = GetAllPublicationRelations(RELKIND_SEQUENCE, false);
+ sequences = GetAllPublicationRelations(publication->oid, RELKIND_SEQUENCE, false);
funcctx->user_fctx = (void *) sequences;
diff --git a/src/backend/commands/publicationcmds.c b/src/backend/commands/publicationcmds.c
index ed88c306677..e70fdefbc90 100644
--- a/src/backend/commands/publicationcmds.c
+++ b/src/backend/commands/publicationcmds.c
@@ -180,6 +180,39 @@ parse_publication_options(ParseState *pstate,
}
}
+/*
+ * Convert the PublicationObjSpec list which is part of
+ * PublicationAllObjSpecType list into PublicationTable list.
+ */
+static void
+ObjectsInAllPublicationToOids(List *puballobjspec_list,
+ ParseState *pstate, List **rels)
+{
+ if (!puballobjspec_list)
+ return;
+
+ foreach_ptr(PublicationAllObjSpec, puballobj, puballobjspec_list)
+ {
+ switch (puballobj->pubobjtype)
+ {
+ case PUBLICATION_ALL_SEQUENCES:
+ break;
+ case PUBLICATION_ALL_TABLES:
+ foreach_ptr(PublicationObjSpec, pubobj, puballobj->except_objects)
+ {
+ pubobj->pubtable->except = true;
+ *rels = lappend(*rels, pubobj->pubtable);
+ }
+ break;
+ default:
+ /* shouldn't happen */
+ elog(ERROR, "invalid publication object type %d",
+ puballobj->pubobjtype);
+ break;
+ }
+ }
+}
+
/*
* Convert the PublicationObjSpecType list into schema oid list and
* PublicationTable list.
@@ -204,6 +237,11 @@ ObjectsInPublicationToOids(List *pubobjspec_list, ParseState *pstate,
switch (pubobj->pubobjtype)
{
case PUBLICATIONOBJ_TABLE:
+ pubobj->pubtable->except = false;
+ *rels = lappend(*rels, pubobj->pubtable);
+ break;
+ case PUBLICATIONOBJ_EXCEPT_TABLE:
+ pubobj->pubtable->except = true;
*rels = lappend(*rels, pubobj->pubtable);
break;
case PUBLICATIONOBJ_TABLES_IN_SCHEMA:
@@ -278,7 +316,7 @@ contain_invalid_rfcolumn_walker(Node *node, rf_context *context)
*/
bool
pub_rf_contains_invalid_column(Oid pubid, Relation relation, List *ancestors,
- bool pubviaroot)
+ bool pubviaroot, bool puballtables)
{
HeapTuple rftuple;
Oid relid = RelationGetRelid(relation);
@@ -305,7 +343,8 @@ pub_rf_contains_invalid_column(Oid pubid, Relation relation, List *ancestors,
if (pubviaroot && relation->rd_rel->relispartition)
{
publish_as_relid
- = GetTopMostAncestorInPublication(pubid, ancestors, NULL);
+ = GetTopMostAncestorInPublication(pubid, ancestors, NULL,
+ puballtables);
if (!OidIsValid(publish_as_relid))
publish_as_relid = relid;
@@ -365,7 +404,7 @@ pub_rf_contains_invalid_column(Oid pubid, Relation relation, List *ancestors,
bool
pub_contains_invalid_column(Oid pubid, Relation relation, List *ancestors,
bool pubviaroot, char pubgencols_type,
- bool *invalid_column_list,
+ bool puballtables, bool *invalid_column_list,
bool *invalid_gen_col)
{
Oid relid = RelationGetRelid(relation);
@@ -389,7 +428,8 @@ pub_contains_invalid_column(Oid pubid, Relation relation, List *ancestors,
*/
if (pubviaroot && relation->rd_rel->relispartition)
{
- publish_as_relid = GetTopMostAncestorInPublication(pubid, ancestors, NULL);
+ publish_as_relid = GetTopMostAncestorInPublication(pubid, ancestors,
+ NULL, puballtables);
if (!OidIsValid(publish_as_relid))
publish_as_relid = relid;
@@ -525,7 +565,7 @@ InvalidatePubRelSyncCache(Oid pubid, bool puballtables)
* its leaves.
*/
relids = GetPublicationRelations(pubid,
- PUBLICATION_PART_ALL);
+ PUBLICATION_PART_ALL, false);
schemarelids = GetAllSchemaPublicationRelations(pubid,
PUBLICATION_PART_ALL);
@@ -933,56 +973,54 @@ CreatePublication(ParseState *pstate, CreatePublicationStmt *stmt)
/* Make the changes visible. */
CommandCounterIncrement();
- /* Associate objects with the publication. */
if (stmt->for_all_tables)
- {
- /*
- * Invalidate relcache so that publication info is rebuilt. Sequences
- * publication doesn't require invalidation, as replica identity
- * checks don't apply to them.
- */
- CacheInvalidateRelcacheAll();
- }
+ ObjectsInAllPublicationToOids(stmt->pubobjects, pstate, &relations);
+
else if (!stmt->for_all_sequences)
- {
ObjectsInPublicationToOids(stmt->pubobjects, pstate, &relations,
&schemaidlist);
- /* FOR TABLES IN SCHEMA requires superuser */
- if (schemaidlist != NIL && !superuser())
- ereport(ERROR,
- errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
- errmsg("must be superuser to create FOR TABLES IN SCHEMA publication"));
+ /* FOR TABLES IN SCHEMA requires superuser */
+ if (schemaidlist != NIL && !superuser())
+ ereport(ERROR,
+ errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("must be superuser to create FOR TABLES IN SCHEMA publication"));
- if (relations != NIL)
- {
- List *rels;
+ if (relations != NIL)
+ {
+ List *rels;
- rels = OpenTableList(relations);
- TransformPubWhereClauses(rels, pstate->p_sourcetext,
- publish_via_partition_root);
+ rels = OpenTableList(relations);
+ TransformPubWhereClauses(rels, pstate->p_sourcetext,
+ publish_via_partition_root);
- CheckPubRelationColumnList(stmt->pubname, rels,
- schemaidlist != NIL,
- publish_via_partition_root);
+ CheckPubRelationColumnList(stmt->pubname, rels,
+ schemaidlist != NIL,
+ publish_via_partition_root);
- PublicationAddTables(puboid, rels, true, NULL);
- CloseTableList(rels);
- }
+ PublicationAddTables(puboid, rels, true, NULL);
+ CloseTableList(rels);
+ }
- if (schemaidlist != NIL)
- {
- /*
- * Schema lock is held until the publication is created to prevent
- * concurrent schema deletion.
- */
- LockSchemaList(schemaidlist);
- PublicationAddSchemas(puboid, schemaidlist, true, NULL);
- }
+ if (schemaidlist != NIL)
+ {
+ /*
+ * Schema lock is held until the publication is created to prevent
+ * concurrent schema deletion.
+ */
+ LockSchemaList(schemaidlist);
+ PublicationAddSchemas(puboid, schemaidlist, true, NULL);
}
table_close(rel, RowExclusiveLock);
+ /* Associate objects with the publication. */
+ if (stmt->for_all_tables)
+ {
+ /* Invalidate relcache so that publication info is rebuilt. */
+ CacheInvalidateRelcacheAll();
+ }
+
InvokeObjectPostCreateHook(PublicationRelationId, puboid, 0);
if (wal_level != WAL_LEVEL_LOGICAL)
@@ -1051,7 +1089,7 @@ AlterPublicationOptions(ParseState *pstate, AlterPublicationStmt *stmt,
AccessShareLock);
root_relids = GetPublicationRelations(pubform->oid,
- PUBLICATION_PART_ROOT);
+ PUBLICATION_PART_ROOT, false);
foreach(lc, root_relids)
{
@@ -1171,7 +1209,7 @@ AlterPublicationOptions(ParseState *pstate, AlterPublicationStmt *stmt,
*/
if (root_relids == NIL)
relids = GetPublicationRelations(pubform->oid,
- PUBLICATION_PART_ALL);
+ PUBLICATION_PART_ALL, false);
else
{
/*
@@ -1267,6 +1305,27 @@ AlterPublicationReset(ParseState *pstate, AlterPublicationStmt *stmt,
values[Anum_pg_publication_pubgencols - 1] = CharGetDatum(PUB_DEFAULT_GENCOLS);
replaces[Anum_pg_publication_pubgencols - 1] = true;
+ /*
+ * Lock the publication so nobody else can do anything with it. This
+ * prevents concurrent publication parameter changes, add/drop tables(s)
+ * to the publication and add/drop schema(s) to the publication.
+ */
+ LockDatabaseObject(PublicationRelationId, pubid, 0,
+ AccessExclusiveLock);
+
+ /*
+ * It is possible that by the time we acquire the lock on publication,
+ * concurrent DDL has removed it. We can test this by checking the
+ * existence of publication. We get the tuple again to avoid the risk of
+ * any publication option getting changed.
+ */
+ tup = SearchSysCacheCopy1(PUBLICATIONOID, ObjectIdGetDatum(pubid));
+ if (!HeapTupleIsValid(tup))
+ ereport(ERROR,
+ errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("publication \"%s\" does not exist",
+ stmt->pubname));
+
/* Set ALL TABLES flag to false */
if (pubform->puballtables)
{
@@ -1301,7 +1360,10 @@ AlterPublicationReset(ParseState *pstate, AlterPublicationStmt *stmt,
PublicationDropSchemas(pubid, schemaids, true);
/* Get all relations associated with the publication */
- relids = GetPublicationRelations(pubid, PUBLICATION_PART_ROOT);
+ if (pubform->puballtables)
+ relids = GetPublicationRelations(pubid, PUBLICATION_PART_ROOT, true);
+ else
+ relids = GetPublicationRelations(pubid, PUBLICATION_PART_ROOT, false);
foreach_oid(relid, relids)
{
@@ -1319,6 +1381,79 @@ AlterPublicationReset(ParseState *pstate, AlterPublicationStmt *stmt,
CloseTableList(rels);
}
+/*
+ * Check if the publication has default values.
+ *
+ * Returns true if the publication satisfies all the following conditions:
+ * a) Publication is not set with "FOR ALL TABLES"
+ * b) Publication is having default publication parameter values
+ * c) Publication is not associated with schemas
+ * d) Publication is not associated with relations
+ */
+static bool
+CheckPublicationDefValues(HeapTuple tup)
+{
+ Form_pg_publication pubform = (Form_pg_publication) GETSTRUCT(tup);
+ Oid pubid = pubform->oid;
+ List *pubobjs = NIL;
+
+ if (pubform->puballtables != PUB_DEFAULT_ALL_TABLES)
+ return false;
+
+ if (pubform->pubinsert != PUB_DEFAULT_ACTION_INSERT ||
+ pubform->pubupdate != PUB_DEFAULT_ACTION_UPDATE ||
+ pubform->pubdelete != PUB_DEFAULT_ACTION_DELETE ||
+ pubform->pubtruncate != PUB_DEFAULT_ACTION_TRUNCATE ||
+ pubform->pubviaroot != PUB_DEFAULT_VIA_ROOT)
+ return false;
+
+ pubobjs = GetPublicationSchemas(pubid);
+ if (list_length(pubobjs))
+ return false;
+
+ pubobjs = GetPublicationRelations(pubid, PUBLICATION_PART_ROOT, false);
+ if (list_length(pubobjs))
+ return false;
+
+ return true;
+}
+
+/*
+ * Set publication to publish all tables.
+ */
+static void
+AlterPublicationSetAllTables(Relation rel, HeapTuple tup)
+{
+ Form_pg_publication pubform PG_USED_FOR_ASSERTS_ONLY = (Form_pg_publication) GETSTRUCT(tup);
+ bool nulls[Natts_pg_publication];
+ bool replaces[Natts_pg_publication];
+ Datum values[Natts_pg_publication];
+
+#ifdef USE_ASSERT_CHECKING
+ Assert(!pubform->puballtables);
+#endif
+
+ /* Add ALL TABLES to the publication requires superuser */
+ if (!superuser())
+ ereport(ERROR,
+ errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("must be superuser to ADD ALL TABLES to the publication"));
+
+ memset(values, 0, sizeof(values));
+ memset(nulls, false, sizeof(nulls));
+ memset(replaces, false, sizeof(replaces));
+
+ /* Set ALL TABLES flag */
+ values[Anum_pg_publication_puballtables - 1] = BoolGetDatum(true);
+ replaces[Anum_pg_publication_puballtables - 1] = true;
+
+ tup = heap_modify_tuple(tup, RelationGetDescr(rel), values, nulls,
+ replaces);
+
+ /* Update the catalog. */
+ CatalogTupleUpdate(rel, &tup->t_self, tup);
+}
+
/*
* Add or remove table to/from publication.
*/
@@ -1357,7 +1492,8 @@ AlterPublicationTables(AlterPublicationStmt *stmt, HeapTuple tup,
else /* AP_SetObjects */
{
List *oldrelids = GetPublicationRelations(pubid,
- PUBLICATION_PART_ROOT);
+ PUBLICATION_PART_ROOT,
+ false);
List *delrels = NIL;
ListCell *oldlc;
@@ -1458,6 +1594,7 @@ AlterPublicationTables(AlterPublicationStmt *stmt, HeapTuple tup,
oldrel = palloc(sizeof(PublicationRelInfo));
oldrel->whereClause = NULL;
oldrel->columns = NIL;
+ oldrel->except = false;
oldrel->relation = table_open(oldrelid,
ShareUpdateExclusiveLock);
delrels = lappend(delrels, oldrel);
@@ -1508,7 +1645,8 @@ AlterPublicationSchemas(AlterPublicationStmt *stmt,
ListCell *lc;
List *reloids;
- reloids = GetPublicationRelations(pubform->oid, PUBLICATION_PART_ROOT);
+ reloids = GetPublicationRelations(pubform->oid, PUBLICATION_PART_ROOT,
+ false);
foreach(lc, reloids)
{
@@ -1661,6 +1799,20 @@ AlterPublication(ParseState *pstate, AlterPublicationStmt *stmt)
aclcheck_error(ACLCHECK_NOT_OWNER, OBJECT_PUBLICATION,
stmt->pubname);
+ if (stmt->for_all_tables)
+ {
+ bool isdefault = CheckPublicationDefValues(tup);
+
+ if (!isdefault)
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("adding ALL TABLES requires the publication to have default publication parameter values"),
+ errdetail("ALL TABLES flag should not be set and no tables/schemas should be associated."),
+ errhint("Use ALTER PUBLICATION ... RESET to reset the publication"));
+
+ AlterPublicationSetAllTables(rel, tup);
+ }
+
if (stmt->options)
AlterPublicationOptions(pstate, stmt, rel, tup);
else if (stmt->action == AP_ResetPublication)
@@ -1873,6 +2025,7 @@ OpenTableList(List *tables)
pub_rel->relation = rel;
pub_rel->whereClause = t->whereClause;
pub_rel->columns = t->columns;
+ pub_rel->except = t->except;
rels = lappend(rels, pub_rel);
relids = lappend_oid(relids, myrelid);
@@ -1945,6 +2098,7 @@ OpenTableList(List *tables)
/* child inherits column list from parent */
pub_rel->columns = t->columns;
+ pub_rel->except = t->except;
rels = lappend(rels, pub_rel);
relids = lappend_oid(relids, childrelid);
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 3aac459e483..7fc8f08b0eb 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -8651,7 +8651,7 @@ ATExecSetExpression(AlteredTableInfo *tab, Relation rel, const char *colName,
* expressions.
*/
if (attgenerated == ATTRIBUTE_GENERATED_VIRTUAL &&
- GetRelationPublications(RelationGetRelid(rel)) != NIL)
+ GetRelationPublications(RelationGetRelid(rel), false) != NIL)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("ALTER TABLE / SET EXPRESSION is not supported for virtual generated columns in tables that are part of a publication"),
@@ -18846,7 +18846,7 @@ ATPrepChangePersistence(AlteredTableInfo *tab, Relation rel, bool toLogged)
* UNLOGGED, as UNLOGGED tables can't be published.
*/
if (!toLogged &&
- GetRelationPublications(RelationGetRelid(rel)) != NIL)
+ list_length(GetRelationPublications(RelationGetRelid(rel), false)) > 0)
ereport(ERROR,
(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
errmsg("cannot change table \"%s\" to unlogged because it is part of a publication",
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 2b88c74a319..87e78f13d94 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -454,6 +454,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
TriggerTransitions TriggerReferencing
vacuum_relation_list opt_vacuum_relation_list
drop_option_list pub_obj_list pub_obj_type_list
+ except_pub_obj_list except_clause
%type <retclause> returning_clause
%type <node> returning_option
@@ -591,6 +592,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type <node> var_value zone_value
%type <rolespec> auth_ident RoleSpec opt_granted_by
%type <publicationobjectspec> PublicationObjSpec
+%type <publicationobjectspec> ExceptPublicationObjSpec
%type <publicationallobjectspec> PublicationAllObjSpec
%type <keyword> unreserved_keyword type_func_name_keyword
@@ -10802,6 +10804,7 @@ PublicationObjSpec:
$$->pubtable->relation = $2;
$$->pubtable->columns = $3;
$$->pubtable->whereClause = $4;
+ $$->location = @1;
}
| TABLES IN_P SCHEMA ColId
{
@@ -10878,10 +10881,13 @@ pub_obj_list: PublicationObjSpec
;
PublicationAllObjSpec:
- ALL TABLES
+ ALL TABLES except_clause
{
$$ = makeNode(PublicationAllObjSpec);
$$->pubobjtype = PUBLICATION_ALL_TABLES;
+ $$->except_objects = (List *) $3;
+ if($$->except_objects != NULL)
+ preprocess_pubobj_list($$->except_objects, yyscanner);
$$->location = @1;
}
| ALL SEQUENCES
@@ -10898,6 +10904,28 @@ pub_obj_type_list: PublicationAllObjSpec
{ $$ = lappend($1, $3); }
;
+except_clause:
+ EXCEPT opt_table '(' except_pub_obj_list ')' { $$ = $4; }
+ | /*EMPTY*/ { $$ = NIL; }
+ ;
+
+ExceptPublicationObjSpec:
+ relation_expr
+ {
+ $$ = makeNode(PublicationObjSpec);
+ $$->pubobjtype = PUBLICATIONOBJ_EXCEPT_TABLE;
+ $$->pubtable = makeNode(PublicationTable);
+ $$->pubtable->except = true;
+ $$->pubtable->relation = $1;
+ $$->location = @1;
+ }
+ ;
+
+except_pub_obj_list: ExceptPublicationObjSpec
+ { $$ = list_make1($1); }
+ | except_pub_obj_list ',' ExceptPublicationObjSpec
+ { $$ = lappend($1, $3); }
+ ;
/*****************************************************************************
*
@@ -10911,6 +10939,8 @@ pub_obj_type_list: PublicationAllObjSpec
*
* ALTER PUBLICATION name RESET
*
+ * ALTER PUBLICATION name ADD ALL TABLES EXCEPT [TABLE] (table_name [, ...])
+ *
* pub_obj is one of:
*
* TABLE table_name [, ...]
@@ -10937,6 +10967,15 @@ AlterPublicationStmt:
n->action = AP_AddObjects;
$$ = (Node *) n;
}
+ | ALTER PUBLICATION name ADD_P ALL TABLES except_clause
+ {
+ AlterPublicationStmt *n = makeNode(AlterPublicationStmt);
+ n->pubname = $3;
+ n->pubobjects = (List *) $7;
+ n->for_all_tables = true;
+ n->action = AP_AddObjects;
+ $$ = (Node *)n;
+ }
| ALTER PUBLICATION name SET pub_obj_list
{
AlterPublicationStmt *n = makeNode(AlterPublicationStmt);
diff --git a/src/backend/replication/pgoutput/pgoutput.c b/src/backend/replication/pgoutput/pgoutput.c
index 942e1abdb58..a9593c5d9da 100644
--- a/src/backend/replication/pgoutput/pgoutput.c
+++ b/src/backend/replication/pgoutput/pgoutput.c
@@ -2084,7 +2084,8 @@ get_rel_sync_entry(PGOutputData *data, Relation relation)
if (!entry->replicate_valid)
{
Oid schemaId = get_rel_namespace(relid);
- List *pubids = GetRelationPublications(relid);
+ List *pubids = GetRelationPublications(relid, false);
+ List *exceptTablePubids = GetRelationPublications(relid, true);
/*
* We don't acquire a lock on the namespace system table as we build
@@ -2195,22 +2196,6 @@ get_rel_sync_entry(PGOutputData *data, Relation relation)
Oid pub_relid = relid;
int ancestor_level = 0;
- /*
- * If this is a FOR ALL TABLES publication, pick the partition
- * root and set the ancestor level accordingly.
- */
- if (pub->alltables)
- {
- publish = true;
- if (pub->pubviaroot && am_partition)
- {
- List *ancestors = get_partition_ancestors(relid);
-
- pub_relid = llast_oid(ancestors);
- ancestor_level = list_length(ancestors);
- }
- }
-
if (!publish)
{
bool ancestor_published = false;
@@ -2229,7 +2214,8 @@ get_rel_sync_entry(PGOutputData *data, Relation relation)
ancestor = GetTopMostAncestorInPublication(pub->oid,
ancestors,
- &level);
+ &level,
+ pub->alltables);
if (ancestor != InvalidOid)
{
@@ -2244,6 +2230,8 @@ get_rel_sync_entry(PGOutputData *data, Relation relation)
if (list_member_oid(pubids, pub->oid) ||
list_member_oid(schemaPubids, pub->oid) ||
+ (pub->alltables &&
+ !list_member_oid(exceptTablePubids, pub->oid)) ||
ancestor_published)
publish = true;
}
@@ -2322,6 +2310,7 @@ get_rel_sync_entry(PGOutputData *data, Relation relation)
list_free(pubids);
list_free(schemaPubids);
+ list_free(exceptTablePubids);
list_free(rel_publications);
entry->replicate_valid = true;
diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c
index 915d0bc9084..96dd0ccf41a 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -5794,6 +5794,8 @@ void
RelationBuildPublicationDesc(Relation relation, PublicationDesc *pubdesc)
{
List *puboids;
+ List *alltablespuboids;
+ List *exceptpuboids = NIL;
ListCell *lc;
MemoryContext oldcxt;
Oid schemaid;
@@ -5831,9 +5833,10 @@ RelationBuildPublicationDesc(Relation relation, PublicationDesc *pubdesc)
pubdesc->gencols_valid_for_delete = true;
/* Fetch the publication membership info. */
- puboids = GetRelationPublications(relid);
+ puboids = GetRelationPublications(relid, false);
schemaid = RelationGetNamespace(relation);
puboids = list_concat_unique_oid(puboids, GetSchemaPublications(schemaid));
+ exceptpuboids = GetRelationPublications(relid, true);
if (relation->rd_rel->relispartition)
{
@@ -5845,14 +5848,19 @@ RelationBuildPublicationDesc(Relation relation, PublicationDesc *pubdesc)
Oid ancestor = lfirst_oid(lc);
puboids = list_concat_unique_oid(puboids,
- GetRelationPublications(ancestor));
+ GetRelationPublications(ancestor, false));
schemaid = get_rel_namespace(ancestor);
puboids = list_concat_unique_oid(puboids,
GetSchemaPublications(schemaid));
+ exceptpuboids = list_concat_unique_oid(exceptpuboids,
+ GetRelationPublications(ancestor, true));
}
}
- puboids = list_concat_unique_oid(puboids, GetAllTablesPublications());
+ alltablespuboids = GetAllTablesPublications();
+ puboids = list_concat_unique_oid(puboids,
+ list_difference_oid(alltablespuboids,
+ exceptpuboids));
foreach(lc, puboids)
{
Oid pubid = lfirst_oid(lc);
@@ -5883,7 +5891,7 @@ RelationBuildPublicationDesc(Relation relation, PublicationDesc *pubdesc)
if (!pubform->puballtables &&
(pubform->pubupdate || pubform->pubdelete) &&
pub_rf_contains_invalid_column(pubid, relation, ancestors,
- pubform->pubviaroot))
+ pubform->pubviaroot, pubform->puballtables))
{
if (pubform->pubupdate)
pubdesc->rf_valid_for_update = false;
@@ -5901,6 +5909,7 @@ RelationBuildPublicationDesc(Relation relation, PublicationDesc *pubdesc)
pub_contains_invalid_column(pubid, relation, ancestors,
pubform->pubviaroot,
pubform->pubgencols,
+ pubform->puballtables,
&invalid_column_list,
&invalid_gen_col))
{
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index a00918bacb4..e34aaba7937 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -186,6 +186,8 @@ static SimpleOidList extension_include_oids = {NULL, NULL};
static SimpleStringList extension_exclude_patterns = {NULL, NULL};
static SimpleOidList extension_exclude_oids = {NULL, NULL};
+static SimplePtrList exceptinfo = {NULL, NULL};
+
static const CatalogId nilCatalogId = {0, 0};
/* override for standard extra_float_digits setting */
@@ -4662,7 +4664,34 @@ dumpPublication(Archive *fout, const PublicationInfo *pubinfo)
if (pubinfo->puballtables && pubinfo->puballsequences)
appendPQExpBufferStr(query, " FOR ALL TABLES, ALL SEQUENCES");
else if (pubinfo->puballtables)
+ {
+ SimplePtrListCell *cell;
+
appendPQExpBufferStr(query, " FOR ALL TABLES");
+
+ /* Include exception tables if the publication has except tables */
+ for (cell = exceptinfo.head; cell; cell = cell->next)
+ {
+ PublicationRelInfo *pubrinfo = (PublicationRelInfo *) cell->ptr;
+ TableInfo *tbinfo;
+
+ if (pubinfo == pubrinfo->publication)
+ {
+ tbinfo = pubrinfo->pubtable;
+
+ if (first)
+ {
+ appendPQExpBufferStr(query, " EXCEPT TABLE (");
+ first = false;
+ }
+ else
+ appendPQExpBufferStr(query, ", ");
+ appendPQExpBuffer(query, "ONLY %s", fmtQualifiedDumpable(tbinfo));
+ }
+ }
+ if (!first)
+ appendPQExpBufferStr(query, ")");
+ }
else if (pubinfo->puballsequences)
appendPQExpBufferStr(query, " FOR ALL SEQUENCES");
@@ -4831,6 +4860,7 @@ getPublicationTables(Archive *fout, TableInfo tblinfo[], int numTables)
int i_prrelid;
int i_prrelqual;
int i_prattrs;
+ int i_prexcept;
int i,
j,
ntups;
@@ -4842,8 +4872,16 @@ getPublicationTables(Archive *fout, TableInfo tblinfo[], int numTables)
/* Collect all publication membership info. */
if (fout->remoteVersion >= 150000)
+ {
+ appendPQExpBufferStr(query,
+ "SELECT tableoid, oid, prpubid, prrelid,\n");
+
+ if (fout->remoteVersion >= 190000)
+ appendPQExpBufferStr(query, " prexcept,\n");
+ else
+ appendPQExpBufferStr(query, " false AS prexcept,\n");
+
appendPQExpBufferStr(query,
- "SELECT tableoid, oid, prpubid, prrelid, "
"pg_catalog.pg_get_expr(prqual, prrelid) AS prrelqual, "
"(CASE\n"
" WHEN pr.prattrs IS NOT NULL THEN\n"
@@ -4854,6 +4892,7 @@ getPublicationTables(Archive *fout, TableInfo tblinfo[], int numTables)
" WHERE attrelid = pr.prrelid AND attnum = prattrs[s])\n"
" ELSE NULL END) prattrs "
"FROM pg_catalog.pg_publication_rel pr");
+ }
else
appendPQExpBufferStr(query,
"SELECT tableoid, oid, prpubid, prrelid, "
@@ -4869,6 +4908,7 @@ getPublicationTables(Archive *fout, TableInfo tblinfo[], int numTables)
i_prrelid = PQfnumber(res, "prrelid");
i_prrelqual = PQfnumber(res, "prrelqual");
i_prattrs = PQfnumber(res, "prattrs");
+ i_prexcept = PQfnumber(res, "prexcept");
/* this allocation may be more than we need */
pubrinfo = pg_malloc(ntups * sizeof(PublicationRelInfo));
@@ -4880,6 +4920,7 @@ getPublicationTables(Archive *fout, TableInfo tblinfo[], int numTables)
Oid prrelid = atooid(PQgetvalue(res, i, i_prrelid));
PublicationInfo *pubinfo;
TableInfo *tbinfo;
+ char *prexcept = pg_strdup(PQgetvalue(res, i, i_prexcept));
/*
* Ignore any entries for which we aren't interested in either the
@@ -4893,7 +4934,11 @@ getPublicationTables(Archive *fout, TableInfo tblinfo[], int numTables)
continue;
/* OK, make a DumpableObject for this relationship */
- pubrinfo[j].dobj.objType = DO_PUBLICATION_REL;
+ if (strcmp(prexcept, "f") == 0)
+ pubrinfo[j].dobj.objType = DO_PUBLICATION_REL;
+ else
+ pubrinfo[j].dobj.objType = DO_PUBLICATION_EXCEPT_REL;
+
pubrinfo[j].dobj.catId.tableoid =
atooid(PQgetvalue(res, i, i_tableoid));
pubrinfo[j].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
@@ -4934,6 +4979,9 @@ getPublicationTables(Archive *fout, TableInfo tblinfo[], int numTables)
/* Decide whether we want to dump it */
selectDumpablePublicationObject(&(pubrinfo[j].dobj), fout);
+ if (strcmp(prexcept, "t") == 0)
+ simple_ptr_list_append(&exceptinfo, &pubrinfo[j]);
+
j++;
}
@@ -11812,6 +11860,9 @@ dumpDumpableObject(Archive *fout, DumpableObject *dobj)
case DO_PUBLICATION:
dumpPublication(fout, (const PublicationInfo *) dobj);
break;
+ case DO_PUBLICATION_EXCEPT_REL:
+ /* will be dumped in dumpPublication */
+ break;
case DO_PUBLICATION_REL:
dumpPublicationTable(fout, (const PublicationRelInfo *) dobj);
break;
@@ -20182,6 +20233,7 @@ addBoundaryDependencies(DumpableObject **dobjs, int numObjs,
case DO_DEFAULT_ACL:
case DO_POLICY:
case DO_PUBLICATION:
+ case DO_PUBLICATION_EXCEPT_REL:
case DO_PUBLICATION_REL:
case DO_PUBLICATION_TABLE_IN_SCHEMA:
case DO_SUBSCRIPTION:
diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h
index 72a00e1bc20..723b5575c53 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -81,6 +81,7 @@ typedef enum
DO_REFRESH_MATVIEW,
DO_POLICY,
DO_PUBLICATION,
+ DO_PUBLICATION_EXCEPT_REL,
DO_PUBLICATION_REL,
DO_PUBLICATION_TABLE_IN_SCHEMA,
DO_REL_STATS,
diff --git a/src/bin/pg_dump/pg_dump_sort.c b/src/bin/pg_dump/pg_dump_sort.c
index 164c76e0864..f3c30f3be37 100644
--- a/src/bin/pg_dump/pg_dump_sort.c
+++ b/src/bin/pg_dump/pg_dump_sort.c
@@ -92,6 +92,7 @@ enum dbObjectTypePriorities
PRIO_FK_CONSTRAINT,
PRIO_POLICY,
PRIO_PUBLICATION,
+ PRIO_PUBLICATION_EXCEPT_REL,
PRIO_PUBLICATION_REL,
PRIO_PUBLICATION_TABLE_IN_SCHEMA,
PRIO_SUBSCRIPTION,
@@ -147,6 +148,7 @@ static const int dbObjectTypePriority[] =
[DO_REFRESH_MATVIEW] = PRIO_REFRESH_MATVIEW,
[DO_POLICY] = PRIO_POLICY,
[DO_PUBLICATION] = PRIO_PUBLICATION,
+ [DO_PUBLICATION_EXCEPT_REL] = PRIO_PUBLICATION_EXCEPT_REL,
[DO_PUBLICATION_REL] = PRIO_PUBLICATION_REL,
[DO_PUBLICATION_TABLE_IN_SCHEMA] = PRIO_PUBLICATION_TABLE_IN_SCHEMA,
[DO_REL_STATS] = PRIO_STATISTICS_DATA_DATA,
@@ -443,6 +445,17 @@ DOTypeNameCompare(const void *p1, const void *p2)
if (cmpval != 0)
return cmpval;
}
+ else if (obj1->objType == DO_PUBLICATION_EXCEPT_REL)
+ {
+ PublicationRelInfo *probj1 = *(PublicationRelInfo *const *) p1;
+ PublicationRelInfo *probj2 = *(PublicationRelInfo *const *) p2;
+
+ /* Sort by publication name, since (namespace, name) match the rel */
+ cmpval = strcmp(probj1->publication->dobj.name,
+ probj2->publication->dobj.name);
+ if (cmpval != 0)
+ return cmpval;
+ }
else if (obj1->objType == DO_PUBLICATION_TABLE_IN_SCHEMA)
{
PublicationSchemaInfo *psobj1 = *(PublicationSchemaInfo *const *) p1;
@@ -1715,6 +1728,11 @@ describeDumpableObject(DumpableObject *obj, char *buf, int bufsize)
"PUBLICATION (ID %d OID %u)",
obj->dumpId, obj->catId.oid);
return;
+ case DO_PUBLICATION_EXCEPT_REL:
+ snprintf(buf, bufsize,
+ "PUBLICATION EXCEPT TABLE (ID %d OID %u)",
+ obj->dumpId, obj->catId.oid);
+ return;
case DO_PUBLICATION_REL:
snprintf(buf, bufsize,
"PUBLICATION TABLE (ID %d OID %u)",
diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
index 445a541abf6..156319b8038 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -3170,6 +3170,26 @@ my %tests = (
like => { %full_runs, section_post_data => 1, },
},
+ 'CREATE PUBLICATION pub8' => {
+ create_order => 50,
+ create_sql =>
+ 'CREATE PUBLICATION pub8 FOR ALL TABLES EXCEPT (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
+ /xm,
+ like => { %full_runs, section_post_data => 1, },
+ },
+
+ '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);',
+ 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
+ /xm,
+ like => { %full_runs, section_post_data => 1, },
+ },
+
'CREATE SUBSCRIPTION sub1' => {
create_order => 50,
create_sql => 'CREATE SUBSCRIPTION sub1
@@ -5157,7 +5177,7 @@ foreach my $run (sort keys %pgdump_runs)
#
# Either "all_runs" should be set or there should be a "like" list,
# even if it is empty. (This makes the test more self-documenting.)
- if (!defined($tests{$test}->{all_runs})
+ if ( !defined($tests{$test}->{all_runs})
&& !defined($tests{$test}->{like}))
{
die "missing \"like\" in test \"$test\"";
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 36f24502842..50b1d435359 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -3073,17 +3073,34 @@ describeOneTableDetails(const char *schemaname,
" WHERE attrelid = pr.prrelid AND attnum = prattrs[s])\n"
" ELSE NULL END) "
"FROM pg_catalog.pg_publication p\n"
- " JOIN pg_catalog.pg_publication_rel pr ON p.oid = pr.prpubid\n"
- " JOIN pg_catalog.pg_class c ON c.oid = pr.prrelid\n"
- "WHERE pr.prrelid = '%s'\n"
+ " JOIN pg_catalog.pg_publication_rel pr ON p.oid = pr.prpubid\n"
+ " JOIN pg_catalog.pg_class c ON c.oid = pr.prrelid\n"
+ "WHERE pr.prrelid = '%s'\n",
+ oid, oid, oid);
+
+ if (pset.sversion >= 190000)
+ appendPQExpBufferStr(&buf, " AND NOT pr.prexcept\n");
+
+ appendPQExpBuffer(&buf,
"UNION\n"
"SELECT pubname\n"
- " , NULL\n"
- " , NULL\n"
+ " , NULL\n"
+ " , NULL\n"
"FROM pg_catalog.pg_publication p\n"
- "WHERE p.puballtables AND pg_catalog.pg_relation_is_publishable('%s')\n"
- "ORDER BY 1;",
- oid, oid, oid, oid);
+ "WHERE p.puballtables AND pg_catalog.pg_relation_is_publishable('%s')\n",
+ oid);
+
+ if (pset.sversion >= 190000)
+ appendPQExpBuffer(&buf,
+ " AND NOT EXISTS (\n"
+ " SELECT 1\n"
+ " FROM pg_catalog.pg_publication_rel pr\n"
+ " JOIN pg_catalog.pg_class pc\n"
+ " ON pr.prrelid = pc.oid\n"
+ " WHERE pr.prrelid = '%s' AND pr.prpubid = p.oid)\n",
+ oid);
+
+ appendPQExpBufferStr(&buf, "ORDER BY 1;");
}
else
{
@@ -6753,8 +6770,12 @@ describePublications(const char *pattern)
" pg_catalog.pg_publication_rel pr\n"
"WHERE c.relnamespace = n.oid\n"
" AND c.oid = pr.prrelid\n"
- " AND pr.prpubid = '%s'\n"
- "ORDER BY 1,2", pubid);
+ " AND pr.prpubid = '%s'\n", pubid);
+
+ if (pset.sversion >= 190000)
+ appendPQExpBuffer(&buf, " AND NOT pr.prexcept\n");
+
+ appendPQExpBuffer(&buf, "ORDER BY 1,2");
if (!addFooterToPublicationDesc(&buf, _("Tables:"), false, &cont))
goto error_return;
@@ -6772,6 +6793,23 @@ describePublications(const char *pattern)
goto error_return;
}
}
+ else
+ {
+ if (pset.sversion >= 190000)
+ {
+ /* Get the excluded tables for the specified publication */
+ printfPQExpBuffer(&buf,
+ "SELECT concat(c.relnamespace::regnamespace, '.', c.relname)\n"
+ "FROM pg_catalog.pg_class c\n"
+ " JOIN pg_catalog.pg_publication_rel pr ON c.oid = pr.prrelid\n"
+ "WHERE pr.prpubid = '%s'\n"
+ " AND pr.prexcept\n"
+ "ORDER BY 1", pubid);
+ if (!addFooterToPublicationDesc(&buf, _("Except tables:"),
+ true, &cont))
+ goto error_return;
+ }
+ }
printTable(&cont, pset.queryFout, false, pset.logfile);
printTableCleanup(&cont);
diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index d16181bc115..c5c8e6e8534 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -2291,11 +2291,16 @@ match_previous_words(int pattern_id,
COMPLETE_WITH("ADD", "DROP", "OWNER TO", "RENAME TO", "RESET", "SET");
/* ALTER PUBLICATION <name> ADD */
else if (Matches("ALTER", "PUBLICATION", MatchAny, "ADD"))
- COMPLETE_WITH("TABLES IN SCHEMA", "TABLE");
+ COMPLETE_WITH("ALL TABLES", "TABLES IN SCHEMA", "TABLE");
+ else if (Matches("ALTER", "PUBLICATION", MatchAny, "ADD", "ALL", "TABLES"))
+ COMPLETE_WITH("EXCEPT TABLE");
else if (Matches("ALTER", "PUBLICATION", MatchAny, "ADD|SET", "TABLE"))
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables);
- else if (HeadMatches("ALTER", "PUBLICATION", MatchAny, "ADD|SET", "TABLE") &&
- ends_with(prev_wd, ','))
+ else if (Matches("ALTER", "PUBLICATION", MatchAny, "ADD", "ALL", "TABLES", "EXCEPT", "TABLE"))
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables);
+ else if (HeadMatches("ALTER", "PUBLICATION", MatchAny, "ADD|SET", "TABLE") && ends_with(prev_wd, ','))
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables);
+ else if (HeadMatches("ALTER", "PUBLICATION", MatchAny, "ADD", "ALL", "TABLES", "EXCEPT", "TABLE") && ends_with(prev_wd, ','))
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables);
/*
@@ -3622,6 +3627,10 @@ 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 (");
+ else if (Matches("CREATE", "PUBLICATION", MatchAny, "FOR", "ALL", "TABLES", "EXCEPT"))
+ COMPLETE_WITH("TABLE");
+ else if (Matches("CREATE", "PUBLICATION", MatchAny, "FOR", "ALL", "TABLES", "EXCEPT", "TABLE", MatchAny) && !ends_with(prev_wd, ','))
COMPLETE_WITH("WITH (");
else if (Matches("CREATE", "PUBLICATION", MatchAny, "FOR", "TABLES"))
COMPLETE_WITH("IN SCHEMA");
diff --git a/src/include/catalog/pg_publication.h b/src/include/catalog/pg_publication.h
index 22f48bb8975..9a07215ae30 100644
--- a/src/include/catalog/pg_publication.h
+++ b/src/include/catalog/pg_publication.h
@@ -146,11 +146,12 @@ typedef struct PublicationRelInfo
Relation relation;
Node *whereClause;
List *columns;
+ bool except;
} PublicationRelInfo;
extern Publication *GetPublication(Oid pubid);
extern Publication *GetPublicationByName(const char *pubname, bool missing_ok);
-extern List *GetRelationPublications(Oid relid);
+extern List *GetRelationPublications(Oid relid, bool except_flag);
/*---------
* Expected values for pub_partopt parameter of GetPublicationRelations(),
@@ -168,9 +169,10 @@ typedef enum PublicationPartOpt
PUBLICATION_PART_ALL,
} PublicationPartOpt;
-extern List *GetPublicationRelations(Oid pubid, PublicationPartOpt pub_partopt);
+extern List *GetPublicationRelations(Oid pubid, PublicationPartOpt pub_partopt,
+ bool except_flag);
extern List *GetAllTablesPublications(void);
-extern List *GetAllPublicationRelations(char relkind, bool pubviaroot);
+extern List *GetAllPublicationRelations(Oid pubid, char relkind, bool pubviaroot);
extern List *GetPublicationSchemas(Oid pubid);
extern List *GetSchemaPublications(Oid schemaid);
extern List *GetSchemaPublicationRelations(Oid schemaid,
@@ -181,7 +183,7 @@ extern List *GetPubPartitionOptionRelations(List *result,
PublicationPartOpt pub_partopt,
Oid relid);
extern Oid GetTopMostAncestorInPublication(Oid puboid, List *ancestors,
- int *ancestor_level);
+ int *ancestor_level, bool puballtables);
extern bool is_publishable_relation(Relation rel);
extern bool is_schema_publication(Oid pubid);
diff --git a/src/include/catalog/pg_publication_rel.h b/src/include/catalog/pg_publication_rel.h
index 92cc36dfdf6..e7d7f3ba85c 100644
--- a/src/include/catalog/pg_publication_rel.h
+++ b/src/include/catalog/pg_publication_rel.h
@@ -31,6 +31,7 @@ CATALOG(pg_publication_rel,6106,PublicationRelRelationId)
Oid oid; /* oid */
Oid prpubid BKI_LOOKUP(pg_publication); /* Oid of the publication */
Oid prrelid BKI_LOOKUP(pg_class); /* Oid of the relation */
+ bool prexcept BKI_DEFAULT(f); /* exclude the relation */
#ifdef CATALOG_VARLEN /* variable-length fields start here */
pg_node_tree prqual; /* qualifications */
diff --git a/src/include/commands/publicationcmds.h b/src/include/commands/publicationcmds.h
index f90cf1ef896..0ad5d28754d 100644
--- a/src/include/commands/publicationcmds.h
+++ b/src/include/commands/publicationcmds.h
@@ -32,10 +32,11 @@ extern ObjectAddress AlterPublicationOwner(const char *name, Oid newOwnerId);
extern void AlterPublicationOwner_oid(Oid pubid, Oid newOwnerId);
extern void InvalidatePublicationRels(List *relids);
extern bool pub_rf_contains_invalid_column(Oid pubid, Relation relation,
- List *ancestors, bool pubviaroot);
+ List *ancestors, bool pubviaroot,
+ bool puballtables);
extern bool pub_contains_invalid_column(Oid pubid, Relation relation,
List *ancestors, bool pubviaroot,
- char pubgencols_type,
+ char pubgencols_type, bool puballtables,
bool *invalid_column_list,
bool *invalid_gen_col);
extern void InvalidatePubRelSyncCache(Oid pubid, bool puballtables);
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 778ef039d86..305c246ea09 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -4271,6 +4271,7 @@ typedef struct PublicationTable
RangeVar *relation; /* relation to be published */
Node *whereClause; /* qualifications */
List *columns; /* List of columns in a publication table */
+ bool except; /* exclude the relation */
} PublicationTable;
/*
@@ -4279,6 +4280,7 @@ typedef struct PublicationTable
typedef enum PublicationObjSpecType
{
PUBLICATIONOBJ_TABLE, /* A table */
+ PUBLICATIONOBJ_EXCEPT_TABLE, /* A table to be excluded */
PUBLICATIONOBJ_TABLES_IN_SCHEMA, /* All tables in schema */
PUBLICATIONOBJ_TABLES_IN_CUR_SCHEMA, /* All tables in first element of
* search_path */
@@ -4307,6 +4309,7 @@ typedef struct PublicationAllObjSpec
{
NodeTag type;
PublicationAllObjType pubobjtype; /* type of this publication object */
+ List *except_objects; /* List of publication object to be excluded */
ParseLoc location; /* token location, or -1 if unknown */
} PublicationAllObjSpec;
@@ -4342,6 +4345,7 @@ typedef struct AlterPublicationStmt
* objects.
*/
List *pubobjects; /* Optional list of publication objects */
+ bool for_all_tables; /* Special publication for all tables in db */
AlterPublicationAction action; /* What action to perform with the given
* objects */
} AlterPublicationStmt;
diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out
index bb614ba5d0a..062469d7220 100644
--- a/src/test/regress/expected/publication.out
+++ b/src/test/regress/expected/publication.out
@@ -213,13 +213,37 @@ Not-null constraints:
regress_publication_user | t | f | t | t | f | f | none | f
(1 row)
+SET client_min_messages = 'ERROR';
+CREATE PUBLICATION testpub_foralltables_excepttable FOR ALL TABLES EXCEPT TABLE (testpub_tbl1, testpub_tbl2);
+-- specify EXCEPT without TABLE
+CREATE PUBLICATION testpub_foralltables_excepttable1 FOR ALL TABLES EXCEPT (testpub_tbl1);
+RESET client_min_messages;
+\dRp+ testpub_foralltables_excepttable
+ Publication testpub_foralltables_excepttable
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | t | f | t | t | t | t | none | f
+Except tables:
+ "public.testpub_tbl1"
+ "public.testpub_tbl2"
+
+\dRp+ testpub_foralltables_excepttable1
+ Publication testpub_foralltables_excepttable1
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | t | f | t | t | t | t | none | f
+Except tables:
+ "public.testpub_tbl1"
+
DROP TABLE testpub_tbl2;
-DROP PUBLICATION testpub_foralltables, testpub_fortable, testpub_forschema, testpub_for_tbl_schema;
+DROP PUBLICATION testpub_foralltables, testpub_fortable, testpub_forschema, testpub_for_tbl_schema, testpub_foralltables_excepttable, testpub_foralltables_excepttable1;
CREATE TABLE testpub_tbl3 (a int);
CREATE TABLE testpub_tbl3a (b text) INHERITS (testpub_tbl3);
SET client_min_messages = 'ERROR';
CREATE PUBLICATION testpub3 FOR TABLE testpub_tbl3;
CREATE PUBLICATION testpub4 FOR TABLE ONLY testpub_tbl3;
+CREATE PUBLICATION testpub5 FOR ALL TABLES EXCEPT TABLE (testpub_tbl3);
+CREATE PUBLICATION testpub6 FOR ALL TABLES EXCEPT TABLE (ONLY testpub_tbl3);
RESET client_min_messages;
\dRp+ testpub3
Publication testpub3
@@ -238,8 +262,25 @@ Tables:
Tables:
"public.testpub_tbl3"
+\dRp+ testpub5
+ Publication testpub5
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | t | f | t | t | t | t | none | f
+Except tables:
+ "public.testpub_tbl3"
+ "public.testpub_tbl3a"
+
+\dRp+ testpub6
+ Publication testpub6
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | t | f | t | t | t | t | none | f
+Except tables:
+ "public.testpub_tbl3"
+
DROP TABLE testpub_tbl3, testpub_tbl3a;
-DROP PUBLICATION testpub3, testpub4;
+DROP PUBLICATION testpub3, testpub4, testpub5, testpub6;
--- Tests for publications with SEQUENCES
CREATE SEQUENCE regress_pub_seq0;
CREATE SEQUENCE pub_test.regress_pub_seq1;
@@ -2012,110 +2053,150 @@ DROP TABLE gencols;
-- Tests for ALTER PUBLICATION ... RESET
CREATE SCHEMA pub_sch1;
CREATE TABLE pub_sch1.tbl1 (a int);
+CREATE TABLE pub_sch1.tbl2 (a int);
SET client_min_messages = 'ERROR';
CREATE PUBLICATION testpub_reset FOR ALL TABLES;
RESET client_min_messages;
+-- Can't add EXCEPT TABLE to 'FOR ALL TABLES' publication
+ALTER PUBLICATION testpub_reset ADD ALL TABLES EXCEPT TABLE (pub_sch1.tbl1);
+ERROR: adding ALL TABLES requires the publication to have default publication parameter values
+DETAIL: ALL TABLES flag should not be set and no tables/schemas should be associated.
+HINT: Use ALTER PUBLICATION ... RESET to reset the publication
-- Verify that 'ALL TABLES' flag is reset
\dRp+ testpub_reset
- Publication testpub_reset
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
---------------------------+------------+---------+---------+---------+-----------+-------------------+----------
- regress_publication_user | t | t | t | t | t | none | f
+ Publication testpub_reset
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | t | f | t | t | t | t | none | f
(1 row)
ALTER PUBLICATION testpub_reset RESET;
\dRp+ testpub_reset
- Publication testpub_reset
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
---------------------------+------------+---------+---------+---------+-----------+-------------------+----------
- regress_publication_user | f | t | t | t | t | none | f
+ Publication testpub_reset
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | f | t | t | t | t | none | f
(1 row)
+-- Should work now after resetting the publication
+ALTER PUBLICATION testpub_reset ADD ALL TABLES EXCEPT TABLE (pub_sch1.tbl1, pub_sch1.tbl2);
+\dRp+ testpub_reset
+ Publication testpub_reset
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | t | f | t | t | t | t | none | f
+Except tables:
+ "pub_sch1.tbl1"
+ "pub_sch1.tbl2"
+
+ALTER PUBLICATION testpub_reset RESET;
ALTER PUBLICATION testpub_reset ADD TABLE pub_sch1.tbl1;
+-- Can't add EXCEPT TABLE to 'FOR TABLE' publication
+ALTER PUBLICATION testpub_reset ADD ALL TABLES EXCEPT TABLE (pub_sch1.tbl1);
+ERROR: adding ALL TABLES requires the publication to have default publication parameter values
+DETAIL: ALL TABLES flag should not be set and no tables/schemas should be associated.
+HINT: Use ALTER PUBLICATION ... RESET to reset the publication
-- Verify that associated tables are removed from the publication after RESET
\dRp+ testpub_reset
- Publication testpub_reset
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
---------------------------+------------+---------+---------+---------+-----------+-------------------+----------
- regress_publication_user | f | t | t | t | t | none | f
+ Publication testpub_reset
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | f | t | t | t | t | none | f
Tables:
"pub_sch1.tbl1"
ALTER PUBLICATION testpub_reset RESET;
\dRp+ testpub_reset
- Publication testpub_reset
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
---------------------------+------------+---------+---------+---------+-----------+-------------------+----------
- regress_publication_user | f | t | t | t | t | none | f
+ Publication testpub_reset
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | f | t | t | t | t | none | f
(1 row)
ALTER PUBLICATION testpub_reset ADD TABLES IN SCHEMA public;
+-- Can't add EXCEPT TABLE to 'FOR ALL TABLES IN SCHEMA' publication
+ALTER PUBLICATION testpub_reset ADD ALL TABLES EXCEPT TABLE (pub_sch1.tbl1);
+ERROR: adding ALL TABLES requires the publication to have default publication parameter values
+DETAIL: ALL TABLES flag should not be set and no tables/schemas should be associated.
+HINT: Use ALTER PUBLICATION ... RESET to reset the publication
-- Verify that associated schemas are reomved from the publication after RESET
\dRp+ testpub_reset
- Publication testpub_reset
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
---------------------------+------------+---------+---------+---------+-----------+-------------------+----------
- regress_publication_user | f | t | t | t | t | none | f
+ Publication testpub_reset
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | f | t | t | t | t | none | f
Tables from schemas:
"public"
ALTER PUBLICATION testpub_reset RESET;
\dRp+ testpub_reset
- Publication testpub_reset
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
---------------------------+------------+---------+---------+---------+-----------+-------------------+----------
- regress_publication_user | f | t | t | t | t | none | f
+ Publication testpub_reset
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | f | t | t | t | t | none | f
(1 row)
ALTER PUBLICATION testpub_reset SET (PUBLISH = '');
+-- Can't add EXCEPT TABLE when the 'PUBLISH' parameter does not have default
+-- value
+ALTER PUBLICATION testpub_reset ADD ALL TABLES EXCEPT TABLE (pub_sch1.tbl1);
+ERROR: adding ALL TABLES requires the publication to have default publication parameter values
+DETAIL: ALL TABLES flag should not be set and no tables/schemas should be associated.
+HINT: Use ALTER PUBLICATION ... RESET to reset the publication
-- Verify that 'PUBLISH' parameter is reset
\dRp+ testpub_reset
- Publication testpub_reset
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
---------------------------+------------+---------+---------+---------+-----------+-------------------+----------
- regress_publication_user | f | f | f | f | f | none | f
+ Publication testpub_reset
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | f | f | f | f | f | none | f
(1 row)
ALTER PUBLICATION testpub_reset RESET;
\dRp+ testpub_reset
- Publication testpub_reset
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
---------------------------+------------+---------+---------+---------+-----------+-------------------+----------
- regress_publication_user | f | t | t | t | t | none | f
+ Publication testpub_reset
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | f | t | t | t | t | none | f
(1 row)
ALTER PUBLICATION testpub_reset SET (PUBLISH_VIA_PARTITION_ROOT = 'true');
+-- Can't add EXCEPT TABLE when 'PUBLISH_VIA_PARTITION_ROOT' parameter does not
+-- have default value
+ALTER PUBLICATION testpub_reset ADD ALL TABLES EXCEPT TABLE (pub_sch1.tbl1);
+ERROR: adding ALL TABLES requires the publication to have default publication parameter values
+DETAIL: ALL TABLES flag should not be set and no tables/schemas should be associated.
+HINT: Use ALTER PUBLICATION ... RESET to reset the publication
-- Verify that 'PUBLISH_VIA_PARTITION_ROOT' parameter is reset
\dRp+ testpub_reset
- Publication testpub_reset
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
---------------------------+------------+---------+---------+---------+-----------+-------------------+----------
- regress_publication_user | f | t | t | t | t | none | t
+ Publication testpub_reset
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | f | t | t | t | t | none | t
(1 row)
ALTER PUBLICATION testpub_reset RESET;
\dRp+ testpub_reset
- Publication testpub_reset
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
---------------------------+------------+---------+---------+---------+-----------+-------------------+----------
- regress_publication_user | f | t | t | t | t | none | f
+ Publication testpub_reset
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | f | t | t | t | t | none | f
(1 row)
ALTER PUBLICATION testpub_reset SET (PUBLISH_GENERATED_COLUMNS = stored);
-- Verify that 'PUBLISH_GENERATED_COLUMNS' parameter is reset
\dRp+ testpub_reset
- Publication testpub_reset
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
---------------------------+------------+---------+---------+---------+-----------+-------------------+----------
- regress_publication_user | f | t | t | t | t | stored | f
+ Publication testpub_reset
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | f | t | t | t | t | stored | f
(1 row)
ALTER PUBLICATION testpub_reset RESET;
\dRp+ testpub_reset
- Publication testpub_reset
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
---------------------------+------------+---------+---------+---------+-----------+-------------------+----------
- regress_publication_user | f | t | t | t | t | none | f
+ Publication testpub_reset
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | f | t | t | t | t | none | f
(1 row)
-- Verify that only superuser can reset a publication
@@ -2123,9 +2204,13 @@ ALTER PUBLICATION testpub_reset OWNER TO regress_publication_user2;
SET ROLE regress_publication_user2;
ALTER PUBLICATION testpub_reset RESET; -- fail - must be superuser
ERROR: must be superuser to RESET publication
+-- Verify that only superuser can ADD ALL TABLES
+ALTER PUBLICATION testpub_reset ADD ALL TABLES;
+ERROR: must be superuser to ADD ALL TABLES to the publication
SET ROLE regress_publication_user;
DROP PUBLICATION testpub_reset;
DROP TABLE pub_sch1.tbl1;
+DROP TABLE pub_sch1.tbl2;
DROP SCHEMA pub_sch1;
RESET client_min_messages;
-- Test that the INSERT ON CONFLICT command correctly checks REPLICA IDENTITY
diff --git a/src/test/regress/sql/publication.sql b/src/test/regress/sql/publication.sql
index 829466d8de0..f5c62338e78 100644
--- a/src/test/regress/sql/publication.sql
+++ b/src/test/regress/sql/publication.sql
@@ -105,20 +105,33 @@ SELECT pubname, puballtables FROM pg_publication WHERE pubname = 'testpub_forall
\d+ testpub_tbl2
\dRp+ testpub_foralltables
+SET client_min_messages = 'ERROR';
+CREATE PUBLICATION testpub_foralltables_excepttable FOR ALL TABLES EXCEPT TABLE (testpub_tbl1, testpub_tbl2);
+-- specify EXCEPT without TABLE
+CREATE PUBLICATION testpub_foralltables_excepttable1 FOR ALL TABLES EXCEPT (testpub_tbl1);
+RESET client_min_messages;
+
+\dRp+ testpub_foralltables_excepttable
+\dRp+ testpub_foralltables_excepttable1
+
DROP TABLE testpub_tbl2;
-DROP PUBLICATION testpub_foralltables, testpub_fortable, testpub_forschema, testpub_for_tbl_schema;
+DROP PUBLICATION testpub_foralltables, testpub_fortable, testpub_forschema, testpub_for_tbl_schema, testpub_foralltables_excepttable, testpub_foralltables_excepttable1;
CREATE TABLE testpub_tbl3 (a int);
CREATE TABLE testpub_tbl3a (b text) INHERITS (testpub_tbl3);
SET client_min_messages = 'ERROR';
CREATE PUBLICATION testpub3 FOR TABLE testpub_tbl3;
CREATE PUBLICATION testpub4 FOR TABLE ONLY testpub_tbl3;
+CREATE PUBLICATION testpub5 FOR ALL TABLES EXCEPT TABLE (testpub_tbl3);
+CREATE PUBLICATION testpub6 FOR ALL TABLES EXCEPT TABLE (ONLY testpub_tbl3);
RESET client_min_messages;
\dRp+ testpub3
\dRp+ testpub4
+\dRp+ testpub5
+\dRp+ testpub6
DROP TABLE testpub_tbl3, testpub_tbl3a;
-DROP PUBLICATION testpub3, testpub4;
+DROP PUBLICATION testpub3, testpub4, testpub5, testpub6;
--- Tests for publications with SEQUENCES
CREATE SEQUENCE regress_pub_seq0;
@@ -1271,17 +1284,30 @@ DROP TABLE gencols;
-- Tests for ALTER PUBLICATION ... RESET
CREATE SCHEMA pub_sch1;
CREATE TABLE pub_sch1.tbl1 (a int);
+CREATE TABLE pub_sch1.tbl2 (a int);
SET client_min_messages = 'ERROR';
CREATE PUBLICATION testpub_reset FOR ALL TABLES;
RESET client_min_messages;
+-- Can't add EXCEPT TABLE to 'FOR ALL TABLES' publication
+ALTER PUBLICATION testpub_reset ADD ALL TABLES EXCEPT TABLE (pub_sch1.tbl1);
+
-- Verify that 'ALL TABLES' flag is reset
\dRp+ testpub_reset
ALTER PUBLICATION testpub_reset RESET;
\dRp+ testpub_reset
+-- Should work now after resetting the publication
+ALTER PUBLICATION testpub_reset ADD ALL TABLES EXCEPT TABLE (pub_sch1.tbl1, pub_sch1.tbl2);
+\dRp+ testpub_reset
+
+ALTER PUBLICATION testpub_reset RESET;
+
ALTER PUBLICATION testpub_reset ADD TABLE pub_sch1.tbl1;
+-- Can't add EXCEPT TABLE to 'FOR TABLE' publication
+ALTER PUBLICATION testpub_reset ADD ALL TABLES EXCEPT TABLE (pub_sch1.tbl1);
+
-- Verify that associated tables are removed from the publication after RESET
\dRp+ testpub_reset
ALTER PUBLICATION testpub_reset RESET;
@@ -1289,6 +1315,9 @@ ALTER PUBLICATION testpub_reset RESET;
ALTER PUBLICATION testpub_reset ADD TABLES IN SCHEMA public;
+-- Can't add EXCEPT TABLE to 'FOR ALL TABLES IN SCHEMA' publication
+ALTER PUBLICATION testpub_reset ADD ALL TABLES EXCEPT TABLE (pub_sch1.tbl1);
+
-- Verify that associated schemas are reomved from the publication after RESET
\dRp+ testpub_reset
ALTER PUBLICATION testpub_reset RESET;
@@ -1296,6 +1325,10 @@ ALTER PUBLICATION testpub_reset RESET;
ALTER PUBLICATION testpub_reset SET (PUBLISH = '');
+-- Can't add EXCEPT TABLE when the 'PUBLISH' parameter does not have default
+-- value
+ALTER PUBLICATION testpub_reset ADD ALL TABLES EXCEPT TABLE (pub_sch1.tbl1);
+
-- Verify that 'PUBLISH' parameter is reset
\dRp+ testpub_reset
ALTER PUBLICATION testpub_reset RESET;
@@ -1303,6 +1336,10 @@ ALTER PUBLICATION testpub_reset RESET;
ALTER PUBLICATION testpub_reset SET (PUBLISH_VIA_PARTITION_ROOT = 'true');
+-- Can't add EXCEPT TABLE when 'PUBLISH_VIA_PARTITION_ROOT' parameter does not
+-- have default value
+ALTER PUBLICATION testpub_reset ADD ALL TABLES EXCEPT TABLE (pub_sch1.tbl1);
+
-- Verify that 'PUBLISH_VIA_PARTITION_ROOT' parameter is reset
\dRp+ testpub_reset
ALTER PUBLICATION testpub_reset RESET;
@@ -1319,10 +1356,14 @@ ALTER PUBLICATION testpub_reset RESET;
ALTER PUBLICATION testpub_reset OWNER TO regress_publication_user2;
SET ROLE regress_publication_user2;
ALTER PUBLICATION testpub_reset RESET; -- fail - must be superuser
+
+-- Verify that only superuser can ADD ALL TABLES
+ALTER PUBLICATION testpub_reset ADD ALL TABLES;
SET ROLE regress_publication_user;
DROP PUBLICATION testpub_reset;
DROP TABLE pub_sch1.tbl1;
+DROP TABLE pub_sch1.tbl2;
DROP SCHEMA pub_sch1;
RESET client_min_messages;
diff --git a/src/test/subscription/meson.build b/src/test/subscription/meson.build
index 85d10a89994..b8e5c54c314 100644
--- a/src/test/subscription/meson.build
+++ b/src/test/subscription/meson.build
@@ -46,6 +46,7 @@ tests += {
't/034_temporal.pl',
't/035_conflicts.pl',
't/036_sequences.pl',
+ 't/037_rep_changes_except_table.pl',
't/100_bugs.pl',
],
},
diff --git a/src/test/subscription/t/037_rep_changes_except_table.pl b/src/test/subscription/t/037_rep_changes_except_table.pl
new file mode 100644
index 00000000000..096e0606365
--- /dev/null
+++ b/src/test/subscription/t/037_rep_changes_except_table.pl
@@ -0,0 +1,186 @@
+
+# Copyright (c) 2021-2025, PostgreSQL Global Development Group
+
+# Logical replication tests for except table publications
+use strict;
+use warnings;
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+# Initialize publisher node
+my $node_publisher = PostgreSQL::Test::Cluster->new('publisher');
+$node_publisher->init(allows_streaming => 'logical');
+$node_publisher->start;
+
+# Create subscriber node
+my $node_subscriber = PostgreSQL::Test::Cluster->new('subscriber');
+$node_subscriber->init(allows_streaming => 'logical');
+$node_subscriber->start;
+
+# Test replication with publications created using FOR ALL TABLES EXCEPT TABLE
+# clause.
+# Create schemas and tables on publisher
+$node_publisher->safe_psql(
+ 'postgres', qq(
+ CREATE SCHEMA sch1;
+ CREATE TABLE sch1.tab1 AS SELECT generate_series(1,10) AS a;
+ CREATE TABLE public.tab1(a int);
+));
+
+# Create schemas and tables on subscriber
+$node_subscriber->safe_psql(
+ 'postgres', qq(
+ CREATE SCHEMA sch1;
+ CREATE TABLE sch1.tab1 (a int);
+ CREATE TABLE public.tab1 (a int);
+));
+
+# Setup logical replication
+my $publisher_connstr = $node_publisher->connstr . ' dbname=postgres';
+$node_publisher->safe_psql('postgres',
+ "CREATE PUBLICATION tap_pub_schema FOR ALL TABLES EXCEPT TABLE (sch1.tab1)"
+);
+
+$node_subscriber->safe_psql('postgres',
+ "CREATE SUBSCRIPTION tap_sub_schema CONNECTION '$publisher_connstr' PUBLICATION tap_pub_schema"
+);
+
+# Wait for initial table sync to finish
+$node_subscriber->wait_for_subscription_sync($node_publisher,
+ 'tap_sub_schema');
+
+# Check the table data does not sync for excluded table
+my $result = $node_subscriber->safe_psql('postgres',
+ "SELECT count(*), min(a), max(a) FROM sch1.tab1");
+is($result, qq(0||),
+ 'check there is no initial data copied for the excluded table');
+
+# Insert some data and verify that inserted data is not replicated
+$node_publisher->safe_psql('postgres',
+ "INSERT INTO sch1.tab1 VALUES(generate_series(11,20))");
+
+$node_publisher->wait_for_catchup('tap_sub_schema');
+
+$result = $node_subscriber->safe_psql('postgres',
+ "SELECT count(*), min(a), max(a) FROM sch1.tab1");
+is($result, qq(0||), 'check replicated inserts on subscriber');
+
+# Alter publication to exclude data changes in public.tab1 and verify that
+# subscriber does not get the changed data for this table.
+$node_publisher->safe_psql(
+ 'postgres', qq(
+ ALTER PUBLICATION tap_pub_schema RESET;
+ ALTER PUBLICATION tap_pub_schema ADD ALL TABLES EXCEPT TABLE (sch1.tab1, public.tab1);
+ INSERT INTO public.tab1 VALUES(generate_series(1,10));
+));
+$node_publisher->wait_for_catchup('tap_sub_schema');
+
+$result = $node_subscriber->safe_psql('postgres',
+ "SELECT count(*), min(a), max(a) FROM public.tab1");
+is($result, qq(0||), 'check rows on subscriber catchup');
+
+# cleanup
+$node_publisher->safe_psql('postgres', "DROP PUBLICATION tap_pub_schema");
+$node_subscriber->safe_psql('postgres', "DROP SUBSCRIPTION tap_sub_schema");
+
+
+# Check behaviour of publish_via_partition_root and EXCEPT clause with
+# partitioned table or partiitions of partitioned table.
+$node_publisher->safe_psql(
+ 'postgres', qq(
+ CREATE TABLE sch1.t1(a int) PARTITION BY RANGE(a);
+ CREATE TABLE sch1.part1 PARTITION OF sch1.t1 FOR VALUES FROM (0) TO (5);
+ INSERT INTO sch1.t1 VALUES (1);
+));
+
+$node_subscriber->safe_psql(
+ 'postgres', qq(
+ CREATE TABLE sch1.t1(a int);
+ CREATE TABLE sch1.part1(a int);
+));
+
+# publish_via_partition_root = false and EXCEPT sch1.part1
+$node_publisher->safe_psql('postgres',
+ "CREATE PUBLICATION tap_pub_part FOR ALL TABLES EXCEPT TABLE (sch1.part1)");
+$node_subscriber->safe_psql('postgres',
+ "CREATE SUBSCRIPTION tap_sub_part CONNECTION '$publisher_connstr' PUBLICATION tap_pub_part"
+);
+$node_subscriber->wait_for_subscription_sync($node_publisher, 'tap_sub_part');
+$node_publisher->safe_psql('postgres', "INSERT INTO sch1.t1 VALUES (2)");
+$node_publisher->wait_for_catchup('tap_sub_part');
+
+$result = $node_subscriber->safe_psql('postgres', "SELECT * FROM sch1.t1");
+is($result, qq(), 'check rows on partitioned table');
+
+$result = $node_subscriber->safe_psql('postgres', "SELECT * FROM sch1.part1");
+is($result, qq(), 'check rows on partitions');
+
+# publish_via_partition_root = false and EXCEPT sch1.t1
+$node_publisher->safe_psql(
+ 'postgres', qq(
+ ALTER PUBLICATION tap_pub_part RESET;
+ ALTER PUBLICATION tap_pub_part ADD ALL TABLES EXCEPT (sch1.t1);
+));
+$node_subscriber->safe_psql('postgres',
+ "ALTER SUBSCRIPTION tap_sub_part REFRESH PUBLICATION");
+$node_subscriber->wait_for_subscription_sync($node_publisher, 'tap_sub_part');
+$node_publisher->safe_psql('postgres', "INSERT INTO sch1.t1 VALUES (3)");
+$node_publisher->wait_for_catchup('tap_sub_part');
+
+$result = $node_subscriber->safe_psql('postgres', "SELECT * FROM sch1.t1");
+is($result, qq(), 'check rows on partitioned table');
+
+$result = $node_subscriber->safe_psql('postgres', "SELECT * FROM sch1.part1");
+is( $result, qq(1
+2
+3), 'check rows on partitions');
+$node_publisher->safe_psql('postgres', "TRUNCATE sch1.t1");
+$node_publisher->wait_for_catchup('tap_sub_part');
+$node_subscriber->safe_psql('postgres', "DROP SUBSCRIPTION tap_sub_part");
+$node_publisher->safe_psql('postgres', "DROP PUBLICATION tap_pub_part;");
+
+# publish_via_partition_root = true and EXCEPT sch1.t1
+$node_publisher->safe_psql(
+ 'postgres', qq(
+ CREATE PUBLICATION tap_pub_part FOR ALL TABLES EXCEPT TABLE (sch1.t1) WITH (publish_via_partition_root);
+ INSERT INTO sch1.t1 VALUES (1)
+));
+$node_subscriber->safe_psql('postgres',
+ "CREATE SUBSCRIPTION tap_sub_part CONNECTION '$publisher_connstr' PUBLICATION tap_pub_part"
+);
+$node_subscriber->wait_for_subscription_sync($node_publisher, 'tap_sub_part');
+$node_publisher->safe_psql('postgres', "INSERT INTO sch1.t1 VALUES (2)");
+$node_publisher->wait_for_catchup('tap_sub_part');
+
+$result = $node_subscriber->safe_psql('postgres', "SELECT * FROM sch1.t1");
+is($result, qq(), 'check rows on partitioned table');
+
+$result = $node_subscriber->safe_psql('postgres', "SELECT * FROM sch1.part1");
+is($result, qq(), 'check rows on partitions');
+
+# publish_via_partition_root = true and EXCEPT sch1.part1
+$node_publisher->safe_psql(
+ 'postgres', qq(
+ ALTER PUBLICATION tap_pub_part RESET;
+ ALTER PUBLICATION tap_pub_part ADD ALL TABLES EXCEPT (sch1.part1);
+ ALTER PUBLICATION tap_pub_part SET (publish_via_partition_root);
+));
+$node_subscriber->safe_psql('postgres',
+ "ALTER SUBSCRIPTION tap_sub_part REFRESH PUBLICATION");
+$node_subscriber->wait_for_subscription_sync($node_publisher, 'tap_sub_part');
+$node_publisher->safe_psql('postgres', "INSERT INTO sch1.t1 VALUES (3)");
+$node_publisher->wait_for_catchup('tap_sub_part');
+
+$result = $node_subscriber->safe_psql('postgres', "SELECT * FROM sch1.t1");
+is( $result, qq(1
+2
+3), 'check rows on partitioned table');
+
+$result = $node_subscriber->safe_psql('postgres', "SELECT * FROM sch1.part1");
+is($result, qq(), 'check rows on partitions');
+
+$node_subscriber->stop('fast');
+$node_publisher->stop('fast');
+
+done_testing();
--
2.34.1
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]
Subject: Re: Skipping schema changes in publication
In-Reply-To: <CANhcyEUh9ki36VTXyYf8UqUrfLX9ZhfP_f2LjpvvycgqWLQqqQ@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