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

Good point. Attached is a patch that adds XLOG_INCLUDE_ORIGIN to
sequence changes. I considered doing that only for wal_level=logical,
but we don't do that elsewhere. Also, I didn't do that for smgr_create,
because we don't actually replicate that.

regards

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