Why is the LSN reported for pg_logical_emit_message() different from other decoded operations?

Hi,

While investigating logical decoding of pg_logical_emit_message(),
I noticed that the LSN reported for logical messages differs from
the LSN reported for other operations such as INSERT, UPDATE, and
DELETE.

For example, with the following transaction:

BEGIN;
INSERT INTO data(data) VALUES('1');
UPDATE data SET data = 'a' WHERE id = 1;
DELETE FROM data WHERE id = 1;
SELECT * FROM pg_logical_emit_message(true, 'test1', 'aaa');
INSERT INTO data(data) VALUES('2');
TRUNCATE data;
COMMIT;

=# SELECT * FROM pg_logical_slot_get_changes('regression_slot', NULL,
NULL);
lsn | xid | data
------------+-----+------------------------------------------------------------
0/017E9640 | 696 | BEGIN 696
0/017E9640 | 696 | table public.data: INSERT: id[integer]:3
data[text]:'1'
0/017E96C0 | 696 | table public.data: UPDATE: id[integer]:1
data[text]:'a'
0/017E9740 | 696 | table public.data: DELETE: id[integer]:1
0/017E97F8 | 696 | message: transactional: 1 prefix: test1, sz: 3
content:aaa
0/017E9830 | 696 | table public.data: INSERT: id[integer]:4
data[text]:'2'
0/017EA420 | 696 | table public.data: TRUNCATE: (no-flags)
0/017EA5C0 | 696 | COMMIT 696

$ pg_waldump -f data/pg_wal/000000010000000000000001
rmgr: Heap len (rec/tot): 61/ 61, tx: 696, lsn:
0/017E9640, prev 0/017E9608, desc: INSERT off: 5, flags: 0x08, blkref
#0: rel 1663/5/16385 blk 0
..(snip)..
rmgr: Heap len (rec/tot): 72/ 72, tx: 696, lsn:
0/017E96C0, prev 0/017E9680, desc: HOT_UPDATE old_xmax: 696, old_off: 2,
old_infobits: [], flags: 0x10, new_xmax: 0, new_off: 6, blkref #0: rel
1663/5/16385 blk 0
..(snip)..
rmgr: Heap len (rec/tot): 64/ 64, tx: 696, lsn:
0/017E9740, prev 0/017E9708, desc: DELETE xmax: 696, off: 6, infobits:
[KEYS_UPDATED], flags: 0x04, blkref #0: rel 1663/5/16385 blk 0
..(snip)..
rmgr: LogicalMessage len (rec/tot): 59/ 59, tx: 696, lsn:
0/017E97B8, prev 0/017E9780, desc: MESSAGE transactional, prefix
"test1"; payload (3 bytes): 61 61 61
rmgr: Standby len (rec/tot): 54/ 54, tx: 0, lsn:
0/017E97F8, prev 0/017E97B8, desc: RUNNING_XACTS nextXid 697
latestCompletedXid 695 oldestRunningXid 696; 1 xacts: 696
..(snip)..
rmgr: Heap len (rec/tot): 61/ 61, tx: 696, lsn:
0/017E9830, prev 0/017E97F8, desc: INSERT off: 7, flags: 0x08, blkref
#0: rel 1663/5/16385 blk 0

Comparing the output of pg_logical_slot_get_changes() with the
pg_waldump, the LSNs reported for INSERT, UPDATE, and DELETE
match the start LSN of the corresponding WAL records. However,
the LSN reported for the logical message does not match the
LogicalMessage WAL record itself (0/017E97B8); instead, it
matches the LSN of the following RUNNING_XACTS record
(0/017E97F8).

I found that changes such as INSERTs and UPDATEs are queued via
ReorderBufferQueueChange() using XLogRecordBuffer.origptr,
whereas logical messages are queued using
XLogRecordBuffer.endptr. This appears to explain the observed
behavior.

My question is: is there a particular reason why logical messages
use endptr instead of origptr?

Looking through the history, this behavior seems to go back to
commit 3fe3511d05127c, which added logical decoding support for
pg_logical_emit_message().

BTW The reason I started investigating this is that we
encountered a data-loss issue in the Debezium PostgreSQL
connector, which uses logical decoding for Change Data Capture.
Under certain circumstances, messages emitted by
pg_logical_emit_message() could be skipped during recovery
because the message LSN behaves differently from other decoded
operations, as described. The attached patch, which uses origptr
instead of endptr, eliminates the issue in my testing.

