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

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
thisreplication 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

Hi,

On 2023-05-17 11:36:37 +0530, Shubham Dhama wrote:
> > consistent_point (text)
> > The WAL location at which the slot became consistent. This is the earliest location from which streaming can start
onthis 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.

I'm somewhat confused - your output actually doesn't seem to contain changes
from before that point?

The earliest LSN in sort.txt is precisely 4B/DB04C730?

I also don't exactly know what wal2json even prints as an LSN - if you have an
actual problem, could you construct a test case that just uses test_decoding?

Greetings,

Andres Freund