On Mon, Mar 16, 2026 at 8:39 PM Amit Kapila <[email protected]> wrote: > > On Mon, Mar 16, 2026 at 2:08 PM Peter Smith <[email protected]> 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.
Right. Doing "SET ALL TABLES, ALL SEQUENCES" is another way to achieve the same outcome using one ALTER command instead of two. But this way requires adding the currently missing SET/ADD/DROP ALL SEQUENCES syntax. > > > -- 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. > The basic expected SET/ADD/DROP behaviours are already very clear; It's just that they are not yet implemented for "ALL TABLES" and "ALL SEQUENCES". Ideally, those would be prerequisites for this EXCEPT patch. If they are done separately later, then it means in the interim, there will be some publication clause combinations that can lead to dead ends, where the user would be unable to modify the publication as they want without dropping and recreating it. Maybe that is OK. Perhaps having functionality gaps doesn't matter as much as I think it does: - e.g1. The FOR ALL SEQUENCES has existed for some months, and we cannot use ALTER to change it. Nobody complained? - e.g2. For years, it's been impossible to convert an "empty" publication into a FOR ALL TABLES publication. Nobody complained? OTOH, doing them in the right order is preferable to avoid churning the ALTER PUBLICATION docs page synopsis/description. ------ Kind Regards, Peter Smith. Fujitsu Australia.