Of course, I think consumers of logical decoding, such as
Debezium, could work around this by treating message LSNs
differently. However, compared to other decoded operations, this
special handling feels somewhat unexpected, so I wanted to ask
whether the current behavior is intentional.

Thanks,

--
Atsushi Torikoshi
Seconded from NTT DATA CORPORATION to SRA OSS K.K.

On Mon, 8 Jun 2026 22:09:06 +0900
torikoshia <torikoshia(at)oss(dot)nttdata(dot)com> wrote:

> Hi,
>
> While investigating logical decoding of pg_logical_emit_message(),
> I noticed that the LSN reported for logical messages differs from
> the LSN reported for other operations such as INSERT, UPDATE, and
> DELETE.
>
> For example, with the following transaction:
>
> BEGIN;
> INSERT INTO data(data) VALUES('1');
> UPDATE data SET data = 'a' WHERE id = 1;
> DELETE FROM data WHERE id = 1;
> SELECT * FROM pg_logical_emit_message(true, 'test1', 'aaa');
> INSERT INTO data(data) VALUES('2');
> TRUNCATE data;
> COMMIT;
>
> =# SELECT * FROM pg_logical_slot_get_changes('regression_slot', NULL,
> NULL);
> lsn | xid | data
> ------------+-----+------------------------------------------------------------
> 0/017E9640 | 696 | BEGIN 696
> 0/017E9640 | 696 | table public.data: INSERT: id[integer]:3
> data[text]:'1'
> 0/017E96C0 | 696 | table public.data: UPDATE: id[integer]:1
> data[text]:'a'
> 0/017E9740 | 696 | table public.data: DELETE: id[integer]:1
> 0/017E97F8 | 696 | message: transactional: 1 prefix: test1, sz: 3
> content:aaa
> 0/017E9830 | 696 | table public.data: INSERT: id[integer]:4
> data[text]:'2'
> 0/017EA420 | 696 | table public.data: TRUNCATE: (no-flags)
> 0/017EA5C0 | 696 | COMMIT 696
>
>
> $ pg_waldump -f data/pg_wal/000000010000000000000001
> rmgr: Heap len (rec/tot): 61/ 61, tx: 696, lsn:
> 0/017E9640, prev 0/017E9608, desc: INSERT off: 5, flags: 0x08, blkref
> #0: rel 1663/5/16385 blk 0
> ..(snip)..
> rmgr: Heap len (rec/tot): 72/ 72, tx: 696, lsn:
> 0/017E96C0, prev 0/017E9680, desc: HOT_UPDATE old_xmax: 696, old_off: 2,
> old_infobits: [], flags: 0x10, new_xmax: 0, new_off: 6, blkref #0: rel
> 1663/5/16385 blk 0
> ..(snip)..
> rmgr: Heap len (rec/tot): 64/ 64, tx: 696, lsn:
> 0/017E9740, prev 0/017E9708, desc: DELETE xmax: 696, off: 6, infobits:
> [KEYS_UPDATED], flags: 0x04, blkref #0: rel 1663/5/16385 blk 0
> ..(snip)..
> rmgr: LogicalMessage len (rec/tot): 59/ 59, tx: 696, lsn:
> 0/017E97B8, prev 0/017E9780, desc: MESSAGE transactional, prefix
> "test1"; payload (3 bytes): 61 61 61
> rmgr: Standby len (rec/tot): 54/ 54, tx: 0, lsn:
> 0/017E97F8, prev 0/017E97B8, desc: RUNNING_XACTS nextXid 697
> latestCompletedXid 695 oldestRunningXid 696; 1 xacts: 696
> ..(snip)..
> rmgr: Heap len (rec/tot): 61/ 61, tx: 696, lsn:
> 0/017E9830, prev 0/017E97F8, desc: INSERT off: 7, flags: 0x08, blkref
> #0: rel 1663/5/16385 blk 0
>
>
> Comparing the output of pg_logical_slot_get_changes() with the
> pg_waldump, the LSNs reported for INSERT, UPDATE, and DELETE
> match the start LSN of the corresponding WAL records. However,
> the LSN reported for the logical message does not match the
> LogicalMessage WAL record itself (0/017E97B8); instead, it
> matches the LSN of the following RUNNING_XACTS record
> (0/017E97F8).
>
> I found that changes such as INSERTs and UPDATEs are queued via
> ReorderBufferQueueChange() using XLogRecordBuffer.origptr,
> whereas logical messages are queued using
> XLogRecordBuffer.endptr. This appears to explain the observed
> behavior.
>
> My question is: is there a particular reason why logical messages
> use endptr instead of origptr?
>
> Looking through the history, this behavior seems to go back to
> commit 3fe3511d05127c, which added logical decoding support for
> pg_logical_emit_message().

