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