Re: Support EXCEPT for ALL SEQUENCES publications - Mailing list pgsql-hackers

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] -
https://www.postgresql.org/message-id/flat/CAHut%2BPt3t_tCYwDStkj5fG4Z%3DYXrHvPBA7iGdh745QipC5zKeg%40mail.gmail.com

Regards,
Vignesh