Earliest streamed message from replication slot has LSN less than the `consistent_point`

From: Shubham Dhama <shubhamdhamaofficial(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Earliest streamed message from replication slot has LSN less than the `consistent_point`
Date: 2023-05-17 06:06:37
Message-ID: CAFi2d6uNO4hp+MMnHK-6-VOA=U4PWuNMgLv1vLuw=0-PmpOE1A@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi everyone,

I have a question, possibly a bug in Postgres or my understanding.

As per the docs,
https://www.postgresql.org/docs/current/protocol-replication.html

> consistent_point (text)
> The WAL location at which the slot became consistent. This is the earliest location from which streaming can start on this replication slot.

But it doesn't seem true in my scenario. Here is how to reproduce,
1. Have parallel transactions on a database
2. Create a replication slot while the above step is ongoing.
3. Peek/get the replication messages from the slot and you will see
the first (and a few more) messages' have LSN less than the
`consistent_point`.

I'm attaching a sample schema and insert queries. After creating the
schema please do the following,

Step 1: for j in {1..20}; do for i in {1..100}; do psql
$CONNECTION_STRING -f ~/insert.sql; done & done

Step 2: select pg_create_logical_replication_slot('test_slot', 'wal2json');

Step 3: psql $CONNECTION_STRING -c "select
pg_logical_slot_peek_changes('test_slot', null, null,
'format-version', '2', 'include-xids', 'true', 'include-schemas'
,'true', 'include-transaction','true', 'include-types', 'true');"
>~/data/slot-data.txt

I am able to reproduce this in PG 14, and here is what I got,

all_types_2=> select pg_create_logical_replication_slot('hello3', 'wal2json');
pg_create_logical_replication_slot
------------------------------------
(hello3,4B/DB04C730)

and I've attached the replication data output to confirm my issue.

Please let me know if you need more information or I'm missing something here.

Regards,
Shubham

Attachment Content-Type Size
slot-data.txt text/plain 247.4 KB
schema.sql application/sql 1.9 KB
insert.sql application/sql 2.2 KB

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Laurenz Albe 2023-05-17 06:16:41 Re: How can I change replication slot's restart_lsn from SQL?
Previous Message Avi Weinberg 2023-05-17 06:05:52 Postgres Incompatibility