Re: Logical Replication of sequences

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 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