Re: logical decoding and replication of sequences, take 2

On 7/24/23 08:31, Amit Kapila wrote:
> On Thu, Jul 20, 2023 at 8:22 PM Tomas Vondra
> <tomas.vondra@enterprisedb.com> wrote:
>>
>> OK, I merged the changes into the patches, with some minor changes to
>> the wording etc.
>>
> 
> I think we can do 0001-Make-test_decoding-ddl.out-shorter-20230720
> even without the rest of the patches. Isn't it a separate improvement?
> 

True.

> I see that origin filtering (origin=none) doesn't work with this
> patch. You can see this by using the following statements:
> Node-1:
> postgres=# create sequence s;
> CREATE SEQUENCE
> postgres=# create publication mypub for all sequences;
> CREATE PUBLICATION
> 
> Node-2:
> postgres=# create sequence s;
> CREATE SEQUENCE
> postgres=# create subscription mysub_sub connection '....' publication
> mypub with (origin=none);
> NOTICE:  created replication slot "mysub_sub" on publisher
> CREATE SUBSCRIPTION
> postgres=# create publication mypub_sub for all sequences;
> CREATE PUBLICATION
> 
> Node-1:
> create subscription mysub_pub connection '...' publication mypub_sub
> with (origin=none);
> NOTICE:  created replication slot "mysub_pub" on publisher
> CREATE SUBSCRIPTION
> 
> SELECT nextval('s') FROM generate_series(1,100);
> 
> After that, you can check on the subscriber that sequences values are
> overridden with older values:
> postgres=# select * from s;
>  last_value | log_cnt | is_called
> ------------+---------+-----------
>          67 |       0 | t
> (1 row)
> postgres=# select * from s;
>  last_value | log_cnt | is_called
> ------------+---------+-----------
>         100 |       0 | t
> (1 row)
> postgres=# select * from s;
>  last_value | log_cnt | is_called
> ------------+---------+-----------
>         133 |       0 | t
> (1 row)
> postgres=# select * from s;
>  last_value | log_cnt | is_called
> ------------+---------+-----------
>          67 |       0 | t
> (1 row)
> 
> I haven't verified all the details but I think that is because we
> don't set XLOG_INCLUDE_ORIGIN while logging sequence values.
> 

Hmmm, yeah. I guess we'll need to set XLOG_INCLUDE_ORIGIN with
wal_level=logical.


regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company