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

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