public inbox for [email protected]
help / color / mirror / Atom feedFrom: Shlok Kyal <[email protected]>
To: Amit Kapila <[email protected]>
Cc: Zhijie Hou (Fujitsu) <[email protected]>
Cc: vignesh C <[email protected]>
Cc: YeXiu <[email protected]>
Cc: Ian Lawrence Barwick <[email protected]>
Cc: Peter Smith <[email protected]>
Cc: Bharath Rupireddy <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: Skipping schema changes in publication
Date: Tue, 17 Jun 2025 13:11:34 +0530
Message-ID: <CANhcyEXspT3v5-Tdop9uqQV2HWBvZoN5P0BxXQ6Md6Mr7GXK9A@mail.gmail.com> (raw)
In-Reply-To: <CANhcyEXZq4mP5dNgg7u=sMPwvxA4_ZN9U92uZEuzs=0xTu+8Yg@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>
On Wed, 11 Jun 2025 at 19:37, Shlok Kyal <[email protected]> wrote:
>
> On Thu, 17 Apr 2025 at 09:12, Amit Kapila <[email protected]> wrote:
> >
> > On Wed, Apr 16, 2025 at 8:22 AM Zhijie Hou (Fujitsu)
> > <[email protected]> wrote:
> > >
> > > On Thu, Apr 10, 2025 at 7:25 PM Amit Kapila wrote:
> > > >
> > > > On Tue, Jan 9, 2024 at 12:02 PM vignesh C <[email protected]> wrote:
> > > > >
> > > > > As I did not see much interest from others, I'm withdrawing this patch
> > > > > for now. But if there is any interest others in future, I would be
> > > > > more than happy to work on this feature.
> > > > >
> > > >
> > > > Just FYI, I noticed a use case for this patch in email [1]. Users would like to
> > > > replicate all except a few columns having sensitive information. The challenge
> > > > with current column list features is that adding new tables to columns would
> > > > lead users to change the respective publications as well.
> > > >
> > > > [1] -
> > > > https://www.postgresql.org/message-id/tencent_DCDF626FCD4A556C51BE
> > > > 270FDC3047540208%40qq.com
> > >
> > > BTW, I noticed that debezium, an open source distributed platform for change
> > > data capture that replies on logical decoding, also support specifying the
> > > column exclusion list[1]. So, this indicates that there could be some use cases
> > > for this feature.
> > >
> >
> > Thanks for sharing the link. I see that they support both the include
> > and exclude lists for columns and tables.
> >
>
> Hi Hackers,
>
> I see there is some interest in the functionality added by this patch.
> I have rebased the patches in [1]. I saw a new column 'pubgencols' was
> added in pg_publication in PG 18. So, I have modified v11-0001 to
> RESET this as well.
> I am also working on creating a patch to exclude columns in
> publication as per suggestion in [2].
>
> [1]: https://www.postgresql.org/message-id/CALDaNm3dWZCYDih55qTNAYsjCvYXMFv%3D46UsDWmfCnXMt3kPCg%40mail.g...
> [2]: https://www.postgresql.org/message-id/CAA4eK1KRdAPC%3D5%3D7tQ1GW0cRwD%3DzaDMi%2BT4u_k4GxPhPY6e8BQ%40...
>
I have attached a patch support excluding columns for publication.
I have added a syntax: "FOR TABLE table_name EXCEPT (c1, c2, ..)"
It can be used with CREATE or ALTER PUBLICATION.
v12-0003 patch contains the changes for the same.
Thanks and Regards,
Shlok Kyal
Attachments:
[application/octet-stream] v12-0001-Add-RESET-clause-to-Alter-Publication-which-will.patch (20.5K, 2-v12-0001-Add-RESET-clause-to-Alter-Publication-which-will.patch)
download | inline diff:
From 9be85f8ffa1440a5d5c39fcd3df2672ca8e6d3dc Mon Sep 17 00:00:00 2001
From: Shlok Kyal <[email protected]>
Date: Wed, 11 Jun 2025 11:41:18 +0530
Subject: [PATCH v12 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 | 35 +++++--
src/backend/commands/publicationcmds.c | 111 ++++++++++++++++++--
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 | 120 ++++++++++++++++++++++
src/test/regress/sql/publication.sql | 57 ++++++++++
7 files changed, 321 insertions(+), 14 deletions(-)
diff --git a/doc/src/sgml/ref/alter_publication.sgml b/doc/src/sgml/ref/alter_publication.sgml
index d5ea383e8bc..06452af9214 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,32 @@ 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 which includes resetting the publication parameters, setting
+ <literal>ALL TABLES</literal> flag to <literal>false</literal> and
+ dropping all relations and schemas that are associated with 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-all-tables"><literal>FOR ALL TABLES</literal></link>
or <link linkend="sql-createpublication-params-for-tables-in-schema"><literal>FOR TABLES IN SCHEMA</literal></link>
@@ -230,6 +245,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 0b23d94c38e..159dc3781d0 100644
--- a/src/backend/commands/publicationcmds.c
+++ b/src/backend/commands/publicationcmds.c
@@ -49,6 +49,15 @@
#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_GENCOLS PUBLISH_GENCOLS_NONE
+
/*
* Information used to validate the columns in the row filter expression. See
* contain_invalid_rfcolumn_walker for details.
@@ -91,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)
@@ -1187,6 +1196,94 @@ 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 *schemas = NIL;
+ List *rels = NIL;
+ bool nulls[Natts_pg_publication];
+ bool replaces[Natts_pg_publication];
+ Datum values[Natts_pg_publication];
+ ObjectAddress obj;
+ ListCell *lc;
+ Oid prid;
+
+ /* 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();
+ }
+
+ tup = heap_modify_tuple(tup, RelationGetDescr(rel), values, nulls,
+ replaces);
+
+ /* Update the catalog. */
+ CatalogTupleUpdate(rel, &tup->t_self, tup);
+
+ /* Drop the schemas associated with the publication */
+ schemas = GetPublicationSchemas(pubid);
+ PublicationDropSchemas(pubid, schemas, false);
+
+ /* Drop the relations associated with the publication */
+ rels = GetPublicationRelations(pubid, PUBLICATION_PART_ROOT);
+ foreach(lc, rels)
+ {
+ Oid relid = lfirst_oid(lc);
+
+ prid = GetSysCacheOid2(PUBLICATIONRELMAP, Anum_pg_publication_rel_oid,
+ ObjectIdGetDatum(relid),
+ ObjectIdGetDatum(pubid));
+ if (!OidIsValid(prid))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("relation \"%s\" is not part of the publication",
+ get_rel_name(relid))));
+
+ ObjectAddressSet(obj, PublicationRelRelationId, prid);
+ performDeletion(&obj, DROP_CASCADE, 0);
+ }
+}
+
/*
* Add or remove table to/from publication.
*/
@@ -1501,6 +1598,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 0b5652071d1..952e8e103cf 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -10762,6 +10762,8 @@ pub_obj_list: PublicationObjSpec
*
* ALTER PUBLICATION name SET pub_obj [, ...]
*
+ * ALTER PUBLICATION name RESET
+ *
* pub_obj is one of:
*
* TABLE table_name [, ...]
@@ -10808,6 +10810,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 620830feb9d..d59ed5f3fd0 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -2242,7 +2242,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 dd00ab420b8..7280e9836cf 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -4272,6 +4272,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 4de96c04f9d..b2ffe0a8c20 100644
--- a/src/test/regress/expected/publication.out
+++ b/src/test/regress/expected/publication.out
@@ -1923,6 +1923,126 @@ 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 tables associated with the publication are dropped 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 ALL TABLES IN SCHEMA public;
+ERROR: syntax error at or near "ALL"
+LINE 1: ALTER PUBLICATION testpub_reset ADD ALL TABLES IN SCHEMA pub...
+ ^
+-- Verify that schemas associated with the publication are dropped 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
+(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 = '');
+-- 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;
RESET SESSION AUTHORIZATION;
DROP ROLE regress_publication_user, regress_publication_user2;
diff --git a/src/test/regress/sql/publication.sql b/src/test/regress/sql/publication.sql
index 68001de4000..15b2b1cfd28 100644
--- a/src/test/regress/sql/publication.sql
+++ b/src/test/regress/sql/publication.sql
@@ -1222,6 +1222,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 tables associated with the publication are dropped after RESET
+\dRp+ testpub_reset
+ALTER PUBLICATION testpub_reset RESET;
+\dRp+ testpub_reset
+
+ALTER PUBLICATION testpub_reset ADD ALL TABLES IN SCHEMA public;
+
+-- Verify that schemas associated with the publication are dropped 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;
RESET SESSION AUTHORIZATION;
DROP ROLE regress_publication_user, regress_publication_user2;
--
2.34.1
[application/octet-stream] v12-0002-Skip-publishing-the-tables-specified-in-EXCEPT-T.patch (68.8K, 3-v12-0002-Skip-publishing-the-tables-specified-in-EXCEPT-T.patch)
download | inline diff:
From a51aee359bde6c2f8f83bee3f42c40a61984f76d Mon Sep 17 00:00:00 2001
From: Shlok Kyal <[email protected]>
Date: Wed, 11 Jun 2025 19:08:35 +0530
Subject: [PATCH v12 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 | 19 +-
doc/src/sgml/ref/create_publication.sgml | 29 ++-
doc/src/sgml/ref/psql-ref.sgml | 5 +-
src/backend/catalog/pg_publication.c | 68 ++++--
src/backend/commands/publicationcmds.c | 197 ++++++++++++++----
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 | 16 +-
src/bin/pg_dump/pg_dump.c | 56 ++++-
src/bin/pg_dump/pg_dump.h | 1 +
src/bin/pg_dump/pg_dump_sort.c | 7 +
src/bin/pg_dump/t/002_pg_dump.pl | 20 ++
src/bin/psql/describe.c | 62 +++++-
src/bin/psql/tab-complete.in.c | 13 +-
src/include/catalog/pg_publication.h | 7 +-
src/include/catalog/pg_publication_rel.h | 1 +
src/include/commands/publicationcmds.h | 5 +-
src/include/nodes/parsenodes.h | 2 +
src/test/regress/expected/publication.out | 97 ++++++++-
src/test/regress/sql/publication.sql | 47 ++++-
src/test/subscription/meson.build | 1 +
.../t/036_rep_changes_except_table.pl | 83 ++++++++
25 files changed, 689 insertions(+), 134 deletions(-)
create mode 100644 src/test/subscription/t/036_rep_changes_except_table.pl
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index fa86c569dc4..4e37c928b44 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -6568,6 +6568,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 686dd441d02..022c4fd4e2c 100644
--- a/doc/src/sgml/logical-replication.sgml
+++ b/doc/src/sgml/logical-replication.sgml
@@ -2251,10 +2251,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 06452af9214..37e2c84bc10 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>
@@ -89,8 +95,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
@@ -238,6 +245,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 802630f2df1..7fd8872db5f 100644
--- a/doc/src/sgml/ref/create_publication.sgml
+++ b/doc/src/sgml/ref/create_publication.sgml
@@ -22,7 +22,7 @@ PostgreSQL documentation
<refsynopsisdiv>
<synopsis>
CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
- [ 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> [ * ]
</synopsis>
</refsynopsisdiv>
@@ -125,7 +129,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>
@@ -161,6 +167,16 @@ 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>.
+ </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>
@@ -442,6 +458,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 8f7d8758ca0..3cce762fa97 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -2094,8 +2094,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 d6f94db5d99..ec580e3b050 100644
--- a/src/backend/catalog/pg_publication.c
+++ b/src/backend/catalog/pg_publication.c
@@ -351,7 +351,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;
@@ -363,32 +364,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;
@@ -479,6 +492,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)
@@ -746,9 +761,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;
@@ -762,7 +777,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);
@@ -861,13 +877,16 @@ GetAllTablesPublications(void)
* root partitioned tables.
*/
List *
-GetAllTablesPublicationRelations(bool pubviaroot)
+GetAllTablesPublicationRelations(Oid pubid, bool pubviaroot)
{
Relation classRel;
ScanKeyData key[1];
TableScanDesc scan;
HeapTuple tuple;
List *result = NIL;
+ List *exceptlist;
+
+ exceptlist = GetPublicationRelations(pubid, PUBLICATION_PART_ALL);
classRel = table_open(RelationRelationId, AccessShareLock);
@@ -884,7 +903,8 @@ GetAllTablesPublicationRelations(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);
}
@@ -905,7 +925,8 @@ GetAllTablesPublicationRelations(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);
}
@@ -1160,7 +1181,8 @@ pg_get_publication_tables(PG_FUNCTION_ARGS)
* those. Otherwise, get the partitioned table itself.
*/
if (pub_elem->alltables)
- pub_elem_tables = GetAllTablesPublicationRelations(pub_elem->pubviaroot);
+ pub_elem_tables = GetAllTablesPublicationRelations(pub_elem->oid,
+ pub_elem->pubviaroot);
else
{
List *relids,
diff --git a/src/backend/commands/publicationcmds.c b/src/backend/commands/publicationcmds.c
index 159dc3781d0..5194b2fb6e2 100644
--- a/src/backend/commands/publicationcmds.c
+++ b/src/backend/commands/publicationcmds.c
@@ -204,6 +204,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 +283,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 +310,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 +371,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 +395,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;
@@ -921,52 +928,50 @@ 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. */
- CacheInvalidateRelcacheAll();
- }
- else
- {
- ObjectsInPublicationToOids(stmt->pubobjects, pstate, &relations,
- &schemaidlist);
+ 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)
@@ -1246,6 +1251,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)
{
@@ -1284,6 +1310,79 @@ AlterPublicationReset(ParseState *pstate, AlterPublicationStmt *stmt,
}
}
+/*
+ * 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);
+ 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.
*/
@@ -1596,6 +1695,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)
@@ -1808,6 +1921,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);
@@ -1880,6 +1994,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);
@@ -1955,8 +2070,6 @@ PublicationAddTables(Oid pubid, List *rels, bool if_not_exists,
{
ListCell *lc;
- Assert(!stmt || !stmt->for_all_tables);
-
foreach(lc, rels)
{
PublicationRelInfo *pub_rel = (PublicationRelInfo *) lfirst(lc);
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index ea96947d813..8a8268a05d2 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -8624,7 +8624,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 on tables that are part of a publication"),
@@ -18794,7 +18794,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 952e8e103cf..89ac0495ce8 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -446,7 +446,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
transform_element_list transform_type_list
TriggerTransitions TriggerReferencing
vacuum_relation_list opt_vacuum_relation_list
- drop_option_list pub_obj_list
+ drop_option_list pub_obj_list except_pub_obj_list
%type <retclause> returning_clause
%type <node> returning_option
@@ -585,6 +585,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 <keyword> unreserved_keyword type_func_name_keyword
%type <keyword> col_name_keyword reserved_keyword
@@ -10614,7 +10615,7 @@ AlterOwnerStmt: ALTER AGGREGATE aggregate_with_argtypes OWNER TO RoleSpec
*
* CREATE PUBLICATION name [WITH options]
*
- * CREATE PUBLICATION FOR ALL TABLES [WITH options]
+ * CREATE PUBLICATION FOR ALL TABLES [EXCEPT [TABLE] table [, ...]] [WITH options]
*
* CREATE PUBLICATION FOR pub_obj [, ...] [WITH options]
*
@@ -10634,12 +10635,13 @@ CreatePublicationStmt:
n->options = $4;
$$ = (Node *) n;
}
- | CREATE PUBLICATION name FOR ALL TABLES opt_definition
+ | CREATE PUBLICATION name FOR ALL TABLES except_pub_obj_list opt_definition
{
CreatePublicationStmt *n = makeNode(CreatePublicationStmt);
n->pubname = $3;
- n->options = $7;
+ n->options = $8;
+ n->pubobjects = (List *)$7;
n->for_all_tables = true;
$$ = (Node *) n;
}
@@ -10677,6 +10679,7 @@ PublicationObjSpec:
$$->pubtable->relation = $2;
$$->pubtable->columns = $3;
$$->pubtable->whereClause = $4;
+ $$->location = @1;
}
| TABLES IN_P SCHEMA ColId
{
@@ -10752,6 +10755,25 @@ pub_obj_list: PublicationObjSpec
{ $$ = lappend($1, $3); }
;
+ExceptPublicationObjSpec:
+ relation_expr
+ {
+ $$ = makeNode(PublicationObjSpec);
+ $$->pubobjtype = PUBLICATIONOBJ_EXCEPT_TABLE;
+ $$->pubtable = makeNode(PublicationTable);
+ $$->pubtable->except = true;
+ $$->pubtable->relation = $1;
+ $$->location = @1;
+ }
+ ;
+
+except_pub_obj_list: EXCEPT opt_table ExceptPublicationObjSpec
+ { $$ = list_make1($3); }
+ | except_pub_obj_list ',' ExceptPublicationObjSpec
+ { $$ = lappend($1, $3); }
+ | /*EMPTY*/ { $$ = NULL; }
+ ;
+
/*****************************************************************************
*
* ALTER PUBLICATION name SET ( options )
@@ -10764,6 +10786,8 @@ pub_obj_list: PublicationObjSpec
*
* ALTER PUBLICATION name RESET
*
+ * ALTER PUBLICATION name ADD ALL TABLES EXCEPT [TABLE] table_name [, ...]
+ *
* pub_obj is one of:
*
* TABLE table_name [, ...]
@@ -10790,6 +10814,15 @@ AlterPublicationStmt:
n->action = AP_AddObjects;
$$ = (Node *) n;
}
+ | ALTER PUBLICATION name ADD_P ALL TABLES except_pub_obj_list
+ {
+ AlterPublicationStmt *n = makeNode(AlterPublicationStmt);
+ n->pubname = $3;
+ n->pubobjects = $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 693a766e6d7..5512b4cba7f 100644
--- a/src/backend/replication/pgoutput/pgoutput.c
+++ b/src/backend/replication/pgoutput/pgoutput.c
@@ -2063,7 +2063,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
@@ -2174,22 +2175,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;
@@ -2208,7 +2193,8 @@ get_rel_sync_entry(PGOutputData *data, Relation relation)
ancestor = GetTopMostAncestorInPublication(pub->oid,
ancestors,
- &level);
+ &level,
+ pub->alltables);
if (ancestor != InvalidOid)
{
@@ -2223,6 +2209,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;
}
@@ -2301,6 +2289,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 559ba9cdb2c..bffdab2ab63 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,7 +5833,7 @@ 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));
@@ -5845,14 +5847,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 +5890,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 +5908,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 37432e66efd..92db5ca8d97 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -183,6 +183,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 */
@@ -4510,8 +4512,34 @@ dumpPublication(Archive *fout, const PublicationInfo *pubinfo)
qpubname);
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));
+ }
+ }
+ }
+
+ first = true;
appendPQExpBufferStr(query, " WITH (publish = '");
if (pubinfo->pubinsert)
{
@@ -4677,6 +4705,7 @@ getPublicationTables(Archive *fout, TableInfo tblinfo[], int numTables)
int i_prrelid;
int i_prrelqual;
int i_prattrs;
+ int i_prexcept;
int i,
j,
ntups;
@@ -4688,8 +4717,17 @@ getPublicationTables(Archive *fout, TableInfo tblinfo[], int numTables)
/* Collect all publication membership info. */
if (fout->remoteVersion >= 150000)
+ {
+ appendPQExpBufferStr(query,
+ "SELECT tableoid, oid, prpubid, prrelid,\n");
+
+ /* FIXME: 180000 should be changed to 190000 later for PG19. */
+ if (fout->remoteVersion >= 180000)
+ 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"
@@ -4700,6 +4738,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, "
@@ -4715,6 +4754,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));
@@ -4726,6 +4766,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
@@ -4739,7 +4780,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));
@@ -4780,6 +4825,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++;
}
@@ -11542,6 +11590,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;
@@ -19781,6 +19832,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 7417eab6aef..096f29346d8 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 0b0977788f1..56d6740b9ea 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,
@@ -1498,6 +1500,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 386e21e0c59..152fd7ff086 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -3273,6 +3273,26 @@ my %tests = (
like => { %full_runs, section_post_data => 1, },
},
+ 'CREATE PUBLICATION pub6' => {
+ create_order => 50,
+ create_sql =>
+ 'CREATE PUBLICATION pub6 FOR ALL TABLES EXCEPT dump_test.test_table;',
+ regexp => qr/^
+ \QCREATE PUBLICATION pub6 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 pub7' => {
+ create_order => 50,
+ create_sql =>
+ 'CREATE PUBLICATION pub7 FOR ALL TABLES EXCEPT TABLE dump_test.test_table, dump_test.test_second_table;',
+ regexp => qr/^
+ \QCREATE PUBLICATION pub7 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
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 24e0100c9f0..2f61be9c17e 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -3038,17 +3038,36 @@ 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);
+
+ /* FIXME: 180000 should be changed to 190000 later for PG19. */
+ if (pset.sversion >= 180000)
+ 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);
+
+ /* FIXME: 180000 should be changed to 190000 later for PG19. */
+ if (pset.sversion >= 180000)
+ 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
{
@@ -6692,8 +6711,13 @@ 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);
+
+ /* FIXME: 180000 should be changed to 190000 later for PG19. */
+ if (pset.sversion >= 180000)
+ appendPQExpBuffer(&buf, " AND NOT pr.prexcept\n");
+
+ appendPQExpBuffer(&buf, "ORDER BY 1,2");
if (!addFooterToPublicationDesc(&buf, _("Tables:"), false, &cont))
goto error_return;
@@ -6711,6 +6735,24 @@ describePublications(const char *pattern)
goto error_return;
}
}
+ else
+ {
+ /* FIXME: 180000 should be changed to 190000 later for PG19. */
+ if (pset.sversion >= 180000)
+ {
+ /* 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 d59ed5f3fd0..bf3b0eb31c1 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -2245,11 +2245,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);
/*
@@ -3536,7 +3541,7 @@ match_previous_words(int pattern_id,
else if (Matches("CREATE", "PUBLICATION", MatchAny, "FOR", "ALL"))
COMPLETE_WITH("TABLES");
else if (Matches("CREATE", "PUBLICATION", MatchAny, "FOR", "ALL", "TABLES"))
- COMPLETE_WITH("WITH (");
+ COMPLETE_WITH("EXCEPT TABLE", "WITH (");
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, ','))
diff --git a/src/include/catalog/pg_publication.h b/src/include/catalog/pg_publication.h
index 48c7d1a8615..33b771990bd 100644
--- a/src/include/catalog/pg_publication.h
+++ b/src/include/catalog/pg_publication.h
@@ -139,11 +139,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 GetRelationPublications(),
@@ -163,7 +164,7 @@ typedef enum PublicationPartOpt
extern List *GetPublicationRelations(Oid pubid, PublicationPartOpt pub_partopt);
extern List *GetAllTablesPublications(void);
-extern List *GetAllTablesPublicationRelations(bool pubviaroot);
+extern List *GetAllTablesPublicationRelations(Oid pubid, bool pubviaroot);
extern List *GetPublicationSchemas(Oid pubid);
extern List *GetSchemaPublications(Oid schemaid);
extern List *GetSchemaPublicationRelations(Oid schemaid,
@@ -174,7 +175,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 7280e9836cf..61a0b2ccf38 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -4235,6 +4235,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;
/*
@@ -4243,6 +4244,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 */
diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out
index b2ffe0a8c20..5d025328704 100644
--- a/src/test/regress/expected/publication.out
+++ b/src/test/regress/expected/publication.out
@@ -209,13 +209,37 @@ Not-null constraints:
regress_publication_user | t | 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 | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | t | 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 | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | t | 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
@@ -234,8 +258,25 @@ Tables:
Tables:
"public.testpub_tbl3"
+\dRp+ testpub5
+ Publication testpub5
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | t | t | t | t | t | none | f
+Except tables:
+ "public.testpub_tbl3"
+ "public.testpub_tbl3a"
+
+\dRp+ testpub6
+ Publication testpub6
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | t | 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 partitioned tables
SET client_min_messages = 'ERROR';
CREATE PUBLICATION testpub_forparted;
@@ -1926,9 +1967,15 @@ 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
@@ -1945,7 +1992,24 @@ ALTER PUBLICATION testpub_reset RESET;
regress_publication_user | 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 | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | t | 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 tables associated with the publication are dropped after RESET
\dRp+ testpub_reset
Publication testpub_reset
@@ -1963,17 +2027,20 @@ ALTER PUBLICATION testpub_reset RESET;
regress_publication_user | f | t | t | t | t | none | f
(1 row)
-ALTER PUBLICATION testpub_reset ADD ALL TABLES IN SCHEMA public;
-ERROR: syntax error at or near "ALL"
-LINE 1: ALTER PUBLICATION testpub_reset ADD ALL TABLES IN SCHEMA pub...
- ^
+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 schemas associated with the publication are dropped 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
-(1 row)
+Tables from schemas:
+ "public"
ALTER PUBLICATION testpub_reset RESET;
\dRp+ testpub_reset
@@ -1984,6 +2051,12 @@ ALTER PUBLICATION testpub_reset RESET;
(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
@@ -2001,6 +2074,12 @@ ALTER PUBLICATION testpub_reset RESET;
(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
@@ -2039,9 +2118,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;
RESET SESSION AUTHORIZATION;
diff --git a/src/test/regress/sql/publication.sql b/src/test/regress/sql/publication.sql
index 15b2b1cfd28..af31a2214ca 100644
--- a/src/test/regress/sql/publication.sql
+++ b/src/test/regress/sql/publication.sql
@@ -104,20 +104,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 partitioned tables
SET client_min_messages = 'ERROR';
@@ -1225,23 +1238,39 @@ 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 tables associated with the publication are dropped after RESET
\dRp+ testpub_reset
ALTER PUBLICATION testpub_reset RESET;
\dRp+ testpub_reset
-ALTER PUBLICATION testpub_reset ADD ALL TABLES IN SCHEMA public;
+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 schemas associated with the publication are dropped after RESET
\dRp+ testpub_reset
@@ -1250,6 +1279,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;
@@ -1257,6 +1290,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;
@@ -1273,10 +1310,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 586ffba434e..e8a117f3421 100644
--- a/src/test/subscription/meson.build
+++ b/src/test/subscription/meson.build
@@ -42,6 +42,7 @@ tests += {
't/033_run_as_table_owner.pl',
't/034_temporal.pl',
't/035_conflicts.pl',
+ 't/036_rep_changes_except_table.pl',
't/100_bugs.pl',
],
},
diff --git a/src/test/subscription/t/036_rep_changes_except_table.pl b/src/test/subscription/t/036_rep_changes_except_table.pl
new file mode 100644
index 00000000000..1d115283809
--- /dev/null
+++ b/src/test/subscription/t/036_rep_changes_except_table.pl
@@ -0,0 +1,83 @@
+
+# Copyright (c) 2021-2022, 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', "CREATE SCHEMA sch1");
+$node_publisher->safe_psql('postgres',
+ "CREATE TABLE sch1.tab1 AS SELECT generate_series(1,10) AS a");
+$node_publisher->safe_psql('postgres', "CREATE TABLE public.tab1(a int)");
+
+# Create schemas and tables on subscriber
+$node_subscriber->safe_psql('postgres', "CREATE SCHEMA sch1");
+$node_subscriber->safe_psql('postgres', "CREATE TABLE sch1.tab1 (a int)");
+$node_subscriber->safe_psql('postgres', "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',
+ "ALTER PUBLICATION tap_pub_schema RESET");
+$node_publisher->safe_psql('postgres',
+ "ALTER PUBLICATION tap_pub_schema ADD ALL TABLES EXCEPT TABLE sch1.tab1, public.tab1"
+);
+$node_publisher->safe_psql('postgres',
+ "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');
+
+$node_subscriber->stop('fast');
+$node_publisher->stop('fast');
+
+done_testing();
--
2.34.1
[application/octet-stream] v12-0003-Skip-publishing-the-columns-specified-in-FOR-TAB.patch (45.5K, 4-v12-0003-Skip-publishing-the-columns-specified-in-FOR-TAB.patch)
download | inline diff:
From 68403578523c96943c3616dbbdb2a82ad8863244 Mon Sep 17 00:00:00 2001
From: Shlok Kyal <[email protected]>
Date: Tue, 17 Jun 2025 12:12:24 +0530
Subject: [PATCH v12 3/3] Skip publishing the columns specified in FOR TABLE
EXCEPT
This patch introduces syntax to exclude columns of tables from
publication. Syntax: FOR TABLE tabname EXCEPT (column_list)
It can be used with CREATE/ ALTER PUBLICATION. Eg:
CREATE PUBLICATION pubname FOR TABLE tabname EXCEPT (exclude_column_list)
ALTER PUBLICATION pubname ADD TABLE tabname EXCEPT (exclude_column_list)
---
doc/src/sgml/catalogs.sgml | 14 ++
doc/src/sgml/ref/alter_publication.sgml | 10 +-
doc/src/sgml/ref/create_publication.sgml | 18 ++-
src/backend/catalog/pg_publication.c | 135 +++++++++++++++++-
src/backend/commands/publicationcmds.c | 73 +++++++++-
src/backend/parser/gram.y | 60 ++++++++
src/backend/replication/pgoutput/pgoutput.c | 47 +++++-
src/bin/pg_dump/pg_dump.c | 39 ++++-
src/bin/pg_dump/pg_dump.h | 1 +
src/include/catalog/pg_publication.h | 9 +-
src/include/catalog/pg_publication_rel.h | 1 +
src/include/nodes/parsenodes.h | 1 +
src/test/regress/expected/publication.out | 65 +++++++++
src/test/regress/sql/publication.sql | 45 ++++++
.../t/036_rep_changes_except_table.pl | 60 +++++++-
15 files changed, 560 insertions(+), 18 deletions(-)
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 4e37c928b44..544998a1725 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -6589,6 +6589,20 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l
A null value indicates that all columns are published.
</para></entry>
</row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>prexcludeattrs</structfield> <type>int2vector</type>
+ (references <link linkend="catalog-pg-attribute"><structname>pg_attribute</structname></link>.<structfield>attnum</structfield>)
+ </para>
+ <para>
+ This is an array of values that indicates which table columns are
+ excluded from the publication. For example, a value of
+ <literal>1 3</literal> would mean that the columns except the first and
+ the third columns are published.
+ A null value indicates that no columns are excluded from being published.
+ </para></entry>
+ </row>
</tbody>
</tgroup>
</table>
diff --git a/doc/src/sgml/ref/alter_publication.sgml b/doc/src/sgml/ref/alter_publication.sgml
index 37e2c84bc10..70b04fc7320 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> [ * ] { [ [ ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) ] | [ EXCEPT ( <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>
@@ -260,6 +260,14 @@ ALTER PUBLICATION production_publication ADD ALL TABLES EXCEPT users, department
<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 7fd8872db5f..bb44a20b28d 100644
--- a/doc/src/sgml/ref/create_publication.sgml
+++ b/doc/src/sgml/ref/create_publication.sgml
@@ -28,7 +28,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> [ * ] { [ [ ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) ] | [ EXCEPT ( <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>
@@ -103,6 +103,14 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
lists.
</para>
+ <para>
+ When a column list is specified with EXCEPT, the named columns are not
+ replicated. The excluded column list cannot contain generated columns. The
+ column list and excluded column list cannot be specified together.
+ 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,
@@ -474,6 +482,14 @@ CREATE PUBLICATION mypublication FOR ALL TABLES EXCEPT users, departments;
<programlisting>
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 columns <structname>security_pin</structname>:
+<programlisting>
+CREATE PUBLICATION users_safe FOR TABLE users EXCEPT (security_pin);
+</programlisting>
+ </para>
</refsect1>
<refsect1>
diff --git a/src/backend/catalog/pg_publication.c b/src/backend/catalog/pg_publication.c
index ec580e3b050..8fd9ac84451 100644
--- a/src/backend/catalog/pg_publication.c
+++ b/src/backend/catalog/pg_publication.c
@@ -302,6 +302,53 @@ check_and_fetch_column_list(Publication *pub, Oid relid, MemoryContext mcxt,
return found;
}
+/*
+ * Returns true if the relation has exluded column list associated with the
+ * publication, false otherwise.
+ *
+ * If a exclude 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).
+ */
+
+bool
+check_and_fetch_exclude_column_list(Publication *pub, Oid relid, MemoryContext mcxt,
+ Bitmapset **cols)
+{
+ HeapTuple cftuple;
+ bool found = false;
+
+ if (pub->alltables)
+ return false;
+
+ cftuple = SearchSysCache2(PUBLICATIONRELMAP,
+ ObjectIdGetDatum(relid),
+ ObjectIdGetDatum(pub->oid));
+ if (HeapTupleIsValid(cftuple))
+ {
+ Datum cfdatum;
+ bool isnull;
+
+ /* Lookup the column list attribute. */
+ cfdatum = SysCacheGetAttr(PUBLICATIONRELMAP, cftuple,
+ Anum_pg_publication_rel_prexcludeattrs, &isnull);
+
+ /* Was a column list found? */
+ if (!isnull)
+ {
+ /* Build the column list bitmap in the given memory context. */
+ if (cols)
+ *cols = pub_collist_to_bitmapset(*cols, cfdatum, mcxt);
+
+ found = true;
+ }
+
+ ReleaseSysCache(cftuple);
+ }
+
+ return found;
+}
+
/*
* Gets the relations based on the publication partition option for a specified
* relation.
@@ -449,6 +496,7 @@ publication_add_relation(Oid pubid, PublicationRelInfo *pri,
Oid relid = RelationGetRelid(targetrel);
Oid pubreloid;
Bitmapset *attnums;
+ Bitmapset *excludeattnums;
Publication *pub = GetPublication(pubid);
ObjectAddress myself,
referenced;
@@ -481,6 +529,13 @@ publication_add_relation(Oid pubid, PublicationRelInfo *pri,
/* Validate and translate column names into a Bitmapset of attnums. */
attnums = pub_collist_validate(pri->relation, pri->columns);
+ /*
+ * Validate and translate excluded column names into a Bitmapset of
+ * attnums.
+ */
+ excludeattnums = pub_exclude_collist_validate(pri->relation,
+ pri->exclude_columns);
+
/* Form a tuple. */
memset(values, 0, sizeof(values));
memset(nulls, false, sizeof(nulls));
@@ -507,6 +562,11 @@ publication_add_relation(Oid pubid, PublicationRelInfo *pri,
else
nulls[Anum_pg_publication_rel_prattrs - 1] = true;
+ if (pri->exclude_columns)
+ values[Anum_pg_publication_rel_prexcludeattrs - 1] = PointerGetDatum(attnumstoint2vector(excludeattnums));
+ else
+ nulls[Anum_pg_publication_rel_prexcludeattrs - 1] = true;
+
tup = heap_form_tuple(RelationGetDescr(rel), values, nulls);
/* Insert tuple into catalog. */
@@ -609,6 +669,58 @@ pub_collist_validate(Relation targetrel, List *columns)
return set;
}
+/*
+ * pub_exclude_collist_validate
+ * Process and validate the 'excluded columns' list and ensure the columns
+ * are all valid to exclude from publication. Checks for and raises an
+ * ERROR for any unknown columns, system columns, duplicate columns, or
+ * generated columns.
+ *
+ * Looks up each column's attnum and returns a 0-based Bitmapset of the
+ * corresponding attnums.
+ */
+Bitmapset *
+pub_exclude_collist_validate(Relation targetrel, List *exclude_columns)
+{
+ Bitmapset *set = NULL;
+ ListCell *lc;
+ TupleDesc tupdesc = RelationGetDescr(targetrel);
+
+ foreach(lc, exclude_columns)
+ {
+ char *colname = strVal(lfirst(lc));
+ AttrNumber attnum = get_attnum(RelationGetRelid(targetrel), colname);
+
+ if (attnum == InvalidAttrNumber)
+ ereport(ERROR,
+ errcode(ERRCODE_UNDEFINED_COLUMN),
+ errmsg("column \"%s\" of relation \"%s\" does not exist",
+ colname, RelationGetRelationName(targetrel)));
+
+ if (!AttrNumberIsForUserDefinedAttr(attnum))
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_COLUMN_REFERENCE),
+ errmsg("cannot use system column \"%s\" in publication except column list",
+ colname));
+
+ if (TupleDescAttr(tupdesc, attnum - 1)->attgenerated)
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_COLUMN_REFERENCE),
+ errmsg("cannot use generated column \"%s\" in publication except column list",
+ colname));
+
+ if (bms_is_member(attnum, set))
+ ereport(ERROR,
+ errcode(ERRCODE_DUPLICATE_OBJECT),
+ errmsg("duplicate column \"%s\" in publication except column list",
+ colname));
+
+ set = bms_add_member(set, attnum);
+ }
+
+ return set;
+}
+
/*
* Transform a column list (represented by an array Datum) to a bitmapset.
*
@@ -646,10 +758,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 excludecols 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 *excludecols)
{
Bitmapset *result = NULL;
TupleDesc desc = RelationGetDescr(relation);
@@ -672,6 +786,9 @@ pub_form_cols_map(Relation relation, PublishGencolsType include_gencols_type)
continue;
}
+ if (excludecols && bms_is_member(att->attnum, excludecols))
+ continue;
+
result = bms_add_member(result, att->attnum);
}
@@ -1263,6 +1380,9 @@ 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};
+ Datum excludeattnums_datum;
+ Bitmapset *excludeattnums = NULL;
+ bool isnull;
/*
* Form tuple with appropriate data.
@@ -1296,6 +1416,13 @@ pg_get_publication_tables(PG_FUNCTION_ARGS)
values[3] = SysCacheGetAttr(PUBLICATIONRELMAP, pubtuple,
Anum_pg_publication_rel_prqual,
&(nulls[3]));
+
+ /* get the excluded column list */
+ excludeattnums_datum = SysCacheGetAttr(PUBLICATIONRELMAP, pubtuple,
+ Anum_pg_publication_rel_prexcludeattrs,
+ &isnull);
+ if (!isnull)
+ excludeattnums = pub_collist_to_bitmapset(NULL, excludeattnums_datum, NULL);
}
else
{
@@ -1335,6 +1462,10 @@ pg_get_publication_tables(PG_FUNCTION_ARGS)
continue;
}
+ /* Skip columns that are part of excluded column list */
+ if (excludeattnums && bms_is_member(att->attnum, excludeattnums))
+ continue;
+
attnums[nattnums++] = att->attnum;
}
diff --git a/src/backend/commands/publicationcmds.c b/src/backend/commands/publicationcmds.c
index 5194b2fb6e2..e850c2345ea 100644
--- a/src/backend/commands/publicationcmds.c
+++ b/src/backend/commands/publicationcmds.c
@@ -358,7 +358,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
+ * by the column list and not part of excluded column list. If any column is
+ * missing or is part of exclude column list, *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
@@ -378,6 +379,7 @@ pub_contains_invalid_column(Oid pubid, Relation relation, List *ancestors,
Oid publish_as_relid = RelationGetRelid(relation);
Bitmapset *idattrs;
Bitmapset *columns = NULL;
+ Bitmapset *exclude_columns = NULL;
TupleDesc desc = RelationGetDescr(relation);
Publication *pub;
int x;
@@ -405,11 +407,15 @@ 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_exclude_column_list(pub, publish_as_relid, NULL, &exclude_columns);
if (relation->rd_rel->relreplident == REPLICA_IDENTITY_FULL)
{
- /* With REPLICA IDENTITY FULL, no column list is allowed. */
- *invalid_column_list = (columns != NULL);
+ /*
+ * With REPLICA IDENTITY FULL, no column list and no excluded column
+ * list is allowed.
+ */
+ *invalid_column_list = (columns != NULL || exclude_columns != NULL);
/*
* As we don't allow a column list with REPLICA IDENTITY FULL, the
@@ -471,6 +477,16 @@ pub_contains_invalid_column(Oid pubid, Relation relation, List *ancestors,
break;
}
+ /*
+ * If REPLICA IDENTITY should not contain columns which are
+ * excluded from the publication.
+ */
+ if (exclude_columns && bms_is_member(att->attnum, exclude_columns))
+ {
+ *invalid_column_list = true;
+ break;
+ }
+
/* Skip validating the column list since it is not defined */
continue;
}
@@ -798,7 +814,7 @@ CheckPubRelationColumnList(char *pubname, List *tables,
{
PublicationRelInfo *pri = (PublicationRelInfo *) lfirst(lc);
- if (pri->columns == NIL)
+ if (pri->columns == NIL && pri->exclude_columns == NIL)
continue;
/*
@@ -1043,6 +1059,7 @@ AlterPublicationOptions(ParseState *pstate, AlterPublicationStmt *stmt,
char *relname;
bool has_rowfilter;
bool has_collist;
+ bool has_exclude_collist;
/*
* Beware: we don't have lock on the relations, so cope silently
@@ -1056,7 +1073,9 @@ AlterPublicationOptions(ParseState *pstate, AlterPublicationStmt *stmt,
continue;
has_rowfilter = !heap_attisnull(rftuple, Anum_pg_publication_rel_prqual, NULL);
has_collist = !heap_attisnull(rftuple, Anum_pg_publication_rel_prattrs, NULL);
- if (!has_rowfilter && !has_collist)
+ has_exclude_collist = !heap_attisnull(rftuple, Anum_pg_publication_rel_prexcludeattrs, NULL);
+
+ if (!has_rowfilter && !has_collist && !has_exclude_collist)
{
ReleaseSysCache(rftuple);
continue;
@@ -1083,6 +1102,14 @@ AlterPublicationOptions(ParseState *pstate, AlterPublicationStmt *stmt,
stmt->pubname),
errdetail("The publication contains a WHERE clause for partitioned table \"%s\", which is not allowed when \"%s\" is false.",
relname, "publish_via_partition_root")));
+ if (has_exclude_collist)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("cannot set parameter \"%s\" to false for publication \"%s\"",
+ "publish_via_partition_root",
+ stmt->pubname),
+ errdetail("The publication contains a except column list for partitioned table \"%s\", which is not allowed when \"%s\" is false.",
+ relname, "publish_via_partition_root")));
Assert(has_collist);
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
@@ -1443,6 +1470,7 @@ AlterPublicationTables(AlterPublicationStmt *stmt, HeapTuple tup,
HeapTuple rftuple;
Node *oldrelwhereclause = NULL;
Bitmapset *oldcolumns = NULL;
+ Bitmapset *oldexcludecolumns = NULL;
/* look up the cache for the old relmap */
rftuple = SearchSysCache2(PUBLICATIONRELMAP,
@@ -1458,6 +1486,7 @@ AlterPublicationTables(AlterPublicationStmt *stmt, HeapTuple tup,
bool isnull = true;
Datum whereClauseDatum;
Datum columnListDatum;
+ Datum excludeColumnListDatum;
/* Load the WHERE clause for this table. */
whereClauseDatum = SysCacheGetAttr(PUBLICATIONRELMAP, rftuple,
@@ -1474,6 +1503,14 @@ AlterPublicationTables(AlterPublicationStmt *stmt, HeapTuple tup,
if (!isnull)
oldcolumns = pub_collist_to_bitmapset(NULL, columnListDatum, NULL);
+ /* Transform the int2vector exclude column list to a bitmap. */
+ excludeColumnListDatum = SysCacheGetAttr(PUBLICATIONRELMAP, rftuple,
+ Anum_pg_publication_rel_prexcludeattrs,
+ &isnull);
+
+ if (!isnull)
+ oldexcludecolumns = pub_collist_to_bitmapset(NULL, excludeColumnListDatum, NULL);
+
ReleaseSysCache(rftuple);
}
@@ -1482,6 +1519,7 @@ AlterPublicationTables(AlterPublicationStmt *stmt, HeapTuple tup,
PublicationRelInfo *newpubrel;
Oid newrelid;
Bitmapset *newcolumns = NULL;
+ Bitmapset *newexcludecolumns = NULL;
newpubrel = (PublicationRelInfo *) lfirst(newlc);
newrelid = RelationGetRelid(newpubrel->relation);
@@ -1495,6 +1533,9 @@ AlterPublicationTables(AlterPublicationStmt *stmt, HeapTuple tup,
newcolumns = pub_collist_validate(newpubrel->relation,
newpubrel->columns);
+ newexcludecolumns = pub_collist_validate(newpubrel->relation,
+ newpubrel->exclude_columns);
+
/*
* Check if any of the new set of relations matches with the
* existing relations in the publication. Additionally, if the
@@ -1505,7 +1546,8 @@ AlterPublicationTables(AlterPublicationStmt *stmt, HeapTuple tup,
if (newrelid == oldrelid)
{
if (equal(oldrelwhereclause, newpubrel->whereClause) &&
- bms_equal(oldcolumns, newcolumns))
+ bms_equal(oldcolumns, newcolumns) &&
+ bms_equal(oldexcludecolumns, newexcludecolumns))
{
found = true;
break;
@@ -1522,6 +1564,7 @@ AlterPublicationTables(AlterPublicationStmt *stmt, HeapTuple tup,
oldrel = palloc(sizeof(PublicationRelInfo));
oldrel->whereClause = NULL;
oldrel->columns = NIL;
+ oldrel->exclude_columns = NIL;
oldrel->relation = table_open(oldrelid,
ShareUpdateExclusiveLock);
delrels = lappend(delrels, oldrel);
@@ -1596,6 +1639,17 @@ AlterPublicationSchemas(AlterPublicationStmt *stmt,
stmt->pubname),
errdetail("Schemas cannot be added if any tables that specify a column list are already part of the publication."));
+ /*
+ * Disallow adding schema if exclude column list is already part
+ * of the publication. See CheckPubRelationColumnList.
+ */
+ if (!heap_attisnull(coltuple, Anum_pg_publication_rel_prexcludeattrs, NULL))
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("cannot add schema to publication \"%s\"",
+ stmt->pubname),
+ errdetail("Schemas cannot be added if any tables that specify an except column list are already part of the publication."));
+
ReleaseSysCache(coltuple);
}
@@ -1922,6 +1976,7 @@ OpenTableList(List *tables)
pub_rel->whereClause = t->whereClause;
pub_rel->columns = t->columns;
pub_rel->except = t->except;
+ pub_rel->exclude_columns = t->exclude_columns;
rels = lappend(rels, pub_rel);
relids = lappend_oid(relids, myrelid);
@@ -1995,6 +2050,7 @@ OpenTableList(List *tables)
/* child inherits column list from parent */
pub_rel->columns = t->columns;
pub_rel->except = t->except;
+ pub_rel->exclude_columns = t->exclude_columns;
rels = lappend(rels, pub_rel);
relids = lappend_oid(relids, childrelid);
@@ -2114,6 +2170,11 @@ PublicationDropTables(Oid pubid, List *rels, bool missing_ok)
errcode(ERRCODE_SYNTAX_ERROR),
errmsg("column list must not be specified in ALTER PUBLICATION ... DROP"));
+ if (pubrel->exclude_columns)
+ ereport(ERROR,
+ errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("except column list must not be specified in ALTER PUBLICATION ... DROP"));
+
prid = GetSysCacheOid2(PUBLICATIONRELMAP, Anum_pg_publication_rel_oid,
ObjectIdGetDatum(relid),
ObjectIdGetDatum(pubid));
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 89ac0495ce8..1be4298bce7 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -447,6 +447,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 except_pub_obj_list
+ opt_exclude_column_list
%type <retclause> returning_clause
%type <node> returning_option
@@ -4415,6 +4416,10 @@ opt_column_list:
| /*EMPTY*/ { $$ = NIL; }
;
+opt_exclude_column_list:
+ '(' columnList ')' { $$ = $2; }
+ ;
+
columnList:
columnElem { $$ = list_make1($1); }
| columnList ',' columnElem { $$ = lappend($1, $3); }
@@ -10681,6 +10686,15 @@ PublicationObjSpec:
$$->pubtable->whereClause = $4;
$$->location = @1;
}
+ | TABLE relation_expr EXCEPT opt_exclude_column_list OptWhereClause
+ {
+ $$ = makeNode(PublicationObjSpec);
+ $$->pubobjtype = PUBLICATIONOBJ_TABLE;
+ $$->pubtable = makeNode(PublicationTable);
+ $$->pubtable->relation = $2;
+ $$->pubtable->exclude_columns = $4;
+ $$->pubtable->whereClause = $5;
+ }
| TABLES IN_P SCHEMA ColId
{
$$ = makeNode(PublicationObjSpec);
@@ -10721,6 +10735,33 @@ PublicationObjSpec:
}
$$->location = @1;
}
+ | ColId EXCEPT opt_exclude_column_list OptWhereClause
+ {
+ $$ = makeNode(PublicationObjSpec);
+ $$->pubobjtype = PUBLICATIONOBJ_CONTINUATION;
+ /*
+ * If either a row filter or exclude column list is
+ * specified, create a PublicationTable object.
+ */
+ if ($3 || $4)
+ {
+ /*
+ * The OptWhereClause must be stored here but it is
+ * valid only for tables. For non-table objects, an
+ * error will be thrown later via
+ * preprocess_pubobj_list().
+ */
+ $$->pubtable = makeNode(PublicationTable);
+ $$->pubtable->relation = makeRangeVar(NULL, $1, @1);
+ $$->pubtable->exclude_columns = $3;
+ $$->pubtable->whereClause = $4;
+ }
+ else
+ {
+ $$->name = $1;
+ }
+ $$->location = @1;
+ }
| ColId indirection opt_column_list OptWhereClause
{
$$ = makeNode(PublicationObjSpec);
@@ -10731,6 +10772,16 @@ PublicationObjSpec:
$$->pubtable->whereClause = $4;
$$->location = @1;
}
+ | ColId indirection EXCEPT opt_exclude_column_list OptWhereClause
+ {
+ $$ = makeNode(PublicationObjSpec);
+ $$->pubobjtype = PUBLICATIONOBJ_CONTINUATION;
+ $$->pubtable = makeNode(PublicationTable);
+ $$->pubtable->relation = makeRangeVarFromQualifiedName($1, $2, @1, yyscanner);
+ $$->pubtable->exclude_columns = $4;
+ $$->pubtable->whereClause = $5;
+ $$->location = @1;
+ }
/* grammar like tablename * , ONLY tablename, ONLY ( tablename ) */
| extended_relation_expr opt_column_list OptWhereClause
{
@@ -10741,6 +10792,15 @@ PublicationObjSpec:
$$->pubtable->columns = $2;
$$->pubtable->whereClause = $3;
}
+ | extended_relation_expr EXCEPT opt_exclude_column_list OptWhereClause
+ {
+ $$ = makeNode(PublicationObjSpec);
+ $$->pubobjtype = PUBLICATIONOBJ_CONTINUATION;
+ $$->pubtable = makeNode(PublicationTable);
+ $$->pubtable->relation = $1;
+ $$->pubtable->exclude_columns = $3;
+ $$->pubtable->whereClause = $4;
+ }
| CURRENT_SCHEMA
{
$$ = makeNode(PublicationObjSpec);
diff --git a/src/backend/replication/pgoutput/pgoutput.c b/src/backend/replication/pgoutput/pgoutput.c
index 5512b4cba7f..f36c361abd5 100644
--- a/src/backend/replication/pgoutput/pgoutput.c
+++ b/src/backend/replication/pgoutput/pgoutput.c
@@ -185,6 +185,9 @@ typedef struct RelationSyncEntry
* row filter expressions, column list, etc.
*/
MemoryContext entry_cxt;
+
+ /* Indicate if no column is included in the publication */
+ bool no_cols_published;
} RelationSyncEntry;
/*
@@ -1099,6 +1102,7 @@ pgoutput_column_list_init(PGOutputData *data, List *publications,
bool first = true;
Relation relation = RelationIdGetRelation(entry->publish_as_relid);
bool found_pub_collist = false;
+ bool found_pub_exclude_collist = false;
Bitmapset *relcols = NULL;
pgoutput_ensure_entry_cxt(data, entry);
@@ -1120,12 +1124,32 @@ pgoutput_column_list_init(PGOutputData *data, List *publications,
{
Publication *pub = lfirst(lc);
Bitmapset *cols = NULL;
+ Bitmapset *excludecols = NULL;
/* 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);
+ /* Retrieve the bitmap of exclude columns for the publication. */
+ found_pub_exclude_collist |= check_and_fetch_exclude_column_list(pub,
+ entry->publish_as_relid,
+ entry->entry_cxt, &excludecols);
+
+ /*
+ * cols and exclude cols can't appear together. Syntax for it is not
+ * supported. If column list is not present check for excluded column
+ * list and construct a corresponding column list.
+ */
+ if (!cols && found_pub_exclude_collist)
+ {
+ MemoryContext oldcxt = MemoryContextSwitchTo(entry->entry_cxt);
+
+ cols = pub_form_cols_map(relation,
+ entry->include_gencols_type, excludecols);
+ MemoryContextSwitchTo(oldcxt);
+ }
+
/*
* For non-column list publications — e.g. TABLE (without a column
* list), ALL TABLES, or ALL TABLES IN SCHEMA, we consider all columns
@@ -1144,7 +1168,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);
}
@@ -1155,8 +1179,11 @@ pgoutput_column_list_init(PGOutputData *data, List *publications,
{
entry->columns = cols;
first = false;
+
+ if (excludecols && !cols)
+ entry->no_cols_published = true;
}
- else if (!bms_equal(entry->columns, cols))
+ else if ((entry->no_cols_published && cols) || !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",
@@ -1165,10 +1192,11 @@ pgoutput_column_list_init(PGOutputData *data, List *publications,
} /* loop all subscribed publications */
/*
- * If no column list publications exist, columns to be published will be
- * computed later according to the 'publish_generated_columns' parameter.
+ * If no column list or excluded column list publications exist, columns
+ * to be published will be computed later according to the
+ * 'publish_generated_columns' parameter.
*/
- if (!found_pub_collist)
+ if (!found_pub_collist && !found_pub_exclude_collist)
entry->columns = NULL;
RelationClose(relation);
@@ -1480,6 +1508,13 @@ pgoutput_change(LogicalDecodingContext *ctx, ReorderBufferTXN *txn,
relentry = get_rel_sync_entry(data, relation);
+ /*
+ * If all columns of a table is present in the exclude column list. Skip
+ * publishing the changes.
+ */
+ if (relentry->no_cols_published)
+ return;
+
/* First check the table filter */
switch (action)
{
@@ -2057,6 +2092,7 @@ get_rel_sync_entry(PGOutputData *data, Relation relation)
entry->publish_as_relid = InvalidOid;
entry->columns = NULL;
entry->attrmap = NULL;
+ entry->no_cols_published = false;
}
/* Validate the entry */
@@ -2106,6 +2142,7 @@ get_rel_sync_entry(PGOutputData *data, Relation relation)
entry->pubactions.pubupdate = false;
entry->pubactions.pubdelete = false;
entry->pubactions.pubtruncate = false;
+ entry->no_cols_published = 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 92db5ca8d97..29364603130 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -4706,6 +4706,7 @@ getPublicationTables(Archive *fout, TableInfo tblinfo[], int numTables)
int i_prrelqual;
int i_prattrs;
int i_prexcept;
+ int i_prexcludeattrs;
int i,
j,
ntups;
@@ -4723,7 +4724,15 @@ getPublicationTables(Archive *fout, TableInfo tblinfo[], int numTables)
/* FIXME: 180000 should be changed to 190000 later for PG19. */
if (fout->remoteVersion >= 180000)
- appendPQExpBufferStr(query, " prexcept,\n");
+ appendPQExpBufferStr(query, " prexcept, "
+ "(CASE\n"
+ " WHEN pr.prexcludeattrs IS NOT NULL THEN\n"
+ " (SELECT array_agg(attname)\n"
+ " FROM\n"
+ " pg_catalog.generate_series(0, pg_catalog.array_upper(pr.prexcludeattrs::pg_catalog.int2[], 1)) s,\n"
+ " pg_catalog.pg_attribute\n"
+ " WHERE attrelid = pr.prrelid AND attnum = prexcludeattrs[s])\n"
+ " ELSE NULL END) prexcludeattrs, \n");
else
appendPQExpBufferStr(query, " false AS prexcept,\n");
@@ -4755,6 +4764,7 @@ getPublicationTables(Archive *fout, TableInfo tblinfo[], int numTables)
i_prrelqual = PQfnumber(res, "prrelqual");
i_prattrs = PQfnumber(res, "prattrs");
i_prexcept = PQfnumber(res, "prexcept");
+ i_prexcludeattrs = PQfnumber(res, "prexcludeattrs");
/* this allocation may be more than we need */
pubrinfo = pg_malloc(ntups * sizeof(PublicationRelInfo));
@@ -4822,6 +4832,30 @@ getPublicationTables(Archive *fout, TableInfo tblinfo[], int numTables)
else
pubrinfo[j].pubrattrs = NULL;
+ if (!PQgetisnull(res, i, i_prexcludeattrs))
+ {
+ char **attnames;
+ int nattnames;
+ PQExpBuffer excludeattribs;
+
+ if (!parsePGArray(PQgetvalue(res, i, i_prexcludeattrs),
+ &attnames, &nattnames))
+ pg_fatal("could not parse %s array", "prattrs");
+ excludeattribs = createPQExpBuffer();
+ for (int k = 0; k < nattnames; k++)
+ {
+ if (k > 0)
+ appendPQExpBufferStr(excludeattribs, ", ");
+
+ appendPQExpBufferStr(excludeattribs, fmtId(attnames[k]));
+ }
+ pubrinfo[j].pubrexcludeattrs = excludeattribs->data;
+ free(excludeattribs); /* but not excludeattribs->data */
+ free(attnames);
+ }
+ else
+ pubrinfo[j].pubrexcludeattrs = NULL;
+
/* Decide whether we want to dump it */
selectDumpablePublicationObject(&(pubrinfo[j].dobj), fout);
@@ -4907,6 +4941,9 @@ dumpPublicationTable(Archive *fout, const PublicationRelInfo *pubrinfo)
if (pubrinfo->pubrattrs)
appendPQExpBuffer(query, " (%s)", pubrinfo->pubrattrs);
+ if (pubrinfo->pubrexcludeattrs)
+ appendPQExpBuffer(query, " EXCEPT (%s)", pubrinfo->pubrexcludeattrs);
+
if (pubrinfo->pubrelqual)
{
/*
diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h
index 096f29346d8..e01c2d1afbd 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -681,6 +681,7 @@ typedef struct _PublicationRelInfo
TableInfo *pubtable;
char *pubrelqual;
char *pubrattrs;
+ char *pubrexcludeattrs;
} PublicationRelInfo;
/*
diff --git a/src/include/catalog/pg_publication.h b/src/include/catalog/pg_publication.h
index 33b771990bd..5344559c88e 100644
--- a/src/include/catalog/pg_publication.h
+++ b/src/include/catalog/pg_publication.h
@@ -140,6 +140,7 @@ typedef struct PublicationRelInfo
Node *whereClause;
List *columns;
bool except;
+ List *exclude_columns;
} PublicationRelInfo;
extern Publication *GetPublication(Oid pubid);
@@ -181,15 +182,21 @@ 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);
+extern bool check_and_fetch_exclude_column_list(Publication *pub, Oid relid,
+ MemoryContext mcxt,
+ Bitmapset **cols);
extern ObjectAddress publication_add_relation(Oid pubid, PublicationRelInfo *pri,
bool if_not_exists);
extern Bitmapset *pub_collist_validate(Relation targetrel, List *columns);
+extern Bitmapset *pub_exclude_collist_validate(Relation targetrel,
+ List *exclude_columns);
extern ObjectAddress publication_add_schema(Oid pubid, Oid schemaid,
bool if_not_exists);
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 *excludecols);
#endif /* PG_PUBLICATION_H */
diff --git a/src/include/catalog/pg_publication_rel.h b/src/include/catalog/pg_publication_rel.h
index e7d7f3ba85c..4c1b4ddbddc 100644
--- a/src/include/catalog/pg_publication_rel.h
+++ b/src/include/catalog/pg_publication_rel.h
@@ -36,6 +36,7 @@ CATALOG(pg_publication_rel,6106,PublicationRelRelationId)
#ifdef CATALOG_VARLEN /* variable-length fields start here */
pg_node_tree prqual; /* qualifications */
int2vector prattrs; /* columns to replicate */
+ int2vector prexcludeattrs; /* columns to exclude */
#endif
} FormData_pg_publication_rel;
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 61a0b2ccf38..f148c8e2323 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -4236,6 +4236,7 @@ typedef struct PublicationTable
Node *whereClause; /* qualifications */
List *columns; /* List of columns in a publication table */
bool except; /* exclude the relation */
+ List *exclude_columns; /* List of columns to be excluded */
} PublicationTable;
/*
diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out
index 5d025328704..a274b3cff31 100644
--- a/src/test/regress/expected/publication.out
+++ b/src/test/regress/expected/publication.out
@@ -2125,6 +2125,71 @@ 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);
+CREATE TABLE pub_test_except3 (a int, gen1 int GENERATED ALWAYS AS (a * 2) STORED);
+-- 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
+----------------+------------+------------------+-----------+-----------
+ testpub_except | public | pub_test_except1 | {a,b,c,d} |
+ testpub_except | pub_sch1 | pub_test_except2 | {a,d} |
+(2 rows)
+
+-- Check for invalid cases
+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.
+CREATE PUBLICATION testpub_except2 FOR TABLE pub_test_except1 EXCEPT;
+ERROR: syntax error at or near ";"
+LINE 1: ...BLICATION testpub_except2 FOR TABLE pub_test_except1 EXCEPT;
+ ^
+CREATE PUBLICATION testpub_except2 FOR TABLE pub_test_except3 EXCEPT (gen1);
+ERROR: cannot use generated column "gen1" in publication except column list
+-- Verify that publication can be altered with EXCEPT
+ALTER PUBLICATION testpub_except SET TABLE pub_test_except1 EXCEPT (a, b), pub_sch1.pub_test_except2;
+SELECT * FROM pg_publication_tables WHERE pubname = 'testpub_except';
+ pubname | schemaname | tablename | attnames | rowfilter
+----------------+------------+------------------+-----------+-----------
+ testpub_except | public | pub_test_except1 | {c,d} |
+ testpub_except | pub_sch1 | pub_test_except2 | {a,b,c,d} |
+(2 rows)
+
+-- Verify ALTER PUBLICATION ... DROP
+ALTER PUBLICATION testpub_except DROP TABLE pub_test_except1 EXCEPT (a, b);
+ERROR: except column list must not be specified in ALTER PUBLICATION ... DROP
+ALTER PUBLICATION testpub_except DROP TABLE pub_test_except1;
+ALTER PUBLICATION testpub_except ADD TABLE pub_test_except1 EXCEPT (c, d);
+SELECT * FROM pg_publication_tables WHERE pubname = 'testpub_except';
+ pubname | schemaname | tablename | attnames | rowfilter
+----------------+------------+------------------+-----------+-----------
+ testpub_except | public | pub_test_except1 | {a,b} |
+ testpub_except | pub_sch1 | pub_test_except2 | {a,b,c,d} |
+(2 rows)
+
+-- Verify excluded columns cannot be part of REPLICA IDENTITY
+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.
+CREATE UNIQUE INDEX pub_test_except1_a_idx ON pub_test_except1 (a, c);
+ALTER TABLE pub_test_except1 REPLICA IDENTITY USING INDEX pub_test_except1_a_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_a_idx;
+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;
+DROP INDEX pub_test_except1_a_idx;
+DROP PUBLICATION testpub_except;
+DROP TABLE pub_test_except1;
+DROP TABLE pub_sch1.pub_test_except2;
+DROP TABLE pub_test_except3;
DROP SCHEMA pub_sch1;
RESET client_min_messages;
RESET SESSION AUTHORIZATION;
diff --git a/src/test/regress/sql/publication.sql b/src/test/regress/sql/publication.sql
index af31a2214ca..6b23f215739 100644
--- a/src/test/regress/sql/publication.sql
+++ b/src/test/regress/sql/publication.sql
@@ -1318,6 +1318,51 @@ 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);
+CREATE TABLE pub_test_except3 (a int, gen1 int GENERATED ALWAYS AS (a * 2) STORED);
+
+-- 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';
+
+-- Check for invalid cases
+CREATE PUBLICATION testpub_except2 FOR TABLES IN SCHEMA pub_sch1, TABLE pub_test_except1 EXCEPT (b, c);
+CREATE PUBLICATION testpub_except2 FOR TABLE pub_test_except1 EXCEPT;
+CREATE PUBLICATION testpub_except2 FOR TABLE pub_test_except3 EXCEPT (gen1);
+
+-- Verify that publication can be altered with EXCEPT
+ALTER PUBLICATION testpub_except SET TABLE pub_test_except1 EXCEPT (a, b), pub_sch1.pub_test_except2;
+SELECT * FROM pg_publication_tables WHERE pubname = 'testpub_except';
+
+-- Verify ALTER PUBLICATION ... DROP
+ALTER PUBLICATION testpub_except DROP TABLE pub_test_except1 EXCEPT (a, b);
+ALTER PUBLICATION testpub_except DROP TABLE pub_test_except1;
+
+ALTER PUBLICATION testpub_except ADD TABLE pub_test_except1 EXCEPT (c, d);
+SELECT * FROM pg_publication_tables WHERE pubname = 'testpub_except';
+
+-- Verify excluded columns cannot be part of REPLICA IDENTITY
+ALTER TABLE pub_test_except1 REPLICA IDENTITY FULL;
+UPDATE pub_test_except1 SET a = 3 WHERE a = 1;
+CREATE UNIQUE INDEX pub_test_except1_a_idx ON pub_test_except1 (a, c);
+ALTER TABLE pub_test_except1 REPLICA IDENTITY USING INDEX pub_test_except1_a_idx;
+UPDATE pub_test_except1 SET a = 3 WHERE a = 1;
+DROP INDEX pub_test_except1_a_idx;
+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;
+
+DROP INDEX pub_test_except1_a_idx;
+DROP PUBLICATION testpub_except;
+DROP TABLE pub_test_except1;
+DROP TABLE pub_sch1.pub_test_except2;
+DROP TABLE pub_test_except3;
DROP SCHEMA pub_sch1;
RESET client_min_messages;
diff --git a/src/test/subscription/t/036_rep_changes_except_table.pl b/src/test/subscription/t/036_rep_changes_except_table.pl
index 1d115283809..ec77f2e8d04 100644
--- a/src/test/subscription/t/036_rep_changes_except_table.pl
+++ b/src/test/subscription/t/036_rep_changes_except_table.pl
@@ -1,7 +1,7 @@
# Copyright (c) 2021-2022, PostgreSQL Global Development Group
-# Logical replication tests for except table publications
+# Logical replication tests for except table and except column publications
use strict;
use warnings;
use PostgreSQL::Test::Cluster;
@@ -77,6 +77,64 @@ $result = $node_subscriber->safe_psql('postgres',
"SELECT count(*), min(a), max(a) FROM public.tab1");
is($result, qq(0||), 'check rows on subscriber catchup');
+# Test for except column publications
+# Initial setup
+$node_publisher->safe_psql('postgres',
+ "CREATE TABLE tab2 (a int, b int NOT NULL, c int)");
+$node_publisher->safe_psql('postgres',
+ "CREATE TABLE sch1.tab2 (a int, b int, c int)");
+$node_publisher->safe_psql('postgres', "INSERT INTO tab2 VALUES (1, 2, 3)");
+$node_publisher->safe_psql('postgres',
+ "INSERT INTO sch1.tab2 VALUES (1, 2, 3)");
+$node_publisher->safe_psql('postgres',
+ "CREATE PUBLICATION tap_pub_col FOR TABLE tab2 EXCEPT (a), sch1.tab2 EXCEPT (b, c)"
+);
+$node_subscriber->safe_psql('postgres',
+ "CREATE TABLE tab2 (a int, b int NOT NULL, c int)");
+$node_subscriber->safe_psql('postgres',
+ "CREATE TABLE sch1.tab2 (a int, b int, c int)");
+$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
+$result = $node_subscriber->safe_psql('postgres', "SELECT * FROM tab2");
+is($result, qq(|2|3),
+ 'check that initial sync for except column publication');
+$result = $node_subscriber->safe_psql('postgres', "SELECT * FROM sch1.tab2");
+is($result, qq(1||), 'check that initial sync for except column publication');
+$node_publisher->safe_psql('postgres', "INSERT INTO tab2 VALUES (4, 5, 6)");
+$node_publisher->safe_psql('postgres',
+ "INSERT INTO sch1.tab2 VALUES (4, 5, 6)");
+$node_publisher->wait_for_catchup('tap_sub_col');
+
+# Test incremental changes
+$result = $node_subscriber->safe_psql('postgres', "SELECT * FROM tab2");
+is( $result, qq(|2|3
+|5|6),
+ 'check incremental insert for except column publication');
+$result = $node_subscriber->safe_psql('postgres', "SELECT * FROM sch1.tab2");
+is( $result, qq(1||
+4||), 'check incremental insert for except column publication');
+
+# Test for update
+$node_publisher->safe_psql('postgres',
+ "CREATE UNIQUE INDEX b_idx ON tab2 (b)");
+$node_publisher->safe_psql('postgres',
+ "ALTER TABLE tab2 REPLICA IDENTITY USING INDEX b_idx");
+$node_subscriber->safe_psql('postgres',
+ "CREATE UNIQUE INDEX b_idx ON tab2 (b)");
+$node_subscriber->safe_psql('postgres',
+ "ALTER TABLE tab2 REPLICA IDENTITY USING INDEX b_idx");
+$node_publisher->safe_psql('postgres',
+ "UPDATE tab2 SET a = 3, b = 4, c = 5 WHERE a = 1");
+$node_publisher->wait_for_catchup('tap_sub_col');
+$result = $node_subscriber->safe_psql('postgres', "SELECT * FROM tab2");
+is( $result, qq(|5|6
+|4|5),
+ 'check update for except column publication');
+
$node_subscriber->stop('fast');
$node_publisher->stop('fast');
--
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: <CANhcyEXspT3v5-Tdop9uqQV2HWBvZoN5P0BxXQ6Md6Mr7GXK9A@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