Re: Logical Replication of sequences

On Wed, 24 Jul 2024 at 11:53, shveta malik <shveta(dot)malik(at)gmail(dot)com> wrote:
>
> On Wed, Jul 24, 2024 at 9:17 AM Peter Smith <smithpb2250(at)gmail(dot)com> wrote:
> >
>
> I had a look at patches v20240720* (considering these as the latest
> one) and tried to do some basic testing (WIP). Few comments:
>
> 1)
> I see 'last_value' is updated wrongly after create-sub. Steps:
>
> -----------
> pub:
> CREATE SEQUENCE myseq0 INCREMENT 5 START 100;
> SELECT nextval('myseq0');
> SELECT nextval('myseq0');
> --last_value on pub is 105
> select * from pg_sequences;
> create publication pub1 for all tables, sequences;
>
> Sub:
> CREATE SEQUENCE myseq0 INCREMENT 5 START 100;
> create subscription sub1 connection 'dbname=postgres host=localhost
> user=shveta port=5433' publication pub1;
>
> --check 'r' state is reached
> select pc.relname, pr.srsubstate, pr.srsublsn from pg_subscription_rel
> pr, pg_class pc where (pr.srrelid = pc.oid);
>
> --check 'last_value', it shows some random value as 136
> select * from pg_sequences;

Earlier I was setting sequence value with the value of publisher +
log_cnt, that is why the difference is there. On further thinking
since we are not supporting incremental replication of sequences, so
no plugin usage is involved which requires the special decoding last
value and log_count. I felt we can use the exact sequence last value
and log count to generate the similar sequence value. So Now I have
changed it to get the last_value and log_count from the publisher and
set it to the same values.

>
> 2)
> I can use 'for all sequences' only with 'for all tables' and can not
> use it with the below. Shouldn't it be allowed?
>
> create publication pub2 for tables in schema public, for all sequences;
> create publication pub2 for table t1, for all sequences;

I feel this can be added as part of a later version while supporting
"add/drop/set sequence and add/drop/set sequences in schema" once the
patch is stable.

> 3)
> preprocess_pub_all_objtype_list():
> Do we need 'alltables_specified' and 'allsequences_specified' ? Can't
> we make a repetition check using *alltables and *allsequences?

Modified

> 4) patch02's commit msg says : 'Additionally, a new system view,
> pg_publication_sequences, has been introduced'
> But it is not part of patch002.

This is removed now

The attached v20240725 version patch has the changes for the same.

Regards,
Vignesh