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

On Mon, 13 Apr 2026 at 11:23, 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  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> [, ... ]

I checked the syntax and think that it is correct.

The syntax is as follows:
    ALL SEQUENCES [ EXCEPT ( <replaceable
class="parameter">except_sequence_object</replaceable> [, ... ] ) ]
.
.
<phrase>and <replaceable
class="parameter">except_sequence_object</replaceable> is:</phrase>

    SEQUENCE <replaceable class="parameter">sequence_name</replaceable> [, ... ]

The [,..] mentioned after "sequence_name" will handle the case:
CREATE PUBLICATION all_sequences_except FOR ALL SEQUENCES EXCEPT
(SEQUENCE seq1, seq2)
and the [,..] mentioned after "except_sequence_object" will handle the case:
CREATE PUBLICATION all_sequences_except FOR ALL SEQUENCES EXCEPT
(SEQUENCE seq1, SEQUENCE seq2);

And the syntax added is similar to the existing syntax for ALL TABLES
(EXCEPT TABLE .. ) case.
So, I think the syntax is correct. Am I missing something?

>
> 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_seq0
>

I have addressed the remaining comments in [1].
[1]: https://www.postgresql.org/message-id/CANhcyEU_Yq9ZJ2n5Sqa7RoHze0TD0RGxLQQgV1F6Jm2AROEh8g%40mail.gmail.com

Thanks,
Shlok Kyal