I could not find any discussion about the LSN choice in the pgsql-hackers
thread [1]. Perhaps it was intended that the LSN reported for a logical
message should match the return value of pg_logical_emit_message(), as
suggested by the test you fixed, but I'm not sure.

However, the documentation [2] for the callback function says:

typedef void (*LogicalDecodeMessageCB) (struct LogicalDecodingContext *ctx,
ReorderBufferTXN *txn,
XLogRecPtr message_lsn,
bool transactional,
const char *prefix,
Size message_size,
const char *message);

... The lsn has WAL location of the message. ...

Based on my reading, the current behavior does not seem to match that
description. However, if reporting the end LSN is intentional, perhaps
the documentation should be updated to clarify that.

BTW, since the test no longer uses $message_lsn after your change, could we
remove the variable that stores the result of pg_logical_emit_message() as well?

[1] https://www.postgresql.org/message-id/flat/5685F999.6010202%402ndquadrant.com
[2] https://www.postgresql.org/docs/current/logicaldecoding-output-plugin.html#LOGICALDECODING-OUTPUT-PLUGIN-MESSAGE

Regards,
Yugo Nagata
--
Yugo Nagata <nagata(at)sraoss(dot)co(dot)jp>

Hello.

At Tue, 9 Jun 2026 11:23:32 +0900, Yugo Nagata <nagata(at)sraoss(dot)co(dot)jp> wrote in
> Based on my reading, the current behavior does not seem to match that
> description. However, if reporting the end LSN is intentional, perhaps
> the documentation should be updated to clarify that.

Looking at the existing code, PostgreSQL appears to use the start LSN
for most record types, except for a few special cases such as COMMIT
records.

As for logical decoding messages, their contents are not sent to the
subscriber in the first place. Even if they were, the subscriber would
only use them to advance its received LSN. From that perspective,
PostgreSQL does not seem to care whether the LSN associated with a
message record refers to the beginning or the end of the WAL record.

Of course, this raises the question of whether any existing extension
relies on the current behavior. I don't know the answer to that.

That said, the documentation says:

https://www.postgresql.org/docs/devel/protocol-logicalrep-message-formats.html

> Message
....
> Int64 (XLogRecPtr)
> The LSN of the logical decoding message.

The value currently comes from XLogRecordBuffer.endptr, which is
passed to logicalmsg_decode(). If the documentation is correct, then
it seems to me that this should instead use origptr.

Regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center

Nagata-san, Horiguchi-san,

Thank you for confirming! My understanding is as follows:

- It is not clear why only logical messages return endptr.
- According to the documentation, the value should be startptr rather
than endptr.
- PostgreSQL itself does not have mechanism that uses this LSN, so
either value does not cause a problem within PostgreSQL.

Regarding the third point, I believe this distinction is
important for consumers of logical decoding. Typically, a
consumer records the LSN up to which the decoded result has been
successfully processed and uses that LSN as the restart point
after a crash or other failure. From that perspective, whether
startptr or endptr is returned matters.

With the current behavior, consumers need to be aware that only
logical messages return endptr (and this does not appear to be
documented, so one would have to discover it through testing).
Then they need special handling to translate the completion LSN
for logical messages back to the previous processing position.

On this point, I have started discussing with the Debezium
community, where I originally encountered this issue. The main
question is whether a change in PostgreSQL's behavior would be
welcomed, or whether Debezium instead treats logical messages
differently from other decoded records. If they reach some
consensus, I would be happy to share the outcome here. It is also
possible that someone from the Debezium community will comment on
this thread directly.

I think it's valuable to hear from anyone familiar with products
or applications that decode pg_logical_emit_message().

For example, I wonder whether Fujitsu's 'Userlog Operation' might
also be affected:
https://www.postgresql.fastware.com/hubfs/_Global/Manuals/FEP-v17forx86-UserlogOperationGuide.pdf

Thanks,

