On Tue, 16 Jul 2024 at 06:00, Peter Smith <smithpb2...@gmail.com> wrote: > > Hi, > > I was reading back through this thread to find out how the proposed new > command for refreshing sequences, came about. The patch 0705 introduces a > new command syntax for ALTER SUBSCRIPTION ... REFRESH SEQUENCES > > So now there are 2 forms of subscription refresh. > > #1. ALTER SUBSCRIPTION name REFRESH PUBLICATION [ WITH ( refresh_option [= > value] [, ... ] ) ]
This is correct. > #2. ALTER SUBSCRIPTION name REFRESH SEQUENCES This is not correct, it is actually "ALTER SUBSCRIPTION name REFRESH PUBLICATION SEQUENCES" > ~~~~ > > IMO, that separation seems complicated. It leaves many questions like: > * It causes a bit of initial confusion. e.g. When I saw the REFRESH SEQUENCES > I first assumed that was needed because sequences were not covered by the > existing REFRESH PUBLICATION > * Why wasn't command #2 called ALTER SUBSCRIPTION REFRESH PUBLICATION > SEQUENCES? E.g. missing keyword PUBLICATION. It seems inconsistent. This is not correct, the existing implementation uses the key word PUBLICATION, the actual syntax is: "ALTER SUBSCRIPTION name REFRESH PUBLICATION SEQUENCES" > * I expect sequence values can become stale pretty much immediately after > command #1, so the user will want to use command #2 anyway... Yes > * ... but if command #2 also does add/remove changed sequences same as > command #1 then what benefit was there of having the command #1 for sequences? > * There is a separation of sequences (from tables) in command #2 but there is > no separation currently possible in command #1. It seemed inconsistent. This can be enhanced if required. It is not included as of now because I'm not sure if there is such a use case in case of tables. > ~~~ > > IIUC some of the goals I saw in the thread are to: > * provide a way to fetch and refresh sequences that also keeps behaviors > (e.g. copy_data etc.) consistent with the refresh of subscription tables > * provide a way to fetch and refresh *only* sequences > > I felt you could just enhance the existing refresh command syntax (command > #1), instead of introducing a new one it would be simpler and it would still > meet those same objectives. > > Synopsis: > ALTER SUBSCRIPTION name REFRESH PUBLICATION [TABLES | SEQUENCES | ALL] [ WITH > ( refresh_option [= value] [, ... ] ) ] > > My only change is the introduction of the optional "[TABLES | SEQUENCES | > ALL]" clause. > > I believe that can do everything your current patch does, plus more: > * Can refresh *only* TABLES if that is what you want (current patch 0705 > cannot do this) > * Can refresh *only* SEQUENCES (same as current patch 0705 command #2) > * Has better integration with refresh options like "copy_data" (current patch > 0705 command #2 doesn't have options) > * Existing REFRESH PUBLICATION syntax still works as-is. You can decide later > what is PG18 default if the "[TABLES | SEQUENCES | ALL]" is omitted. > > ~~~ > > More examples using proposed syntax. > > ex1. > ALTER SUBSCRIPTION sub REFRESH PUBLICATION TABLES WITH (copy_data = false) > - same as PG17 functionality for ALTER SUBSCRIPTION sub REFRESH PUBLICATION > WITH (copy_data = false) > > ex2. > ALTER SUBSCRIPTION sub REFRESH PUBLICATION TABLES WITH (copy_data = true) > - same as PG17 functionality for ALTER SUBSCRIPTION sub REFRESH PUBLICATION > WITH (copy_data = true) > > ex3. > ALTER SUBSCRIPTION sub REFRESH PUBLICATION SEQUENCES WITH (copy data = false) > - this adds/removes only sequences to pg_subscription_rel but doesn't update > their sequence values > > ex4. > ALTER SUBSCRIPTION sub REFRESH PUBLICATION SEQUENCES WITH (copy data = true) > - this adds/removes only sequences to pg_subscription_rel and also updates > all sequence values. > - this is equivalent behaviour of what your current 0705 patch is doing for > command #2, ALTER SUBSCRIPTION sub REFRESH SEQUENCES > > ex5. > ALTER SUBSCRIPTION sub REFRESH PUBLICATION ALL WITH (copy_data = false) > - this is equivalent behaviour of what your current 0705 patch is doing for > command #1, ALTER SUBSCRIPTION sub REFRESH PUBLICATION WITH (copy_data = > false) > > ex6. > ALTER SUBSCRIPTION sub REFRESH PUBLICATION ALL WITH (copy_data = true) > - this adds/removes tables and sequences and updates all table initial data > sequence values.- I think it is equivalent to your current 0705 patch doing > command #1 ALTER SUBSCRIPTION sub REFRESH PUBLICATION WITH (copy_data = > true), followed by another command #2 ALTER SUBSCRIPTION sub REFRESH SEQUENCES > > ex7. > ALTER SUBSCRIPTION sub REFRESH PUBLICATION SEQUENCES > - Because default copy_data is true you do not need to specify options, so > this is the same behaviour as your current 0705 patch command #2, ALTER > SUBSCRIPTION sub REFRESH SEQUENCES. I felt ex:4 is equivalent to command #2 "ALTER SUBSCRIPTION name REFRESH PUBLICATION SEQUENCES" and ex:3 just updates the pg_subscription_rel. But I'm not seeing an equivalent for "ALTER SUBSCRIPTION name REFRESH PUBLICATION with (copy_data = true)" which will identify and remove the stale entries and add entries/synchronize the sequences for the newly added sequences in the publisher. Regards, Vignesh
