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(dot)vondra(at)enterprisedb(dot)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