--
Atsushi Torikoshi

Dear Torikoshi-san,

Sorry for the late join...

> With the current behavior, consumers need to be aware that only
> logical messages return endptr (and this does not appear to be
> documented, so one would have to discover it through testing).
> Then they need special handling to translate the completion LSN
> for logical messages back to the previous processing position.

Actually I did not recognize till I found the thread, and I'm also unclear
the reason of the difference. From my perspective any blockers within
core are not found.

> For example, I wonder whether Fujitsu's 'Userlog Operation' might
> also be affected:
> https://www.postgresql.fastware.com/hubfs/_Global/Manuals/FEP-v17forx86-
> UserlogOperationGuide.pdf
>

FYI, I confirmed the proprietary won't be affected by the change you proposed.

Best regards,
Hayato Kuroda
FUJITSU LIMITED

On 2026-06-18 17:27, Hayato Kuroda (Fujitsu) wrote:
> Dear Torikoshi-san,
>
> Sorry for the late join...
>
>> With the current behavior, consumers need to be aware that only
>> logical messages return endptr (and this does not appear to be
>> documented, so one would have to discover it through testing).
>> Then they need special handling to translate the completion LSN
>> for logical messages back to the previous processing position.
> Actually I did not recognize till I found the thread, and I'm also
> unclear
> the reason of the difference. From my perspective any blockers within
> core are not found.
>
>> For example, I wonder whether Fujitsu's 'Userlog Operation' might
>> also be affected:
>> https://www.postgresql.fastware.com/hubfs/_Global/Manuals/FEP-v17forx86-
>> UserlogOperationGuide.pdf
>>
>
> FYI, I confirmed the proprietary won't be affected by the change you
> proposed.

Thanks for your confirmation! I appreciate that.

On 2026-06-11 11:27, torikoshia wrote:
> On this point, I have started discussing with the Debezium
> community, where I originally encountered this issue. The main
> question is whether a change in PostgreSQL's behavior would be
> welcomed, or whether Debezium instead treats logical messages
> differently from other decoded records.

FYI, this discussion is still ongoing.

--
Atsushi Torikoshi

On 2026-06-11 11:27, torikoshia wrote:
> On this point, I have started discussing with the Debezium
> community, where I originally encountered this issue. The main
> question is whether a change in PostgreSQL's behavior would be
> welcomed, or whether Debezium instead treats logical messages
> differently from other decoded records.

After asking the Debezium community, I learned that they have no
particular preference as to how PostgreSQL addresses this issue:

-- https://github.com/debezium/dbz/issues/2058#issuecomment-4774845738
> Based on the discussions so far, my understanding is that, regardless
> of whether PostgreSQL implements this fix in a minor or major release,
> Debezium will accommodate either approach and therefore does not have a
> preference for one over the other, or for any alternative solution.

I think there would be roughly three ways to address the issue:

1) Preserve the current behavior, that is, continue returning endptr
only when decoding pg_logical_emit_message(), and update the
documentation accordingly.
2) Change the behavior so that decoding pg_logical_emit_message()
returns startptr, as is the case for DML records.
2.1) Make the change in minor releases.
2.2) Make the change in a major release, while updating the
documentation for earlier major versions to describe their existing
behavior.

If there are requirements from other consumers of the logical
decoding, I think those should of course be taken into account.
Personally, however, I do not think we should choose option 1.
As I wrote previously, the current behavior can be a pitfall
that may lead to data loss when implementing a logical
decoding consumer:

```
Regarding the third point, I believe this distinction is
important for consumers of logical decoding. Typically, a
consumer records the LSN up to which the decoded result has been
successfully processed and uses that LSN as the restart point
after a crash or other failure. From that perspective, whether
startptr or endptr is returned matters.
```

As for options 2.1 and 2.2, given the potential impact on consumers,
changing the behavior only in a major release, as in option 2.2,
initially seems like a good approach.

However, the discussion so far has not identified any particular
reason why endptr should be returned, and the current behavior
also appears to differ from the documentation. This therefore
seems to me more like a bug. If we regard it as such, fixing it
in minor releases, as in option 2.1, would seem to be the most
straightforward approach.

