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

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