Support EXCEPT for ALL SEQUENCES publications
Hi hackers,
Thread [1]/messages/by-id/CALDaNm3=JrucjhiiwsYQw5-PGtBHFONa6F7hhWCXMsGvh=tamA@mail.gmail.com introduced support for the EXCEPT clause for publications
defined with ALL TABLES. To extend this functionality, as discussed in
[2]: /messages/by-id/CAA4eK1JWz=+-zEhQRszrGEbOzYKReXtsS9hGycWubHV8v2U-QA@mail.gmail.com
publications defined with ALL SEQUENCES.
The series consists of the following patches:
0001: Support EXCEPT for ALL SEQUENCES in CREATE PUBLICATION
This allows excluding specific sequences when using CREATE PUBLICATION
... FOR ALL SEQUENCES.
Example:
CREATE PUBLICATION pub1 FOR ALL SEQUENCES EXCEPT (SEQUENCE s1, s2);
0002: Support EXCEPT for ALL SEQUENCES in ALTER PUBLICATION
This extends ALTER PUBLICATION to manage exclusions for ALL SEQUENCES.
Examples:
ALTER PUBLICATION pub1 SET ALL SEQUENCES;
This clears any existing sequence exclusions.
ALTER PUBLICATION pub1 SET ALL SEQUENCES EXCEPT (SEQUENCE s1, s2);
This replaces the exclusion list with the specified sequences.
Sequences listed in the EXCEPT clause are excluded from the
publication and are not replicated to the subscriber.
This brings sequence publication behavior in line with the existing
support for FOR ALL TABLES ... EXCEPT.
[1]: /messages/by-id/CALDaNm3=JrucjhiiwsYQw5-PGtBHFONa6F7hhWCXMsGvh=tamA@mail.gmail.com
[2]: /messages/by-id/CAA4eK1JWz=+-zEhQRszrGEbOzYKReXtsS9hGycWubHV8v2U-QA@mail.gmail.com
Thanks,
Shlok Kyal
Attachments:
v1-0001-Support-EXCEPT-for-ALL-SEQUENCES-in-CREATE-PUBLIC.patchapplication/octet-stream; name=v1-0001-Support-EXCEPT-for-ALL-SEQUENCES-in-CREATE-PUBLIC.patchDownload+447-131
v1-0002-Support-EXCEPT-for-ALL-SEQUENCES-in-ALTER-PUBLICA.patchapplication/octet-stream; name=v1-0002-Support-EXCEPT-for-ALL-SEQUENCES-in-ALTER-PUBLICA.patchDownload+200-139
On Fri, 10 Apr 2026 at 11:39, Shlok Kyal <shlok.kyal.oss@gmail.com> wrote:
Hi hackers,
Thread [1] introduced support for the EXCEPT clause for publications
defined with ALL TABLES. To extend this functionality, as discussed in
[2], this patch series adds support for the EXCEPT clause for
publications defined with ALL SEQUENCES.The series consists of the following patches:
0001: Support EXCEPT for ALL SEQUENCES in CREATE PUBLICATION
This allows excluding specific sequences when using CREATE PUBLICATION
... FOR ALL SEQUENCES.
Example:
CREATE PUBLICATION pub1 FOR ALL SEQUENCES EXCEPT (SEQUENCE s1, s2);0002: Support EXCEPT for ALL SEQUENCES in ALTER PUBLICATION
This extends ALTER PUBLICATION to manage exclusions for ALL SEQUENCES.
Examples:
ALTER PUBLICATION pub1 SET ALL SEQUENCES;
This clears any existing sequence exclusions.ALTER PUBLICATION pub1 SET ALL SEQUENCES EXCEPT (SEQUENCE s1, s2);
This replaces the exclusion list with the specified sequences.Sequences listed in the EXCEPT clause are excluded from the
publication and are not replicated to the subscriber.
+1 for this feature.
Currently, FOR ALL SEQUENCES is effectively an all-or-nothing choice.
In practice, there are often specific sequences, such as temporary or
locally managed ones, that do not need to be replicated. With this
change, those can now be excluded cleanly.
Regards,
Vignesh
On Fri, Apr 10, 2026 at 11:59 AM vignesh C <vignesh21@gmail.com> wrote:
On Fri, 10 Apr 2026 at 11:39, Shlok Kyal <shlok.kyal.oss@gmail.com> wrote:
Hi hackers,
Thread [1] introduced support for the EXCEPT clause for publications
defined with ALL TABLES. To extend this functionality, as discussed in
[2], this patch series adds support for the EXCEPT clause for
publications defined with ALL SEQUENCES.The series consists of the following patches:
0001: Support EXCEPT for ALL SEQUENCES in CREATE PUBLICATION
This allows excluding specific sequences when using CREATE PUBLICATION
... FOR ALL SEQUENCES.
Example:
CREATE PUBLICATION pub1 FOR ALL SEQUENCES EXCEPT (SEQUENCE s1, s2);0002: Support EXCEPT for ALL SEQUENCES in ALTER PUBLICATION
This extends ALTER PUBLICATION to manage exclusions for ALL SEQUENCES.
Examples:
ALTER PUBLICATION pub1 SET ALL SEQUENCES;
This clears any existing sequence exclusions.ALTER PUBLICATION pub1 SET ALL SEQUENCES EXCEPT (SEQUENCE s1, s2);
This replaces the exclusion list with the specified sequences.Sequences listed in the EXCEPT clause are excluded from the
publication and are not replicated to the subscriber.+1 for this feature.
Currently, FOR ALL SEQUENCES is effectively an all-or-nothing choice.
In practice, there are often specific sequences, such as temporary or
locally managed ones, that do not need to be replicated. With this
change, those can now be excluded cleanly.
+1 on the idea. The use cases will be such temproary or short
duration sequences (created for temp purpose but persisted ones) which
are node specific and may not require replication to replica node.
I have not finished full review yet, but few comments on 001:
1)
postgres=# create unlogged sequence seq4;
CREATE SEQUENCE
postgres=# create publication pub9 for all sequences EXCEPT ( SEQUENCE seq4);
ERROR: cannot specify relation "seq4" in the publication EXCEPT clause
DETAIL: This operation is not supported for unlogged tables.
Detail need to be corrected. Same for temporary sequence.
2)
/*
* Add or remove table to/from publication.
*/
AlterPublicationTables
This function is now called even to add sequence in except-clause.
Shall we change the name and comment?
3)
CreatePublication():
+ if (stmt->for_all_sequences && exceptseqs != NIL)
+ {
+ List *rels;+ /* Process EXCEPT sequence list */
+ rels = OpenRelationList(exceptseqs);
+ PublicationAddRelations(puboid, rels, true, NULL, RELKIND_SEQUENCE);
+ CloseRelationList(rels);
+ }
if (stmt->for_all_tables)We can have a blank line in between.
4)
Below comment in getPublications() need update to incoporate sequence
in full comment.
* Get the list of tables for publications specified in the EXCEPT
* TABLE clause.
*
* Although individual table entries in EXCEPT list could be stored in
...
thanks
Shveta
On Fri, 10 Apr 2026 at 11:39, Shlok Kyal <shlok.kyal.oss@gmail.com> wrote:
Hi hackers,
Thread [1] introduced support for the EXCEPT clause for publications
defined with ALL TABLES. To extend this functionality, as discussed in
[2], this patch series adds support for the EXCEPT clause for
publications defined with ALL SEQUENCES.The series consists of the following patches:
0001: Support EXCEPT for ALL SEQUENCES in CREATE PUBLICATION
This allows excluding specific sequences when using CREATE PUBLICATION
... FOR ALL SEQUENCES.
Example:
CREATE PUBLICATION pub1 FOR ALL SEQUENCES EXCEPT (SEQUENCE s1, s2);0002: Support EXCEPT for ALL SEQUENCES in ALTER PUBLICATION
This extends ALTER PUBLICATION to manage exclusions for ALL SEQUENCES.
Examples:
ALTER PUBLICATION pub1 SET ALL SEQUENCES;
This clears any existing sequence exclusions.ALTER PUBLICATION pub1 SET ALL SEQUENCES EXCEPT (SEQUENCE s1, s2);
This replaces the exclusion list with the specified sequences.Sequences listed in the EXCEPT clause are excluded from the
publication and are not replicated to the subscriber.This brings sequence publication behavior in line with the existing
support for FOR ALL TABLES ... EXCEPT.
Few comments on first patch:
1) There are few warnings while building:
gram.y: warning: 1 nonterminal useless in grammar [-Wother]
gram.y: warning: 2 rules useless in grammar [-Wother]
gram.y:14038.1-12: warning: nonterminal useless in grammar:
opt_sequence [-Wother]
14038 | opt_sequence: SEQUENCE
| ^~~~~~~~~~~~
preproc.y: warning: 1 nonterminal useless in grammar [-Wother]
preproc.y: warning: 2 rules useless in grammar [-Wother]
preproc.y:5589.2-13: warning: nonterminal useless in grammar:
opt_sequence [-Wother]
5589 | opt_sequence:
| ^~~~~~~~~~~~
2) The renaming of table to relation can be moved to a separate patch
and kept as 0001 patch:
2.a) pubtable to pubrelation
- $$->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;2.b) except_tables to except_relations
- $$->except_tables = $3;
+ $$->except_relations = $3;similarly there may be few others
3) In case of except table we have used "Except tables:", we can
change it to "Except sequences:" to keep it consistent:
+ if (!addFooterToPublicationDesc(&buf,
_("Except Sequences:"),
+
true, &cont))
+ goto error_return;Also it is agreed at [1]/messages/by-id/CAHut+Pt3t_tCYwDStkj5fG4Z=YXrHvPBA7iGdh745QipC5zKeg@mail.gmail.com to use lower case for the second one.
4) add one example in doc for except (sequence)
5) Tab completion for "create publication pub1 for all sequences
except (" is missing:
+ 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);[1]: /messages/by-id/CAHut+Pt3t_tCYwDStkj5fG4Z=YXrHvPBA7iGdh745QipC5zKeg@mail.gmail.com
Regards,
Vignesh
On Fri, 10 Apr 2026 at 16:31, vignesh C <vignesh21@gmail.com> wrote:
On Fri, 10 Apr 2026 at 11:39, Shlok Kyal <shlok.kyal.oss@gmail.com> wrote:
Hi hackers,
Thread [1] introduced support for the EXCEPT clause for publications
defined with ALL TABLES. To extend this functionality, as discussed in
[2], this patch series adds support for the EXCEPT clause for
publications defined with ALL SEQUENCES.The series consists of the following patches:
0001: Support EXCEPT for ALL SEQUENCES in CREATE PUBLICATION
This allows excluding specific sequences when using CREATE PUBLICATION
... FOR ALL SEQUENCES.
Example:
CREATE PUBLICATION pub1 FOR ALL SEQUENCES EXCEPT (SEQUENCE s1, s2);0002: Support EXCEPT for ALL SEQUENCES in ALTER PUBLICATION
This extends ALTER PUBLICATION to manage exclusions for ALL SEQUENCES.
Examples:
ALTER PUBLICATION pub1 SET ALL SEQUENCES;
This clears any existing sequence exclusions.ALTER PUBLICATION pub1 SET ALL SEQUENCES EXCEPT (SEQUENCE s1, s2);
This replaces the exclusion list with the specified sequences.Sequences listed in the EXCEPT clause are excluded from the
publication and are not replicated to the subscriber.This brings sequence publication behavior in line with the existing
support for FOR ALL TABLES ... EXCEPT.Few comments on first patch:
1) There are few warnings while building:
gram.y: warning: 1 nonterminal useless in grammar [-Wother]
gram.y: warning: 2 rules useless in grammar [-Wother]
gram.y:14038.1-12: warning: nonterminal useless in grammar:
opt_sequence [-Wother]
14038 | opt_sequence: SEQUENCE
| ^~~~~~~~~~~~
preproc.y: warning: 1 nonterminal useless in grammar [-Wother]
preproc.y: warning: 2 rules useless in grammar [-Wother]
preproc.y:5589.2-13: warning: nonterminal useless in grammar:
opt_sequence [-Wother]
5589 | opt_sequence:
| ^~~~~~~~~~~~2) The renaming of table to relation can be moved to a separate patch and kept as 0001 patch: 2.a) pubtable to pubrelation - $$->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;2.b) except_tables to except_relations - $$->except_tables = $3; + $$->except_relations = $3;similarly there may be few others
3) In case of except table we have used "Except tables:", we can change it to "Except sequences:" to keep it consistent: + if (!addFooterToPublicationDesc(&buf, _("Except Sequences:"), + true, &cont)) + goto error_return;Also it is agreed at [1] to use lower case for the second one.
4) add one example in doc for except (sequence)
5) Tab completion for "create publication pub1 for all sequences
except (" is missing:+ 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);[1] - /messages/by-id/CAHut+Pt3t_tCYwDStkj5fG4Z=YXrHvPBA7iGdh745QipC5zKeg@mail.gmail.com
Hi Vignesh and Shveta,
Thanks for reviewing the patches.
I have addressed the comments and attached the updated patch.
This also addressed the comments shared by Shveta in [1]/messages/by-id/CAJpy0uDU0PrH=gvFZjpphOX7t=2jH5wTqYry=C22vnuJJ9Q5=g@mail.gmail.com.
[1]: /messages/by-id/CAJpy0uDU0PrH=gvFZjpphOX7t=2jH5wTqYry=C22vnuJJ9Q5=g@mail.gmail.com
Thanks,
Shlok Kyal
Attachments:
v2-0001-Rename-identifiers-to-use-generic-relation-orient.patchapplication/octet-stream; name=v2-0001-Rename-identifiers-to-use-generic-relation-orient.patchDownload+91-90
v2-0002-Support-EXCEPT-for-ALL-SEQUENCES-in-CREATE-PUBLIC.patchapplication/octet-stream; name=v2-0002-Support-EXCEPT-for-ALL-SEQUENCES-in-CREATE-PUBLIC.patchDownload+384-60
v2-0003-Support-EXCEPT-for-ALL-SEQUENCES-in-ALTER-PUBLICA.patchapplication/octet-stream; name=v2-0003-Support-EXCEPT-for-ALL-SEQUENCES-in-ALTER-PUBLICA.patchDownload+200-135
On Sun, 12 Apr 2026 at 00:32, Shlok Kyal <shlok.kyal.oss@gmail.com> wrote:
On Fri, 10 Apr 2026 at 16:31, vignesh C <vignesh21@gmail.com> wrote:
On Fri, 10 Apr 2026 at 11:39, Shlok Kyal <shlok.kyal.oss@gmail.com> wrote:
Hi hackers,
Thread [1] introduced support for the EXCEPT clause for publications
defined with ALL TABLES. To extend this functionality, as discussed in
[2], this patch series adds support for the EXCEPT clause for
publications defined with ALL SEQUENCES.The series consists of the following patches:
0001: Support EXCEPT for ALL SEQUENCES in CREATE PUBLICATION
This allows excluding specific sequences when using CREATE PUBLICATION
... FOR ALL SEQUENCES.
Example:
CREATE PUBLICATION pub1 FOR ALL SEQUENCES EXCEPT (SEQUENCE s1, s2);0002: Support EXCEPT for ALL SEQUENCES in ALTER PUBLICATION
This extends ALTER PUBLICATION to manage exclusions for ALL SEQUENCES.
Examples:
ALTER PUBLICATION pub1 SET ALL SEQUENCES;
This clears any existing sequence exclusions.ALTER PUBLICATION pub1 SET ALL SEQUENCES EXCEPT (SEQUENCE s1, s2);
This replaces the exclusion list with the specified sequences.Sequences listed in the EXCEPT clause are excluded from the
publication and are not replicated to the subscriber.This brings sequence publication behavior in line with the existing
support for FOR ALL TABLES ... EXCEPT.Few comments on first patch:
1) There are few warnings while building:
gram.y: warning: 1 nonterminal useless in grammar [-Wother]
gram.y: warning: 2 rules useless in grammar [-Wother]
gram.y:14038.1-12: warning: nonterminal useless in grammar:
opt_sequence [-Wother]
14038 | opt_sequence: SEQUENCE
| ^~~~~~~~~~~~
preproc.y: warning: 1 nonterminal useless in grammar [-Wother]
preproc.y: warning: 2 rules useless in grammar [-Wother]
preproc.y:5589.2-13: warning: nonterminal useless in grammar:
opt_sequence [-Wother]
5589 | opt_sequence:
| ^~~~~~~~~~~~2) The renaming of table to relation can be moved to a separate patch and kept as 0001 patch: 2.a) pubtable to pubrelation - $$->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;2.b) except_tables to except_relations - $$->except_tables = $3; + $$->except_relations = $3;similarly there may be few others
3) In case of except table we have used "Except tables:", we can change it to "Except sequences:" to keep it consistent: + if (!addFooterToPublicationDesc(&buf, _("Except Sequences:"), + true, &cont)) + goto error_return;Also it is agreed at [1] to use lower case for the second one.
4) add one example in doc for except (sequence)
5) Tab completion for "create publication pub1 for all sequences
except (" is missing:+ 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);[1] - /messages/by-id/CAHut+Pt3t_tCYwDStkj5fG4Z=YXrHvPBA7iGdh745QipC5zKeg@mail.gmail.com
Hi Vignesh and Shveta,
Thanks for reviewing the patches.
I have addressed the comments and attached the updated patch.
FYI, this patch no longer applies cleanly after commit
49ce41810faca2722424b3d8fabda79bf4902339, which also includes changes
in pg_dump.
git am v2-0002-Support-EXCEPT-for-ALL-SEQUENCES-in-CREATE-PUBLIC.patch
Applying: Support EXCEPT for ALL SEQUENCES in CREATE PUBLICATION
error: patch failed: src/bin/pg_dump/pg_dump.c:4721
error: src/bin/pg_dump/pg_dump.c: patch does not apply
You can handle this in the next version. I will use an older version
to review currently.
Regards,
Vignesh
On Sun, Apr 12, 2026 at 12:33 AM Shlok Kyal <shlok.kyal.oss@gmail.com> wrote:
Hi Vignesh and Shveta,
Thanks for reviewing the patches.
I have addressed the comments and attached the updated patch.This also addressed the comments shared by Shveta in [1].
[1]: /messages/by-id/CAJpy0uDU0PrH=gvFZjpphOX7t=2jH5wTqYry=C22vnuJJ9Q5=g@mail.gmail.com
Thanks Shlok. The patch does not apply to latest head. Would you be
able ot rebase and post?
thanks
Shveta
On Sun, 12 Apr 2026 at 00:32, Shlok Kyal <shlok.kyal.oss@gmail.com> wrote:
Hi Vignesh and Shveta,
Thanks for reviewing the patches.
I have addressed the comments and attached the updated patch.
Few comments for the first patch:
1) I felt these also could be moved to first patch:
- * Gets list of table oids that were specified in the EXCEPT clause for a
+ * Gets list of relation oids that were specified in the EXCEPT clause for a2) Similarly here too:
- * The returned tables are locked in ShareUpdateExclusiveLock mode in order to
- * add them to a publication.
+ * The returned relations are locked in ShareUpdateExclusiveLock mode in order
+ * to add them to a publication.3) Similarly here too:
/*
- * Add listed tables to the publication.
+ * Add listed relations to the publication.
*/4) Similarly here too:
- /* Must be owner of the table or superuser. */
+ /* Must be owner of the relation or superuser. */5) Ordering issue here, PublicationRelation should be after PublicationRelKind:
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index ea95e7984bc..ff20501223c 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -2461,7 +2461,7 @@ PublicationPartOpt
PublicationRelInfo
PublicationRelKind
PublicationSchemaInfo
-PublicationTable
+PublicationRelationRegards,
Vignesh
Hi Shlok.
Before this patch goes too far, I had a fundamental question.
I understand that sequences and tables are closely related; sequences
are just like a single-row table, but they have a RELKIND_SEQUENCE.
There is a lot of shared code internally, so I guess it is tempting to
specify both the published sequences and tables together in the System
Catalog 'pg_publication_rels'.
I'm just wondering whether that is really the best way to go?
Currently, pg_publication_rels has only tables. So they might be:
* only included tables -- from a publication using "FOR TABLE ..."
* only excluded tables -- from a publication using "FOR ALL TABLES
EXCEPT (TABLE ...)"
Because included/excluded tables cannot co-exist, we can easily know
the type of the CREATE/ALTER PUBLICATION command and the type of
'pg_publication_rels' content without digging deeper.
~~~
But introducing sequences introduces complexity. Now, AFAICT, we
cannot know what each row of 'pg_publication_rels' means without
inspecting the relation type of that row. e.g. now we have lots of
possible combinations like.
pg_publication_rels has:
* only included tables.
* only excluded tables.
* only excluded sequences.
* excluded tables and excluded sequences.
* included tables and excluded sequences.
Furthermore, there will be yet more combinations one day if the
individual "FOR SEQUENCE ..." syntax is implemented.
pg_publication_rels has:
* only included sequences
* included sequences and included tables
* included sequences and excluded tables
IIUC, it means that the SQL everywhere now requires joins and relkind
checks to identify the type.
~~
Furthermore, AFAICT, the 'pg_publication_rels' attributes 'prattrs'
and 'prquals' don't even have meaning for sequences. That's another
reason why it feels a bit like a square peg was jammed into a round
hole just because 'pg_publication_rels' was conveniently available.
~~
SUMMARY
Given:
* Option 1 = use 'pg_publication_rels' for both tables and sequences.
* Option 2 = use 'pg_publication_rels' just for tables and use a new
'pg_publication_seq' just for sequences.
I'm not convinced that the chosen way (Option 1) is better. Can you
explain why it is?
======
Kind Regards,
Peter Smith.
Fujitsu Australia
On Sun, Apr 12, 2026 at 12:33 AM Shlok Kyal <shlok.kyal.oss@gmail.com> wrote:
Hi Vignesh and Shveta,
Thanks for reviewing the patches.
I have addressed the comments and attached the updated patch.This also addressed the comments shared by Shveta in [1].
[1]: /messages/by-id/CAJpy0uDU0PrH=gvFZjpphOX7t=2jH5wTqYry=C22vnuJJ9Q5=g@mail.gmail.com
Please find few comments on 001 and 002:
v-001:
1)
- List *except_tables; /* tables specified in the EXCEPT clause */
+ List *except_relations; /* relations specified in the EXCEPT
+ * clause */
Since we have not changed the comments for anything else in patch001,
we can keep this comment too same as old and changeit in 002.v-002:
2)
pg_publication_rel's prrelid doc says:
Reference to table
We shall change it now to 'Reference to table or sequence'
3)
In doc, do we eed to change pg_publication_rel's prqual too? IMO, it
is not applicable to sequence and thus we can change 'relation' to
'table' in explanation..
4)
Marks the publication as one that synchronizes changes for all sequences
- in the database, including sequences created in the future.
+ in the database, including sequences created in the future. Sequences
+ listed in <literal>EXCEPT</literal> clause are excluded from the
+ publication.I think we should place it the end of second paragraph rather than at
the end of first. How about something liek this:
Marks the publication as one that synchronizes changes for all
sequences in the database, including sequences created in the future.
Only persistent sequences are included in the publication. Temporary
sequences and unlogged sequences are excluded from the publication.
Sequences listed in EXCEPT clause are also excluded from the
publication.
5)
+ In such a case, a table or 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.'a table or partition or sequence' can be changed to 'a table,
partition, or sequence'
6)
In existign doc, shall we give example of publication creation for
both tables and sequences, each having its except list? This is
important to show that EXCEPT to be given with individual ALL OBJ. We
can cahnge last example of doc file to make this. This one:
'Create a publication that publishes all sequences for
synchronization, and all changes in all tables except users and
departments:'
7)
getPublications:
+ * Get the list of tables/sequences for publications specified in the
+ * EXCEPT clause.We can have both tables and sequences in single publication. We should change
'tables/sequences' --> tables and sequences
8)
In describePublications(),
We had:
if (!puballtables)
else
* Get tables in the EXCEPT clause for this publication */
now we have added:
if (puballsequences)
/* Get sequences in the EXCEPT clause for this publication */
Since now we can hit this function for 'all-seq' pub too, shall we
change if-block's condition to:
if (!puballtables && !puballsequences)
and else-block to
else if (puballtables)
otherwise all-seq case will unnecessary enter these blocks and will
exceute the logic
Please review other functions too in pg_dump to see if we need such
conditions altering.
9)
+# Check the initial data on subscriber
+$result = $node_subscriber->safe_psql('postgres',
+ "SELECT last_value, is_called FROM seq1");
+is($result, '200|t', 'sequences in EXCEPT list is excluded');
+
+$result = $node_subscriber->safe_psql('postgres',
+ "SELECT last_value, is_called FROM seq2");
+is($result, '200|t', 'initial test data replicated for seq2');Since both are replicated now because of conflicting EXCEPT in
multi-pub, shall we change
comment in 'is(..)'?
thanks
Shveta
On Mon, Apr 13, 2026 at 9:52 AM vignesh C <vignesh21@gmail.com> wrote:
On Sun, 12 Apr 2026 at 00:32, Shlok Kyal <shlok.kyal.oss@gmail.com> wrote:
Hi Vignesh and Shveta,
Thanks for reviewing the patches.
I have addressed the comments and attached the updated patch.Few comments for the first patch: 1) I felt these also could be moved to first patch: - * Gets list of table oids that were specified in the EXCEPT clause for a + * Gets list of relation oids that were specified in the EXCEPT clause for a
I noticed all these, but since we are actually fetching sequences in
patch002,. I thought changing comments made more sense in patch002 as
done by Shlok currently. But I don't have a strong opinion here, so
fine with this too.
Show quoted text
2) Similarly here too: - * The returned tables are locked in ShareUpdateExclusiveLock mode in order to - * add them to a publication. + * The returned relations are locked in ShareUpdateExclusiveLock mode in order + * to add them to a publication.3) Similarly here too: /* - * Add listed tables to the publication. + * Add listed relations to the publication. */4) Similarly here too: - /* Must be owner of the table or superuser. */ + /* Must be owner of the relation or superuser. */5) Ordering issue here, PublicationRelation should be after PublicationRelKind: diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list index ea95e7984bc..ff20501223c 100644 --- a/src/tools/pgindent/typedefs.list +++ b/src/tools/pgindent/typedefs.list @@ -2461,7 +2461,7 @@ PublicationPartOpt PublicationRelInfo PublicationRelKind PublicationSchemaInfo -PublicationTable +PublicationRelationRegards,
Vignesh
On Sun, 12 Apr 2026 at 00:32, Shlok Kyal <shlok.kyal.oss@gmail.com> wrote:
Hi Vignesh and Shveta,
Thanks for reviewing the patches.
I have addressed the comments and attached the updated patch.
Few comments for the second patch patch:
1) This documentation can handle "CREATE PUBLICATION
all_sequences_except FOR ALL SEQUENCES EXCEPT (SEQUENCE seq1, SEQUENCE
seq2);" but cannot handle "CREATE PUBLICATION all_sequences_except FOR
ALL SEQUENCES EXCEPT (SEQUENCE seq1, seq2);
", should we document similar to how it is done for except table:
+ 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> [, ... ]2) There is no comment when run with --echo-hidden, we can include the
following:
printfPQExpBuffer(&buf, "/* %s */\n",
_("Get publications that exclude
this sequence"));
3) "Except Publications" should be "Except publications"
+ if (tuples > 0)
+ {
+ printfPQExpBuffer(&tmpbuf,
_("Except Publications:"));Check except table which handles similar:
+ /* Print publications where the sequence is in the
EXCEPT clause */
+ if (pset.sversion >= 190000)
+ {
+ printfPQExpBuffer(&buf,
+ "SELECT 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);4) Add one test for a publication having both EXCEPT TABLE AND EXCEPT SEQUENCE:
+ '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, },
+ },5) Similarly add one here too:
+-- 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_seq0Regards,
Vignesh
Hi Shlok.
Here are a couple of review comments about the documentation (patch v2-0002).
======
doc/src/sgml/logical-replication.sgml
(29.1. Publication #)
1a.
- <link linkend="sql-createpublication-params-for-except-table"><literal>EXCEPT</literal></link>
- clause.
+ <link linkend="sql-createpublication-params-for-except"><literal>EXCEPT</literal></link>
+ clause. When a publication is created with <literal>FOR ALL
SEQUENCES</literal>,
+ a sequence or set of sequences can be explicitly excluded from publication
+ using the
+ <link linkend="sql-createpublication-params-for-except"><literal>EXCEPT</literal></link>SUGGESTION
Similarly, when a publication is created with <literal>FOR ALL
SEQUENCES</literal>, a sequence or set of sequences can be explicitly
excluded from publication.
~~
1b.
Last year, I had suggested there should be an entirely separate
chapter in Logical Replication" for describing "Excluding objects from
the Publication". In the past, that idea was rejected because there
was only "EXCEPT tables". But now there is a growing list, so the idea
of having a separate chapter is becoming increasingly relevant. IMO, a
new chapter will be a good common place to describe everything, with
examples as necessary. It can help reduce some clutter from the CREATE
PUBLICATION page,
Then, this patch text (1a) could say something more like: "Specific
tables or sequences can be excluded from the publication. See XXX for
details."
======
doc/src/sgml/ref/create_publication.sgml
(EXCEPT)
2.
- This clause specifies a list of tables to be excluded from the
- publication.
+ This clause specifies a list of tables for <literal>ALL TABLES</literal>
+ publication or a list of sequences for <literal>ALL SEQUENCES</literal>
+ to be excluded from the publication.SUGGESTION:
This clause specifies the tables or sequences to exclude from an
<literal>ALL TABLES</literal> or <literal>ALL SEQUENCES</literal>
publication.
======
Kind Regards,
Peter Smith.
Fujitsu Australia
On Mon, Apr 13, 2026 at 9:58 AM Peter Smith <smithpb2250@gmail.com> wrote:
Hi Shlok.
Before this patch goes too far, I had a fundamental question.
I understand that sequences and tables are closely related; sequences
are just like a single-row table, but they have a RELKIND_SEQUENCE.There is a lot of shared code internally, so I guess it is tempting to
specify both the published sequences and tables together in the System
Catalog 'pg_publication_rels'.I'm just wondering whether that is really the best way to go?
Currently, pg_publication_rels has only tables. So they might be:
* only included tables -- from a publication using "FOR TABLE ..."
* only excluded tables -- from a publication using "FOR ALL TABLES
EXCEPT (TABLE ...)"Because included/excluded tables cannot co-exist, we can easily know
the type of the CREATE/ALTER PUBLICATION command and the type of
'pg_publication_rels' content without digging deeper.~~~
But introducing sequences introduces complexity. Now, AFAICT, we
cannot know what each row of 'pg_publication_rels' means without
inspecting the relation type of that row. e.g. now we have lots of
possible combinations like.pg_publication_rels has:
* only included tables.
* only excluded tables.
* only excluded sequences.
* excluded tables and excluded sequences.
* included tables and excluded sequences.Furthermore, there will be yet more combinations one day if the
individual "FOR SEQUENCE ..." syntax is implemented.pg_publication_rels has:
* only included sequences
* included sequences and included tables
* included sequences and excluded tablesIIUC, it means that the SQL everywhere now requires joins and relkind
checks to identify the type.~~
Furthermore, AFAICT, the 'pg_publication_rels' attributes 'prattrs'
and 'prquals' don't even have meaning for sequences. That's another
reason why it feels a bit like a square peg was jammed into a round
hole just because 'pg_publication_rels' was conveniently available.~~
SUMMARY
Given:
* Option 1 = use 'pg_publication_rels' for both tables and sequences.
* Option 2 = use 'pg_publication_rels' just for tables and use a new
'pg_publication_seq' just for sequences.I'm not convinced that the chosen way (Option 1) is better. Can you
explain why it is?
pg_publication_seq seems like an idea worth considering. After giving
it some thought, we already have a view named
pg_publication_sequences, and adding another catalog with a similar
name may introduce confusion. Apart from that, here are my thoughts:
1)
pg_publication_rels already represents 'relations', and thus I feel
sequences already fit naturally into this.
2)
I agree that relkind checks may be required, but IMO these are
typically cheap. In C code paths we already rely heavily on
cache-lookups such as get_rel_relkind, and thus pg_class.relkind
access is well-optimized. This is already used widely across the
codebase, so IMO, extending it here does not introduce meaningful
overhead.
3)
Looking at the code paths accessing pg_publication_rels, they broadly
fall into 4 categories:
a) Publication DDL (CREATE/ALTER/DROP PUBLICATION)
b) Tools and queries (e.g., pg_publication_tables view, pg_dump)
c) Logical decoding (pgoutput paths like get_rel_sync_entry,
pgoutput_column_list_init, pgoutput_row_filter_init)
d) DML paths (INSERT/UPDATE) to check publication membership and RI
applicability (CheckCmdReplicaIdentity)
The first two are infrequent operations. For the logical decoding
path, relation information is cached in RelationSyncCache, so relkind
checks are not repeatedly performed in the hot path. For INSERT/UPDATE
paths, PublicationDesc (rd_pubdesc) is built once per relation and is
cached and reused, so pg_publication_rels is not accessed heavily here
too.
~~
Considering all the above, I feel we should be good with Option 1.
But, I would like to see what others think on this.
thanks
Shveta
On Mon, 13 Apr 2026 at 15:46, shveta malik <shveta.malik@gmail.com> wrote:
On Mon, Apr 13, 2026 at 9:58 AM Peter Smith <smithpb2250@gmail.com> wrote:
Hi Shlok.
Before this patch goes too far, I had a fundamental question.
I understand that sequences and tables are closely related; sequences
are just like a single-row table, but they have a RELKIND_SEQUENCE.There is a lot of shared code internally, so I guess it is tempting to
specify both the published sequences and tables together in the System
Catalog 'pg_publication_rels'.I'm just wondering whether that is really the best way to go?
Currently, pg_publication_rels has only tables. So they might be:
* only included tables -- from a publication using "FOR TABLE ..."
* only excluded tables -- from a publication using "FOR ALL TABLES
EXCEPT (TABLE ...)"Because included/excluded tables cannot co-exist, we can easily know
the type of the CREATE/ALTER PUBLICATION command and the type of
'pg_publication_rels' content without digging deeper.~~~
But introducing sequences introduces complexity. Now, AFAICT, we
cannot know what each row of 'pg_publication_rels' means without
inspecting the relation type of that row. e.g. now we have lots of
possible combinations like.pg_publication_rels has:
* only included tables.
* only excluded tables.
* only excluded sequences.
* excluded tables and excluded sequences.
* included tables and excluded sequences.Furthermore, there will be yet more combinations one day if the
individual "FOR SEQUENCE ..." syntax is implemented.pg_publication_rels has:
* only included sequences
* included sequences and included tables
* included sequences and excluded tablesIIUC, it means that the SQL everywhere now requires joins and relkind
checks to identify the type.~~
Furthermore, AFAICT, the 'pg_publication_rels' attributes 'prattrs'
and 'prquals' don't even have meaning for sequences. That's another
reason why it feels a bit like a square peg was jammed into a round
hole just because 'pg_publication_rels' was conveniently available.~~
SUMMARY
Given:
* Option 1 = use 'pg_publication_rels' for both tables and sequences.
* Option 2 = use 'pg_publication_rels' just for tables and use a new
'pg_publication_seq' just for sequences.I'm not convinced that the chosen way (Option 1) is better. Can you
explain why it is?pg_publication_seq seems like an idea worth considering. After giving
it some thought, we already have a view named
pg_publication_sequences, and adding another catalog with a similar
name may introduce confusion. Apart from that, here are my thoughts:1)
pg_publication_rels already represents 'relations', and thus I feel
sequences already fit naturally into this.2)
I agree that relkind checks may be required, but IMO these are
typically cheap. In C code paths we already rely heavily on
cache-lookups such as get_rel_relkind, and thus pg_class.relkind
access is well-optimized. This is already used widely across the
codebase, so IMO, extending it here does not introduce meaningful
overhead.3)
Looking at the code paths accessing pg_publication_rels, they broadly
fall into 4 categories:a) Publication DDL (CREATE/ALTER/DROP PUBLICATION)
b) Tools and queries (e.g., pg_publication_tables view, pg_dump)
c) Logical decoding (pgoutput paths like get_rel_sync_entry,
pgoutput_column_list_init, pgoutput_row_filter_init)
d) DML paths (INSERT/UPDATE) to check publication membership and RI
applicability (CheckCmdReplicaIdentity)The first two are infrequent operations. For the logical decoding
path, relation information is cached in RelationSyncCache, so relkind
checks are not repeatedly performed in the hot path. For INSERT/UPDATE
paths, PublicationDesc (rd_pubdesc) is built once per relation and is
cached and reused, so pg_publication_rels is not accessed heavily here
too.~~
Considering all the above, I feel we should be good with Option 1.
But, I would like to see what others think on this.
+1 for option 1 and checking relkind for the same reason mentioned by
shveta above. It seems like a reasonable tradeoff compared to
introducing and maintaining a separate pg_publication_seq system
table.
Regards,
Vignesh
On Sun, 12 Apr 2026 at 00:32, Shlok Kyal <shlok.kyal.oss@gmail.com> wrote:
On Fri, 10 Apr 2026 at 16:31, vignesh C <vignesh21@gmail.com> wrote:
On Fri, 10 Apr 2026 at 11:39, Shlok Kyal <shlok.kyal.oss@gmail.com> wrote:
Hi hackers,
Thread [1] introduced support for the EXCEPT clause for publications
defined with ALL TABLES. To extend this functionality, as discussed in
[2], this patch series adds support for the EXCEPT clause for
publications defined with ALL SEQUENCES.The series consists of the following patches:
0001: Support EXCEPT for ALL SEQUENCES in CREATE PUBLICATION
This allows excluding specific sequences when using CREATE PUBLICATION
... FOR ALL SEQUENCES.
Example:
CREATE PUBLICATION pub1 FOR ALL SEQUENCES EXCEPT (SEQUENCE s1, s2);0002: Support EXCEPT for ALL SEQUENCES in ALTER PUBLICATION
This extends ALTER PUBLICATION to manage exclusions for ALL SEQUENCES.
Examples:
ALTER PUBLICATION pub1 SET ALL SEQUENCES;
This clears any existing sequence exclusions.ALTER PUBLICATION pub1 SET ALL SEQUENCES EXCEPT (SEQUENCE s1, s2);
This replaces the exclusion list with the specified sequences.Sequences listed in the EXCEPT clause are excluded from the
publication and are not replicated to the subscriber.This brings sequence publication behavior in line with the existing
support for FOR ALL TABLES ... EXCEPT.Few comments on first patch:
1) There are few warnings while building:
gram.y: warning: 1 nonterminal useless in grammar [-Wother]
gram.y: warning: 2 rules useless in grammar [-Wother]
gram.y:14038.1-12: warning: nonterminal useless in grammar:
opt_sequence [-Wother]
14038 | opt_sequence: SEQUENCE
| ^~~~~~~~~~~~
preproc.y: warning: 1 nonterminal useless in grammar [-Wother]
preproc.y: warning: 2 rules useless in grammar [-Wother]
preproc.y:5589.2-13: warning: nonterminal useless in grammar:
opt_sequence [-Wother]
5589 | opt_sequence:
| ^~~~~~~~~~~~2) The renaming of table to relation can be moved to a separate patch and kept as 0001 patch: 2.a) pubtable to pubrelation - $$->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;2.b) except_tables to except_relations - $$->except_tables = $3; + $$->except_relations = $3;similarly there may be few others
3) In case of except table we have used "Except tables:", we can change it to "Except sequences:" to keep it consistent: + if (!addFooterToPublicationDesc(&buf, _("Except Sequences:"), + true, &cont)) + goto error_return;Also it is agreed at [1] to use lower case for the second one.
4) add one example in doc for except (sequence)
5) Tab completion for "create publication pub1 for all sequences
except (" is missing:+ 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);[1] - /messages/by-id/CAHut+Pt3t_tCYwDStkj5fG4Z=YXrHvPBA7iGdh745QipC5zKeg@mail.gmail.com
Hi Vignesh and Shveta,
Thanks for reviewing the patches.
I have addressed the comments and attached the updated patch.
One issue with the alter publication patch:
Tab completion lists tables instead of sequences here:
postgres=# alter publication pub1 set all sequences EXCEPT ( SEQUENCE
information_schema. public. t1 t2
tbl1 tbl2
Here Query_for_list_of_tables should be Query_for_list_of_sequences:
+ else if (Matches("ALTER", "PUBLICATION", MatchAny, "SET",
"ALL", "SEQUENCES", "EXCEPT", "(", "SEQUENCE"))
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables);
+ 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);Regards,
Vignesh
On Mon, Apr 13, 2026 at 10:48 AM shveta malik <shveta.malik@gmail.com> wrote:
On Sun, Apr 12, 2026 at 12:33 AM Shlok Kyal <shlok.kyal.oss@gmail.com> wrote:
Hi Vignesh and Shveta,
Thanks for reviewing the patches.
I have addressed the comments and attached the updated patch.This also addressed the comments shared by Shveta in [1].
[1]: /messages/by-id/CAJpy0uDU0PrH=gvFZjpphOX7t=2jH5wTqYry=C22vnuJJ9Q5=g@mail.gmail.comPlease find few comments on 001 and 002:
v-001: 1) - List *except_tables; /* tables specified in the EXCEPT clause */ + List *except_relations; /* relations specified in the EXCEPT + * clause */ Since we have not changed the comments for anything else in patch001, we can keep this comment too same as old and changeit in 002.v-002:
2)
pg_publication_rel's prrelid doc says:
Reference to table
We shall change it now to 'Reference to table or sequence'3)
In doc, do we eed to change pg_publication_rel's prqual too? IMO, it
is not applicable to sequence and thus we can change 'relation' to
'table' in explanation..4) Marks the publication as one that synchronizes changes for all sequences - in the database, including sequences created in the future. + in the database, including sequences created in the future. Sequences + listed in <literal>EXCEPT</literal> clause are excluded from the + publication.I think we should place it the end of second paragraph rather than at
the end of first. How about something liek this:Marks the publication as one that synchronizes changes for all
sequences in the database, including sequences created in the future.Only persistent sequences are included in the publication. Temporary
sequences and unlogged sequences are excluded from the publication.
Sequences listed in EXCEPT clause are also excluded from the
publication.5) + In such a case, a table or 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.'a table or partition or sequence' can be changed to 'a table,
partition, or sequence'6)
In existign doc, shall we give example of publication creation for
both tables and sequences, each having its except list? This is
important to show that EXCEPT to be given with individual ALL OBJ. We
can cahnge last example of doc file to make this. This one:
'Create a publication that publishes all sequences for
synchronization, and all changes in all tables except users and
departments:'7) getPublications: + * Get the list of tables/sequences for publications specified in the + * EXCEPT clause.We can have both tables and sequences in single publication. We should change
'tables/sequences' --> tables and sequences
8)
In describePublications(),We had:
if (!puballtables)
else
* Get tables in the EXCEPT clause for this publication */now we have added:
if (puballsequences)
/* Get sequences in the EXCEPT clause for this publication */Since now we can hit this function for 'all-seq' pub too, shall we
change if-block's condition to:if (!puballtables && !puballsequences)
and else-block to
else if (puballtables)
otherwise all-seq case will unnecessary enter these blocks and will
exceute the logicPlease review other functions too in pg_dump to see if we need such
conditions altering.9) +# Check the initial data on subscriber +$result = $node_subscriber->safe_psql('postgres', + "SELECT last_value, is_called FROM seq1"); +is($result, '200|t', 'sequences in EXCEPT list is excluded'); + +$result = $node_subscriber->safe_psql('postgres', + "SELECT last_value, is_called FROM seq2"); +is($result, '200|t', 'initial test data replicated for seq2');Since both are replicated now because of conflicting EXCEPT in
multi-pub, shall we change
comment in 'is(..)'?
For v-003, one trivial thing:
Shall we change the name of AlterPublicationTables() as well? It now
deals in both tables and sequences.
thanks
Shveta
On Mon, 13 Apr 2026 at 10:51, shveta malik <shveta.malik@gmail.com> wrote:
On Mon, Apr 13, 2026 at 9:52 AM vignesh C <vignesh21@gmail.com> wrote:
On Sun, 12 Apr 2026 at 00:32, Shlok Kyal <shlok.kyal.oss@gmail.com> wrote:
Hi Vignesh and Shveta,
Thanks for reviewing the patches.
I have addressed the comments and attached the updated patch.Few comments for the first patch: 1) I felt these also could be moved to first patch: - * Gets list of table oids that were specified in the EXCEPT clause for a + * Gets list of relation oids that were specified in the EXCEPT clause for aI noticed all these, but since we are actually fetching sequences in
patch002,. I thought changing comments made more sense in patch002 as
done by Shlok currently. But I don't have a strong opinion here, so
fine with this too.
I agree with Shveta and had the same thought while splitting the
patch. Since we start fetching sequences in patch 0002, it makes more
sense to keep the comment changes there. I will keep the changes in
patch 0002.
Show quoted text
2) Similarly here too: - * The returned tables are locked in ShareUpdateExclusiveLock mode in order to - * add them to a publication. + * The returned relations are locked in ShareUpdateExclusiveLock mode in order + * to add them to a publication.3) Similarly here too: /* - * Add listed tables to the publication. + * Add listed relations to the publication. */4) Similarly here too: - /* Must be owner of the table or superuser. */ + /* Must be owner of the relation or superuser. */5) Ordering issue here, PublicationRelation should be after PublicationRelKind: diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list index ea95e7984bc..ff20501223c 100644 --- a/src/tools/pgindent/typedefs.list +++ b/src/tools/pgindent/typedefs.list @@ -2461,7 +2461,7 @@ PublicationPartOpt PublicationRelInfo PublicationRelKind PublicationSchemaInfo -PublicationTable +PublicationRelationRegards,
Vignesh
On Mon, 13 Apr 2026 at 16:21, vignesh C <vignesh21@gmail.com> wrote:
On Mon, 13 Apr 2026 at 15:46, shveta malik <shveta.malik@gmail.com> wrote:
On Mon, Apr 13, 2026 at 9:58 AM Peter Smith <smithpb2250@gmail.com> wrote:
Hi Shlok.
Before this patch goes too far, I had a fundamental question.
I understand that sequences and tables are closely related; sequences
are just like a single-row table, but they have a RELKIND_SEQUENCE.There is a lot of shared code internally, so I guess it is tempting to
specify both the published sequences and tables together in the System
Catalog 'pg_publication_rels'.I'm just wondering whether that is really the best way to go?
Currently, pg_publication_rels has only tables. So they might be:
* only included tables -- from a publication using "FOR TABLE ..."
* only excluded tables -- from a publication using "FOR ALL TABLES
EXCEPT (TABLE ...)"Because included/excluded tables cannot co-exist, we can easily know
the type of the CREATE/ALTER PUBLICATION command and the type of
'pg_publication_rels' content without digging deeper.~~~
But introducing sequences introduces complexity. Now, AFAICT, we
cannot know what each row of 'pg_publication_rels' means without
inspecting the relation type of that row. e.g. now we have lots of
possible combinations like.pg_publication_rels has:
* only included tables.
* only excluded tables.
* only excluded sequences.
* excluded tables and excluded sequences.
* included tables and excluded sequences.Furthermore, there will be yet more combinations one day if the
individual "FOR SEQUENCE ..." syntax is implemented.pg_publication_rels has:
* only included sequences
* included sequences and included tables
* included sequences and excluded tablesIIUC, it means that the SQL everywhere now requires joins and relkind
checks to identify the type.~~
Furthermore, AFAICT, the 'pg_publication_rels' attributes 'prattrs'
and 'prquals' don't even have meaning for sequences. That's another
reason why it feels a bit like a square peg was jammed into a round
hole just because 'pg_publication_rels' was conveniently available.~~
SUMMARY
Given:
* Option 1 = use 'pg_publication_rels' for both tables and sequences.
* Option 2 = use 'pg_publication_rels' just for tables and use a new
'pg_publication_seq' just for sequences.I'm not convinced that the chosen way (Option 1) is better. Can you
explain why it is?pg_publication_seq seems like an idea worth considering. After giving
it some thought, we already have a view named
pg_publication_sequences, and adding another catalog with a similar
name may introduce confusion. Apart from that, here are my thoughts:1)
pg_publication_rels already represents 'relations', and thus I feel
sequences already fit naturally into this.2)
I agree that relkind checks may be required, but IMO these are
typically cheap. In C code paths we already rely heavily on
cache-lookups such as get_rel_relkind, and thus pg_class.relkind
access is well-optimized. This is already used widely across the
codebase, so IMO, extending it here does not introduce meaningful
overhead.3)
Looking at the code paths accessing pg_publication_rels, they broadly
fall into 4 categories:a) Publication DDL (CREATE/ALTER/DROP PUBLICATION)
b) Tools and queries (e.g., pg_publication_tables view, pg_dump)
c) Logical decoding (pgoutput paths like get_rel_sync_entry,
pgoutput_column_list_init, pgoutput_row_filter_init)
d) DML paths (INSERT/UPDATE) to check publication membership and RI
applicability (CheckCmdReplicaIdentity)The first two are infrequent operations. For the logical decoding
path, relation information is cached in RelationSyncCache, so relkind
checks are not repeatedly performed in the hot path. For INSERT/UPDATE
paths, PublicationDesc (rd_pubdesc) is built once per relation and is
cached and reused, so pg_publication_rels is not accessed heavily here
too.~~
Considering all the above, I feel we should be good with Option 1.
But, I would like to see what others think on this.+1 for option 1 and checking relkind for the same reason mentioned by
shveta above. It seems like a reasonable tradeoff compared to
introducing and maintaining a separate pg_publication_seq system
table.
Thanks Peter, Shveta, and Vignesh for the analysis.
I agree with the points shared by Shveta and Vignesh and have verified
them. Based on the reasoning, I think Option 1 is better. I will
retain this design and use the pg_publication_rel to store sequences.
Thanks,
Shlok Kyal
On Tue, 14 Apr 2026 at 15:12, shveta malik <shveta.malik@gmail.com> wrote:
On Mon, Apr 13, 2026 at 10:48 AM shveta malik <shveta.malik@gmail.com> wrote:
On Sun, Apr 12, 2026 at 12:33 AM Shlok Kyal <shlok.kyal.oss@gmail.com> wrote:
Hi Vignesh and Shveta,
Thanks for reviewing the patches.
I have addressed the comments and attached the updated patch.This also addressed the comments shared by Shveta in [1].
[1]: /messages/by-id/CAJpy0uDU0PrH=gvFZjpphOX7t=2jH5wTqYry=C22vnuJJ9Q5=g@mail.gmail.comPlease find few comments on 001 and 002:
v-001: 1) - List *except_tables; /* tables specified in the EXCEPT clause */ + List *except_relations; /* relations specified in the EXCEPT + * clause */ Since we have not changed the comments for anything else in patch001, we can keep this comment too same as old and changeit in 002.v-002:
2)
pg_publication_rel's prrelid doc says:
Reference to table
We shall change it now to 'Reference to table or sequence'3)
In doc, do we eed to change pg_publication_rel's prqual too? IMO, it
is not applicable to sequence and thus we can change 'relation' to
'table' in explanation..4) Marks the publication as one that synchronizes changes for all sequences - in the database, including sequences created in the future. + in the database, including sequences created in the future. Sequences + listed in <literal>EXCEPT</literal> clause are excluded from the + publication.I think we should place it the end of second paragraph rather than at
the end of first. How about something liek this:Marks the publication as one that synchronizes changes for all
sequences in the database, including sequences created in the future.Only persistent sequences are included in the publication. Temporary
sequences and unlogged sequences are excluded from the publication.
Sequences listed in EXCEPT clause are also excluded from the
publication.5) + In such a case, a table or 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.'a table or partition or sequence' can be changed to 'a table,
partition, or sequence'6)
In existign doc, shall we give example of publication creation for
both tables and sequences, each having its except list? This is
important to show that EXCEPT to be given with individual ALL OBJ. We
can cahnge last example of doc file to make this. This one:
'Create a publication that publishes all sequences for
synchronization, and all changes in all tables except users and
departments:'7) getPublications: + * Get the list of tables/sequences for publications specified in the + * EXCEPT clause.We can have both tables and sequences in single publication. We should change
'tables/sequences' --> tables and sequences
8)
In describePublications(),We had:
if (!puballtables)
else
* Get tables in the EXCEPT clause for this publication */now we have added:
if (puballsequences)
/* Get sequences in the EXCEPT clause for this publication */Since now we can hit this function for 'all-seq' pub too, shall we
change if-block's condition to:if (!puballtables && !puballsequences)
and else-block to
else if (puballtables)
otherwise all-seq case will unnecessary enter these blocks and will
exceute the logicPlease review other functions too in pg_dump to see if we need such
conditions altering.9) +# Check the initial data on subscriber +$result = $node_subscriber->safe_psql('postgres', + "SELECT last_value, is_called FROM seq1"); +is($result, '200|t', 'sequences in EXCEPT list is excluded'); + +$result = $node_subscriber->safe_psql('postgres', + "SELECT last_value, is_called FROM seq2"); +is($result, '200|t', 'initial test data replicated for seq2');Since both are replicated now because of conflicting EXCEPT in
multi-pub, shall we change
comment in 'is(..)'?For v-003, one trivial thing:
Shall we change the name of AlterPublicationTables() as well? It now
deals in both tables and sequences.
Thanks for reviewing the patch. I agree that we should change the name
here. Modified the patch.
I have also addressed the remaining comments by you and Vignesh in [1]/messages/by-id/CALDaNm2pGi3FAkN+x+10nqFKNHOUdMwEgqt_TtjLbvz04F3Ktg@mail.gmail.com, [2]/messages/by-id/CAJpy0uBB4N8KOrHchdgprVi2Ws1+gTcEr+bC2A_ziAHOcZcTqA@mail.gmail.com, [3]/messages/by-id/CALDaNm1BRQ1s9na_gwLwN3BYER9be+4QNn4V8sDjiMUvao28Jg@mail.gmail.com
Attached the updated version.
[1]: /messages/by-id/CALDaNm2pGi3FAkN+x+10nqFKNHOUdMwEgqt_TtjLbvz04F3Ktg@mail.gmail.com
[2]: /messages/by-id/CAJpy0uBB4N8KOrHchdgprVi2Ws1+gTcEr+bC2A_ziAHOcZcTqA@mail.gmail.com
[3]: /messages/by-id/CALDaNm1BRQ1s9na_gwLwN3BYER9be+4QNn4V8sDjiMUvao28Jg@mail.gmail.com
Thanks,
Shlok Kyal