If there are consumers that are already aware of this issue and
have implemented special handling for pg_logical_emit_message(),
they would need to remove or adjust that handling, which could
be inconvenient.
That said, this behavior cannot readily be inferred from the
documentation. It is also a relatively niche issue that is likely
to be noticed only after it causes a problem such as data loss.
Moreover, once someone identifies the cause, it seems like the kind
of issue they would be inclined to report to the PostgreSQL
community, but it seems that we haven't received such reports.
For these reasons, I suspect there are few, if any, products that
currently implement such a workaround.

For consumers that are affected by this issue but are not yet aware
of it, fixing it in minor releases would have the advantage that
simply upgrading PostgreSQL to a newer minor release would resolve
the problem without adding special handling for
pg_logical_emit_message() to their products.
I imagine most consumers would gain this advantage.

For these reasons, I am beginning to think that fixing this in minor
releases may be the preferable approach.

What do you think?

--
Thanks,

--
Atsushi Torikoshi
Seconded from NTT DATA CORPORATION to SRA OSS K.K.

Hi all,

On Tue, Jul 21, 2026 at 10:30 AM torikoshia <torikoshia(at)oss(dot)nttdata(dot)com> wrote:
>
> On 2026-06-11 11:27, torikoshia wrote:
> > On this point, I have started discussing with the Debezium
> > community, where I originally encountered this issue. The main
> > question is whether a change in PostgreSQL's behavior would be
> > welcomed, or whether Debezium instead treats logical messages
> > differently from other decoded records.
>
> After asking the Debezium community, I learned that they have no
> particular preference as to how PostgreSQL addresses this issue:
>
> -- https://github.com/debezium/dbz/issues/2058#issuecomment-4774845738
> > Based on the discussions so far, my understanding is that, regardless
> > of whether PostgreSQL implements this fix in a minor or major release,
> > Debezium will accommodate either approach and therefore does not have a
> > preference for one over the other, or for any alternative solution.
>
>
> I think there would be roughly three ways to address the issue:
>
> 1) Preserve the current behavior, that is, continue returning endptr
> only when decoding pg_logical_emit_message(), and update the
> documentation accordingly.
> 2) Change the behavior so that decoding pg_logical_emit_message()
> returns startptr, as is the case for DML records.
> 2.1) Make the change in minor releases.
> 2.2) Make the change in a major release, while updating the
> documentation for earlier major versions to describe their existing
> behavior.
>
> If there are requirements from other consumers of the logical
> decoding, I think those should of course be taken into account.
> Personally, however, I do not think we should choose option 1.
> As I wrote previously, the current behavior can be a pitfall
> that may lead to data loss when implementing a logical
> decoding consumer:
>
> ```
> Regarding the third point, I believe this distinction is
> important for consumers of logical decoding. Typically, a
> consumer records the LSN up to which the decoded result has been
> successfully processed and uses that LSN as the restart point
> after a crash or other failure. From that perspective, whether
> startptr or endptr is returned matters.
> ```
>
> As for options 2.1 and 2.2, given the potential impact on consumers,
> changing the behavior only in a major release, as in option 2.2,
> initially seems like a good approach.
>
> However, the discussion so far has not identified any particular
> reason why endptr should be returned, and the current behavior
> also appears to differ from the documentation. This therefore
> seems to me more like a bug. If we regard it as such, fixing it
> in minor releases, as in option 2.1, would seem to be the most
> straightforward approach.
>
> If there are consumers that are already aware of this issue and
> have implemented special handling for pg_logical_emit_message(),
> they would need to remove or adjust that handling, which could
> be inconvenient.
> That said, this behavior cannot readily be inferred from the
> documentation. It is also a relatively niche issue that is likely
> to be noticed only after it causes a problem such as data loss.
> Moreover, once someone identifies the cause, it seems like the kind
> of issue they would be inclined to report to the PostgreSQL
> community, but it seems that we haven't received such reports.
> For these reasons, I suspect there are few, if any, products that
> currently implement such a workaround.
>
> For consumers that are affected by this issue but are not yet aware
> of it, fixing it in minor releases would have the advantage that
> simply upgrading PostgreSQL to a newer minor release would resolve
> the problem without adding special handling for
> pg_logical_emit_message() to their products.
> I imagine most consumers would gain this advantage.
>
> For these reasons, I am beginning to think that fixing this in minor
> releases may be the preferable approach.
>
> What do you think?
>
>

