Horton

Re: Logical Replication of sequences

vignesh C <vignesh21@gmail.com>

View thread
From: vignesh C <vignesh21@gmail.com>
To: shveta malik <shveta.malik@gmail.com>
Cc: Amit Kapila <amit.kapila16@gmail.com>, Peter Smith <smithpb2250@gmail.com>, Shlok Kyal <shlok.kyal.oss@gmail.com>, Masahiko Sawada <sawada.mshk@gmail.com>, Peter Eisentraut <peter@eisentraut.org>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>, Tomas Vondra <tomas.vondra@enterprisedb.com>, Euler Taveira <euler@eulerto.com>, Michael Paquier <michael@paquier.xyz>, "Hayato Kuroda (Fujitsu)" <kuroda.hayato@fujitsu.com>, Hou, Zhijie/侯 志杰 <houzj.fnst@fujitsu.com>, "Jonathan S. Katz" <jkatz@postgresql.org>
Date: 2024-08-07T08:25:36Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Doc: Add documentation for sequence synchronization.

  2. Remove unused assignment in CREATE PUBLICATION grammar.

  3. Add seq_sync_error_count to subscription statistics.

  4. Fix few issues in commit 5509055d69.

  5. Add sequence synchronization for logical replication.

  6. Add worker type argument to logical replication worker functions.

  7. Introduce "REFRESH SEQUENCES" for subscriptions.

  8. Refactor logical worker synchronization code into a separate file.

  9. Standardize use of REFRESH PUBLICATION in code and messages.

  10. Add "ALL SEQUENCES" support to publications.

  11. Expose sequence page LSN via pg_get_sequence_data.

  12. Resume conflict-relevant data retention automatically.

  13. Post-commit review fixes for 228c370868.

  14. Generate GUC tables from .dat file

On Tue, 6 Aug 2024 at 09:28, shveta malik <shveta.malik@gmail.com> wrote:
>
> On Tue, Aug 6, 2024 at 8:49 AM shveta malik <shveta.malik@gmail.com> wrote:
> >
>
> Do we need some kind of coordination between table sync and sequence
> sync for internally generated sequences? Lets say we have an identity
> column with a 'GENERATED ALWAYS' sequence. When the sequence is synced
> to subscriber, subscriber can also do an insert to table (extra one)
> incrementing the sequence and then when publisher performs an insert,
> apply worker will blindly copy that row to sub's table making identity
> column's duplicate entries.
>
> CREATE TABLE color ( color_id INT GENERATED ALWAYS AS
> IDENTITY,color_name VARCHAR NOT NULL);
>
> Pub: insert into color(color_name) values('red');
>
> Sub: perform sequence refresh and check 'r' state is reached, then do insert:
> insert into color(color_name) values('yellow');
>
> Pub: insert into color(color_name) values('blue');
>
> After above, data on Pub: (1, 'red') ;(2, 'blue'),
>
> After above, data on Sub: (1, 'red') ;(2, 'yellow'); (2, 'blue'),
>
> Identity column has duplicate values. Should the apply worker error
> out while inserting such a  row to the table? Or it is not in the
> scope of this project?

This behavior is documented at [1]:
Sequence data is not replicated. The data in serial or identity
columns backed by sequences will of course be replicated as part of
the table, but the sequence itself would still show the start value on
the subscriber.

This behavior is because of the above logical replication restriction.
So the behavior looks ok to me and I feel this is not part of the
scope of this project. I have updated this documentation section here
to mention sequences can be updated using ALTER SEQUENCE ... REFRESH
PUBLICATION SEQUENCES at v20240807 version patch attached at [2].

[1] - https://www.postgresql.org/docs/devel/logical-replication-restrictions.html
[2] - https://www.postgresql.org/message-id/CALDaNm01Z6Oo9osGMFTOoyTR1kVoyh1rEvZ%2B6uJn-ZymV%3D0dbQ%40mail.gmail.com

Regards,
Vignesh