Re: Logical Replication of sequences
| От | Peter Smith |
|---|---|
| Тема | Re: Logical Replication of sequences |
| Дата | |
| Msg-id | CAHut+PuFH1OCj-P1UKoRQE2X4-0zMG+N1V7jdn=tOQV4RNbAbw@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Logical Replication of sequences (vignesh C <vignesh21@gmail.com>) |
| Список | pgsql-hackers |
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] [, ... ] ) ]
#2. ALTER SUBSCRIPTION name REFRESH SEQUENCES
~~~~
IMO, that separation seems complicated. It leaves many questions like:
So now there are 2 forms of subscription refresh.
#1. ALTER SUBSCRIPTION name REFRESH PUBLICATION [ WITH ( refresh_option [= value] [, ... ] ) ]
#2. ALTER SUBSCRIPTION name REFRESH 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.
* I expect sequence values can become stale pretty much immediately after command #1, so the user will want to use command #2 anyway...
* I expect sequence values can become stale pretty much immediately after command #1, so the user will want to use command #2 anyway...
* ... 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.
~~~
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 hope this post was able to demonstrate that by enhancing the existing command:
- it is less tricky to understand the separate command distinctions
- there is more functionality/flexibility possible
- there is better integration with the refresh options like copy_data
- behaviour for tables/sequences is more consistent
Anyway, it is just my opinion. Maybe there are some pitfalls I'm unaware of.
Thoughts?
======
Kind Regards,
Peter Smith.
Fujitsu Australia
* There is a separation of sequences (from tables) in command #2 but there is no separation currently possible in command #1. It seemed inconsistent.
~~~
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 hope this post was able to demonstrate that by enhancing the existing command:
- it is less tricky to understand the separate command distinctions
- there is more functionality/flexibility possible
- there is better integration with the refresh options like copy_data
- behaviour for tables/sequences is more consistent
Anyway, it is just my opinion. Maybe there are some pitfalls I'm unaware of.
Thoughts?
======
Kind Regards,
Peter Smith.
Fujitsu Australia
В списке pgsql-hackers по дате отправления: