Re: Logical Replication of sequences - Mailing list pgsql-hackers
From | vignesh C |
---|---|
Subject | Re: Logical Replication of sequences |
Date | |
Msg-id | CALDaNm2s8MAOg2tGsSHA6XoWce9Hcqti5cbzK+PWP-kcn9k1Pw@mail.gmail.com Whole thread Raw |
In response to | Re: Logical Replication of sequences (Peter Smith <smithpb2250@gmail.com>) |
Responses |
Re: Logical Replication of sequences
|
List | pgsql-hackers |
On Tue, 16 Jul 2024 at 06:00, Peter Smith <smithpb2250@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 sequenceswere not covered by the existing REFRESH PUBLICATION > * Why wasn't command #2 called ALTER SUBSCRIPTION REFRESH PUBLICATION SEQUENCES? E.g. missing keyword PUBLICATION. It seemsinconsistent. 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 havingthe 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 refreshof 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 wouldbe 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 REFRESHSEQUENCES > > 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 REFRESHPUBLICATION 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 equivalentto your current 0705 patch doing > command #1 ALTER SUBSCRIPTION sub REFRESH PUBLICATION WITH (copy_data = true), followed by another command #2 ALTER SUBSCRIPTIONsub 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 0705patch 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
pgsql-hackers by date: