Why is the LSN key called 'nextlsn'?

[sidmutha]

This is more of a query than an issue.
The LSN for each change comes with key as 'nextlsn' even though the value is the actual LSN. This gives the impression that the value given by wal2json for the 'nextlsn' key is for the next change rather than the current one. This threw me off.

The code says that it is the value pointing to the next commit record.

Output using JDBC and setting the slot option include-lsn to true:

{
	"xid": 740,
	"nextlsn": "0/18154A8",
	"timestamp": "2018-02-16 12:46:40.877369+05:30",
	"change": [
		{
			"kind": "insert",
			"schema": "public",
			"table": "test_table",
			"columnnames": ["pk", "name"],
			"columntypes": ["integer", "text"],
			"columnvalues": [160, "val1"]
		}
	]
}
-----
{
	"xid": 741,
	"nextlsn": "0/1815558",
	"timestamp": "2018-02-16 12:46:43.485518+05:30",
	"change": [
		{
			"kind": "insert",
			"schema": "public",
			"table": "test_table",
			"columnnames": ["pk", "name"],
			"columntypes": ["integer", "text"],
			"columnvalues": [161, "val2"]
		}
	]
}
-----
{
	"xid": 742,
	"nextlsn": "0/1815608",
	"timestamp": "2018-02-16 12:46:46.437739+05:30",
	"change": [
		{
			"kind": "insert",
			"schema": "public",
			"table": "test_table",
			"columnnames": ["pk", "name"],
			"columntypes": ["integer", "text"],
			"columnvalues": [162, "val3"]
		}
	]
}

Output from psql:

SELECT * FROM pg_logical_slot_peek_changes('test_slot', NULL, NULL);
    lsn    | xid |                                                                              data                                                                              
-----------+-----+----------------------------------------------------------------------------------------------------------------------------------------------------------------
 0/18154A8 | 740 | {"change":[{"kind":"insert","schema":"public","table":"test_table","columnnames":["pk","name"],"columntypes":["integer","text"],"columnvalues":[160,"val1"]}]}
 0/1815558 | 741 | {"change":[{"kind":"insert","schema":"public","table":"test_table","columnnames":["pk","name"],"columntypes":["integer","text"],"columnvalues":[161,"val2"]}]}
 0/1815608 | 742 | {"change":[{"kind":"insert","schema":"public","table":"test_table","columnnames":["pk","name"],"columntypes":["integer","text"],"columnvalues":[162,"val3"]}]}

As you can see the nextlsn from wal2json maps to the lsn column given by Postgres.

Can someone clear the confusion?

[eulerto]

See commit dc6e390.

wal2json nextlsn is different from pg_logical_slot_*_changes lsn. lsn assumes different positions depending on the WAL record type (first_lsn for begin, end_lsn for commit, and lsn for changes). See details in backend/replication/logical/logical.c (functions begin_cb_wrapper, commit_cb_wrapper, and change_cb_wrapper).

[sidmutha]

Hi
Sorry for the late reply.

Does it mean that each WAL record doesn't have a single LSN but multiple? (first_lsn, lsn, end_lsn) i.e. a single WAL record spans multiple LSNs?

I'm not clear how the LSN numbering works. Can you explain a bit more about it?

Also wal2json gives the nextlsn which is apparently the lsn of the next record (which is the same as end_lsn of the current record?). I can use this nextlsn value as a starting point for resuming consumption.
But what value do I use to mark as the flushed lsn value to let postgres know that the client has read upto the current record?

If I use the nextlsn value, won't it mean that the client has read upto next record whereas it actually has read only upto the current record (something like current_lsn)?

[eulerto]

Does it mean that each WAL record doesn't have a single LSN but multiple? (first_lsn, lsn, end_lsn) i.e. a single WAL record spans multiple LSNs?

WAL is a stream of changes. LSN stands for Log Sequence Numbers i.e. a position in the WAL stream whose unit is bytes. When I mentioned first_lsn, lsn, and end_lsn I was refering to the logical replication context which use those "marks" to point to start of the transaction, a command in the transaction, and end of the transaction, respectively.

I'm not clear how the LSN numbering works. Can you explain a bit more about it?

Don't get me wrong, read src/backend/access/transam/README.

Also wal2json gives the nextlsn which is apparently the lsn of the next record (which is the same as end_lsn of the current record?). I can use this nextlsn value as a starting point for resuming consumption.
But what value do I use to mark as the flushed lsn value to let postgres know that the client has read upto the current record?

I suggest that you use replication origin instead of inventing your own replication progress tracking solution.

If I use the nextlsn value, won't it mean that the client has read upto next record whereas it actually has read only upto the current record (something like current_lsn)?

nextlsn points to the end of the commit record. That is what a replication progress tracking solution should store to restart stream in case of crash (if you search for end_lsn in src/backend/replication/logical/*.c you'll understand what I'm trying to explain).

[sidmutha]

Commenting to say that I utilized this in one of my projects, PGDeltaStream 😃
Thanks!

[erikvanzijst]

Could I pick this discussion back up for a related question? I notice that sometimes the first record I receive after attaching to an existing logical replication slot carries nextlsn: 0/0

$ pg_recvlogical -p 33107 -h localhost -U postgres -S slot -d postgres --start  -f - -o include-lsn=1 -v
pg_recvlogical: starting log streaming at 0/0 (slot slot)
pg_recvlogical: streaming initiated
pg_recvlogical: confirming write up to 0/0, flush to 0/0 (slot slot)
{"nextlsn":"0/0","change":[{"kind":"insert","schema":"public","table":"test","columnnames":["col1","col2"],"columntypes":["character varying","integer"],"columnvalues":[null,104]}]}
pg_recvlogical: confirming write up to 0/173A488, flush to 0/173A488 (slot slot)

This is a slot that has received and acknowledged records through the same pg_recvlogical command before and the database was not at 0/0.

This is not a bug report btw and I understand wal2json just echoes what's in the ReorderBufferTXN, but I was wondering if either of you two could shed some light on why I'm seeing 0/0?

[dko-slapdash]

Regarding replication origins: people say that to use them, one needs some superuser permissions which is not compatible with e.g. AWS RDS. So sometimes manual tracking is still beneficial.

Also, would be cool to have some “vanilla pseudocode” example in wal2json’s readme which shows, how exactly should the messages be processed/consumed and when should they be acked (especially in case of write-in-chunks mode). It’s non trivial, people make mistakes here and then even live with them for days/months until it appears that their solutions don’t fully work. I see many questions here and on StackOverflow which could’ve just been answered all at once with such a pseudocode.