Re: Support EXCEPT for ALL SEQUENCES publications

On Sun, 12 Apr 2026 at 00:32, Shlok Kyal <shlok(dot)kyal(dot)oss(at)gmail(dot)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_seq0

Regards,
Vignesh