On Fri, May 13, 2022 at 9:37 AM Peter Smith <smithpb2...@gmail.com> wrote:
>
> On Thu, May 12, 2022 at 2:24 PM vignesh C <vignes...@gmail.com> wrote:
> >
> ...
> > The attached patch has the implementation for "ALTER PUBLICATION
> > pubname RESET". This command will reset the publication to default
> > state which includes resetting the publication options, setting ALL
> > TABLES option to false and dropping the relations and schemas that are
> > associated with the publication.
> >
>
> Please see below my review comments for the v1-0001 (RESET) patch
>
> ======
>
> 1. Commit message
>
> This patch adds a new RESET option to ALTER PUBLICATION which
>
> Wording: "RESET option" -> "RESET clause"
Modified
> ~~~
>
> 2. doc/src/sgml/ref/alter_publication.sgml
>
> + <para>
> + The <literal>RESET</literal> clause will reset the publication to default
> + state which includes resetting the publication options, setting
> + <literal>ALL TABLES</literal> option to <literal>false</literal>
> and drop the
> + relations and schemas that are associated with the publication.
> </para>
>
> 2a. Wording: "to default state" -> "to the default state"
Modified
> 2b. Wording: "and drop the relations..." -> "and dropping all relations..."
Modified
> ~~~
>
> 3. doc/src/sgml/ref/alter_publication.sgml
>
> + invoking user to be a superuser. <literal>RESET</literal> of publication
> + requires invoking user to be a superuser. To alter the owner, you must
> also
>
> Wording: "requires invoking user" -> "requires the invoking user"
Modified
> ~~~
>
> 4. doc/src/sgml/ref/alter_publication.sgml - Example
>
> @@ -207,6 +220,12 @@ ALTER PUBLICATION sales_publication ADD ALL
> TABLES IN SCHEMA marketing, sales;
> <structname>production_publication</structname>:
> <programlisting>
> ALTER PUBLICATION production_publication ADD TABLE users,
> departments, ALL TABLES IN SCHEMA production;
> +</programlisting></para>
> +
> + <para>
> + Resetting the publication <structname>production_publication</structname>:
> +<programlisting>
> +ALTER PUBLICATION production_publication RESET;
>
> Wording: "Resetting the publication" -> "Reset the publication"
Modified
> ~~~
>
> 5. src/backend/commands/publicationcmds.c
>
> + /* Check and reset the options */
>
> IMO the code can just reset all these options unconditionally. I did
> not see the point to check for existing option values first. I feel
> the simpler code outweighs any negligible performance difference in
> this case.
Modified
> ~~~
>
> 6. src/backend/commands/publicationcmds.c
>
> + /* Check and reset the options */
>
> Somehow it seemed a pity having to hardcode all these default values
> true/false in multiple places; e.g. the same is already hardcoded in
> the parse_publication_options function.
>
> To avoid multiple hard coded bools you could just call the
> parse_publication_options with an empty options list. That would set
> the defaults which you can then use:
> values[Anum_pg_publication_pubinsert - 1] =
> BoolGetDatum(pubactiondefs->insert);
>
> Alternatively, maybe there should be #defines to use instead of having
> the scattered hardcoded bool defaults:
> #define PUBACTION_DEFAULT_INSERT true
> #define PUBACTION_DEFAULT_UPDATE true
> etc
I have used #define for default value and used it in both the functions.
> ~~~
>
> 7. src/include/nodes/parsenodes.h
>
> @@ -4033,7 +4033,8 @@ typedef enum AlterPublicationAction
> {
> AP_AddObjects, /* add objects to publication */
> AP_DropObjects, /* remove objects from publication */
> - AP_SetObjects /* set list of objects */
> + AP_SetObjects, /* set list of objects */
> + AP_ReSetPublication /* reset the publication */
> } AlterPublicationAction;
>
> Unusual case: "AP_ReSetPublication" -> "AP_ResetPublication"
Modified
> ~~~
>
> 8. src/test/regress/sql/publication.sql
>
> 8a.
> +-- Test for RESET PUBLICATION
> SUGGESTED
> +-- Tests for ALTER PUBLICATION ... RESET
Modified
> 8b.
> +-- Verify that 'ALL TABLES' option is reset
> SUGGESTED:
> +-- Verify that 'ALL TABLES' flag is reset
Modified
> 8c.
> +-- Verify that publish option and publish via root option is reset
> SUGGESTED:
> +-- Verify that publish options and publish_via_partition_root option are
> reset
Modified
> 8d.
> +-- Verify that only superuser can execute RESET publication
> SUGGESTED
> +-- Verify that only superuser can reset a publication
Modified
Thanks for the comments, the attached v5 patch has the changes for the
same. Also I have made the changes for SKIP Table based on the new
syntax, the changes for the same are available in
v5-0002-Skip-publishing-the-tables-specified-in-EXCEPT-TA.patch.
Regards,
Vignesh
From b38b5fa76c88c2d2df6abf46a760a9422072c989 Mon Sep 17 00:00:00 2001
From: Vigneshwaran C <vignes...@gmail.com>
Date: Sat, 14 May 2022 13:13:46 +0530
Subject: [PATCH v5 1/2] 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 default state which includes resetting the publication
options, setting ALL TABLES option 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 | 33 ++++++--
src/backend/commands/publicationcmds.c | 98 +++++++++++++++++++++--
src/backend/parser/gram.y | 9 +++
src/bin/psql/tab-complete.c | 2 +-
src/include/nodes/parsenodes.h | 3 +-
src/test/regress/expected/publication.out | 69 ++++++++++++++++
src/test/regress/sql/publication.sql | 37 +++++++++
7 files changed, 237 insertions(+), 14 deletions(-)
diff --git a/doc/src/sgml/ref/alter_publication.sgml b/doc/src/sgml/ref/alter_publication.sgml
index e2cce49471..189727af62 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>
@@ -65,7 +66,18 @@ 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 options, setting
+ <literal>ALL TABLES</literal> option to <literal>false</literal> and
+ dropping all relations and schemas that are associated with the publication.
</para>
<para>
@@ -73,12 +85,13 @@ ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RENAME TO <r
Adding a table to a publication additionally requires owning that table.
The <literal>ADD ALL TABLES IN SCHEMA</literal> and
<literal>SET ALL TABLES IN SCHEMA</literal> to a publication requires the
- invoking user to be a superuser. To alter the owner, you must also be a
- direct or indirect member of the new owning role. The new owner must have
- <literal>CREATE</literal> privilege on the database. Also, the new owner
- of a <literal>FOR ALL TABLES</literal> or <literal>FOR ALL TABLES IN
- SCHEMA</literal> publication must be a superuser. However, a superuser can
- change the ownership of a publication regardless of these restrictions.
+ invoking user to be a superuser. <literal>RESET</literal> of publication
+ requires the invoking user to be a superuser. To alter the owner, you must
+ also be a direct or indirect member of the new owning role. The new owner
+ must have <literal>CREATE</literal> privilege on the database. Also, the
+ new owner of a <literal>FOR ALL TABLES</literal> or <literal>FOR ALL TABLES
+ IN SCHEMA</literal> publication must be a superuser. However, a superuser
+ can change the ownership of a publication regardless of these restrictions.
</para>
<para>
@@ -207,6 +220,12 @@ ALTER PUBLICATION sales_publication ADD ALL TABLES IN SCHEMA marketing, sales;
<structname>production_publication</structname>:
<programlisting>
ALTER PUBLICATION production_publication ADD TABLE users, departments, ALL 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 8e645741e4..956b02d501 100644
--- a/src/backend/commands/publicationcmds.c
+++ b/src/backend/commands/publicationcmds.c
@@ -53,6 +53,13 @@
#include "utils/syscache.h"
#include "utils/varlena.h"
+#define PUB_ATION_INSERT_DEFAULT true
+#define PUB_ACTION_UPDATE_DEFAULT true
+#define PUB_ACTION_DELETE_DEFAULT true
+#define PUB_ACTION_TRUNCATE_DEFAULT true
+#define PUB_VIA_ROOT_DEFAULT false
+#define PUB_ALL_TABLES_DEFAULT false
+
/*
* Information used to validate the columns in the row filter expression. See
* contain_invalid_rfcolumn_walker for details.
@@ -91,11 +98,11 @@ parse_publication_options(ParseState *pstate,
*publish_via_partition_root_given = false;
/* defaults */
- pubactions->pubinsert = true;
- pubactions->pubupdate = true;
- pubactions->pubdelete = true;
- pubactions->pubtruncate = true;
- *publish_via_partition_root = false;
+ pubactions->pubinsert = PUB_ATION_INSERT_DEFAULT;
+ pubactions->pubupdate = PUB_ACTION_UPDATE_DEFAULT;
+ pubactions->pubdelete = PUB_ACTION_DELETE_DEFAULT;
+ pubactions->pubtruncate = PUB_ACTION_TRUNCATE_DEFAULT;
+ *publish_via_partition_root = PUB_VIA_ROOT_DEFAULT;
/* Parse options */
foreach(lc, options)
@@ -1105,6 +1112,85 @@ InvalidatePublicationRels(List *relids)
CacheInvalidateRelcacheAll();
}
+/*
+ * Reset the publication.
+ *
+ * Reset the publication options, publication relations and publication schemas.
+ */
+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 options */
+ values[Anum_pg_publication_pubinsert - 1] = BoolGetDatum(PUB_ATION_INSERT_DEFAULT);
+ replaces[Anum_pg_publication_pubinsert - 1] = true;
+
+ values[Anum_pg_publication_pubupdate - 1] = BoolGetDatum(PUB_ACTION_UPDATE_DEFAULT);
+ replaces[Anum_pg_publication_pubupdate - 1] = true;
+
+ values[Anum_pg_publication_pubdelete - 1] = BoolGetDatum(PUB_ACTION_DELETE_DEFAULT);
+ replaces[Anum_pg_publication_pubdelete - 1] = true;
+
+ values[Anum_pg_publication_pubtruncate - 1] = BoolGetDatum(PUB_ACTION_TRUNCATE_DEFAULT);
+ replaces[Anum_pg_publication_pubtruncate - 1] = true;
+
+ values[Anum_pg_publication_pubviaroot - 1] = BoolGetDatum(PUB_VIA_ROOT_DEFAULT);
+ replaces[Anum_pg_publication_pubviaroot - 1] = true;
+
+ values[Anum_pg_publication_puballtables - 1] = BoolGetDatum(PUB_ALL_TABLES_DEFAULT);
+ 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);
+
+ /* 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",
+ RelationGetRelationName(rel))));
+
+ ObjectAddressSet(obj, PublicationRelRelationId, prid);
+ performDeletion(&obj, DROP_CASCADE, 0);
+ }
+}
+
/*
* Add or remove table to/from publication.
*/
@@ -1416,6 +1502,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 989db0dbec..d7e13666a2 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -10558,6 +10558,8 @@ pub_obj_list: PublicationObjSpec
*
* ALTER PUBLICATION name SET pub_obj [, ...]
*
+ * ALTER PUBLICATION name RESET
+ *
* pub_obj is one of:
*
* TABLE table_name [, ...]
@@ -10604,6 +10606,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.c b/src/bin/psql/tab-complete.c
index 55af9eb04e..62ecc3cdab 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1819,7 +1819,7 @@ psql_completion(const char *text, int start, int end)
/* 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("ALL TABLES IN SCHEMA", "TABLE");
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 73f635b455..9726fdae58 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -4035,7 +4035,8 @@ typedef enum AlterPublicationAction
{
AP_AddObjects, /* add objects to publication */
AP_DropObjects, /* remove objects from publication */
- AP_SetObjects /* set list of objects */
+ 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 398c0f38f6..f8527dae02 100644
--- a/src/test/regress/expected/publication.out
+++ b/src/test/regress/expected/publication.out
@@ -1653,6 +1653,75 @@ DROP PUBLICATION pub;
DROP TABLE sch1.tbl1;
DROP SCHEMA sch1 cascade;
DROP SCHEMA sch2 cascade;
+-- 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 | Via root
+--------------------------+------------+---------+---------+---------+-----------+----------
+ regress_publication_user | t | t | t | t | t | f
+(1 row)
+
+ALTER PUBLICATION testpub_reset RESET;
+\dRp+ testpub_reset
+ Publication testpub_reset
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
+--------------------------+------------+---------+---------+---------+-----------+----------
+ regress_publication_user | f | t | t | t | t | f
+(1 row)
+
+ALTER PUBLICATION testpub_reset ADD TABLE pub_sch1.tbl1, ALL TABLES IN SCHEMA public;
+-- Verify that tables and schemas associated with the publication are dropped
+-- after RESET
+\dRp+ testpub_reset
+ Publication testpub_reset
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
+--------------------------+------------+---------+---------+---------+-----------+----------
+ regress_publication_user | f | t | t | t | t | f
+Tables:
+ "pub_sch1.tbl1"
+Tables from schemas:
+ "public"
+
+ALTER PUBLICATION testpub_reset RESET;
+\dRp+ testpub_reset
+ Publication testpub_reset
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
+--------------------------+------------+---------+---------+---------+-----------+----------
+ regress_publication_user | f | t | t | t | t | f
+(1 row)
+
+ALTER PUBLICATION testpub_reset SET (PUBLISH = '', PUBLISH_VIA_PARTITION_ROOT = 'true');
+-- Verify that publish options and publish_via_partition_root option are reset
+\dRp+ testpub_reset
+ Publication testpub_reset
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
+--------------------------+------------+---------+---------+---------+-----------+----------
+ regress_publication_user | f | f | f | f | f | t
+(1 row)
+
+ALTER PUBLICATION testpub_reset RESET;
+\dRp+ testpub_reset
+ Publication testpub_reset
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
+--------------------------+------------+---------+---------+---------+-----------+----------
+ regress_publication_user | f | t | t | t | t | 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
+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 SESSION AUTHORIZATION;
DROP ROLE regress_publication_user, regress_publication_user2;
DROP ROLE regress_publication_user_dummy;
diff --git a/src/test/regress/sql/publication.sql b/src/test/regress/sql/publication.sql
index 9eb86fd54f..0612315488 100644
--- a/src/test/regress/sql/publication.sql
+++ b/src/test/regress/sql/publication.sql
@@ -1057,6 +1057,43 @@ DROP TABLE sch1.tbl1;
DROP SCHEMA sch1 cascade;
DROP SCHEMA sch2 cascade;
+-- 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, ALL TABLES IN SCHEMA public;
+
+-- Verify that tables and 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 = '', PUBLISH_VIA_PARTITION_ROOT = 'true');
+
+-- Verify that publish options and publish_via_partition_root option are 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
+SET ROLE regress_publication_user;
+
+DROP PUBLICATION testpub_reset;
+DROP TABLE pub_sch1.tbl1;
+DROP SCHEMA pub_sch1;
+
RESET SESSION AUTHORIZATION;
DROP ROLE regress_publication_user, regress_publication_user2;
DROP ROLE regress_publication_user_dummy;
--
2.32.0
From cf0e031f8f82541bbd10bd4963e7da051e17a022 Mon Sep 17 00:00:00 2001
From: Vigneshwaran C <vignes...@gmail.com>
Date: Sat, 14 May 2022 13:22:11 +0530
Subject: [PATCH v5 2/2] Skip publishing the tables specified in EXCEPT TABLE.
A new option "EXCEPT TABLE" in Create/Alter Publication allows
one or more tables to be excluded, publisher will exclude sending the data
of the excluded tables to the subscriber.
The new syntax allows specifying exclude relations while creating a publication
or exclude relations in alter 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 publishing through the publication.
Modified the output plugin (pgoutput) to exclude publishing the changes of the
excluded tables.
Updates pg_dump to identify and dump the excluded tables of the publications.
Updates the \d family of commands to display excluded tables of the
publications and \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 | 5 +-
doc/src/sgml/ref/alter_publication.sgml | 13 +-
doc/src/sgml/ref/create_publication.sgml | 29 ++-
doc/src/sgml/ref/psql-ref.sgml | 5 +-
src/backend/catalog/pg_publication.c | 38 +++-
src/backend/commands/publicationcmds.c | 196 ++++++++++++++----
src/backend/commands/tablecmds.c | 4 +-
src/backend/parser/gram.y | 48 ++++-
src/backend/replication/pgoutput/pgoutput.c | 25 +--
src/backend/utils/cache/relcache.c | 17 +-
src/bin/pg_dump/pg_dump.c | 61 +++++-
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 | 12 ++
src/bin/psql/describe.c | 56 ++++-
src/bin/psql/tab-complete.c | 12 +-
src/include/catalog/pg_publication.h | 7 +-
src/include/catalog/pg_publication_rel.h | 1 +
src/include/commands/publicationcmds.h | 4 +-
src/include/nodes/parsenodes.h | 2 +
src/test/regress/expected/publication.out | 90 +++++++-
src/test/regress/sql/publication.sql | 54 ++++-
.../t/032_rep_changes_except_table.pl | 86 ++++++++
24 files changed, 660 insertions(+), 122 deletions(-)
create mode 100644 src/test/subscription/t/032_rep_changes_except_table.pl
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index a533a2153e..78e8c22a59 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -6426,6 +6426,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 table 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 145ea71d61..d7d6ba0529 100644
--- a/doc/src/sgml/logical-replication.sgml
+++ b/doc/src/sgml/logical-replication.sgml
@@ -1167,8 +1167,9 @@ CONTEXT: processing remote data for replication origin "pg_16395" during "INSER
<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.
+ superuser. To add all tables 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.
</para>
<para>
diff --git a/doc/src/sgml/ref/alter_publication.sgml b/doc/src/sgml/ref/alter_publication.sgml
index 189727af62..e895add262 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 [ ONLY ] <replaceable class="parameter">table_name</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>] [, ... ] )
@@ -82,8 +83,8 @@ 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 ALL TABLES IN SCHEMA</literal> and
+ Adding a table or excluding a table to a publication additionally requires
+ owning that table. The <literal>ADD ALL TABLES IN SCHEMA</literal> and
<literal>SET ALL TABLES IN SCHEMA</literal> to a publication requires the
invoking user to be a superuser. <literal>RESET</literal> of publication
requires the invoking user to be a superuser. To alter the owner, you must
@@ -213,6 +214,14 @@ ALTER PUBLICATION sales_publication ADD ALL TABLES IN SCHEMA marketing, sales;
</programlisting>
</para>
+ <para>
+ Alter publication <structname>production_publication</structname> that
+ publishes all tables except <structname>users</structname> and
+ <structname>departments</structname> tables:
+<programlisting>
+ALTER PUBLICATION production_publication ADD ALL TABLES EXCEPT TABLE 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 1a828e8d2f..9ce6bc7e37 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 [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [, ... ]]
| FOR <replaceable class="parameter">publication_object</replaceable> [, ... ] ]
[ WITH ( <replaceable class="parameter">publication_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] ) ]
@@ -156,6 +156,24 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
</listitem>
</varlistentry>
+
+ <varlistentry>
+ <term><literal>EXCEPT TABLE</literal></term>
+ <listitem>
+ <para>
+ Marks the publication as one that excludes replicating changes for the
+ specified tables.
+ </para>
+
+ <para>
+ <literal>EXCEPT TABLE</literal> can be specified only for
+ <literal>FOR ALL TABLES</literal> publication. It is not supported for
+ <literal>FOR ALL TABLES IN SCHEMA </literal> publication and
+ <literal>FOR TABLE</literal> publication.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>WITH ( <replaceable class="parameter">publication_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] )</literal></term>
<listitem>
@@ -351,6 +369,15 @@ CREATE PUBLICATION production_publication FOR TABLE users, departments, ALL TABL
CREATE PUBLICATION sales_publication FOR ALL 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> table:
+<programlisting>
+CREATE PUBLICATION mypublication FOR ALL TABLE EXCEPT TABLE 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 5fc6b1034a..3889796b3f 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1868,8 +1868,9 @@ testdb=>
If <replaceable class="parameter">pattern</replaceable> is
specified, only those publications whose names match the pattern are
listed.
- 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 e2c8bcb279..d84aad526e 100644
--- a/src/backend/catalog/pg_publication.c
+++ b/src/backend/catalog/pg_publication.c
@@ -290,7 +290,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;
@@ -302,8 +303,9 @@ GetTopMostAncestorInPublication(Oid puboid, List *ancestors, int *ancestor_level
foreach(lc, ancestors)
{
Oid ancestor = lfirst_oid(lc);
- List *apubids = GetRelationPublications(ancestor);
+ List *apubids = GetRelationPublications(ancestor, false);
List *aschemaPubids = NIL;
+ List *aexceptpubids = NIL;
level++;
@@ -317,7 +319,9 @@ GetTopMostAncestorInPublication(Oid puboid, List *ancestors, int *ancestor_level
else
{
aschemaPubids = GetSchemaPublications(get_rel_namespace(ancestor));
- if (list_member_oid(aschemaPubids, puboid))
+ aexceptpubids = GetRelationPublications(ancestor, true);
+ if (list_member_oid(aschemaPubids, puboid) ||
+ (puballtables && !list_member_oid(aexceptpubids, puboid)))
{
topmost_relid = ancestor;
@@ -328,6 +332,7 @@ GetTopMostAncestorInPublication(Oid puboid, List *ancestors, int *ancestor_level
list_free(apubids);
list_free(aschemaPubids);
+ list_free(aexceptpubids);
}
return topmost_relid;
@@ -396,6 +401,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)
@@ -666,7 +673,7 @@ publication_add_schema(Oid pubid, Oid schemaid, bool if_not_exists)
/* Gets list of publication oids for a relation */
List *
-GetRelationPublications(Oid relid)
+GetRelationPublications(Oid relid, bool bexcept)
{
List *result = NIL;
CatCList *pubrellist;
@@ -680,7 +687,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 (bexcept == ((Form_pg_publication_rel) GETSTRUCT(tup))->prexcept)
+ result = lappend_oid(result, pubid);
}
ReleaseSysCacheList(pubrellist);
@@ -779,7 +787,7 @@ GetAllTablesPublications(void)
* root partitioned tables.
*/
List *
-GetAllTablesPublicationRelations(bool pubviaroot)
+GetAllTablesPublicationRelations(Oid pubid, bool pubviaroot)
{
Relation classRel;
ScanKeyData key[1];
@@ -787,6 +795,15 @@ GetAllTablesPublicationRelations(bool pubviaroot)
HeapTuple tuple;
List *result = NIL;
+ /*
+ * pg_publication_rel and pg_publication_namespace will only have excluded
+ * tables in case of all tables publication, no need to pass except flag
+ * to get the relations.
+ */
+ List *exceptpubtablelist;
+
+ exceptpubtablelist = GetPublicationRelations(pubid, PUBLICATION_PART_ALL);
+
classRel = table_open(RelationRelationId, AccessShareLock);
ScanKeyInit(&key[0],
@@ -802,7 +819,8 @@ GetAllTablesPublicationRelations(bool pubviaroot)
Oid relid = relForm->oid;
if (is_publishable_class(relid, relForm) &&
- !(relForm->relispartition && pubviaroot))
+ !(relForm->relispartition && pubviaroot) &&
+ !list_member_oid(exceptpubtablelist, relid))
result = lappend_oid(result, relid);
}
@@ -823,7 +841,8 @@ GetAllTablesPublicationRelations(bool pubviaroot)
Oid relid = relForm->oid;
if (is_publishable_class(relid, relForm) &&
- !relForm->relispartition)
+ !relForm->relispartition &&
+ !list_member_oid(exceptpubtablelist, relid))
result = lappend_oid(result, relid);
}
@@ -1107,7 +1126,8 @@ pg_get_publication_tables(PG_FUNCTION_ARGS)
*/
if (publication->alltables)
{
- tables = GetAllTablesPublicationRelations(publication->pubviaroot);
+ tables = GetAllTablesPublicationRelations(publication->oid,
+ publication->pubviaroot);
}
else
{
diff --git a/src/backend/commands/publicationcmds.c b/src/backend/commands/publicationcmds.c
index 956b02d501..ca6edbd397 100644
--- a/src/backend/commands/publicationcmds.c
+++ b/src/backend/commands/publicationcmds.c
@@ -192,6 +192,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:
@@ -304,7 +309,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);
@@ -331,7 +336,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;
@@ -380,7 +386,7 @@ pub_rf_contains_invalid_column(Oid pubid, Relation relation, List *ancestors,
*/
bool
pub_collist_contains_invalid_column(Oid pubid, Relation relation, List *ancestors,
- bool pubviaroot)
+ bool pubviaroot, bool puballtables)
{
HeapTuple tuple;
Oid relid = RelationGetRelid(relation);
@@ -399,7 +405,7 @@ pub_collist_contains_invalid_column(Oid pubid, Relation relation, List *ancestor
*/
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;
@@ -843,54 +849,52 @@ 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 ALL TABLES IN SCHEMA requires superuser */
- if (list_length(schemaidlist) > 0 && !superuser())
- ereport(ERROR,
- errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
- errmsg("must be superuser to create FOR ALL TABLES IN SCHEMA publication"));
+ /* FOR ALL TABLES IN SCHEMA requires superuser */
+ if (list_length(schemaidlist) > 0 && !superuser())
+ ereport(ERROR,
+ errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("must be superuser to create FOR ALL TABLES IN SCHEMA publication"));
- if (list_length(relations) > 0)
- {
- List *rels;
+ if (list_length(relations) > 0)
+ {
+ List *rels;
- rels = OpenTableList(relations);
- CheckObjSchemaNotAlreadyInPublication(rels, schemaidlist,
- PUBLICATIONOBJ_TABLE);
+ rels = OpenTableList(relations);
+ CheckObjSchemaNotAlreadyInPublication(rels, schemaidlist,
+ PUBLICATIONOBJ_TABLE);
- TransformPubWhereClauses(rels, pstate->p_sourcetext,
- publish_via_partition_root);
+ TransformPubWhereClauses(rels, pstate->p_sourcetext,
+ publish_via_partition_root);
- CheckPubRelationColumnList(rels, pstate->p_sourcetext,
- publish_via_partition_root);
+ CheckPubRelationColumnList(rels, pstate->p_sourcetext,
+ publish_via_partition_root);
- PublicationAddTables(puboid, rels, true, NULL);
- CloseTableList(rels);
- }
+ PublicationAddTables(puboid, rels, true, NULL);
+ CloseTableList(rels);
+ }
- if (list_length(schemaidlist) > 0)
- {
- /*
- * Schema lock is held until the publication is created to prevent
- * concurrent schema deletion.
- */
- LockSchemaList(schemaidlist);
- PublicationAddSchemas(puboid, schemaidlist, true, NULL);
- }
+ if (list_length(schemaidlist) > 0)
+ {
+ /*
+ * 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)
@@ -1191,6 +1195,76 @@ AlterPublicationReset(ParseState *pstate, AlterPublicationStmt *stmt,
}
}
+/*
+ * Check if the publication has default values
+ *
+ * Check the following:
+ * Publication is having default options
+ * Publication is not associated with relations
+ * Publication is not associated with schemas
+ * Publication is not set with "FOR ALL TABLES"
+ */
+static bool
+CheckPublicationDefValues(HeapTuple tup)
+{
+ Form_pg_publication pubform = (Form_pg_publication) GETSTRUCT(tup);
+ Oid pubid = pubform->oid;
+ List *pubobjs = NIL;
+
+ if (pubform->puballtables)
+ return false;
+
+ if (!pubform->pubinsert || !pubform->pubupdate || !pubform->pubdelete ||
+ !pubform->pubtruncate || pubform->pubviaroot)
+ 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;
+}
+
+/*
+ * Reset the publication.
+ *
+ * Reset the publication options, publication relations and publication schemas.
+ */
+static void
+AlterPublicationSetAllTables(Relation rel, HeapTuple tup)
+{
+ Form_pg_publication pubform = (Form_pg_publication) GETSTRUCT(tup);
+ bool nulls[Natts_pg_publication];
+ bool replaces[Natts_pg_publication];
+ Datum values[Natts_pg_publication];
+
+ Assert(!pubform->puballtables);
+
+ /* 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));
+
+ /* set all tables option */
+ 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.
*/
@@ -1442,6 +1516,19 @@ CheckAlterPublication(AlterPublicationStmt *stmt, HeapTuple tup,
List *tables, List *schemaidlist)
{
Form_pg_publication pubform = (Form_pg_publication) GETSTRUCT(tup);
+ ListCell *lc;
+ bool nonexcepttable = false;
+ bool excepttable = false;
+
+ foreach(lc, tables)
+ {
+ PublicationTable *pub_table = lfirst_node(PublicationTable, lc);
+
+ if (!pub_table->except)
+ nonexcepttable = true;
+ else
+ excepttable = true;
+ }
if ((stmt->action == AP_AddObjects || stmt->action == AP_SetObjects) &&
schemaidlist && !superuser())
@@ -1461,12 +1548,19 @@ CheckAlterPublication(AlterPublicationStmt *stmt, HeapTuple tup,
errdetail("Tables from schema cannot be added to, dropped from, or set on FOR ALL TABLES publications.")));
/* Check that user is allowed to manipulate the publication tables. */
- if (tables && pubform->puballtables)
+ if (nonexcepttable && tables && pubform->puballtables)
ereport(ERROR,
(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
errmsg("publication \"%s\" is defined as FOR ALL TABLES",
NameStr(pubform->pubname)),
errdetail("Tables cannot be added to or dropped from FOR ALL TABLES publications.")));
+
+ if (excepttable && !stmt->for_all_tables)
+ ereport(ERROR,
+ (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("publication \"%s\" is not defined as FOR ALL TABLES",
+ NameStr(pubform->pubname)),
+ errdetail("except table cannot be added to, dropped from, or set on NON ALL TABLES publications.")));
}
/*
@@ -1500,6 +1594,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("Setting ALL TABLES requires publication \"%s\" to have default values",
+ stmt->pubname),
+ errhint("Either the publication has tables/schemas associated or does not have default publication options or ALL TABLES option is set."));
+
+ AlterPublicationSetAllTables(rel, tup);
+ }
+
if (stmt->options)
AlterPublicationOptions(pstate, stmt, rel, tup);
else if (stmt->action == AP_ResetPublication)
@@ -1745,6 +1853,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);
@@ -1817,6 +1926,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);
@@ -1892,8 +2002,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 2de0ebacec..5d97eadf54 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -16276,7 +16276,7 @@ ATPrepChangePersistence(Relation rel, bool toLogged)
* UNLOGGED as UNLOGGED tables can't be published.
*/
if (!toLogged &&
- list_length(GetRelationPublications(RelationGetRelid(rel))) > 0)
+ 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",
@@ -16413,7 +16413,7 @@ AlterTableNamespace(AlterObjectSchemaStmt *stmt, Oid *oldschema)
{
ListCell *lc;
List *schemaPubids = GetSchemaPublications(nspOid);
- List *relPubids = GetRelationPublications(RelationGetRelid(rel));
+ List *relPubids = GetRelationPublications(RelationGetRelid(rel), false);
foreach(lc, relPubids)
{
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index d7e13666a2..b6ead7da4b 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -455,7 +455,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 <node> opt_routine_body
%type <groupclause> group_clause
@@ -588,6 +588,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
@@ -10410,7 +10411,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]
*
@@ -10430,12 +10431,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;
}
@@ -10473,6 +10475,7 @@ PublicationObjSpec:
$$->pubtable->relation = $2;
$$->pubtable->columns = $3;
$$->pubtable->whereClause = $4;
+ $$->location = @1;
}
| ALL TABLES IN_P SCHEMA ColId
{
@@ -10548,6 +10551,34 @@ pub_obj_list: PublicationObjSpec
{ $$ = lappend($1, $3); }
;
+ExceptPublicationObjSpec:
+ EXCEPT TABLE relation_expr
+ {
+ $$ = makeNode(PublicationObjSpec);
+ $$->pubobjtype = PUBLICATIONOBJ_EXCEPT_TABLE;
+ $$->pubtable = makeNode(PublicationTable);
+ $$->pubtable->except = $1;
+ $$->pubtable->relation = $3;
+ $$->location = @1;
+ }
+ | relation_expr
+ {
+ $$ = makeNode(PublicationObjSpec);
+ $$->pubobjtype = PUBLICATIONOBJ_EXCEPT_TABLE;
+ $$->pubtable = makeNode(PublicationTable);
+ $$->pubtable->except = true;
+ $$->pubtable->relation = $1;
+ $$->location = @1;
+ }
+ ;
+
+ except_pub_obj_list: ExceptPublicationObjSpec
+ { $$ = list_make1($1); }
+ | except_pub_obj_list ',' ExceptPublicationObjSpec
+ { $$ = lappend($1, $3); }
+ | /*EMPTY*/ { $$ = NULL; }
+ ;
+
/*****************************************************************************
*
* ALTER PUBLICATION name SET ( options )
@@ -10586,6 +10617,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 42c06af239..6394466dab 100644
--- a/src/backend/replication/pgoutput/pgoutput.c
+++ b/src/backend/replication/pgoutput/pgoutput.c
@@ -1996,7 +1996,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
@@ -2085,22 +2086,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;
@@ -2119,7 +2104,8 @@ get_rel_sync_entry(PGOutputData *data, Relation relation)
ancestor = GetTopMostAncestorInPublication(pub->oid,
ancestors,
- &level);
+ &level,
+ pub->alltables);
if (ancestor != InvalidOid)
{
@@ -2134,6 +2120,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;
}
@@ -2209,6 +2197,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 60e72f9e8b..4659c766dc 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -5561,6 +5561,8 @@ void
RelationBuildPublicationDesc(Relation relation, PublicationDesc *pubdesc)
{
List *puboids;
+ List *alltablespuboids;
+ List *exceptpuboids = NIL;
ListCell *lc;
MemoryContext oldcxt;
Oid schemaid;
@@ -5594,7 +5596,7 @@ RelationBuildPublicationDesc(Relation relation, PublicationDesc *pubdesc)
pubdesc->cols_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));
@@ -5608,14 +5610,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);
@@ -5644,7 +5651,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;
@@ -5661,7 +5668,7 @@ RelationBuildPublicationDesc(Relation relation, PublicationDesc *pubdesc)
if (!pubform->puballtables &&
(pubform->pubupdate || pubform->pubdelete) &&
pub_collist_contains_invalid_column(pubid, relation, ancestors,
- pubform->pubviaroot))
+ pubform->pubviaroot, pubform->puballtables))
{
if (pubform->pubupdate)
pubdesc->cols_valid_for_update = false;
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 7cc9c72e49..4ec4c35caa 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -127,6 +127,8 @@ static SimpleOidList foreign_servers_include_oids = {NULL, NULL};
static SimpleStringList extension_include_patterns = {NULL, NULL};
static SimpleOidList extension_include_oids = {NULL, NULL};
+static SimplePtrList exceptinfo = {NULL, NULL};
+
static const CatalogId nilCatalogId = {0, 0};
/* override for standard extra_float_digits setting */
@@ -3980,8 +3982,34 @@ dumpPublication(Archive *fout, const PublicationInfo *pubinfo)
qpubname);
if (pubinfo->puballtables)
+ {
+ SimplePtrListCell *cell;
+ bool first = true;
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;
+ PublicationInfo *relpubinfo = pubrinfo->publication;
+ TableInfo *tbinfo;
+
+ if (pubinfo == relpubinfo)
+ {
+ tbinfo = pubrinfo->pubtable;
+
+ if (first)
+ {
+ appendPQExpBufferStr(query, " EXCEPT TABLE ONLY");
+ first = false;
+ }
+ else
+ appendPQExpBufferStr(query, ", ");
+ appendPQExpBuffer(query, " %s", fmtQualifiedDumpable(tbinfo));
+ }
+ }
+ }
+
appendPQExpBufferStr(query, " WITH (publish = '");
if (pubinfo->pubinsert)
{
@@ -4151,6 +4179,7 @@ getPublicationTables(Archive *fout, TableInfo tblinfo[], int numTables)
int i_prrelid;
int i_prrelqual;
int i_prattrs;
+ int i_prexcept;
int i,
j,
ntups;
@@ -4162,8 +4191,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: 150000 should be changed to 160000 later for PG16. */
+ if (fout->remoteVersion >= 150000)
+ 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"
@@ -4174,6 +4212,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, "
@@ -4189,6 +4228,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));
@@ -4200,6 +4240,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
@@ -4220,7 +4261,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));
@@ -4259,6 +4304,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++;
}
@@ -4330,8 +4378,11 @@ dumpPublicationTable(Archive *fout, const PublicationRelInfo *pubrinfo)
query = createPQExpBuffer();
- appendPQExpBuffer(query, "ALTER PUBLICATION %s ADD TABLE ONLY",
+ appendPQExpBuffer(query, "ALTER PUBLICATION %s ADD ",
fmtId(pubinfo->dobj.name));
+
+ appendPQExpBufferStr(query, "TABLE ONLY");
+
appendPQExpBuffer(query, " %s",
fmtQualifiedDumpable(tbinfo));
@@ -9936,6 +9987,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;
@@ -17869,6 +17923,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 1d21c2906f..07a50ece68 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -80,6 +80,7 @@ typedef enum
DO_REFRESH_MATVIEW,
DO_POLICY,
DO_PUBLICATION,
+ DO_PUBLICATION_EXCEPT_REL,
DO_PUBLICATION_REL,
DO_PUBLICATION_TABLE_IN_SCHEMA,
DO_SUBSCRIPTION
diff --git a/src/bin/pg_dump/pg_dump_sort.c b/src/bin/pg_dump/pg_dump_sort.c
index 5de3241eb4..a6595f25a6 100644
--- a/src/bin/pg_dump/pg_dump_sort.c
+++ b/src/bin/pg_dump/pg_dump_sort.c
@@ -90,6 +90,7 @@ enum dbObjectTypePriorities
PRIO_FK_CONSTRAINT,
PRIO_POLICY,
PRIO_PUBLICATION,
+ PRIO_PUBLICATION_EXCEPT_REL,
PRIO_PUBLICATION_REL,
PRIO_PUBLICATION_TABLE_IN_SCHEMA,
PRIO_SUBSCRIPTION,
@@ -144,6 +145,7 @@ static const int dbObjectTypePriority[] =
PRIO_REFRESH_MATVIEW, /* DO_REFRESH_MATVIEW */
PRIO_POLICY, /* DO_POLICY */
PRIO_PUBLICATION, /* DO_PUBLICATION */
+ PRIO_PUBLICATION_EXCEPT_REL, /* DO_PUBLICATION_EXCEPT_REL */
PRIO_PUBLICATION_REL, /* DO_PUBLICATION_REL */
PRIO_PUBLICATION_TABLE_IN_SCHEMA, /* DO_PUBLICATION_TABLE_IN_SCHEMA */
PRIO_SUBSCRIPTION /* DO_SUBSCRIPTION */
@@ -1483,6 +1485,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 1f08716f69..13a3b3f875 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -2454,6 +2454,18 @@ my %tests = (
like => { %full_runs, section_post_data => 1, },
},
+ 'CREATE PUBLICATION pub5' => {
+ create_order => 50,
+ create_sql => 'CREATE PUBLICATION pub5 FOR ALL TABLES EXCEPT TABLE dump_test.test_table;',
+ regexp => qr/^
+ \QCREATE PUBLICATION pub5 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, },
+ unlike => {
+ exclude_dump_test_schema => 1,
+ exclude_test_table => 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 1a5d924a23..f0ef41cb68 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -2950,17 +2950,34 @@ describeOneTableDetails(const char *schemaname,
" WHERE attrelid = pr.prrelid AND attnum = prattrs[s])\n"
" ELSE NULL END) "
"FROM pg_catalog.pg_publication p\n"
- " JOIN pg_catalog.pg_publication_rel pr ON p.oid = pr.prpubid\n"
- " JOIN pg_catalog.pg_class c ON c.oid = pr.prrelid\n"
- "WHERE pr.prrelid = '%s'\n"
- "UNION\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'",
+ oid, oid, oid);
+
+ /* FIXME: 150000 should be changed to 160000 later for PG16. */
+ if (pset.sversion >= 150000)
+ appendPQExpBufferStr(&buf, " AND pr.prexcept = 'f'\n");
+
+ appendPQExpBuffer(&buf, "UNION\n"
"SELECT pubname\n"
" , NULL\n"
" , NULL\n"
"FROM pg_catalog.pg_publication p\n"
- "WHERE p.puballtables AND pg_catalog.pg_relation_is_publishable('%s')\n"
- "ORDER BY 1;",
- oid, oid, oid, oid);
+ "WHERE p.puballtables AND pg_catalog.pg_relation_is_publishable('%s')\n",
+ oid);
+
+ /* FIXME: 150000 should be changed to 160000 later for PG16. */
+ if (pset.sversion >= 150000)
+ appendPQExpBuffer(&buf,
+ " AND NOT EXISTS (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
{
@@ -6302,8 +6319,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: 150000 should be changed to 160000 later for PG16. */
+ if (pset.sversion >= 150000)
+ appendPQExpBuffer(&buf, " AND pr.prexcept = 'f'\n");
+
+ appendPQExpBuffer(&buf, "ORDER BY 1,2");
if (!addFooterToPublicationDesc(&buf, _("Tables:"), false, &cont))
goto error_return;
@@ -6322,6 +6344,22 @@ describePublications(const char *pattern)
}
}
+ /* FIXME: 150000 should be changed to 160000 later for PG16. */
+ if (pset.sversion >= 150000)
+ {
+ /* 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 = 't'\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.c b/src/bin/psql/tab-complete.c
index 62ecc3cdab..3cb512d12c 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1822,9 +1822,13 @@ psql_completion(const char *text, int start, int end)
COMPLETE_WITH("ADD", "DROP", "OWNER TO", "RENAME TO", "RESET", "SET");
/* ALTER PUBLICATION <name> ADD */
else if (Matches("ALTER", "PUBLICATION", MatchAny, "ADD"))
- COMPLETE_WITH("ALL TABLES IN SCHEMA", "TABLE");
+ COMPLETE_WITH("ALL TABLES IN SCHEMA", "ALL TABLES", "TABLE");
+ else if (Matches("ALTER", "PUBLICATION", MatchAny, "ADD", "ALL", "TABLES"))
+ COMPLETE_WITH("EXCEPT TABLE");
else if (Matches("ALTER", "PUBLICATION", MatchAny, "ADD|SET", "TABLE") ||
- (HeadMatches("ALTER", "PUBLICATION", MatchAny, "ADD|SET", "TABLE") &&
+ Matches("ALTER", "PUBLICATION", MatchAny, "ADD", "ALL", "TABLES", "EXCEPT", "TABLE") ||
+ ((HeadMatches("ALTER", "PUBLICATION", MatchAny, "ADD|SET", "TABLE") ||
+ HeadMatches("ALTER", "PUBLICATION", MatchAny, "ADD", "ALL", "TABLES", "EXCEPT", "TABLE")) &&
ends_with(prev_wd, ',')))
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables);
@@ -2986,7 +2990,9 @@ psql_completion(const char *text, int start, int end)
else if (Matches("CREATE", "PUBLICATION", MatchAny, "FOR", "ALL"))
COMPLETE_WITH("TABLES", "TABLES IN SCHEMA");
else if (Matches("CREATE", "PUBLICATION", MatchAny, "FOR", "ALL", "TABLES"))
- COMPLETE_WITH("IN SCHEMA", "WITH (");
+ COMPLETE_WITH("IN SCHEMA", "WITH (", "EXCEPT TABLE");
+ else if (Matches("CREATE", "PUBLICATION", MatchAny, "FOR", "ALL", "TABLES", "EXCEPT"))
+ COMPLETE_WITH("TABLE");
else if (Matches("CREATE", "PUBLICATION", MatchAny, "FOR", "TABLE", MatchAny) && !ends_with(prev_wd, ','))
COMPLETE_WITH("WHERE (", "WITH (");
/* Complete "CREATE PUBLICATION <name> FOR TABLE" with "<table>, ..." */
diff --git a/src/include/catalog/pg_publication.h b/src/include/catalog/pg_publication.h
index 48205ba429..b6a7e53bf8 100644
--- a/src/include/catalog/pg_publication.h
+++ b/src/include/catalog/pg_publication.h
@@ -108,11 +108,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 bexcept);
/*---------
* Expected values for pub_partopt parameter of GetRelationPublications(),
@@ -132,7 +133,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,
@@ -143,7 +144,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 ecd3739f1a..595c87c540 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); /* except 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 57df3fc1e3..a515cdb802 100644
--- a/src/include/commands/publicationcmds.h
+++ b/src/include/commands/publicationcmds.h
@@ -32,8 +32,8 @@ 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 alltables);
extern bool pub_collist_contains_invalid_column(Oid pubid, Relation relation,
- List *ancestors, bool pubviaroot);
+ List *ancestors, bool pubviaroot, bool alltables);
#endif /* PUBLICATIONCMDS_H */
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 9726fdae58..134ad05936 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -3999,6 +3999,7 @@ typedef struct PublicationTable
RangeVar *relation; /* relation to be published */
Node *whereClause; /* qualifications */
List *columns; /* List of columns in a publication table */
+ bool except; /* except relation */
} PublicationTable;
/*
@@ -4007,6 +4008,7 @@ typedef struct PublicationTable
typedef enum PublicationObjSpecType
{
PUBLICATIONOBJ_TABLE, /* A table */
+ PUBLICATIONOBJ_EXCEPT_TABLE, /* An Except table */
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 f8527dae02..94adb2b873 100644
--- a/src/test/regress/expected/publication.out
+++ b/src/test/regress/expected/publication.out
@@ -165,8 +165,19 @@ Publications:
regress_publication_user | t | t | t | f | f | f
(1 row)
+SET client_min_messages = 'ERROR';
+CREATE PUBLICATION testpub_foralltables_excepttable FOR ALL TABLES EXCEPT TABLE testpub_tbl1;
+RESET client_min_messages;
+\dRp+ testpub_foralltables_excepttable
+ Publication testpub_foralltables_excepttable
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
+--------------------------+------------+---------+---------+---------+-----------+----------
+ regress_publication_user | t | t | t | t | t | f
+Except tables:
+ "public.testpub_tbl1"
+
DROP TABLE testpub_tbl2;
-DROP PUBLICATION testpub_foralltables, testpub_fortable, testpub_forschema;
+DROP PUBLICATION testpub_foralltables, testpub_fortable, testpub_forschema, testpub_foralltables_excepttable;
CREATE TABLE testpub_tbl3 (a int);
CREATE TABLE testpub_tbl3a (b text) INHERITS (testpub_tbl3);
SET client_min_messages = 'ERROR';
@@ -1659,6 +1670,10 @@ CREATE TABLE pub_sch1.tbl1 (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: Setting ALL TABLES requires publication "testpub_reset" to have default values
+HINT: Either the publication has tables/schemas associated or does not have default publication options or ALL TABLES option is set.
-- Verify that 'ALL TABLES' flag is reset
\dRp+ testpub_reset
Publication testpub_reset
@@ -1675,9 +1690,23 @@ ALTER PUBLICATION testpub_reset RESET;
regress_publication_user | f | t | t | t | t | f
(1 row)
-ALTER PUBLICATION testpub_reset ADD TABLE pub_sch1.tbl1, ALL TABLES IN SCHEMA public;
--- Verify that tables and schemas associated with the publication are dropped
--- after RESET
+-- should work now after resetting the publication
+ALTER PUBLICATION testpub_reset ADD ALL TABLES EXCEPT TABLE pub_sch1.tbl1;
+\dRp+ testpub_reset
+ Publication testpub_reset
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
+--------------------------+------------+---------+---------+---------+-----------+----------
+ regress_publication_user | t | t | t | t | t | f
+Except tables:
+ "pub_sch1.tbl1"
+
+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: Setting ALL TABLES requires publication "testpub_reset" to have default values
+HINT: Either the publication has tables/schemas associated or does not have default publication options or ALL TABLES option is set.
+-- Verify that tables associated with the publication are dropped after RESET
\dRp+ testpub_reset
Publication testpub_reset
Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
@@ -1685,6 +1714,26 @@ ALTER PUBLICATION testpub_reset ADD TABLE pub_sch1.tbl1, ALL TABLES IN SCHEMA pu
regress_publication_user | f | t | t | t | t | f
Tables:
"pub_sch1.tbl1"
+
+ALTER PUBLICATION testpub_reset RESET;
+\dRp+ testpub_reset
+ Publication testpub_reset
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
+--------------------------+------------+---------+---------+---------+-----------+----------
+ regress_publication_user | f | t | t | t | t | f
+(1 row)
+
+ALTER PUBLICATION testpub_reset ADD ALL 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: Setting ALL TABLES requires publication "testpub_reset" to have default values
+HINT: Either the publication has tables/schemas associated or does not have default publication options or ALL TABLES option is set.
+-- Verify that schemas associated with the publication are dropped after RESET
+\dRp+ testpub_reset
+ Publication testpub_reset
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
+--------------------------+------------+---------+---------+---------+-----------+----------
+ regress_publication_user | f | t | t | t | t | f
Tables from schemas:
"public"
@@ -1696,13 +1745,40 @@ ALTER PUBLICATION testpub_reset RESET;
regress_publication_user | f | t | t | t | t | f
(1 row)
-ALTER PUBLICATION testpub_reset SET (PUBLISH = '', PUBLISH_VIA_PARTITION_ROOT = 'true');
--- Verify that publish options and publish_via_partition_root option are reset
+ALTER PUBLICATION testpub_reset SET (PUBLISH = '');
+-- can't add except table when the publication options does not have default
+-- values
+ALTER PUBLICATION testpub_reset ADD ALL TABLES EXCEPT TABLE pub_sch1.tbl1;
+ERROR: Setting ALL TABLES requires publication "testpub_reset" to have default values
+HINT: Either the publication has tables/schemas associated or does not have default publication options or ALL TABLES option is set.
+-- Verify that publish option is reset
+\dRp+ testpub_reset
+ Publication testpub_reset
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
+--------------------------+------------+---------+---------+---------+-----------+----------
+ regress_publication_user | f | f | f | f | f | f
+(1 row)
+
+ALTER PUBLICATION testpub_reset RESET;
+\dRp+ testpub_reset
+ Publication testpub_reset
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
+--------------------------+------------+---------+---------+---------+-----------+----------
+ regress_publication_user | f | t | t | t | t | f
+(1 row)
+
+ALTER PUBLICATION testpub_reset SET (PUBLISH_VIA_PARTITION_ROOT = 'true');
+-- can't add except table when publish_via_partition_root option does not
+-- have default value
+ALTER PUBLICATION testpub_reset ADD ALL TABLES EXCEPT TABLE pub_sch1.tbl1;
+ERROR: Setting ALL TABLES requires publication "testpub_reset" to have default values
+HINT: Either the publication has tables/schemas associated or does not have default publication options or ALL TABLES option is set.
+-- Verify that publish_via_partition_root option is reset
\dRp+ testpub_reset
Publication testpub_reset
Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
--------------------------+------------+---------+---------+---------+-----------+----------
- regress_publication_user | f | f | f | f | f | t
+ regress_publication_user | f | t | t | t | t | t
(1 row)
ALTER PUBLICATION testpub_reset RESET;
diff --git a/src/test/regress/sql/publication.sql b/src/test/regress/sql/publication.sql
index 0612315488..38dc887260 100644
--- a/src/test/regress/sql/publication.sql
+++ b/src/test/regress/sql/publication.sql
@@ -89,8 +89,14 @@ 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;
+RESET client_min_messages;
+
+\dRp+ testpub_foralltables_excepttable
+
DROP TABLE testpub_tbl2;
-DROP PUBLICATION testpub_foralltables, testpub_fortable, testpub_forschema;
+DROP PUBLICATION testpub_foralltables, testpub_fortable, testpub_forschema, testpub_foralltables_excepttable;
CREATE TABLE testpub_tbl3 (a int);
CREATE TABLE testpub_tbl3a (b text) INHERITS (testpub_tbl3);
@@ -1064,22 +1070,58 @@ 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
-ALTER PUBLICATION testpub_reset ADD TABLE pub_sch1.tbl1, ALL TABLES IN SCHEMA public;
+-- should work now after resetting the publication
+ALTER PUBLICATION testpub_reset ADD ALL TABLES EXCEPT TABLE pub_sch1.tbl1;
+\dRp+ testpub_reset
+
+ALTER PUBLICATION testpub_reset RESET;
+
+ALTER PUBLICATION testpub_reset ADD TABLE pub_sch1.tbl1;
--- Verify that tables and schemas associated with the publication are dropped
--- after RESET
+-- 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 SET (PUBLISH = '', PUBLISH_VIA_PARTITION_ROOT = 'true');
+ALTER PUBLICATION testpub_reset ADD ALL 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
+ALTER PUBLICATION testpub_reset RESET;
+\dRp+ testpub_reset
+
+ALTER PUBLICATION testpub_reset SET (PUBLISH = '');
+
+-- can't add except table when the publication options does not have default
+-- values
+ALTER PUBLICATION testpub_reset ADD ALL TABLES EXCEPT TABLE pub_sch1.tbl1;
+
+-- Verify that publish option is reset
+\dRp+ testpub_reset
+ALTER PUBLICATION testpub_reset RESET;
+\dRp+ testpub_reset
+
+ALTER PUBLICATION testpub_reset SET (PUBLISH_VIA_PARTITION_ROOT = 'true');
+
+-- can't add except table when publish_via_partition_root option does not
+-- have default value
+ALTER PUBLICATION testpub_reset ADD ALL TABLES EXCEPT TABLE pub_sch1.tbl1;
--- Verify that publish options and publish_via_partition_root option are reset
+-- Verify that publish_via_partition_root option is reset
\dRp+ testpub_reset
ALTER PUBLICATION testpub_reset RESET;
\dRp+ testpub_reset
diff --git a/src/test/subscription/t/032_rep_changes_except_table.pl b/src/test/subscription/t/032_rep_changes_except_table.pl
new file mode 100644
index 0000000000..8142779f22
--- /dev/null
+++ b/src/test/subscription/t/032_rep_changes_except_table.pl
@@ -0,0 +1,86 @@
+
+# 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
+# option.
+# 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"
+);
+
+$node_publisher->wait_for_catchup('tap_sub_schema');
+
+# Also wait for initial table sync to finish
+my $synced_query =
+ "SELECT count(1) = 0 FROM pg_subscription_rel WHERE srsubstate NOT IN ('r', 's');";
+$node_subscriber->poll_query_until('postgres', $synced_query)
+ or die "Timed out while waiting for subscriber to synchronize data";
+
+# 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 tablesync is excluded for excluded tables');
+
+# Insert some data into few tables 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 new table data.
+$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.32.0
