public inbox for [email protected]
help / color / mirror / Atom feedFrom: Shlok Kyal <[email protected]>
To: Peter Smith <[email protected]>
Cc: shveta malik <[email protected]>
Cc: vignesh C <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: Support EXCEPT for ALL SEQUENCES publications
Date: Tue, 23 Jun 2026 15:03:20 +0530
Message-ID: <CANhcyEWfzDRCiny6A_swAD7WUgdb37-TA4xTAUQ3JJXK9UPBZQ@mail.gmail.com> (raw)
In-Reply-To: <CAHut+PvXpsFrNn2NOhepVOS7Zf_OvX-m71Wu+m-o_q9odYFXnA@mail.gmail.com>
References: <CANhcyEVSXyQkvmrsOWPdQqnm2J3GMyQQrKhyCJiBQzqs6AvSow@mail.gmail.com>
<CALDaNm125dv88fUDgBPBM-N-hXbF0NLqKe-ymEpMRNymUYRQAA@mail.gmail.com>
<CANhcyEUxgzaNJNNO_-12wYwGLihpuKmsMH2g4TFnRPx5AQVZmg@mail.gmail.com>
<CAJpy0uBB4N8KOrHchdgprVi2Ws1+gTcEr+bC2A_ziAHOcZcTqA@mail.gmail.com>
<CAJpy0uCAJQvBjD7qNWWGnZP_LDwS8AiUJC7YMict9UcYqH=XeQ@mail.gmail.com>
<CANhcyEU_Yq9ZJ2n5Sqa7RoHze0TD0RGxLQQgV1F6Jm2AROEh8g@mail.gmail.com>
<CANhcyEWj-ECj=WC+HD_kv27Dn6FkTngFQCVJVTVZfJnjCTKMBQ@mail.gmail.com>
<CANhcyEW03XO5tLb7opt1yQGHWJ7Ew=L65EWcdrKH=F0mUpuR3A@mail.gmail.com>
<CAHut+Ptu0Bkwnr5eetdmFhJC7SsEtKjNe_cTwOg5wF65fjyV8w@mail.gmail.com>
<CANhcyEU+4Z6NXMEk6OmctFv=_pY8K5AiazkfVmn7zxhwQO6CEQ@mail.gmail.com>
<CAHut+PvOWjKJfNorGO8whnaAtqZb-4zN8u6aOajCw3hn-tMwyA@mail.gmail.com>
<CANhcyEV_Sv+xQzsjo6hbowDbGV5J7RhFWuQQxWeUWPNPd0k1=w@mail.gmail.com>
<CAHut+Pt6O3YD8h61RCtzMrRwgvS=RDRNf4KuXAUn1WYnoFU_uQ@mail.gmail.com>
<CANhcyEU=Vi2oNRWTSph3x2884J7aTt5BTb4AzwmmY0uQAsEMMg@mail.gmail.com>
<CAHut+Pv9ru8tNLz1LaPjuG-1X5dsLd36cFbb11e=bhYrHz60CQ@mail.gmail.com>
<CANhcyEVDStqoR3qDSgsv5VEuBMzMX4YpdEfW-7VPX3c5Vf1YJA@mail.gmail.com>
<CAHut+PsFNboBR8E+NRY_3hMqdpbj3y3jX1+vQTow2khgj4t=qw@mail.gmail.com>
<CANhcyEW+aJ1rAAodQZDvCd-WpQ+b8r5wBs8H+mNdQS8URmkODg@mail.gmail.com>
<CAHut+PvXpsFrNn2NOhepVOS7Zf_OvX-m71Wu+m-o_q9odYFXnA@mail.gmail.com>
On Mon, 22 Jun 2026 at 10:05, Peter Smith <[email protected]> wrote:
>
> Hi Shlok.
>
> Some review comments for v11-0001 and v11-0002.
>
> //////////
> v11-0001
>
> ======
> doc/src/sgml/ref/create_publication.sgml
>
> (EXCEPT)
>
> 1.
> The recent commit 77b6dd9 added some more information to say:
>
> ---
> Once a table is excluded, the exclusion applies to that table
> regardless of its name or schema. Renaming the table or moving it to
> another schema using <command>ALTER TABLE ... SET SCHEMA</command> does
> not remove the exclusion.
> ---
>
> 1a
> AFAIK this same rule (the exclusion follows the object regardless of
> the schema) is going to apply also for excluding sequences. So, the
> docs should be updated to say something similar about behaviour for
> sequences.
>
Modified
> ~
>
> 1b.
> Maybe you need to add another test case to exclude some sequence, then
> alter the sequence's schema, then verify that the moved sequence is
> still excluded.
>
Added the test
> ~~~
> On Thu, Jun 18, 2026 at 11:29 PM Shlok Kyal <[email protected]> wrote:
> >
> > On Wed, 17 Jun 2026 at 12:37, Peter Smith <[email protected]> wrote:
> ...
> > > ======
> > > src/bin/psql/describe.c
> > >
> > > describeOneTableDetails:
> > >
> > > 5.
> > > printfPQExpBuffer(&buf, "/* %s */\n",
> > > _("Get publications containing this sequence"));
> > > - appendPQExpBuffer(&buf, "SELECT pubname FROM pg_catalog.pg_publication p"
> > > + appendPQExpBuffer(&buf, "SELECT p.pubname FROM pg_catalog.pg_publication p"
> > > "\nWHERE p.puballsequences"
> > > "\n AND pg_catalog.pg_relation_is_publishable('%s')"
> > > + "\n AND NOT EXISTS (\n"
> > > + " SELECT 1\n"
> > > + " FROM pg_catalog.pg_publication_rel pr\n"
> > > + " WHERE pr.prpubid = p.oid AND\n"
> > > + " pr.prrelid = '%s')"
> > > "\nORDER BY 1",
> > > - oid);
> > > + oid, oid);
> > >
> > > 5a.
> > > IIUC, the "NOT EXISTS" part is for skipping the excluded sequences.
> > > And, you don't say "AND pr.prexcept" because it is redundant since
> > > the only way for a SEQUENCE to be individually in pg_publication_rel
> > > is if it is an excluded sequence.
> > >
> > > Is my understanding correct? I think it is too subtle -- it might be
> > > better to say "AND pr.prexcept" even if it is not strictly needed.
> > >
> > Yes, your understanding is correct.
> > For ALL SEQUENCES publication, the pg_publication_rel will have the
> > entry only if the sequence is specified in the EXCEPT list.
> > I didn't add "AND pr.prexcept" because the corresponding code for ALL
> > TABLES publications also does not use "AND pr.prexcept".
> ...
> > I think it will be consistent with EXCEPT (TABLE .. ) case. So, I
> > think we should not add "AND pr.prexcept".
>
> That’s OK, but I felt if you are going to omit the 'prexcept', then
> perhaps there should be a comment to explain (in all places) why it is
> ok to do that.
>
Added the comments
> //////////
> v11-0002.
>
> ======
> doc/src/sgml/ref/alter_publication.sgml
>
> 1.
> ...or marks the publication as FOR ALL SEQUENCES or FOR ALL TABLES,
> optionally using EXCEPT to exclude specific tables or sequences.
>
> ~
>
> That current documentation sentence seems muddled because the
> publication types are not in the same as the order of the excluded
> types.
>
> IMO, alternatives below are better:
> a) ...FOR ALL SEQUENCES or FOR ALL TABLES, optionally using EXCEPT to
> exclude specific sequences or tables.
> b) ...FOR ALL TABLES or FOR ALL SEQUENCES, optionally using EXCEPT to
> exclude specific tables or sequences.
>
> (I preferred b, since everywhere else seems to be ordered
> tables/sequences not sequences/tables.).
>
I also think (b) is better.
I have addressed the comments and attached the updated v12 patch
Thanks,
Shlok Kyal
Attachments:
[application/octet-stream] v12-0002-Support-EXCEPT-for-ALL-SEQUENCES-in-ALTER-PUBLIC.patch (27.8K, ../CANhcyEWfzDRCiny6A_swAD7WUgdb37-TA4xTAUQ3JJXK9UPBZQ@mail.gmail.com/2-v12-0002-Support-EXCEPT-for-ALL-SEQUENCES-in-ALTER-PUBLIC.patch)
download | inline diff:
From b294b39449ebf35e2a92be9491396b9178be8c4b Mon Sep 17 00:00:00 2001
From: Shlok Kyal <[email protected]>
Date: Tue, 23 Jun 2026 14:29:14 +0530
Subject: [PATCH v12 2/2] Support EXCEPT for ALL SEQUENCES in ALTER PUBLICATION
Extend ALTER PUBLICATION to support an EXCEPT clause when using
ALL SEQUENCES, allowing specific sequences to be excluded from the
publication.
If the EXCEPT clause is specified, the existing exclusion list for the
publication is replaced with the provided sequences. If the EXCEPT
clause is omitted, any existing exclusions for sequences are cleared.
Example:
ALTER PUBLICATION pub1 SET ALL SEQUENCES;
This clears any existing sequence exclusions for the publication.
ALTER PUBLICATION pub1 SET ALL SEQUENCES EXCEPT (SEQUENCE s1, s2);
This sets the exclusion list to the specified sequences.
---
doc/src/sgml/ref/alter_publication.sgml | 51 ++++-
src/backend/catalog/pg_publication.c | 36 ++-
src/backend/commands/publicationcmds.c | 265 ++++++++++++----------
src/bin/psql/tab-complete.in.c | 14 ++
src/include/catalog/pg_publication.h | 6 +-
src/test/regress/expected/publication.out | 16 ++
src/test/regress/sql/publication.sql | 6 +
7 files changed, 245 insertions(+), 149 deletions(-)
diff --git a/doc/src/sgml/ref/alter_publication.sgml b/doc/src/sgml/ref/alter_publication.sgml
index 52114a16a39..d82ff3079db 100644
--- a/doc/src/sgml/ref/alter_publication.sgml
+++ b/doc/src/sgml/ref/alter_publication.sgml
@@ -36,7 +36,7 @@ ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RENAME TO <r
<phrase>and <replaceable class="parameter">publication_all_object</replaceable> is one of:</phrase>
ALL TABLES [ EXCEPT ( <replaceable class="parameter">except_table_object</replaceable> [, ... ] ) ]
- ALL SEQUENCES
+ ALL SEQUENCES [ EXCEPT ( <replaceable class="parameter">except_sequence_object</replaceable> [, ... ] ) ]
<phrase>and <replaceable class="parameter">publication_drop_object</replaceable> is one of:</phrase>
@@ -54,6 +54,10 @@ ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RENAME TO <r
<phrase>and <replaceable class="parameter">table_object</replaceable> is:</phrase>
[ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ]
+
+<phrase>and <replaceable class="parameter">except_sequence_object</replaceable> is:</phrase>
+
+ SEQUENCE <replaceable class="parameter">sequence_name</replaceable> [, ... ]
</synopsis>
</refsynopsisdiv>
@@ -73,9 +77,9 @@ ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RENAME TO <r
<para>
The third variant either modifies the included tables/schemas
- or marks the publication as <literal>FOR ALL SEQUENCES</literal> or
- <literal>FOR ALL TABLES</literal>, optionally using
- <literal>EXCEPT</literal> to exclude specific tables. The
+ or marks the publication as <literal>FOR ALL TABLES</literal> or
+ <literal>FOR ALL SEQUENCES</literal>, optionally using
+ <literal>EXCEPT</literal> to exclude specific tables or sequences. The
<literal>SET ALL TABLES</literal> clause can transform an empty publication,
or one defined for <literal>ALL SEQUENCES</literal> (or both
<literal>ALL TABLES</literal> and <literal>ALL SEQUENCES</literal>), into
@@ -86,11 +90,15 @@ ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RENAME TO <r
publication defined for <literal>ALL SEQUENCES</literal>. In addition,
<literal>SET ALL TABLES</literal> can be used to update the tables specified
in the <literal>EXCEPT</literal> clause of a
- <literal>FOR ALL TABLES</literal> publication. If <literal>EXCEPT</literal>
- is specified with a list of tables, the existing exclusion list is replaced
- with the specified tables. If <literal>EXCEPT</literal> is omitted, the
- existing exclusion list is cleared. The <literal>SET</literal> clause, when
- used with a publication defined with <literal>FOR TABLE</literal> or
+ <literal>FOR ALL TABLES</literal> publication and
+ <literal>SET ALL SEQUENCES</literal> can be used to update the sequences
+ specified in the <literal>EXCEPT</literal> clause of a
+ <literal>FOR ALL SEQUENCES</literal> publication. If
+ <literal>EXCEPT</literal> is specified with a list of tables or sequences,
+ the existing exclusion list is replaced with the specified tables or
+ sequences. If <literal>EXCEPT</literal> is omitted, the existing exclusion
+ list is cleared. The <literal>SET</literal> clause, when used with a
+ publication defined with <literal>FOR TABLE</literal> or
<literal>FOR TABLES IN SCHEMA</literal>, replaces the list of tables/schemas
in the publication with the specified list; the existing tables or schemas
that were present in the publication will be removed.
@@ -273,10 +281,31 @@ ALTER PUBLICATION mypublication SET ALL TABLES EXCEPT (TABLE users, departments)
</programlisting></para>
<para>
- Reset the publication to be a <literal>FOR ALL TABLES</literal> publication
- with no excluded tables:
+ Reset the publication to be <literal>FOR ALL TABLES</literal> with no
+ exclusions:
<programlisting>
ALTER PUBLICATION mypublication SET ALL TABLES;
+</programlisting></para>
+
+ <para>
+ Reset the publication to be <literal>ALL SEQUENCES</literal> with no
+ exclusions:
+<programlisting>
+ALTER PUBLICATION mypublication SET ALL SEQUENCES;
+</programlisting></para>
+
+ <para>
+ Replace the sequence list in the publication's <literal>EXCEPT</literal>
+ clause:
+<programlisting>
+ALTER PUBLICATION mypublication SET ALL SEQUENCES EXCEPT (SEQUENCE seq1, seq2);
+</programlisting></para>
+
+ <para>
+ Replace the table and sequence list in the publication's
+ <literal>EXCEPT</literal> clauses:
+<programlisting>
+ALTER PUBLICATION mypublication SET ALL TABLES EXCEPT (TABLE users, departments), ALL SEQUENCES EXCEPT (SEQUENCE seq1, seq2);
</programlisting></para>
<para>
diff --git a/src/backend/catalog/pg_publication.c b/src/backend/catalog/pg_publication.c
index f03b85fa34b..34a8ce4d728 100644
--- a/src/backend/catalog/pg_publication.c
+++ b/src/backend/catalog/pg_publication.c
@@ -946,7 +946,7 @@ GetRelationExcludedPublications(Oid relid)
*/
static List *
get_publication_relations(Oid pubid, PublicationPartOpt pub_partopt,
- bool except_flag)
+ bool except_flag, char pubrelkind)
{
List *result;
Relation pubrelsrel;
@@ -954,6 +954,8 @@ get_publication_relations(Oid pubid, PublicationPartOpt pub_partopt,
SysScanDesc scan;
HeapTuple tup;
+ Assert(pubrelkind == RELKIND_RELATION || pubrelkind == RELKIND_SEQUENCE);
+
/* Find all relations associated with the publication. */
pubrelsrel = table_open(PublicationRelRelationId, AccessShareLock);
@@ -973,8 +975,15 @@ get_publication_relations(Oid pubid, PublicationPartOpt pub_partopt,
pubrel = (Form_pg_publication_rel) GETSTRUCT(tup);
if (except_flag == pubrel->prexcept)
- result = GetPubPartitionOptionRelations(result, pub_partopt,
- pubrel->prrelid);
+ {
+ char relkind = get_rel_relkind(pubrel->prrelid);
+
+ if ((pubrelkind == RELKIND_RELATION && relkind == RELKIND_RELATION) ||
+ (pubrelkind == RELKIND_RELATION && relkind == RELKIND_PARTITIONED_TABLE) ||
+ (pubrelkind == RELKIND_SEQUENCE && relkind == RELKIND_SEQUENCE))
+ result = GetPubPartitionOptionRelations(result, pub_partopt,
+ pubrel->prrelid);
+ }
}
systable_endscan(scan);
@@ -990,15 +999,16 @@ get_publication_relations(Oid pubid, PublicationPartOpt pub_partopt,
/*
* Gets list of relation oids that are associated with a publication.
*
- * This should only be used FOR TABLE publications, the FOR ALL TABLES/SEQUENCES
- * should use GetAllPublicationRelations().
+ * This is mainly used for FOR TABLE publications and must not be called for
+ * ALL TABLES publications. For ALL SEQUENCES publications, the result is an
+ * empty list.
*/
List *
GetIncludedPublicationRelations(Oid pubid, PublicationPartOpt pub_partopt)
{
Assert(!GetPublication(pubid)->alltables);
- return get_publication_relations(pubid, pub_partopt, false);
+ return get_publication_relations(pubid, pub_partopt, false, RELKIND_RELATION);
}
/*
@@ -1006,7 +1016,8 @@ GetIncludedPublicationRelations(Oid pubid, PublicationPartOpt pub_partopt)
* 'FOR ALL TABLES' or a 'FOR ALL SEQUENCES' publication.
*/
List *
-GetExcludedPublicationRelations(Oid pubid, PublicationPartOpt pub_partopt)
+GetExcludedPublicationRelations(Oid pubid, PublicationPartOpt pub_partopt,
+ char pubrelkind)
{
#ifdef USE_ASSERT_CHECKING
Publication *pub = GetPublication(pubid);
@@ -1014,7 +1025,7 @@ GetExcludedPublicationRelations(Oid pubid, PublicationPartOpt pub_partopt)
Assert(pub->alltables || pub->allsequences);
#endif
- return get_publication_relations(pubid, pub_partopt, true);
+ return get_publication_relations(pubid, pub_partopt, true, pubrelkind);
}
/*
@@ -1068,7 +1079,7 @@ GetAllTablesPublications(void)
* it excludes sequences specified in the EXCEPT clause.
*/
List *
-GetAllPublicationRelations(Oid pubid, char relkind, bool pubviaroot)
+GetAllPublicationRelations(Oid pubid, char pubrelkind, bool pubviaroot)
{
Relation classRel;
ScanKeyData key[1];
@@ -1077,18 +1088,19 @@ GetAllPublicationRelations(Oid pubid, char relkind, bool pubviaroot)
List *result = NIL;
List *exceptlist = NIL;
- Assert(!(relkind == RELKIND_SEQUENCE && pubviaroot));
+ Assert(!(pubrelkind == RELKIND_SEQUENCE && pubviaroot));
exceptlist = GetExcludedPublicationRelations(pubid, pubviaroot ?
PUBLICATION_PART_ROOT :
- PUBLICATION_PART_LEAF);
+ PUBLICATION_PART_LEAF,
+ pubrelkind);
classRel = table_open(RelationRelationId, AccessShareLock);
ScanKeyInit(&key[0],
Anum_pg_class_relkind,
BTEqualStrategyNumber, F_CHAREQ,
- CharGetDatum(relkind));
+ CharGetDatum(pubrelkind));
scan = table_beginscan_catalog(classRel, 1, key);
diff --git a/src/backend/commands/publicationcmds.c b/src/backend/commands/publicationcmds.c
index 76cd8f58f13..16b004a7fd3 100644
--- a/src/backend/commands/publicationcmds.c
+++ b/src/backend/commands/publicationcmds.c
@@ -66,7 +66,7 @@ static void CloseRelationList(List *rels);
static void LockSchemaList(List *schemalist);
static void PublicationAddRelations(Oid pubid, List *rels, bool if_not_exists,
AlterPublicationStmt *stmt, char pubrelkind);
-static void PublicationDropTables(Oid pubid, List *rels, bool missing_ok);
+static void PublicationDropRelations(Oid pubid, List *rels, bool missing_ok);
static void PublicationAddSchemas(Oid pubid, List *schemas, bool if_not_exists,
AlterPublicationStmt *stmt);
static void PublicationDropSchemas(Oid pubid, List *schemas, bool missing_ok);
@@ -1254,26 +1254,131 @@ InvalidatePublicationRels(List *relids)
}
/*
- * Add or remove table to/from publication.
+ * Return the list of tables/sequences to be removed from a publication during
+ * ALTER PUBLICATION ... SET.
+ *
+ * 'rels' contains the relations specified by the SET command, and 'oldrelids'
+ * contains the existing relations in the publication. The function returns the
+ * existing relations that are not present in 'rels' and therefore need to be
+ * removed.
+ */
+static List *
+get_delete_rels(Oid pubid, List *rels, List *oldrelids)
+{
+ List *delrels = NIL;
+
+ foreach_oid(oldrelid, oldrelids)
+ {
+ ListCell *newlc;
+ PublicationRelInfo *oldrel;
+ bool found = false;
+ HeapTuple rftuple;
+ Node *oldrelwhereclause = NULL;
+ Bitmapset *oldcolumns = NULL;
+
+ /* Look up the cache for the old relmap */
+ rftuple = SearchSysCache2(PUBLICATIONRELMAP,
+ ObjectIdGetDatum(oldrelid),
+ ObjectIdGetDatum(pubid));
+
+ /*
+ * See if the existing relation currently has a WHERE clause or a
+ * column list. We need to compare those too.
+ */
+ if (HeapTupleIsValid(rftuple))
+ {
+ bool isnull = true;
+ Datum whereClauseDatum;
+ Datum columnListDatum;
+
+ /* Load the WHERE clause for this table. */
+ whereClauseDatum = SysCacheGetAttr(PUBLICATIONRELMAP, rftuple,
+ Anum_pg_publication_rel_prqual,
+ &isnull);
+ if (!isnull)
+ oldrelwhereclause = stringToNode(TextDatumGetCString(whereClauseDatum));
+
+ /* Transform the int2vector column list to a bitmap. */
+ columnListDatum = SysCacheGetAttr(PUBLICATIONRELMAP, rftuple,
+ Anum_pg_publication_rel_prattrs,
+ &isnull);
+
+ if (!isnull)
+ oldcolumns = pub_collist_to_bitmapset(NULL, columnListDatum, NULL);
+
+ ReleaseSysCache(rftuple);
+ }
+
+ /*
+ * Check if any of the new set of relations matches with the existing
+ * relations in the publication. Additionally, if the relation has an
+ * associated WHERE clause, check the WHERE expressions also match.
+ * Same for the column list. Drop the rest.
+ */
+ foreach(newlc, rels)
+ {
+ PublicationRelInfo *newpubrel;
+ Oid newrelid;
+ Bitmapset *newcolumns = NULL;
+
+ newpubrel = (PublicationRelInfo *) lfirst(newlc);
+ newrelid = RelationGetRelid(newpubrel->relation);
+
+ /*
+ * Validate the column list. If the column list or WHERE clause
+ * changes, then the validation done here will be duplicated
+ * inside PublicationAddRelations(). The validation is cheap
+ * enough that that seems harmless.
+ */
+ newcolumns = pub_collist_validate(newpubrel->relation,
+ newpubrel->columns);
+
+ found = (newrelid == oldrelid) &&
+ equal(oldrelwhereclause, newpubrel->whereClause) &&
+ bms_equal(oldcolumns, newcolumns);
+
+ if (found)
+ break;
+ }
+
+ /*
+ * Add non-matching relations to the drop list. The relation will be
+ * dropped irrespective of the column list and WHERE clause.
+ */
+ if (!found)
+ {
+ oldrel = palloc0_object(PublicationRelInfo);
+ oldrel->relation = table_open(oldrelid,
+ ShareUpdateExclusiveLock);
+ delrels = lappend(delrels, oldrel);
+ }
+ }
+
+ return delrels;
+}
+
+/*
+ * Add or remove table or sequence to/from publication.
*/
static void
-AlterPublicationTables(AlterPublicationStmt *stmt, HeapTuple tup,
- List *tables, const char *queryString,
- bool publish_schema)
+AlterPublicationRelations(AlterPublicationStmt *stmt, HeapTuple tup,
+ List *tables, List *sequences, const char *queryString,
+ bool publish_schema)
{
List *rels = NIL;
+ List *seqs = NIL;
Form_pg_publication pubform = (Form_pg_publication) GETSTRUCT(tup);
Oid pubid = pubform->oid;
/*
- * Nothing to do if no objects, except in SET: for that it is quite
- * possible that user has not specified any tables in which case we need
- * to remove all the existing tables.
+ * Nothing to do if no objects were specified, unless this is a SET
+ * command, which may need to remove all existing tables and sequences.
*/
- if (!tables && stmt->action != AP_SetObjects)
+ if (!tables && !sequences && stmt->action != AP_SetObjects)
return;
rels = OpenRelationList(tables);
+ seqs = OpenRelationList(sequences);
if (stmt->action == AP_AddObjects)
{
@@ -1287,29 +1392,33 @@ AlterPublicationTables(AlterPublicationStmt *stmt, HeapTuple tup,
PublicationAddRelations(pubid, rels, false, stmt, RELKIND_RELATION);
}
else if (stmt->action == AP_DropObjects)
- PublicationDropTables(pubid, rels, false);
+ PublicationDropRelations(pubid, rels, false);
else /* AP_SetObjects */
{
List *oldrelids = NIL;
+ List *oldseqids = NIL;
List *delrels = NIL;
- ListCell *oldlc;
if (stmt->for_all_tables || stmt->for_all_sequences)
{
/*
- * In FOR ALL TABLES mode, relations are tracked as exclusions
- * (EXCEPT clause). Fetch the current excluded relations so they
- * can be reconciled with the specified EXCEPT list.
+ * In FOR ALL TABLES/ SEQUENCES mode, relations are tracked as
+ * exclusions (EXCEPT clause). Fetch the current excluded
+ * relations so they can be reconciled with the specified EXCEPT
+ * list.
*
* This applies only if the existing publication is already
- * defined as FOR ALL TABLES; otherwise, there are no exclusion
- * entries to process.
+ * defined as FOR ALL TABLES/ FOR ALL SEQUENCES; otherwise, there
+ * are no exclusion entries to process.
*/
if (pubform->puballtables)
- {
oldrelids = GetExcludedPublicationRelations(pubid,
- PUBLICATION_PART_ROOT);
- }
+ PUBLICATION_PART_ROOT,
+ RELKIND_RELATION);
+ if (pubform->puballsequences)
+ oldseqids = GetExcludedPublicationRelations(pubid,
+ PUBLICATION_PART_ROOT,
+ RELKIND_SEQUENCE);
}
else
{
@@ -1322,118 +1431,25 @@ AlterPublicationTables(AlterPublicationStmt *stmt, HeapTuple tup,
pubform->pubviaroot);
}
- /*
- * To recreate the relation list for the publication, look for
- * existing relations that do not need to be dropped.
- */
- foreach(oldlc, oldrelids)
- {
- Oid oldrelid = lfirst_oid(oldlc);
- ListCell *newlc;
- PublicationRelInfo *oldrel;
- bool found = false;
- HeapTuple rftuple;
- Node *oldrelwhereclause = NULL;
- Bitmapset *oldcolumns = NULL;
-
- /* look up the cache for the old relmap */
- rftuple = SearchSysCache2(PUBLICATIONRELMAP,
- ObjectIdGetDatum(oldrelid),
- ObjectIdGetDatum(pubid));
-
- /*
- * See if the existing relation currently has a WHERE clause or a
- * column list. We need to compare those too.
- */
- if (HeapTupleIsValid(rftuple))
- {
- bool isnull = true;
- Datum whereClauseDatum;
- Datum columnListDatum;
-
- /* Load the WHERE clause for this table. */
- whereClauseDatum = SysCacheGetAttr(PUBLICATIONRELMAP, rftuple,
- Anum_pg_publication_rel_prqual,
- &isnull);
- if (!isnull)
- oldrelwhereclause = stringToNode(TextDatumGetCString(whereClauseDatum));
-
- /* Transform the int2vector column list to a bitmap. */
- columnListDatum = SysCacheGetAttr(PUBLICATIONRELMAP, rftuple,
- Anum_pg_publication_rel_prattrs,
- &isnull);
-
- if (!isnull)
- oldcolumns = pub_collist_to_bitmapset(NULL, columnListDatum, NULL);
-
- ReleaseSysCache(rftuple);
- }
-
- foreach(newlc, rels)
- {
- PublicationRelInfo *newpubrel;
- Oid newrelid;
- Bitmapset *newcolumns = NULL;
-
- newpubrel = (PublicationRelInfo *) lfirst(newlc);
- newrelid = RelationGetRelid(newpubrel->relation);
-
- /*
- * Validate the column list. If the column list or WHERE
- * clause changes, then the validation done here will be
- * duplicated inside PublicationAddRelations(). The
- * validation is cheap enough that that seems harmless.
- */
- newcolumns = pub_collist_validate(newpubrel->relation,
- newpubrel->columns);
-
- /*
- * Check if any of the new set of relations matches with the
- * existing relations in the publication. Additionally, if the
- * relation has an associated WHERE clause, check the WHERE
- * expressions also match. Same for the column list. Drop the
- * rest.
- */
- if (newrelid == oldrelid)
- {
- if (equal(oldrelwhereclause, newpubrel->whereClause) &&
- bms_equal(oldcolumns, newcolumns))
- {
- found = true;
- break;
- }
- }
- }
-
- /*
- * Add the non-matched relations to a list so that they can be
- * dropped.
- */
- if (!found)
- {
- oldrel = palloc_object(PublicationRelInfo);
- oldrel->whereClause = NULL;
- oldrel->columns = NIL;
- oldrel->except = false;
- oldrel->relation = table_open(oldrelid,
- ShareUpdateExclusiveLock);
- delrels = lappend(delrels, oldrel);
- }
- }
+ /* Get tables and sequences to be dropped */
+ delrels = get_delete_rels(pubid, rels, oldrelids);
+ delrels = list_concat(delrels, get_delete_rels(pubid, seqs, oldseqids));
/* And drop them. */
- PublicationDropTables(pubid, delrels, true);
+ PublicationDropRelations(pubid, delrels, true);
/*
* Don't bother calculating the difference for adding, we'll catch and
* skip existing ones when doing catalog update.
*/
PublicationAddRelations(pubid, rels, true, stmt, RELKIND_RELATION);
+ PublicationAddRelations(pubid, seqs, true, stmt, RELKIND_SEQUENCE);
CloseRelationList(delrels);
}
CloseRelationList(rels);
+ CloseRelationList(seqs);
}
/*
@@ -1709,8 +1725,9 @@ AlterPublication(ParseState *pstate, AlterPublicationStmt *stmt)
ObjectsInPublicationToOids(stmt->pubobjects, pstate, &relations,
&excepttbls, &exceptseqs, &schemaidlist);
- /* EXCEPT clause is not supported with ALTER PUBLICATION */
- Assert(exceptseqs == NIL);
+ /* EXCEPT clause is only supported for ALTER PUBLICATION ... SET */
+ Assert((excepttbls == NIL && exceptseqs == NIL) ||
+ stmt->action == AP_SetObjects);
CheckAlterPublication(stmt, tup, relations, schemaidlist);
@@ -1734,8 +1751,8 @@ AlterPublication(ParseState *pstate, AlterPublicationStmt *stmt)
stmt->pubname));
relations = list_concat(relations, excepttbls);
- AlterPublicationTables(stmt, tup, relations, pstate->p_sourcetext,
- schemaidlist != NIL);
+ AlterPublicationRelations(stmt, tup, relations, exceptseqs,
+ pstate->p_sourcetext, schemaidlist != NIL);
AlterPublicationSchemas(stmt, tup, schemaidlist);
AlterPublicationAllFlags(stmt, rel, tup);
}
@@ -2090,10 +2107,10 @@ PublicationAddRelations(Oid pubid, List *rels, bool if_not_exists,
}
/*
- * Remove listed tables from the publication.
+ * Remove listed relations from the publication.
*/
static void
-PublicationDropTables(Oid pubid, List *rels, bool missing_ok)
+PublicationDropRelations(Oid pubid, List *rels, bool missing_ok)
{
ObjectAddress obj;
ListCell *lc;
diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index 12d276cbb65..aaf03e3a94a 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -2351,6 +2351,20 @@ match_previous_words(int pattern_id,
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables);
else if (Matches("ALTER", "PUBLICATION", MatchAny, "SET", "ALL", "TABLES", "EXCEPT", "(", "TABLE", MatchAnyN) && !ends_with(prev_wd, ','))
COMPLETE_WITH(")");
+ else if (Matches("ALTER", "PUBLICATION", MatchAny, "SET", "ALL", "SEQUENCES", "EXCEPT", "("))
+ COMPLETE_WITH("SEQUENCE");
+ else if (Matches("ALTER", "PUBLICATION", MatchAny, "SET", "ALL", "SEQUENCES"))
+ COMPLETE_WITH("EXCEPT ( SEQUENCE");
+ else if (Matches("ALTER", "PUBLICATION", MatchAny, "SET", "ALL", "SEQUENCES", "EXCEPT"))
+ COMPLETE_WITH("( SEQUENCE");
+ else if (Matches("ALTER", "PUBLICATION", MatchAny, "SET", "ALL", "SEQUENCES", "EXCEPT", "("))
+ COMPLETE_WITH("SEQUENCE");
+ else if (Matches("ALTER", "PUBLICATION", MatchAny, "SET", "ALL", "SEQUENCES", "EXCEPT", "(", "SEQUENCE"))
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_sequences);
+ else if (Matches("ALTER", "PUBLICATION", MatchAny, "SET", "ALL", "SEQUENCES", "EXCEPT", "(", "SEQUENCE", MatchAnyN) && ends_with(prev_wd, ','))
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_sequences);
+ else if (Matches("ALTER", "PUBLICATION", MatchAny, "SET", "ALL", "SEQUENCES", "EXCEPT", "(", "SEQUENCE", MatchAnyN) && !ends_with(prev_wd, ','))
+ COMPLETE_WITH(")");
else if (Matches("ALTER", "PUBLICATION", MatchAny, "ADD|DROP|SET", "TABLES", "IN", "SCHEMA"))
COMPLETE_WITH_QUERY_PLUS(Query_for_list_of_schemas
" AND nspname NOT LIKE E'pg\\\\_%%'",
diff --git a/src/include/catalog/pg_publication.h b/src/include/catalog/pg_publication.h
index 49c29a87630..21a1527bcc7 100644
--- a/src/include/catalog/pg_publication.h
+++ b/src/include/catalog/pg_publication.h
@@ -178,9 +178,11 @@ typedef enum PublicationPartOpt
extern List *GetIncludedPublicationRelations(Oid pubid,
PublicationPartOpt pub_partopt);
extern List *GetExcludedPublicationRelations(Oid pubid,
- PublicationPartOpt pub_partopt);
+ PublicationPartOpt pub_partopt,
+ char pubrelkind);
extern List *GetAllTablesPublications(void);
-extern List *GetAllPublicationRelations(Oid pubid, char relkind, bool pubviaroot);
+extern List *GetAllPublicationRelations(Oid pubid, char pubrelkind,
+ bool pubviaroot);
extern List *GetPublicationSchemas(Oid pubid);
extern List *GetSchemaPublications(Oid schemaid);
extern List *GetSchemaPublicationRelations(Oid schemaid,
diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out
index c1a5fea2a96..206aeb181c6 100644
--- a/src/test/regress/expected/publication.out
+++ b/src/test/regress/expected/publication.out
@@ -575,16 +575,32 @@ Except sequences:
"pub_test.regress_pub_seq2"
"public.regress_pub_seq0"
+-- Modify the sequence list in the EXCEPT clause
+ALTER PUBLICATION regress_pub_forallsequences3 SET ALL SEQUENCES EXCEPT (SEQUENCE regress_pub_seq0);
+\dRp+ regress_pub_forallsequences3
+ Publication regress_pub_forallsequences3
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+-------------
+ regress_publication_user | f | t | t | t | t | t | none | f |
+Except sequences:
+ "public.regress_pub_seq0"
+
RESET client_min_messages;
-- fail - sequence object is specified in EXCEPT table list
CREATE PUBLICATION regress_pub_forallsequences4 FOR ALL TABLES EXCEPT (TABLE regress_pub_seq0);
ERROR: cannot specify "public.regress_pub_seq0" in the publication EXCEPT (TABLE) clause
DETAIL: This operation is not supported for sequences.
+ALTER PUBLICATION regress_pub_forallsequences3 SET ALL TABLES EXCEPT (TABLE regress_pub_seq0);
+ERROR: cannot specify "public.regress_pub_seq0" in the publication EXCEPT (TABLE) clause
+DETAIL: This operation is not supported for sequences.
-- fail - table object is specified in EXCEPT sequence list
CREATE TABLE tab_seq(a int);
CREATE PUBLICATION regress_pub_forallsequences4 FOR ALL SEQUENCES EXCEPT (SEQUENCE tab_seq);
ERROR: cannot specify "public.tab_seq" in the publication EXCEPT (SEQUENCE) clause
DETAIL: This operation is not supported for tables.
+ALTER PUBLICATION regress_pub_forallsequences3 SET ALL SEQUENCES EXCEPT (SEQUENCE tab_seq);
+ERROR: cannot specify "public.tab_seq" in the publication EXCEPT (SEQUENCE) clause
+DETAIL: This operation is not supported for tables.
-- Test combination of ALL SEQUENCES and ALL TABLES with EXCEPT clause
SET client_min_messages = 'ERROR';
CREATE PUBLICATION regress_pub_for_allsequences_alltables1 FOR ALL TABLES EXCEPT (TABLE testpub_tbl1), ALL SEQUENCES EXCEPT (SEQUENCE regress_pub_seq0);
diff --git a/src/test/regress/sql/publication.sql b/src/test/regress/sql/publication.sql
index 0423a77d5de..d72fc40d404 100644
--- a/src/test/regress/sql/publication.sql
+++ b/src/test/regress/sql/publication.sql
@@ -266,14 +266,20 @@ CREATE PUBLICATION regress_pub_forallsequences3 FOR ALL SEQUENCES EXCEPT (SEQUEN
-- another schema.
ALTER SEQUENCE regress_pub_seq2 SET SCHEMA pub_test;
\dRp+ regress_pub_forallsequences3
+
+-- Modify the sequence list in the EXCEPT clause
+ALTER PUBLICATION regress_pub_forallsequences3 SET ALL SEQUENCES EXCEPT (SEQUENCE regress_pub_seq0);
+\dRp+ regress_pub_forallsequences3
RESET client_min_messages;
-- fail - sequence object is specified in EXCEPT table list
CREATE PUBLICATION regress_pub_forallsequences4 FOR ALL TABLES EXCEPT (TABLE regress_pub_seq0);
+ALTER PUBLICATION regress_pub_forallsequences3 SET ALL TABLES EXCEPT (TABLE regress_pub_seq0);
-- fail - table object is specified in EXCEPT sequence list
CREATE TABLE tab_seq(a int);
CREATE PUBLICATION regress_pub_forallsequences4 FOR ALL SEQUENCES EXCEPT (SEQUENCE tab_seq);
+ALTER PUBLICATION regress_pub_forallsequences3 SET ALL SEQUENCES EXCEPT (SEQUENCE tab_seq);
-- Test combination of ALL SEQUENCES and ALL TABLES with EXCEPT clause
SET client_min_messages = 'ERROR';
--
2.34.1
[application/octet-stream] v12-0001-Support-EXCEPT-for-ALL-SEQUENCES-in-CREATE-PUBLI.patch (61.4K, ../CANhcyEWfzDRCiny6A_swAD7WUgdb37-TA4xTAUQ3JJXK9UPBZQ@mail.gmail.com/3-v12-0001-Support-EXCEPT-for-ALL-SEQUENCES-in-CREATE-PUBLI.patch)
download | inline diff:
From df6693da56d37a99927e0e0f56189743e957b6da Mon Sep 17 00:00:00 2001
From: Shlok Kyal <[email protected]>
Date: Tue, 26 May 2026 10:51:26 +0530
Subject: [PATCH v12 1/2] Support EXCEPT for ALL SEQUENCES in CREATE
PUBLICATION
Extend CREATE PUBLICATION ... FOR ALL SEQUENCES to support the
EXCEPT syntax. This allows one or more sequences to be excluded. The
publisher will not send the data of excluded sequences to the subscriber.
Example:
CREATE PUBLICATION pub1 FOR ALL SEQUENCES EXCEPT (SEQUENCE s1, s2);
---
doc/src/sgml/catalogs.sgml | 8 +-
doc/src/sgml/logical-replication.sgml | 8 +-
doc/src/sgml/ref/create_publication.sgml | 46 ++++++---
src/backend/catalog/pg_publication.c | 70 +++++++++-----
src/backend/commands/publicationcmds.c | 112 +++++++++++++---------
src/backend/parser/gram.y | 111 +++++++++++++--------
src/bin/pg_dump/pg_dump.c | 57 ++++++++---
src/bin/pg_dump/pg_dump.h | 1 +
src/bin/pg_dump/t/002_pg_dump.pl | 22 +++++
src/bin/psql/describe.c | 93 ++++++++++++++++--
src/bin/psql/tab-complete.in.c | 12 +++
src/include/catalog/pg_publication.h | 7 +-
src/include/nodes/parsenodes.h | 11 ++-
src/test/regress/expected/publication.out | 72 +++++++++++++-
src/test/regress/sql/publication.sql | 33 ++++++-
src/test/subscription/t/037_except.pl | 75 +++++++++++++++
src/tools/pgindent/typedefs.list | 2 +-
17 files changed, 582 insertions(+), 158 deletions(-)
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 4b474c13917..43a3566dda2 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -7099,7 +7099,7 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l
(references <link linkend="catalog-pg-class"><structname>pg_class</structname></link>.<structfield>oid</structfield>)
</para>
<para>
- Reference to table
+ Reference to table or sequence
</para></entry>
</row>
@@ -7108,8 +7108,8 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l
<structfield>prexcept</structfield> <type>bool</type>
</para>
<para>
- True if the table is excluded from the publication. See
- <link linkend="sql-createpublication-params-for-except-table"><literal>EXCEPT</literal></link>.
+ True if the table or sequence is excluded from the publication. See
+ <link linkend="sql-createpublication-params-for-except"><literal>EXCEPT</literal></link>.
</para></entry>
</row>
@@ -7118,7 +7118,7 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l
<structfield>prqual</structfield> <type>pg_node_tree</type>
</para>
<para>Expression tree (in <function>nodeToString()</function>
- representation) for the relation's publication qualifying condition. Null
+ representation) for the table's publication qualifying condition. Null
if there is no publication qualifying condition.</para></entry>
</row>
diff --git a/doc/src/sgml/logical-replication.sgml b/doc/src/sgml/logical-replication.sgml
index 9e7868487de..f55ca9ac71a 100644
--- a/doc/src/sgml/logical-replication.sgml
+++ b/doc/src/sgml/logical-replication.sgml
@@ -118,8 +118,12 @@
synchronized at any time. For more information, see
<xref linkend="logical-replication-sequences"/>. When a publication is
created with <literal>FOR ALL TABLES</literal>, a table or set of tables can
- be explicitly excluded from publication using the
- <link linkend="sql-createpublication-params-for-except-table"><literal>EXCEPT</literal></link>
+ be explicitly excluded from the publication using the
+ <link linkend="sql-createpublication-params-for-except"><literal>EXCEPT</literal></link>
+ clause. Similarly, when a publication is created with
+ <literal>FOR ALL SEQUENCES</literal>, a sequence or set of sequences can be
+ explicitly excluded from the publication using the
+ <link linkend="sql-createpublication-params-for-except"><literal>EXCEPT</literal></link>
clause.
</para>
diff --git a/doc/src/sgml/ref/create_publication.sgml b/doc/src/sgml/ref/create_publication.sgml
index 85cfcaddafa..360dc7eda6a 100644
--- a/doc/src/sgml/ref/create_publication.sgml
+++ b/doc/src/sgml/ref/create_publication.sgml
@@ -33,7 +33,7 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
<phrase>and <replaceable class="parameter">publication_all_object</replaceable> is one of:</phrase>
ALL TABLES [ EXCEPT ( <replaceable class="parameter">except_table_object</replaceable> [, ... ] ) ]
- ALL SEQUENCES
+ ALL SEQUENCES [ EXCEPT ( <replaceable class="parameter">except_sequence_object</replaceable> [, ... ] ) ]
<phrase>and <replaceable class="parameter">table_and_columns</replaceable> is:</phrase>
@@ -46,6 +46,10 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
<phrase>and <replaceable class="parameter">table_object</replaceable> is:</phrase>
[ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ]
+
+<phrase>and <replaceable class="parameter">except_sequence_object</replaceable> is:</phrase>
+
+ SEQUENCE <replaceable class="parameter">sequence_name</replaceable> [, ... ]
</synopsis>
</refsynopsisdiv>
@@ -189,22 +193,26 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
<para>
Only persistent sequences are included in the publication. Temporary
sequences and unlogged sequences are excluded from the publication.
+ Sequences listed in the <literal>EXCEPT</literal> clause are excluded from
+ the publication.
</para>
</listitem>
</varlistentry>
- <varlistentry id="sql-createpublication-params-for-except-table">
+ <varlistentry id="sql-createpublication-params-for-except">
<term><literal>EXCEPT</literal></term>
<listitem>
<para>
- This clause specifies a list of tables to be excluded from the
+ This clause specifies the tables or sequences to be excluded from an
+ <literal>ALL TABLES</literal> or <literal>ALL SEQUENCES</literal>
publication.
</para>
<para>
- Once a table is excluded, the exclusion applies to that table
- regardless of its name or schema. Renaming the table or moving it to
- another schema using <command>ALTER TABLE ... SET SCHEMA</command> does
- not remove the exclusion.
+ Once a table or sequence is excluded, the exclusion applies to that
+ object regardless of its name or schema. Renaming the object or moving
+ it to another schema using <command>ALTER TABLE ... SET SCHEMA</command>
+ or <command>ALTER SEQUENCE ... SET SCHEMA</command> does not remove the
+ exclusion.
</para>
<para>
For inherited tables, if <literal>ONLY</literal> is specified before the
@@ -223,9 +231,10 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
</para>
<para>
There can be a case where a subscription includes multiple publications.
- In such a case, a table or partition that is included in one publication
- but excluded (explicitly or implicitly) by the <literal>EXCEPT</literal>
- clause of another is considered included for replication.
+ In such a case, a table, partition or sequence that is included in one
+ publication but excluded (explicitly or implicitly) by the
+ <literal>EXCEPT</literal> clause of another is considered included for
+ replication.
</para>
</listitem>
</varlistentry>
@@ -553,11 +562,20 @@ CREATE PUBLICATION all_tables_except FOR ALL TABLES EXCEPT (TABLE users, departm
</para>
<para>
- Create a publication that publishes all sequences for synchronization, and
- all changes in all tables except <structname>users</structname> and
- <structname>departments</structname>:
+ Create a publication that publishes all sequences for synchronization except
+ <structname>seq1</structname> and <structname>seq2</structname>:
+<programlisting>
+CREATE PUBLICATION all_sequences_except FOR ALL SEQUENCES EXCEPT (SEQUENCE seq1, seq2);
+</programlisting>
+ </para>
+
+ <para>
+ Create a publication that publishes all sequences for synchronization except
+ <structname>seq1</structname> and <structname>seq2</structname>, and all
+ changes in all tables except
+ <structname>users</structname> and <structname>departments</structname>:
<programlisting>
-CREATE PUBLICATION all_sequences_tables_except FOR ALL SEQUENCES, ALL TABLES EXCEPT (TABLE users, departments);
+CREATE PUBLICATION all_sequences_tables_except FOR ALL SEQUENCES EXCEPT (SEQUENCE seq1, seq2), ALL TABLES EXCEPT (TABLE users, departments);
</programlisting>
</para>
</refsect1>
diff --git a/src/backend/catalog/pg_publication.c b/src/backend/catalog/pg_publication.c
index 5c457d9aca8..f03b85fa34b 100644
--- a/src/backend/catalog/pg_publication.c
+++ b/src/backend/catalog/pg_publication.c
@@ -49,20 +49,28 @@ typedef struct
} published_rel;
/*
- * Check if relation can be in given publication and throws appropriate
- * error if not.
+ * Check if the target relation is allowed to be specified in the given
+ * publication and throw an error if not.
+ *
+ * 'pubrelkind' is the relkind accepted by the publication clause,
+ * while 'targetrelkind' is the relkind of the relation being added.
+ * Error if they are not compatible.
*/
static void
-check_publication_add_relation(PublicationRelInfo *pri)
+check_publication_add_relation(PublicationRelInfo *pri, char pubrelkind)
{
Relation targetrel = pri->relation;
+ char targetrelkind = RelationGetForm(targetrel)->relkind;
const char *relname;
const char *errormsg;
if (pri->except)
{
relname = RelationGetQualifiedRelationName(targetrel);
- errormsg = gettext_noop("cannot specify relation \"%s\" in the publication EXCEPT clause");
+ if (pubrelkind == RELKIND_SEQUENCE)
+ errormsg = gettext_noop("cannot specify \"%s\" in the publication EXCEPT (SEQUENCE) clause");
+ else
+ errormsg = gettext_noop("cannot specify \"%s\" in the publication EXCEPT (TABLE) clause");
}
else
{
@@ -77,13 +85,23 @@ check_publication_add_relation(PublicationRelInfo *pri)
errmsg(errormsg, relname),
errdetail("This operation is not supported for individual partitions.")));
- /* Must be a regular or partitioned table */
- if (RelationGetForm(targetrel)->relkind != RELKIND_RELATION &&
- RelationGetForm(targetrel)->relkind != RELKIND_PARTITIONED_TABLE)
+ /*
+ * Must be a regular or partitioned table when specified in FOR TABLE or
+ * EXCEPT table list
+ */
+ if (pubrelkind == RELKIND_RELATION && targetrelkind != RELKIND_RELATION &&
+ targetrelkind != RELKIND_PARTITIONED_TABLE)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg(errormsg, relname),
+ errdetail_relkind_not_supported(targetrelkind)));
+
+ /* Must be a sequence if specified in EXCEPT sequence list */
+ if (pubrelkind == RELKIND_SEQUENCE && targetrelkind != RELKIND_SEQUENCE)
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg(errormsg, relname),
- errdetail_relkind_not_supported(RelationGetForm(targetrel)->relkind)));
+ errdetail_relkind_not_supported(targetrelkind)));
/* Can't be system table */
if (IsCatalogRelation(targetrel))
@@ -97,11 +115,15 @@ check_publication_add_relation(PublicationRelInfo *pri)
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg(errormsg, relname),
+ targetrelkind == RELKIND_SEQUENCE ?
+ errdetail("This operation is not supported for temporary sequences.") :
errdetail("This operation is not supported for temporary tables.")));
else if (targetrel->rd_rel->relpersistence == RELPERSISTENCE_UNLOGGED)
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg(errormsg, relname),
+ targetrelkind == RELKIND_SEQUENCE ?
+ errdetail("This operation is not supported for unlogged sequences.") :
errdetail("This operation is not supported for unlogged tables.")));
}
@@ -521,7 +543,8 @@ attnumstoint2vector(Bitmapset *attrs)
*/
ObjectAddress
publication_add_relation(Oid pubid, PublicationRelInfo *pri,
- bool if_not_exists, AlterPublicationStmt *alter_stmt)
+ bool if_not_exists, AlterPublicationStmt *alter_stmt,
+ char pubrelkind)
{
Relation rel;
HeapTuple tup;
@@ -559,7 +582,7 @@ publication_add_relation(Oid pubid, PublicationRelInfo *pri,
RelationGetRelationName(targetrel), pub->name)));
}
- check_publication_add_relation(pri);
+ check_publication_add_relation(pri, pubrelkind);
/* Validate and translate column names into a Bitmapset of attnums. */
attnums = pub_collist_validate(pri->relation, pri->columns);
@@ -979,15 +1002,17 @@ GetIncludedPublicationRelations(Oid pubid, PublicationPartOpt pub_partopt)
}
/*
- * Gets list of table oids that were specified in the EXCEPT clause for a
- * publication.
- *
- * This should only be used FOR ALL TABLES publications.
+ * Gets list of relation oids that were specified in the EXCEPT clause for a
+ * 'FOR ALL TABLES' or a 'FOR ALL SEQUENCES' publication.
*/
List *
-GetExcludedPublicationTables(Oid pubid, PublicationPartOpt pub_partopt)
+GetExcludedPublicationRelations(Oid pubid, PublicationPartOpt pub_partopt)
{
- Assert(GetPublication(pubid)->alltables);
+#ifdef USE_ASSERT_CHECKING
+ Publication *pub = GetPublication(pubid);
+
+ Assert(pub->alltables || pub->allsequences);
+#endif
return get_publication_relations(pubid, pub_partopt, true);
}
@@ -1038,8 +1063,9 @@ GetAllTablesPublications(void)
* root partitioned tables. This is not applicable to FOR ALL SEQUENCES
* publication.
*
- * For a FOR ALL TABLES publication, the returned list excludes tables mentioned
- * in the EXCEPT clause.
+ * For a FOR ALL TABLES publication, the returned list excludes tables
+ * specified in the EXCEPT clause. For a FOR ALL SEQUENCES publication,
+ * it excludes sequences specified in the EXCEPT clause.
*/
List *
GetAllPublicationRelations(Oid pubid, char relkind, bool pubviaroot)
@@ -1053,11 +1079,9 @@ GetAllPublicationRelations(Oid pubid, char relkind, bool pubviaroot)
Assert(!(relkind == RELKIND_SEQUENCE && pubviaroot));
- /* EXCEPT filtering applies only to relations, not sequences */
- if (relkind == RELKIND_RELATION)
- exceptlist = GetExcludedPublicationTables(pubid, pubviaroot ?
- PUBLICATION_PART_ROOT :
- PUBLICATION_PART_LEAF);
+ exceptlist = GetExcludedPublicationRelations(pubid, pubviaroot ?
+ PUBLICATION_PART_ROOT :
+ PUBLICATION_PART_LEAF);
classRel = table_open(RelationRelationId, AccessShareLock);
diff --git a/src/backend/commands/publicationcmds.c b/src/backend/commands/publicationcmds.c
index 440adb356ad..76cd8f58f13 100644
--- a/src/backend/commands/publicationcmds.c
+++ b/src/backend/commands/publicationcmds.c
@@ -61,11 +61,11 @@ typedef struct rf_context
Oid parentid; /* relid of the parent relation */
} rf_context;
-static List *OpenTableList(List *tables);
-static void CloseTableList(List *rels);
+static List *OpenRelationList(List *tables);
+static void CloseRelationList(List *rels);
static void LockSchemaList(List *schemalist);
-static void PublicationAddTables(Oid pubid, List *rels, bool if_not_exists,
- AlterPublicationStmt *stmt);
+static void PublicationAddRelations(Oid pubid, List *rels, bool if_not_exists,
+ AlterPublicationStmt *stmt, char pubrelkind);
static void PublicationDropTables(Oid pubid, List *rels, bool missing_ok);
static void PublicationAddSchemas(Oid pubid, List *schemas, bool if_not_exists,
AlterPublicationStmt *stmt);
@@ -177,11 +177,12 @@ parse_publication_options(ParseState *pstate,
/*
* Convert the PublicationObjSpecType list into schema oid list and
- * PublicationTable list.
+ * PublicationRelation list.
*/
static void
ObjectsInPublicationToOids(List *pubobjspec_list, ParseState *pstate,
- List **rels, List **exceptrels, List **schemas)
+ List **rels, List **excepttbls, List **exceptseqs,
+ List **schemas)
{
ListCell *cell;
PublicationObjSpec *pubobj;
@@ -199,12 +200,16 @@ ObjectsInPublicationToOids(List *pubobjspec_list, ParseState *pstate,
switch (pubobj->pubobjtype)
{
case PUBLICATIONOBJ_EXCEPT_TABLE:
- pubobj->pubtable->except = true;
- *exceptrels = lappend(*exceptrels, pubobj->pubtable);
+ pubobj->pubrelation->except = true;
+ *excepttbls = lappend(*excepttbls, pubobj->pubrelation);
+ break;
+ case PUBLICATIONOBJ_EXCEPT_SEQUENCE:
+ pubobj->pubrelation->except = true;
+ *exceptseqs = lappend(*exceptseqs, pubobj->pubrelation);
break;
case PUBLICATIONOBJ_TABLE:
- pubobj->pubtable->except = false;
- *rels = lappend(*rels, pubobj->pubtable);
+ pubobj->pubrelation->except = false;
+ *rels = lappend(*rels, pubobj->pubrelation);
break;
case PUBLICATIONOBJ_TABLES_IN_SCHEMA:
schemaid = get_namespace_oid(pubobj->name, false);
@@ -849,7 +854,8 @@ CreatePublication(ParseState *pstate, CreatePublicationStmt *stmt)
char publish_generated_columns;
AclResult aclresult;
List *relations = NIL;
- List *exceptrelations = NIL;
+ List *excepttbls = NIL;
+ List *exceptseqs = NIL;
List *schemaidlist = NIL;
/* must have CREATE privilege on database */
@@ -936,18 +942,30 @@ CreatePublication(ParseState *pstate, CreatePublicationStmt *stmt)
/* Associate objects with the publication. */
ObjectsInPublicationToOids(stmt->pubobjects, pstate, &relations,
- &exceptrelations, &schemaidlist);
+ &excepttbls, &exceptseqs, &schemaidlist);
+
+ if (stmt->for_all_sequences)
+ {
+ /* Process EXCEPT sequence list */
+ if (exceptseqs != NIL)
+ {
+ List *rels = OpenRelationList(exceptseqs);
+
+ PublicationAddRelations(puboid, rels, true, NULL, RELKIND_SEQUENCE);
+ CloseRelationList(rels);
+ }
+ }
if (stmt->for_all_tables)
{
/* Process EXCEPT table list */
- if (exceptrelations != NIL)
+ if (excepttbls != NIL)
{
List *rels;
- rels = OpenTableList(exceptrelations);
- PublicationAddTables(puboid, rels, true, NULL);
- CloseTableList(rels);
+ rels = OpenRelationList(excepttbls);
+ PublicationAddRelations(puboid, rels, true, NULL, RELKIND_RELATION);
+ CloseRelationList(rels);
}
/*
@@ -969,7 +987,7 @@ CreatePublication(ParseState *pstate, CreatePublicationStmt *stmt)
{
List *rels;
- rels = OpenTableList(relations);
+ rels = OpenRelationList(relations);
TransformPubWhereClauses(rels, pstate->p_sourcetext,
publish_via_partition_root);
@@ -977,8 +995,8 @@ CreatePublication(ParseState *pstate, CreatePublicationStmt *stmt)
schemaidlist != NIL,
publish_via_partition_root);
- PublicationAddTables(puboid, rels, true, NULL);
- CloseTableList(rels);
+ PublicationAddRelations(puboid, rels, true, NULL, RELKIND_RELATION);
+ CloseRelationList(rels);
}
if (schemaidlist != NIL)
@@ -1255,7 +1273,7 @@ AlterPublicationTables(AlterPublicationStmt *stmt, HeapTuple tup,
if (!tables && stmt->action != AP_SetObjects)
return;
- rels = OpenTableList(tables);
+ rels = OpenRelationList(tables);
if (stmt->action == AP_AddObjects)
{
@@ -1266,7 +1284,7 @@ AlterPublicationTables(AlterPublicationStmt *stmt, HeapTuple tup,
CheckPubRelationColumnList(stmt->pubname, rels, publish_schema,
pubform->pubviaroot);
- PublicationAddTables(pubid, rels, false, stmt);
+ PublicationAddRelations(pubid, rels, false, stmt, RELKIND_RELATION);
}
else if (stmt->action == AP_DropObjects)
PublicationDropTables(pubid, rels, false);
@@ -1289,8 +1307,8 @@ AlterPublicationTables(AlterPublicationStmt *stmt, HeapTuple tup,
*/
if (pubform->puballtables)
{
- oldrelids = GetExcludedPublicationTables(pubid,
- PUBLICATION_PART_ROOT);
+ oldrelids = GetExcludedPublicationRelations(pubid,
+ PUBLICATION_PART_ROOT);
}
}
else
@@ -1363,8 +1381,8 @@ AlterPublicationTables(AlterPublicationStmt *stmt, HeapTuple tup,
/*
* Validate the column list. If the column list or WHERE
* clause changes, then the validation done here will be
- * duplicated inside PublicationAddTables(). The validation
- * is cheap enough that that seems harmless.
+ * duplicated inside PublicationAddRelations(). The
+ * validation is cheap enough that that seems harmless.
*/
newcolumns = pub_collist_validate(newpubrel->relation,
newpubrel->columns);
@@ -1410,12 +1428,12 @@ AlterPublicationTables(AlterPublicationStmt *stmt, HeapTuple tup,
* Don't bother calculating the difference for adding, we'll catch and
* skip existing ones when doing catalog update.
*/
- PublicationAddTables(pubid, rels, true, stmt);
+ PublicationAddRelations(pubid, rels, true, stmt, RELKIND_RELATION);
- CloseTableList(delrels);
+ CloseRelationList(delrels);
}
- CloseTableList(rels);
+ CloseRelationList(rels);
}
/*
@@ -1651,7 +1669,7 @@ AlterPublicationAllFlags(AlterPublicationStmt *stmt, Relation rel,
* Alter the existing publication.
*
* This is dispatcher function for AlterPublicationOptions,
- * AlterPublicationSchemas and AlterPublicationTables.
+ * AlterPublicationSchemas and AlterPublicationRelations.
*/
void
AlterPublication(ParseState *pstate, AlterPublicationStmt *stmt)
@@ -1683,12 +1701,16 @@ AlterPublication(ParseState *pstate, AlterPublicationStmt *stmt)
else
{
List *relations = NIL;
- List *exceptrelations = NIL;
+ List *excepttbls = NIL;
+ List *exceptseqs = NIL;
List *schemaidlist = NIL;
Oid pubid = pubform->oid;
ObjectsInPublicationToOids(stmt->pubobjects, pstate, &relations,
- &exceptrelations, &schemaidlist);
+ &excepttbls, &exceptseqs, &schemaidlist);
+
+ /* EXCEPT clause is not supported with ALTER PUBLICATION */
+ Assert(exceptseqs == NIL);
CheckAlterPublication(stmt, tup, relations, schemaidlist);
@@ -1711,7 +1733,7 @@ AlterPublication(ParseState *pstate, AlterPublicationStmt *stmt)
errmsg("publication \"%s\" does not exist",
stmt->pubname));
- relations = list_concat(relations, exceptrelations);
+ relations = list_concat(relations, excepttbls);
AlterPublicationTables(stmt, tup, relations, pstate->p_sourcetext,
schemaidlist != NIL);
AlterPublicationSchemas(stmt, tup, schemaidlist);
@@ -1830,12 +1852,12 @@ RemovePublicationSchemaById(Oid psoid)
}
/*
- * Open relations specified by a PublicationTable list.
- * The returned tables are locked in ShareUpdateExclusiveLock mode in order to
- * add them to a publication.
+ * Open relations specified by a PublicationRelation list.
+ * The returned relations are locked in ShareUpdateExclusiveLock mode in order
+ * to add them to a publication.
*/
static List *
-OpenTableList(List *tables)
+OpenRelationList(List *tables)
{
List *relids = NIL;
List *rels = NIL;
@@ -1848,7 +1870,7 @@ OpenTableList(List *tables)
*/
foreach(lc, tables)
{
- PublicationTable *t = lfirst_node(PublicationTable, lc);
+ PublicationRelation *t = lfirst_node(PublicationRelation, lc);
bool recurse = t->relation->inh;
Relation rel;
Oid myrelid;
@@ -1987,7 +2009,7 @@ OpenTableList(List *tables)
* Close all relations in the list.
*/
static void
-CloseTableList(List *rels)
+CloseRelationList(List *rels)
{
ListCell *lc;
@@ -2032,11 +2054,15 @@ LockSchemaList(List *schemalist)
}
/*
- * Add listed tables to the publication.
+ * Add listed relations to the publication.
+ *
+ * 'pubrelkind' is the relkind accepted by the publication clause.
+ * The relkind of each relation in 'rels' is checked for compatibility
+ * against it.
*/
static void
-PublicationAddTables(Oid pubid, List *rels, bool if_not_exists,
- AlterPublicationStmt *stmt)
+PublicationAddRelations(Oid pubid, List *rels, bool if_not_exists,
+ AlterPublicationStmt *stmt, char pubrelkind)
{
ListCell *lc;
@@ -2046,12 +2072,12 @@ PublicationAddTables(Oid pubid, List *rels, bool if_not_exists,
Relation rel = pub_rel->relation;
ObjectAddress obj;
- /* Must be owner of the table or superuser. */
+ /* Must be owner of the relation or superuser. */
if (!object_ownercheck(RelationRelationId, RelationGetRelid(rel), GetUserId()))
aclcheck_error(ACLCHECK_NOT_OWNER, get_relkind_objtype(rel->rd_rel->relkind),
RelationGetRelationName(rel));
- obj = publication_add_relation(pubid, pub_rel, if_not_exists, stmt);
+ obj = publication_add_relation(pubid, pub_rel, if_not_exists, stmt, pubrelkind);
if (stmt)
{
EventTriggerCollectSimpleCommand(obj, InvalidObjectAddress,
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index ff4e1388c55..4c001ef2853 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -457,7 +457,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
TriggerTransitions TriggerReferencing
vacuum_relation_list opt_vacuum_relation_list
drop_option_list pub_obj_list pub_all_obj_type_list
- pub_except_obj_list opt_pub_except_clause
+ pub_except_tbl_list opt_pub_except_tbl_clause
+ pub_except_seq_list opt_pub_except_seq_clause
%type <retclause> returning_clause
%type <node> returning_option
@@ -597,7 +598,8 @@ 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> PublicationExceptObjSpec
+%type <publicationobjectspec> PublicationExceptTblSpec
+%type <publicationobjectspec> PublicationExceptSeqSpec
%type <publicationallobjectspec> PublicationAllObjSpec
%type <keyword> unreserved_keyword type_func_name_keyword
@@ -11327,10 +11329,10 @@ PublicationObjSpec:
{
$$ = makeNode(PublicationObjSpec);
$$->pubobjtype = PUBLICATIONOBJ_TABLE;
- $$->pubtable = makeNode(PublicationTable);
- $$->pubtable->relation = $2;
- $$->pubtable->columns = $3;
- $$->pubtable->whereClause = $4;
+ $$->pubrelation = makeNode(PublicationRelation);
+ $$->pubrelation->relation = $2;
+ $$->pubrelation->columns = $3;
+ $$->pubrelation->whereClause = $4;
}
| TABLES IN_P SCHEMA ColId
{
@@ -11351,7 +11353,7 @@ PublicationObjSpec:
$$->pubobjtype = PUBLICATIONOBJ_CONTINUATION;
/*
* If either a row filter or column list is specified, create
- * a PublicationTable object.
+ * a PublicationRelation object.
*/
if ($2 || $3)
{
@@ -11361,10 +11363,10 @@ PublicationObjSpec:
* error will be thrown later via
* preprocess_pubobj_list().
*/
- $$->pubtable = makeNode(PublicationTable);
- $$->pubtable->relation = makeRangeVar(NULL, $1, @1);
- $$->pubtable->columns = $2;
- $$->pubtable->whereClause = $3;
+ $$->pubrelation = makeNode(PublicationRelation);
+ $$->pubrelation->relation = makeRangeVar(NULL, $1, @1);
+ $$->pubrelation->columns = $2;
+ $$->pubrelation->whereClause = $3;
}
else
{
@@ -11376,10 +11378,10 @@ PublicationObjSpec:
{
$$ = makeNode(PublicationObjSpec);
$$->pubobjtype = PUBLICATIONOBJ_CONTINUATION;
- $$->pubtable = makeNode(PublicationTable);
- $$->pubtable->relation = makeRangeVarFromQualifiedName($1, $2, @1, yyscanner);
- $$->pubtable->columns = $3;
- $$->pubtable->whereClause = $4;
+ $$->pubrelation = makeNode(PublicationRelation);
+ $$->pubrelation->relation = makeRangeVarFromQualifiedName($1, $2, @1, yyscanner);
+ $$->pubrelation->columns = $3;
+ $$->pubrelation->whereClause = $4;
$$->location = @1;
}
/* grammar like tablename * , ONLY tablename, ONLY ( tablename ) */
@@ -11387,10 +11389,10 @@ PublicationObjSpec:
{
$$ = makeNode(PublicationObjSpec);
$$->pubobjtype = PUBLICATIONOBJ_CONTINUATION;
- $$->pubtable = makeNode(PublicationTable);
- $$->pubtable->relation = $1;
- $$->pubtable->columns = $2;
- $$->pubtable->whereClause = $3;
+ $$->pubrelation = makeNode(PublicationRelation);
+ $$->pubrelation->relation = $1;
+ $$->pubrelation->columns = $2;
+ $$->pubrelation->whereClause = $3;
}
| CURRENT_SCHEMA
{
@@ -11406,23 +11408,29 @@ pub_obj_list: PublicationObjSpec
{ $$ = lappend($1, $3); }
;
-opt_pub_except_clause:
- EXCEPT '(' TABLE pub_except_obj_list ')' { $$ = $4; }
+opt_pub_except_tbl_clause:
+ EXCEPT '(' TABLE pub_except_tbl_list ')' { $$ = $4; }
+ | /*EMPTY*/ { $$ = NIL; }
+ ;
+
+opt_pub_except_seq_clause:
+ EXCEPT '(' SEQUENCE pub_except_seq_list ')' { $$ = $4; }
| /*EMPTY*/ { $$ = NIL; }
;
PublicationAllObjSpec:
- ALL TABLES opt_pub_except_clause
+ ALL TABLES opt_pub_except_tbl_clause
{
$$ = makeNode(PublicationAllObjSpec);
$$->pubobjtype = PUBLICATION_ALL_TABLES;
- $$->except_tables = $3;
+ $$->except_relations = $3;
$$->location = @1;
}
- | ALL SEQUENCES
+ | ALL SEQUENCES opt_pub_except_seq_clause
{
$$ = makeNode(PublicationAllObjSpec);
$$->pubobjtype = PUBLICATION_ALL_SEQUENCES;
+ $$->except_relations = $3;
$$->location = @1;
}
;
@@ -11433,21 +11441,41 @@ pub_all_obj_type_list: PublicationAllObjSpec
{ $$ = lappend($1, $3); }
;
-PublicationExceptObjSpec:
+PublicationExceptTblSpec:
relation_expr
{
$$ = makeNode(PublicationObjSpec);
$$->pubobjtype = PUBLICATIONOBJ_EXCEPT_TABLE;
- $$->pubtable = makeNode(PublicationTable);
- $$->pubtable->except = true;
- $$->pubtable->relation = $1;
+ $$->pubrelation = makeNode(PublicationRelation);
+ $$->pubrelation->except = true;
+ $$->pubrelation->relation = $1;
$$->location = @1;
}
;
-pub_except_obj_list: PublicationExceptObjSpec
+PublicationExceptSeqSpec:
+ relation_expr
+ {
+ $$ = makeNode(PublicationObjSpec);
+ $$->pubobjtype = PUBLICATIONOBJ_EXCEPT_SEQUENCE;
+ $$->pubrelation = makeNode(PublicationRelation);
+ $$->pubrelation->except = true;
+ $$->pubrelation->relation = $1;
+ $$->location = @1;
+ }
+ ;
+
+pub_except_tbl_list: PublicationExceptTblSpec
{ $$ = list_make1($1); }
- | pub_except_obj_list ',' opt_table PublicationExceptObjSpec
+ | pub_except_tbl_list ',' opt_table PublicationExceptTblSpec
+ { $$ = lappend($1, $4); }
+ ;
+
+pub_except_seq_list: PublicationExceptSeqSpec
+ { $$ = list_make1($1); }
+ | pub_except_seq_list ',' PublicationExceptSeqSpec
+ { $$ = lappend($1, $3); }
+ | pub_except_seq_list ',' SEQUENCE PublicationExceptSeqSpec
{ $$ = lappend($1, $4); }
;
@@ -20765,7 +20793,7 @@ preprocess_pub_all_objtype_list(List *all_objects_list, List **pubobjects,
parser_errposition(obj->location));
*all_tables = true;
- *pubobjects = list_concat(*pubobjects, obj->except_tables);
+ *pubobjects = list_concat(*pubobjects, obj->except_relations);
}
else if (obj->pubobjtype == PUBLICATION_ALL_SEQUENCES)
{
@@ -20777,6 +20805,7 @@ preprocess_pub_all_objtype_list(List *all_objects_list, List **pubobjects,
parser_errposition(obj->location));
*all_sequences = true;
+ *pubobjects = list_concat(*pubobjects, obj->except_relations);
}
}
}
@@ -20812,8 +20841,8 @@ preprocess_pubobj_list(List *pubobjspec_list, core_yyscan_t yyscanner)
if (pubobj->pubobjtype == PUBLICATIONOBJ_TABLE)
{
- /* relation name or pubtable must be set for this type of object */
- if (!pubobj->name && !pubobj->pubtable)
+ /* Relation name or pubrelation must be set for this type of object */
+ if (!pubobj->name && !pubobj->pubrelation)
ereport(ERROR,
errcode(ERRCODE_SYNTAX_ERROR),
errmsg("invalid table name"),
@@ -20821,12 +20850,12 @@ preprocess_pubobj_list(List *pubobjspec_list, core_yyscan_t yyscanner)
if (pubobj->name)
{
- /* convert it to PublicationTable */
- PublicationTable *pubtable = makeNode(PublicationTable);
+ /* Convert it to PublicationRelation */
+ PublicationRelation *pubrelation = makeNode(PublicationRelation);
- pubtable->relation =
+ pubrelation->relation =
makeRangeVar(NULL, pubobj->name, pubobj->location);
- pubobj->pubtable = pubtable;
+ pubobj->pubrelation = pubrelation;
pubobj->name = NULL;
}
}
@@ -20834,14 +20863,14 @@ preprocess_pubobj_list(List *pubobjspec_list, core_yyscan_t yyscanner)
pubobj->pubobjtype == PUBLICATIONOBJ_TABLES_IN_CUR_SCHEMA)
{
/* WHERE clause is not allowed on a schema object */
- if (pubobj->pubtable && pubobj->pubtable->whereClause)
+ if (pubobj->pubrelation && pubobj->pubrelation->whereClause)
ereport(ERROR,
errcode(ERRCODE_SYNTAX_ERROR),
errmsg("WHERE clause not allowed for schema"),
parser_errposition(pubobj->location));
/* Column list is not allowed on a schema object */
- if (pubobj->pubtable && pubobj->pubtable->columns)
+ if (pubobj->pubrelation && pubobj->pubrelation->columns)
ereport(ERROR,
errcode(ERRCODE_SYNTAX_ERROR),
errmsg("column specification not allowed for schema"),
@@ -20849,11 +20878,11 @@ preprocess_pubobj_list(List *pubobjspec_list, core_yyscan_t yyscanner)
/*
* We can distinguish between the different type of schema objects
- * based on whether name and pubtable is set.
+ * based on whether name and pubrelation is set.
*/
if (pubobj->name)
pubobj->pubobjtype = PUBLICATIONOBJ_TABLES_IN_SCHEMA;
- else if (!pubobj->name && !pubobj->pubtable)
+ else if (!pubobj->name && !pubobj->pubrelation)
pubobj->pubobjtype = PUBLICATIONOBJ_TABLES_IN_CUR_SCHEMA;
else
ereport(ERROR,
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index c56437d6057..d52809c26a8 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -4617,18 +4617,22 @@ getPublications(Archive *fout)
{
NULL, NULL
};
+ pubinfo[i].except_sequences = (SimplePtrList)
+ {
+ NULL, NULL
+ };
/* Decide whether we want to dump it */
selectDumpableObject(&(pubinfo[i].dobj), fout);
/*
- * Get the list of tables for publications specified in the EXCEPT
- * TABLE clause.
+ * Get the list of tables and sequences for publications specified in
+ * the EXCEPT clause.
*
- * Although individual table entries in EXCEPT list could be stored in
- * PublicationRelInfo, dumpPublicationTable cannot be used to emit
- * them, because there is no ALTER PUBLICATION ... ADD command to add
- * individual table entries to the EXCEPT list.
+ * Although individual table/sequence entries in EXCEPT list could be
+ * stored in PublicationRelInfo, dumpPublicationTable cannot be used
+ * to emit them, because there is no ALTER PUBLICATION ... ADD command
+ * to add individual table entries to the EXCEPT list.
*
* Therefore, the approach is to dump the complete EXCEPT list in a
* single CREATE PUBLICATION statement. PublicationInfo is used to
@@ -4642,9 +4646,10 @@ getPublications(Archive *fout)
resetPQExpBuffer(query);
appendPQExpBuffer(query,
- "SELECT prrelid\n"
- "FROM pg_catalog.pg_publication_rel\n"
- "WHERE prpubid = %u AND prexcept",
+ "SELECT pr.prrelid, pc.relkind\n"
+ "FROM pg_catalog.pg_publication_rel pr\n"
+ "JOIN pg_catalog.pg_class pc ON pr.prrelid = pc.oid\n"
+ "WHERE pr.prpubid = %u AND pr.prexcept",
pubinfo[i].dobj.catId.oid);
res_tbls = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
@@ -4654,14 +4659,22 @@ getPublications(Archive *fout)
for (int j = 0; j < ntbls; j++)
{
Oid prrelid;
+ char relkind;
TableInfo *tbinfo;
prrelid = atooid(PQgetvalue(res_tbls, j, 0));
+ relkind = *PQgetvalue(res_tbls, j, 1);
tbinfo = findTableByOid(prrelid);
if (tbinfo != NULL)
- simple_ptr_list_append(&pubinfo[i].except_tables, tbinfo);
+ {
+ /* TODO : Add a version check for PG 20 */
+ if (relkind == RELKIND_SEQUENCE)
+ simple_ptr_list_append(&pubinfo[i].except_sequences, tbinfo);
+ else
+ simple_ptr_list_append(&pubinfo[i].except_tables, tbinfo);
+ }
}
PQclear(res_tbls);
@@ -4721,12 +4734,30 @@ dumpPublication(Archive *fout, const PublicationInfo *pubinfo)
}
if (n_except > 0)
appendPQExpBufferChar(query, ')');
+ }
+ if (pubinfo->puballsequences)
+ {
+ int n_except = 0;
- if (pubinfo->puballsequences)
+ if (pubinfo->puballtables)
appendPQExpBufferStr(query, ", ALL SEQUENCES");
+ else
+ appendPQExpBufferStr(query, " FOR ALL SEQUENCES");
+
+ /* Include EXCEPT (SEQUENCE) clause if there are except_sequences. */
+ for (SimplePtrListCell *cell = pubinfo->except_sequences.head; cell; cell = cell->next)
+ {
+ TableInfo *tbinfo = (TableInfo *) cell->ptr;
+
+ if (++n_except == 1)
+ appendPQExpBufferStr(query, " EXCEPT (");
+ else
+ appendPQExpBufferStr(query, ", ");
+ appendPQExpBuffer(query, "SEQUENCE %s", fmtQualifiedDumpable(tbinfo));
+ }
+ if (n_except > 0)
+ appendPQExpBufferChar(query, ')');
}
- else if (pubinfo->puballsequences)
- appendPQExpBufferStr(query, " FOR ALL SEQUENCES");
appendPQExpBufferStr(query, " WITH (publish = '");
if (pubinfo->pubinsert)
diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h
index 5a6726d8b12..8e869fe791a 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -679,6 +679,7 @@ typedef struct _PublicationInfo
bool pubviaroot;
PublishGencolsType pubgencols_type;
SimplePtrList except_tables;
+ SimplePtrList except_sequences;
} PublicationInfo;
/*
diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
index 9258948b583..c2c1d515674 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -3283,6 +3283,26 @@ my %tests = (
like => { %full_runs, section_post_data => 1, },
},
+ 'CREATE PUBLICATION pub11' => {
+ create_order => 92,
+ create_sql =>
+ 'CREATE PUBLICATION pub11 FOR ALL SEQUENCES EXCEPT (SEQUENCE public.test_table_col1_seq);',
+ regexp => qr/^
+ \QCREATE PUBLICATION pub11 FOR ALL SEQUENCES EXCEPT (SEQUENCE public.test_table_col1_seq) WITH (publish = 'insert, update, delete, truncate');\E
+ /xm,
+ like => { %full_runs, section_post_data => 1, },
+ },
+
+ 'CREATE PUBLICATION pub12' => {
+ create_order => 92,
+ create_sql =>
+ 'CREATE PUBLICATION pub12 FOR ALL TABLES EXCEPT (TABLE dump_test.test_table), ALL SEQUENCES EXCEPT (SEQUENCE public.test_table_col1_seq);',
+ regexp => qr/^
+ \QCREATE PUBLICATION pub12 FOR ALL TABLES EXCEPT (TABLE ONLY dump_test.test_table), ALL SEQUENCES EXCEPT (SEQUENCE public.test_table_col1_seq) 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
@@ -4082,6 +4102,8 @@ my %tests = (
},
'CREATE SEQUENCE test_table_col1_seq' => {
+ create_order => 90,
+ create_sql => 'CREATE SEQUENCE test_table_col1_seq',
regexp => qr/^
\QCREATE SEQUENCE dump_test.test_table_col1_seq\E
\n\s+\QAS integer\E
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index af3935b0078..cd30f36f9b2 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -1778,7 +1778,7 @@ describeOneTableDetails(const char *schemaname,
{
PGresult *result = NULL;
printQueryOpt myopt = pset.popt;
- char *footers[3] = {NULL, NULL, NULL};
+ char *footers[4] = {NULL, NULL, NULL, NULL};
printfPQExpBuffer(&buf, "/* %s */\n", _("Get sequence information"));
if (pset.sversion >= 100000)
@@ -1882,12 +1882,32 @@ describeOneTableDetails(const char *schemaname,
{
printfPQExpBuffer(&buf, "/* %s */\n",
_("Get publications containing this sequence"));
- appendPQExpBuffer(&buf, "SELECT pubname FROM pg_catalog.pg_publication p"
+ appendPQExpBuffer(&buf, "SELECT p.pubname FROM pg_catalog.pg_publication p"
"\nWHERE p.puballsequences"
- "\n AND pg_catalog.pg_relation_is_publishable('%s')"
- "\nORDER BY 1",
+ "\n AND pg_catalog.pg_relation_is_publishable('%s')",
oid);
+ /*
+ * Skip entries where this sequence appears in the publication's
+ * EXCEPT list.
+ *
+ * For a FOR ALL SEQUENCES publication, pg_publication_rel
+ * contains entries only for sequences specified in the EXCEPT
+ * clause, so there is no need to check pr.prexcept explicitly.
+ *
+ * TODO: Add a version check for PG 20.
+ */
+ if (pset.sversion >= 190000)
+ {
+ appendPQExpBuffer(&buf, "\n AND NOT EXISTS (\n"
+ " SELECT 1\n"
+ " FROM pg_catalog.pg_publication_rel pr\n"
+ " WHERE pr.prpubid = p.oid AND\n"
+ " pr.prrelid = '%s')", oid);
+ }
+
+ appendPQExpBuffer(&buf, "\nORDER BY 1");
+
result = PSQLexec(buf.data);
if (result)
{
@@ -1912,6 +1932,43 @@ describeOneTableDetails(const char *schemaname,
}
}
+ /* TODO : Add a version check for PG 20 */
+ /* Print publications where the sequence is in the EXCEPT clause */
+ if (pset.sversion >= 190000)
+ {
+ printfPQExpBuffer(&buf,
+ "SELECT p.pubname\n"
+ "FROM pg_catalog.pg_publication p\n"
+ "JOIN pg_catalog.pg_publication_rel pr ON p.oid = pr.prpubid\n"
+ "WHERE pr.prrelid = '%s' AND pr.prexcept\n"
+ "ORDER BY 1;", oid);
+
+ result = PSQLexec(buf.data);
+ if (result)
+ {
+ int tuples = PQntuples(result);
+
+ if (tuples > 0)
+ {
+ printfPQExpBuffer(&tmpbuf, _("Excluded from publications:"));
+
+ /* Might be an empty set - that's ok */
+ for (i = 0; i < tuples; i++)
+ appendPQExpBuffer(&tmpbuf, "\n \"%s\"", PQgetvalue(result, i, 0));
+
+ if (footers[0] == NULL)
+ footers[0] = pg_strdup(tmpbuf.data);
+ else if (footers[1] == NULL)
+ footers[1] = pg_strdup(tmpbuf.data);
+ else
+ footers[2] = pg_strdup(tmpbuf.data);
+ resetPQExpBuffer(&tmpbuf);
+ }
+
+ PQclear(result);
+ }
+ }
+
if (tableinfo.relpersistence == RELPERSISTENCE_UNLOGGED)
printfPQExpBuffer(&title, _("Unlogged sequence \"%s.%s\""),
schemaname, relationname);
@@ -1928,6 +1985,7 @@ describeOneTableDetails(const char *schemaname,
free(footers[0]);
free(footers[1]);
+ free(footers[2]);
retval = true;
goto error_return; /* not an error, just return early */
@@ -6950,6 +7008,7 @@ describePublications(const char *pattern)
char *pubid = PQgetvalue(res, i, 0);
char *pubname = PQgetvalue(res, i, 1);
bool puballtables = strcmp(PQgetvalue(res, i, 3), "t") == 0;
+ bool puballsequences = strcmp(PQgetvalue(res, i, 4), "t") == 0;
printTableOpt myopt = pset.popt.topt;
initPQExpBuffer(&title);
@@ -6986,7 +7045,7 @@ describePublications(const char *pattern)
printTableAddCell(&cont, PQgetvalue(res, i, 10), false, false);
printTableAddCell(&cont, PQgetvalue(res, i, 11), false, false);
- if (!puballtables)
+ if (!puballtables && !puballsequences)
{
/* Get the tables for the specified publication */
printfPQExpBuffer(&buf, "/* %s */\n",
@@ -7040,7 +7099,7 @@ describePublications(const char *pattern)
goto error_return;
}
}
- else
+ if (puballtables)
{
if (pset.sversion >= 190000)
{
@@ -7052,13 +7111,33 @@ describePublications(const char *pattern)
"FROM pg_catalog.pg_class c\n"
" JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n"
" JOIN pg_catalog.pg_publication_rel pr ON c.oid = pr.prrelid\n"
- "WHERE pr.prpubid = '%s' AND pr.prexcept\n"
+ "WHERE pr.prpubid = '%s' AND pr.prexcept AND c.relkind IN ('r','p')\n"
"ORDER BY 1", pubid);
if (!addFooterToPublicationDesc(&buf, _("Except tables:"),
true, &cont))
goto error_return;
}
}
+ if (puballsequences)
+ {
+ /* TODO : Add a version check for PG 20 */
+ if (pset.sversion >= 190000)
+ {
+ /* Get sequences in the EXCEPT clause for this publication */
+ printfPQExpBuffer(&buf, "/* %s */\n",
+ _("Get sequences excluded by this publication"));
+ printfPQExpBuffer(&buf,
+ "SELECT n.nspname || '.' || c.relname\n"
+ "FROM pg_catalog.pg_class c\n"
+ " JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n"
+ " JOIN pg_catalog.pg_publication_rel pr ON c.oid = pr.prrelid\n"
+ "WHERE pr.prpubid = '%s' AND pr.prexcept AND c.relkind = 'S'\n"
+ "ORDER BY 1", pubid);
+ if (!addFooterToPublicationDesc(&buf, _("Except sequences:"),
+ 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 de547a8cb37..12d276cbb65 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -3761,6 +3761,18 @@ match_previous_words(int pattern_id,
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables);
else if (Matches("CREATE", "PUBLICATION", MatchAny, "FOR", "ALL", "TABLES", "EXCEPT", "(", "TABLE", MatchAnyN) && !ends_with(prev_wd, ','))
COMPLETE_WITH(")");
+ else if (Matches("CREATE", "PUBLICATION", MatchAny, "FOR", "ALL", "SEQUENCES"))
+ COMPLETE_WITH("EXCEPT ( SEQUENCE", "WITH (");
+ else if (Matches("CREATE", "PUBLICATION", MatchAny, "FOR", "ALL", "SEQUENCES", "EXCEPT"))
+ COMPLETE_WITH("( SEQUENCE");
+ else if (Matches("CREATE", "PUBLICATION", MatchAny, "FOR", "ALL", "SEQUENCES", "EXCEPT", "("))
+ COMPLETE_WITH("SEQUENCE");
+ else if (Matches("CREATE", "PUBLICATION", MatchAny, "FOR", "ALL", "SEQUENCES", "EXCEPT", "(", "SEQUENCE"))
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_sequences);
+ else if (Matches("CREATE", "PUBLICATION", MatchAny, "FOR", "ALL", "SEQUENCES", "EXCEPT", "(", "SEQUENCE", MatchAnyN) && ends_with(prev_wd, ','))
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_sequences);
+ else if (Matches("CREATE", "PUBLICATION", MatchAny, "FOR", "ALL", "SEQUENCES", "EXCEPT", "(", "SEQUENCE", MatchAnyN) && !ends_with(prev_wd, ','))
+ COMPLETE_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 89b4bb14f62..49c29a87630 100644
--- a/src/include/catalog/pg_publication.h
+++ b/src/include/catalog/pg_publication.h
@@ -177,8 +177,8 @@ typedef enum PublicationPartOpt
extern List *GetIncludedPublicationRelations(Oid pubid,
PublicationPartOpt pub_partopt);
-extern List *GetExcludedPublicationTables(Oid pubid,
- PublicationPartOpt pub_partopt);
+extern List *GetExcludedPublicationRelations(Oid pubid,
+ PublicationPartOpt pub_partopt);
extern List *GetAllTablesPublications(void);
extern List *GetAllPublicationRelations(Oid pubid, char relkind, bool pubviaroot);
extern List *GetPublicationSchemas(Oid pubid);
@@ -200,7 +200,8 @@ extern bool check_and_fetch_column_list(Publication *pub, Oid relid,
MemoryContext mcxt, Bitmapset **cols);
extern ObjectAddress publication_add_relation(Oid pubid, PublicationRelInfo *pri,
bool if_not_exists,
- AlterPublicationStmt *alter_stmt);
+ AlterPublicationStmt *alter_stmt,
+ char pubrelkind);
extern Bitmapset *pub_collist_validate(Relation targetrel, List *columns);
extern ObjectAddress publication_add_schema(Oid pubid, Oid schemaid,
bool if_not_exists);
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 4133c404a6b..6870daab5fe 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -4460,14 +4460,14 @@ typedef struct AlterTSConfigurationStmt
bool missing_ok; /* for DROP - skip error if missing? */
} AlterTSConfigurationStmt;
-typedef struct PublicationTable
+typedef struct PublicationRelation
{
NodeTag type;
RangeVar *relation; /* publication relation */
Node *whereClause; /* qualifications */
List *columns; /* List of columns in a publication table */
bool except; /* True if listed in the EXCEPT clause */
-} PublicationTable;
+} PublicationRelation;
/*
* Publication object type
@@ -4476,6 +4476,7 @@ typedef enum PublicationObjSpecType
{
PUBLICATIONOBJ_TABLE, /* A table */
PUBLICATIONOBJ_EXCEPT_TABLE, /* A table in the EXCEPT clause */
+ PUBLICATIONOBJ_EXCEPT_SEQUENCE, /* A sequence in the EXCEPT clause */
PUBLICATIONOBJ_TABLES_IN_SCHEMA, /* All tables in schema */
PUBLICATIONOBJ_TABLES_IN_CUR_SCHEMA, /* All tables in first element of
* search_path */
@@ -4487,7 +4488,7 @@ typedef struct PublicationObjSpec
NodeTag type;
PublicationObjSpecType pubobjtype; /* type of this publication object */
char *name;
- PublicationTable *pubtable;
+ PublicationRelation *pubrelation;
ParseLoc location; /* token location, or -1 if unknown */
} PublicationObjSpec;
@@ -4504,7 +4505,9 @@ typedef struct PublicationAllObjSpec
{
NodeTag type;
PublicationAllObjType pubobjtype; /* type of this publication object */
- List *except_tables; /* tables specified in the EXCEPT clause */
+ List *except_relations; /* depending on the 'pubobjtype', this is
+ * a list of either tables or sequences
+ * specified in the EXCEPT clause */
ParseLoc location; /* token location, or -1 if unknown */
} PublicationAllObjSpec;
diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out
index 29e54b214a0..c1a5fea2a96 100644
--- a/src/test/regress/expected/publication.out
+++ b/src/test/regress/expected/publication.out
@@ -458,7 +458,7 @@ Excluded from publications:
Number of partitions: 1 (Use \d+ to list them.)
CREATE PUBLICATION testpub9 FOR ALL TABLES EXCEPT (TABLE testpub_part1);
-ERROR: cannot specify relation "public.testpub_part1" in the publication EXCEPT clause
+ERROR: cannot specify "public.testpub_part1" in the publication EXCEPT (TABLE) clause
DETAIL: This operation is not supported for individual partitions.
CREATE TABLE tab_main (a int) PARTITION BY RANGE(a);
-- Attaching a partition is not allowed if the partitioned table appears in a
@@ -473,6 +473,7 @@ DROP PUBLICATION testpub8;
--- Tests for publications with SEQUENCES
CREATE SEQUENCE regress_pub_seq0;
CREATE SEQUENCE pub_test.regress_pub_seq1;
+CREATE SEQUENCE regress_pub_seq2;
-- FOR ALL SEQUENCES
SET client_min_messages = 'ERROR';
CREATE PUBLICATION regress_pub_forallsequences1 FOR ALL SEQUENCES;
@@ -534,10 +535,77 @@ SELECT pubname, puballtables, puballsequences FROM pg_publication WHERE pubname
regress_publication_user | t | t | t | f | f | f | stored | f |
(1 row)
-DROP SEQUENCE regress_pub_seq0, pub_test.regress_pub_seq1;
+-- Test ALL SEQUENCES with EXCEPT clause
+SET client_min_messages = 'ERROR';
+CREATE PUBLICATION regress_pub_forallsequences3 FOR ALL SEQUENCES EXCEPT (SEQUENCE regress_pub_seq0, pub_test.regress_pub_seq1, SEQUENCE regress_pub_seq2);
+\dRp+ regress_pub_forallsequences3
+ Publication regress_pub_forallsequences3
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+-------------
+ regress_publication_user | f | t | t | t | t | t | none | f |
+Except sequences:
+ "pub_test.regress_pub_seq1"
+ "public.regress_pub_seq0"
+ "public.regress_pub_seq2"
+
+-- Check that the sequence description shows the publications where it is listed
+-- in the EXCEPT clause
+\d+ regress_pub_seq0
+ Sequence "public.regress_pub_seq0"
+ Type | Start | Minimum | Maximum | Increment | Cycles? | Cache
+--------+-------+---------+---------------------+-----------+---------+-------
+ bigint | 1 | 1 | 9223372036854775807 | 1 | no | 1
+Included in publications:
+ "regress_pub_for_allsequences_alltables"
+ "regress_pub_forallsequences1"
+ "regress_pub_forallsequences2"
+Excluded from publications:
+ "regress_pub_forallsequences3"
+
+-- Verify that an excluded sequence remains excluded after being moved to
+-- another schema.
+ALTER SEQUENCE regress_pub_seq2 SET SCHEMA pub_test;
+\dRp+ regress_pub_forallsequences3
+ Publication regress_pub_forallsequences3
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+-------------
+ regress_publication_user | f | t | t | t | t | t | none | f |
+Except sequences:
+ "pub_test.regress_pub_seq1"
+ "pub_test.regress_pub_seq2"
+ "public.regress_pub_seq0"
+
+RESET client_min_messages;
+-- fail - sequence object is specified in EXCEPT table list
+CREATE PUBLICATION regress_pub_forallsequences4 FOR ALL TABLES EXCEPT (TABLE regress_pub_seq0);
+ERROR: cannot specify "public.regress_pub_seq0" in the publication EXCEPT (TABLE) clause
+DETAIL: This operation is not supported for sequences.
+-- fail - table object is specified in EXCEPT sequence list
+CREATE TABLE tab_seq(a int);
+CREATE PUBLICATION regress_pub_forallsequences4 FOR ALL SEQUENCES EXCEPT (SEQUENCE tab_seq);
+ERROR: cannot specify "public.tab_seq" in the publication EXCEPT (SEQUENCE) clause
+DETAIL: This operation is not supported for tables.
+-- Test combination of ALL SEQUENCES and ALL TABLES with EXCEPT clause
+SET client_min_messages = 'ERROR';
+CREATE PUBLICATION regress_pub_for_allsequences_alltables1 FOR ALL TABLES EXCEPT (TABLE testpub_tbl1), ALL SEQUENCES EXCEPT (SEQUENCE regress_pub_seq0);
+\dRp+ regress_pub_for_allsequences_alltables1
+ Publication regress_pub_for_allsequences_alltables1
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+-------------
+ regress_publication_user | t | t | t | t | t | t | none | f |
+Except tables:
+ "public.testpub_tbl1"
+Except sequences:
+ "public.regress_pub_seq0"
+
+RESET client_min_messages;
+DROP SEQUENCE regress_pub_seq0, pub_test.regress_pub_seq1, pub_test.regress_pub_seq2;
DROP PUBLICATION regress_pub_forallsequences1;
DROP PUBLICATION regress_pub_forallsequences2;
+DROP PUBLICATION regress_pub_forallsequences3;
DROP PUBLICATION regress_pub_for_allsequences_alltables;
+DROP PUBLICATION regress_pub_for_allsequences_alltables1;
+DROP TABLE tab_seq;
-- fail - Specifying ALL TABLES more than once
CREATE PUBLICATION regress_pub_for_allsequences_alltables FOR ALL SEQUENCES, ALL TABLES, ALL TABLES;
ERROR: invalid publication object list
diff --git a/src/test/regress/sql/publication.sql b/src/test/regress/sql/publication.sql
index 041e14a4de6..0423a77d5de 100644
--- a/src/test/regress/sql/publication.sql
+++ b/src/test/regress/sql/publication.sql
@@ -223,6 +223,7 @@ DROP PUBLICATION testpub8;
--- Tests for publications with SEQUENCES
CREATE SEQUENCE regress_pub_seq0;
CREATE SEQUENCE pub_test.regress_pub_seq1;
+CREATE SEQUENCE regress_pub_seq2;
-- FOR ALL SEQUENCES
SET client_min_messages = 'ERROR';
@@ -253,10 +254,40 @@ RESET client_min_messages;
SELECT pubname, puballtables, puballsequences FROM pg_publication WHERE pubname = 'regress_pub_for_allsequences_alltables';
\dRp+ regress_pub_for_allsequences_alltables
-DROP SEQUENCE regress_pub_seq0, pub_test.regress_pub_seq1;
+-- Test ALL SEQUENCES with EXCEPT clause
+SET client_min_messages = 'ERROR';
+CREATE PUBLICATION regress_pub_forallsequences3 FOR ALL SEQUENCES EXCEPT (SEQUENCE regress_pub_seq0, pub_test.regress_pub_seq1, SEQUENCE regress_pub_seq2);
+\dRp+ regress_pub_forallsequences3
+-- Check that the sequence description shows the publications where it is listed
+-- in the EXCEPT clause
+\d+ regress_pub_seq0
+
+-- Verify that an excluded sequence remains excluded after being moved to
+-- another schema.
+ALTER SEQUENCE regress_pub_seq2 SET SCHEMA pub_test;
+\dRp+ regress_pub_forallsequences3
+RESET client_min_messages;
+
+-- fail - sequence object is specified in EXCEPT table list
+CREATE PUBLICATION regress_pub_forallsequences4 FOR ALL TABLES EXCEPT (TABLE regress_pub_seq0);
+
+-- fail - table object is specified in EXCEPT sequence list
+CREATE TABLE tab_seq(a int);
+CREATE PUBLICATION regress_pub_forallsequences4 FOR ALL SEQUENCES EXCEPT (SEQUENCE tab_seq);
+
+-- Test combination of ALL SEQUENCES and ALL TABLES with EXCEPT clause
+SET client_min_messages = 'ERROR';
+CREATE PUBLICATION regress_pub_for_allsequences_alltables1 FOR ALL TABLES EXCEPT (TABLE testpub_tbl1), ALL SEQUENCES EXCEPT (SEQUENCE regress_pub_seq0);
+\dRp+ regress_pub_for_allsequences_alltables1
+RESET client_min_messages;
+
+DROP SEQUENCE regress_pub_seq0, pub_test.regress_pub_seq1, pub_test.regress_pub_seq2;
DROP PUBLICATION regress_pub_forallsequences1;
DROP PUBLICATION regress_pub_forallsequences2;
+DROP PUBLICATION regress_pub_forallsequences3;
DROP PUBLICATION regress_pub_for_allsequences_alltables;
+DROP PUBLICATION regress_pub_for_allsequences_alltables1;
+DROP TABLE tab_seq;
-- fail - Specifying ALL TABLES more than once
CREATE PUBLICATION regress_pub_for_allsequences_alltables FOR ALL SEQUENCES, ALL TABLES, ALL TABLES;
diff --git a/src/test/subscription/t/037_except.pl b/src/test/subscription/t/037_except.pl
index 8c58d282eee..b559076a4d1 100644
--- a/src/test/subscription/t/037_except.pl
+++ b/src/test/subscription/t/037_except.pl
@@ -282,6 +282,81 @@ $node_subscriber->safe_psql('postgres', 'DROP SUBSCRIPTION tap_sub');
$node_publisher->safe_psql('postgres', 'DROP PUBLICATION tap_pub1');
$node_publisher->safe_psql('postgres', 'DROP PUBLICATION tap_pub2');
+# ============================================
+# EXCEPT clause test cases for sequences
+# ============================================
+$node_publisher->safe_psql(
+ 'postgres', qq (
+ CREATE TABLE seq_test (v BIGINT);
+ CREATE SEQUENCE seq1;
+ CREATE SEQUENCE seq2;
+ INSERT INTO seq_test SELECT nextval('seq1') FROM generate_series(1,100);
+ INSERT INTO seq_test SELECT nextval('seq2') FROM generate_series(1,100);
+ CREATE PUBLICATION tap_pub1 FOR ALL SEQUENCES EXCEPT (SEQUENCE seq1);
+));
+$node_subscriber->safe_psql(
+ 'postgres', qq(
+ CREATE SEQUENCE seq1;
+ CREATE SEQUENCE seq2;
+ CREATE SUBSCRIPTION tap_sub CONNECTION '$publisher_connstr' PUBLICATION tap_pub1;
+));
+
+# Wait for initial sync to finish
+my $synced_query =
+ "SELECT count(1) = 0 FROM pg_subscription_rel WHERE srsubstate NOT IN ('r');";
+$node_subscriber->poll_query_until('postgres', $synced_query)
+ or die "Timed out while waiting for subscriber to synchronize data";
+
+# Check the initial data on subscriber
+$result = $node_subscriber->safe_psql('postgres',
+ "SELECT last_value, is_called FROM seq1");
+is($result, '1|f', 'sequences in EXCEPT list is excluded');
+
+$result = $node_subscriber->safe_psql('postgres',
+ "SELECT last_value, is_called FROM seq2");
+is($result, '100|t', 'initial test data replicated for seq2');
+
+# ============================================
+# Test when a subscription is subscribing to multiple publications
+# ============================================
+$node_publisher->safe_psql(
+ 'postgres', qq(
+ INSERT INTO seq_test SELECT nextval('seq1') FROM generate_series(1,100);
+ INSERT INTO seq_test SELECT nextval('seq2') FROM generate_series(1,100);
+ CREATE PUBLICATION tap_pub2 FOR ALL SEQUENCES EXCEPT (SEQUENCE seq2);
+));
+
+# Subscribe to multiple publications with different EXCEPT sequence lists
+$node_subscriber->safe_psql(
+ 'postgres', qq(
+ ALTER SUBSCRIPTION tap_sub SET PUBLICATION tap_pub1, tap_pub2;
+ ALTER SUBSCRIPTION tap_sub REFRESH SEQUENCES;
+));
+$synced_query =
+ "SELECT count(1) = 0 FROM pg_subscription_rel WHERE srsubstate NOT IN ('r');";
+$node_subscriber->poll_query_until('postgres', $synced_query)
+ or die "Timed out while waiting for subscriber to synchronize data";
+
+# seq1 is excluded in tap_pub1 but included in tap_pub2, so overall the
+# subscription treats it as included.
+$result = $node_subscriber->safe_psql('postgres',
+ "SELECT last_value, is_called FROM seq1");
+is($result, '200|t',
+ 'check replication of a sequence in the EXCEPT clause of one publication but included by another'
+);
+
+# seq2 is excluded in tap_pub2 but included in tap_pub1, so overall the
+# subscription treats it as included.
+$result = $node_subscriber->safe_psql('postgres',
+ "SELECT last_value, is_called FROM seq2");
+is($result, '200|t',
+ 'check replication of a sequence in the EXCEPT clause of one publication but included by another'
+);
+
+$node_subscriber->safe_psql('postgres', 'DROP SUBSCRIPTION tap_sub');
+$node_publisher->safe_psql('postgres', 'DROP PUBLICATION tap_pub1');
+$node_publisher->safe_psql('postgres', 'DROP PUBLICATION tap_pub2');
+
$node_publisher->stop('fast');
done_testing();
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index c5db6ca6705..b09f71e790a 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -2472,8 +2472,8 @@ PublicationObjSpecType
PublicationPartOpt
PublicationRelInfo
PublicationRelKind
+PublicationRelation
PublicationSchemaInfo
-PublicationTable
PublishGencolsType
PullFilter
PullFilterOps
--
2.34.1
view thread (41+ 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]
Subject: Re: Support EXCEPT for ALL SEQUENCES publications
In-Reply-To: <CANhcyEWfzDRCiny6A_swAD7WUgdb37-TA4xTAUQ3JJXK9UPBZQ@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