Re: Logical Replication of sequences

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:
* 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...
* ... 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