On 2026-08-11 09:04, Masahiko Sawada wrote:
Thanks for the comment!
I'm still studying this patch but I want to clarify: was the problem
you faced caused by logical decoding showing different types of
changes(insert/update/delete/truncate/message) with the same LSN?
Yes. The Debezium issue I encountered was caused by different types of
changes being reported with the same LSN.
More specifically, consider a transaction like this:
begin;
select pg_logical_emit_message(true, 'test1', 'xxx');
insert into t1 values (1, 'aaa');
commit;
The problem occurred when Debezium received the logical message but
crashed before receiving the INSERT.
After a restart, Debezium uses the LSN of the last record it received
to determine where to resume and which records have already been
processed. Since the INSERT is reported with the same LSN as the
logical message, it can be treated as already processed and skipped,
resulting in data loss.
IIUC
logical decoding of MULTI_INSERT emits all INSERT changes with the
same LSN, so I think showing the same LSN multiple times is fine.
So I agree that having the same LSN for multiple changes is not in
itself a problem. The problem in this case is that changes originating
from different WAL positions can be reported with the same LSN.
One possible reason why the message uses endptr instead of origptr is
for non-transactional messages; on receiver sides a non-transactional
message would be handled as a separate transaction, so it would be
useful to use endptr as the confirmed flush position. That said, it
doesn't apply for transactional messages.
While I agree that logical decoding uses origptr of logical decoding
messages as their LSN, I think having its endptr is also useful for
the above reason. For instance, I proposed to extend logical decoding
message handling[1] so that extension can define a function to handle
logical decoding messages on the subscriber. If we use origptr as the
flushed position, the same message is replicated again after the
server restart even if the subscriber has committed the message as a
separate transaction and sent an ack to the publisher.
That makes sense.
So an alternative idea would be to use origptr for transactional
messages and endptr for non-transactional ones. With this idea, a
transactional message's LSN could coincide with the LSN reported for
the preceding COMMIT, or for a preceding non-transactional message. I
think that is acceptable for the same reason as MULTI_INSERT above:
what matters is that the LSN identifies the record the change came
from, not that it is unique.
That approach seems reasonable to me.
I'm hesitant to backpatch it. What this issue tells us is that
consumers do look at the LSN of individual changes and possibly
persist it, and use it to decide where to resume and what has already
been processed. If we change the LSN we report in a minor release, a
position that a consumer recorded under the old behavior will be
interpreted under the new one after the upgrade. Whether that ends up
re-processing changes, skipping them, or failing to locate the resume
point depends on the consumer, and none of those seems like something
an operator should have to expect from a minor upgrade.
There is no correctness problem within PostgreSQL here. So I think
this is pushed to master only, with the current behavior documented in
the back branches. Consumers hitting this can handle it on their side
in the meantime.
This may be a safer approach.
One thing that still concerns me is that consumer developers may
assume that the LSN reported for a logical message identifies
the WAL record containing that message, just as it does for other
decoded changes. Even after consulting the documentation, one
could reach that conclusion from this description:
https://www.postgresql.org/docs/devel/protocol-logicalrep-message-formats.html
Message
....
Int64 (XLogRecPtr)
The LSN of the logical decoding message.
Also, as far as I imagine, the LSN of an individual decoded change is
typically relevant mainly when a consumer has to determine its resume
position after an unexpected interruption. Therefore, there may be
consumers for which this behavior has not caused a visible problem
during normal replication, and whose developers are not aware of this
difference for logical messages.
If that assumption is common, treating the documentation as defining
the intended behavior and backpatching the change would make the fix
easier for consumers: they could get the corrected behavior simply by
upgrading PostgreSQL, without adding special handling for logical
message LSNs only for PostgreSQL versions before 19.
That said, I don't know whether there are actually many such consumers.
It seems difficult to predict how changing the behavior in already
released major versions might affect existing consumers.
From that perspective, leaving the behavior unchanged in released
branches seems safer.
So I'm fine with changing this only in master and documenting the
existing behavior in the back branches.
For the released branches, though, I think it would be useful not only
to update the documentation, but also to make the issue clearly visible
in the release notes, since some consumers may need to review or adjust
their logics.
Atatched patches.
--
Thanks,
--
Atsushi Torikoshi
Seconded from NTT DATA CORPORATION to SRA OSS K.K.
From f9b34ca0ce82abbb30a5418afae7f034b540b49f Mon Sep 17 00:00:00 2001
From: Atsushi Torikoshi <[email protected]>
Date: Wed, 12 Aug 2026 08:50:52 +0900
Subject: [PATCH v3] Correct documentation of logical decoding message LSN
The documentation described the reported LSN as the LSN of the logical
decoding message itself. However, the value actually reported is the
end LSN of the message record.
This difference may matter to logical decoding consumers, for example,
those that persist the LSN of decoded changes and use it to determine
where to resume after an unexpected interruption. Unlike DML changes,
a logical message is reported with the end LSN of its WAL record, so
such consumers may need special handling for logical messages when
determining the resume position.
---
doc/src/sgml/protocol.sgml | 2 +-
1 file changed, 1 insertion(+), 1 deletion(-)
diff --git a/doc/src/sgml/protocol.sgml b/doc/src/sgml/protocol.sgml
index 49f81676712..a5e00721810 100644
--- a/doc/src/sgml/protocol.sgml
+++ b/doc/src/sgml/protocol.sgml
@@ -6678,7 +6678,7 @@ psql "dbname=postgres replication=database" -c "IDENTIFY_SYSTEM;"
<term>Int64 (XLogRecPtr)</term>
<listitem>
<para>
- The LSN of the logical decoding message.
+ The end LSN of the logical decoding message.
</para>
</listitem>
</varlistentry>
--
2.48.1
From ed1555a53a1fd6fbc3870f0da578d53a265abb43 Mon Sep 17 00:00:00 2001
From: Atsushi Torikoshi <[email protected]>
Date: Wed, 12 Aug 2026 15:39:02 +0900
Subject: [PATCH v3] Use start LSN for transactional logical decoding messages
Logical decoding currently reports the end LSN of a logical message
record for both transactional and non-transactional messages. This
differs from regular transactional changes, for which the reported LSN
identifies the start of the WAL record.
This difference can matter to logical decoding consumers that persist
the LSN of decoded changes and use it to determine where to resume
decoding after an unexpected interruption.
For transactional logical messages, this patch uses the start LSN, as
is done for other transactional changes.
For non-transactional logical messages, it continues to use the end
LSN. Such messages are processed independently of a surrounding
transaction, so the end LSN can be used as their confirmed flush
position.
---
doc/src/sgml/protocol.sgml | 3 ++-
src/backend/replication/logical/decode.c | 7 ++++++-
2 files changed, 8 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/protocol.sgml b/doc/src/sgml/protocol.sgml
index 49f81676712..a65a15ad9c7 100644
--- a/doc/src/sgml/protocol.sgml
+++ b/doc/src/sgml/protocol.sgml
@@ -6678,7 +6678,8 @@ psql "dbname=postgres replication=database" -c "IDENTIFY_SYSTEM;"
<term>Int64 (XLogRecPtr)</term>
<listitem>
<para>
- The LSN of the logical decoding message.
+ The start LSN of the logical decoding message for transactional
+ messages, or its end LSN for non-transactional messages.
</para>
</listitem>
</varlistentry>
diff --git a/src/backend/replication/logical/decode.c b/src/backend/replication/logical/decode.c
index c944be4ac83..fd727a7ec7f 100644
--- a/src/backend/replication/logical/decode.c
+++ b/src/backend/replication/logical/decode.c
@@ -668,7 +668,12 @@ logicalmsg_decode(LogicalDecodingContext *ctx, XLogRecordBuffer *buf)
if (!message->transactional)
snapshot = SnapBuildGetOrBuildSnapshot(builder);
- ReorderBufferQueueMessage(ctx->reorder, xid, snapshot, buf->endptr,
+ /*
+ * Non-transactional messages are processed as separate transactions on
+ * the receiver, so use endptr as the confirmed flush position for them.
+ */
+ ReorderBufferQueueMessage(ctx->reorder, xid, snapshot,
+ message->transactional ? buf->origptr : buf->endptr,
message->transactional,
message->message, /* first part of message is
* prefix */
--
2.48.1
From f9b34ca0ce82abbb30a5418afae7f034b540b49f Mon Sep 17 00:00:00 2001
From: Atsushi Torikoshi <[email protected]>
Date: Wed, 12 Aug 2026 08:50:52 +0900
Subject: [PATCH v3] Correct documentation of logical decoding message LSN
The documentation described the reported LSN as the LSN of the logical
decoding message itself. However, the value actually reported is the
end LSN of the message record.
This difference may matter to logical decoding consumers, for example,
those that persist the LSN of decoded changes and use it to determine
where to resume after an unexpected interruption. Unlike DML changes,
a logical message is reported with the end LSN of its WAL record, so
such consumers may need special handling for logical messages when
determining the resume position.
---
doc/src/sgml/protocol.sgml | 2 +-
1 file changed, 1 insertion(+), 1 deletion(-)
diff --git a/doc/src/sgml/protocol.sgml b/doc/src/sgml/protocol.sgml
index 62125a5746a..04922dc7c39 100644
--- a/doc/src/sgml/protocol.sgml
+++ b/doc/src/sgml/protocol.sgml
@@ -6629,7 +6629,7 @@ psql "dbname=postgres replication=database" -c "IDENTIFY_SYSTEM;"
</term>
<listitem>
<para>
- The LSN of the logical decoding message.
+ The end LSN of the logical decoding message.
</para>
</listitem>
</varlistentry>
--
2.48.1