Thank you for the patch. I reviewed and tested this patch on my
PostgreSQL 19beta2 tree. The patch did not apply cleanly on my branch
because of a context mismatch in
src/test/subscription/t/020_messages.pl. However, the backend change
in decode.c was applied manually for functional verification. I
reproduced the reported issue before applying the fix for
confirmation. After emitting a transactional logical message using
pg_logical_emit_message(), pg_logical_slot_get_changes() reported the
message LSN as the end of the WAL record, which matched the LSN of the
following INSERT record instead of the LogicalMessage WAL record
itself. After applying the backend change, and rerunning the test, the
decoded message LSN matched the LogicalMessage WAL record's start LSN
(origptr) as expected. The following INSERT retained its own higher
LSN, confirming that the decoded logical message now reports the
correct WAL position.

pg_logical_slot_get_changes():

0/0188DE08 | 711 | message: transactional: 1 prefix: test1, sz: 3 content:aaa
0/0188DE48 | 711 | table public.data: INSERT: id[integer]:2 data[text]:'2'

pg_waldump:

rmgr: LogicalMessage len (rec/tot): 59/ 59, tx: 711,
lsn: 0/0188DE08, prev 0/0188DDC8, desc: MESSAGE transactional, prefix
"test1"; payload (3 bytes): 61 61 61
rmgr: Heap len (rec/tot): 61/ 61, tx: 711, lsn:
0/0188DE48, prev 0/0188DE08, desc: INSERT off: 3, flags: 0x08, blkref
#0: rel 1663/5/16417 blk 0

The behavior is now consistent with other decoded records such as
INSERT, UPDATE, DELETE, and TRUNCATE, which already report the start
LSN of their corresponding WAL records. Overall, the functional
change behaves as intended, and I did not observe any issues during
testing. The patch looks good to me.

Regards,
Solai

On 2026-07-21 18:57, solai v wrote:
> Thank you for the patch. I reviewed and tested this patch on my
> PostgreSQL 19beta2 tree. The patch did not apply cleanly on my branch
> because of a context mismatch in
> src/test/subscription/t/020_messages.pl.

Sorry for the late reply, and thanks for testing the patch.

Just to clarify, I tried applying v2 directly to the REL_19_BETA2 tag
(7873db5369b),
and git apply succeeded without any conflicts, including the change to
src/test/subscription/t/020_messages.pl.

I also checked whether that file differs between REL_19_BETA2 and
master, but found no differences:

$ git diff REL_19_BETA2 master --
src/test/subscription/t/020_messages.pl
$

The same is true for REL_19_STABLE and master:
$ git diff REL_19_STABLE master --
src/test/subscription/t/020_messages.pl
$

So I am not sure what caused the context mismatch.

Separately, I proposed fixing the issue in minor releases, but the v2
patch does not apply cleanly to 14 and 15.
Attached a patch for these versions.

--
Thanks,

--
Atsushi Torikoshi
Seconded from NTT DATA CORPORATION to SRA OSS K.K.

Hi,

On Mon, Aug 10, 2026 at 12:25 AM torikoshia <torikoshia(at)oss(dot)nttdata(dot)com> wrote:
>
> On 2026-07-21 18:57, solai v wrote:
> > Thank you for the patch. I reviewed and tested this patch on my
> > PostgreSQL 19beta2 tree. The patch did not apply cleanly on my branch
> > because of a context mismatch in
> > src/test/subscription/t/020_messages.pl.
>
> Sorry for the late reply, and thanks for testing the patch.
>
> Just to clarify, I tried applying v2 directly to the REL_19_BETA2 tag
> (7873db5369b),
> and git apply succeeded without any conflicts, including the change to
> src/test/subscription/t/020_messages.pl.
>
> I also checked whether that file differs between REL_19_BETA2 and
> master, but found no differences:
>
> $ git diff REL_19_BETA2 master --
> src/test/subscription/t/020_messages.pl
> $
>
> The same is true for REL_19_STABLE and master:
> $ git diff REL_19_STABLE master --
> src/test/subscription/t/020_messages.pl
> $
>
> So I am not sure what caused the context mismatch.
>
>
> Separately, I proposed fixing the issue in minor releases, but the v2
> patch does not apply cleanly to 14 and 15.
> Attached a patch for these versions.
>

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

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.

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.

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.

Regards,

[1] https://postgr.es/m/CAD21AoCTNGiddikkUcDKj5QLnsg-51bpr-o6L-GTHWZL4ZFYtQ%40mail.gmail.com

--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com

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.