3 ms·

How does Paxos replication interact with Postgres transactions? This doesn't seem to be explained in the article or docs, and the examples only show the behavio

How does Paxos replication interact with Postgres transactions? This doesn't seem to be explained in the article or docs, and the examples only show the behavior with autocommit. In particular, suppose I do the following sequence of operations on an empty table: BEGIN INSERT INTO table VALUES ('foobar', 123); SELECT * FROM table; ROLLBACK; Would the INSERT be submitted to the Paxos log immediately, causing it to be applied on other replicas even though the transaction never committed? Or would it be deferred until commit time, causing the SELECT to return an empty result? Or is there something more sophisticated going on?

mslot 11y ago
Once a query has been logged there's no turning back. The problem with transactions is mainly that Paxos is fundamentally incompatible with read committed mode. It is technically possible to log a multi-statement transaction as a single string, which makes it serializable.
trhway 11y ago
in case of one single node the INSERT would result in a 2 (logical, real implementations do various optimizations to avoid having 2 full copies) versions of table data: 1. empty table for everybody else and 2. the same table containing 1 record for consumption inside the current transaction. Thus the SELECT inside the same tx will see that 1 record. Submitting INSERT into Paxos log immediately would allow the rest of the cluster to see those 2 logical states of the table. Thus one can imagine [ have no idea whether Citus implemented it or not] that if failover happens after INSERT before SELECT, the cluster would still be able to continue the transaction and executing the transaction's SELECT on another node would still produce 1 record while any other session would still see empty table. Upon COMMIT or ROLLBACK, the same as in one node situation, one of those table states will become permanent and another will disappear. Through Paxos log whole cluster would be aware (and in consensus :) about it. If there were other transactions in progress, the relations between them would be subject to isolation levels configured as usual. For example if there were another transaction running with READ_COMMITTED isolation level on some another node and that transaction were wanting to do SELECT on that table, it would see the COMMIT and thus would be able to read that one record. Paxos serialisability of the state of the cluster has nothing to do with transaction isolation levels. Paxos just provides that all nodes are in consensus about the state, for example the state of relationship between transactions in progress.

mslot 11y ago

Once a query has been logged there's no turning back. The problem with transactions is mainly that Paxos is fundamentally incompatible with read committed mode. It is technically possible to log a multi-statement transaction as a single string, which makes it serializable.

trhway 11y ago

in case of one single node the INSERT would result in a 2 (logical, real implementations do various optimizations to avoid having 2 full copies) versions of table data: 1. empty table for everybody else and 2. the same table containing 1 record for consumption inside the current transaction. Thus the SELECT inside the same tx will see that 1 record. Submitting INSERT into Paxos log immediately would allow the rest of the cluster to see those 2 logical states of the table. Thus one can imagine [ have no idea whether Citus implemented it or not] that if failover happens after INSERT before SELECT, the cluster would still be able to continue the transaction and executing the transaction's SELECT on another node would still produce 1 record while any other session would still see empty table. Upon COMMIT or ROLLBACK, the same as in one node situation, one of those table states will become permanent and another will disappear. Through Paxos log whole cluster would be aware (and in consensus :) about it. If there were other transactions in progress, the relations between them would be subject to isolation levels configured as usual. For example if there were another transaction running with READ_COMMITTED isolation level on some another node and that transaction were wanting to do SELECT on that table, it would see the COMMIT and thus would be able to read that one record. Paxos serialisability of the state of the cluster has nothing to do with transaction isolation levels. Paxos just provides that all nodes are in consensus about the state, for example the state of relationship between transactions in progress.