Re: Skipping schema changes in publication

On Mon, Mar 16, 2026 at 2:08 PM Peter Smith <smithpb2250(at)gmail(dot)com> wrote:
>
> IMO, a user will need to take care when using ALTER PUBLICATION ...
> SET ALL TABLES that it does not destroy the publication of sequences
> (and vice versa)
>
> -- Start with an "empty" publication and make it a "FOR ALL TABLES"
> publication...
> CREATE PUBLICATION pub;
> ALTER PUBLICATION pub SET ALL TABLES;
> -- result is equivalent to "CREATE PUBLICATION ... FOR ALL TABLES"
>
>
> -- give some table exceptions to it
> ALTER PUBLICATION pub SET ALL TABLES EXCEPT TABLE(t1,t2);
> -- result is equivalent to "CREATE PUBLICATION ... FOR ALL TABLES
> EXCEPT TABLE(t1,t2)"
>
>
> -- add sequences to this
> ALTER PUBLICATION pub ADD ALL SEQUENCES
> -- result is equivalent to "CREATE PUBLICATION ... FOR ALL TABLES
> EXCEPT TABLE(t1,t2), FOR ALL SEQUENCES"
>
>
> -- remove the table exception
> -- here you cannot simply use SET ALL TABLES because you will lose the
> ALL SEQUENCES part of the publication!!

Though the patch is not doing that but the syntax intends that. So, we
should have a variant like "Alter Publication pub1 Set All Tables, All
Sequences" to retain both.

> -- So, instead you need to do like below
> ALTER PUBLICATION pub DROP ALL TABLES;
> ALTER PUBLICATION pub ADD ALL TABLES;
> -- result is equivalent to "CREATE PUBLICATION ... FOR ALL TABLES, FOR
> ALL SEQUENCES"
>

I think we can make a separate case for ADD/DROP rather than tie it
along with this patch. For example, DROP ALL Tables could be used for
ALL TABLES and non-ALL TABLES publication, for later it could mean to
drop all the publicized tables and for earlier it just toggles the
flag.

--
With Regards,
Amit Kapila.