4 ms·
The logical replication protocol sends a series of messages that essentially follow the flow that a database transaction would. i.e. a stream of messages like:
by zknill 3y ago
The logical replication protocol sends a series of messages that essentially follow the flow that a database transaction would.
i.e. a stream of messages like: "BEGIN", "[the data]", ["COMMIT" or "ROLLBACK"].
So any application that listens to the Postgres replication protocol can handle the transaction in the same way that Postgres does. Concretely you might choose to open a SQLite transaction on BEGIN, apply the statements, and then COMMIT or ROLLBACK based on the next messages received on the stream replication protocol.
The data sent on the replication protocol includes the state of the row after the write query has completed. This means you don't need to worry about getting out of sync on queries like "UPDATE field = field + 1" because you have access to the exact resulting value as stored by Postgres.
TL;DR - you can follow the same begin/change/commit flow that the original transaction did on the upstream Postgres server, and you have access to the exact underlying data after the write was committed.
It's also true (as other commenters have pointed out) that for not-huge transactions (i.e. not streaming transactions, new feature in Postgres 15) the BEGIN message will only be sent if the transaction was committed. It's pretty unlikely that you will ever process a ROLLBACK message from the protocol (although possible).
- deleted 3y ago[deleted]
- paulddraper 3y agoLogical replication never includes uncommitted data, unless you have written an custom output plugin. EDIT: https://stackoverflow.com/questions/52202534/postgresql-does-logical-replication-include-rollback-transaction https://stackoverflow.com/questions/52202534/postgresql-does...
- zknill 3y agoIt's true for protocol version 1 that only the committed data is sent on the replication connection. In protocol version 2 (introduced in postgres 14) large in-progress transactions can appear in the new "Stream" messages sent on the replication connection: StreamStart, StreamStop, StreamAbort, StreamCommit, etc. In the case of large in-progress transactions uncommitted data might end up in the replication connection after a StreamStart message. But you would also receive a StreamCommit or StreamAbort message to tell you what happened to that transaction. I've not worked out what qualifies as a "large" transaction though. But it is _possible_ to get uncommitted data in the replication connection, although unlikely. https://www.postgresql.org/docs/15/protocol-logicalrep-message-formats.html https://www.postgresql.org/docs/15/protocol-logicalrep-messa...