Thread: Transactions over pathological TCP connections
Out of (mostly idle) curiousity, when exactly does a transaction commit, especially with respect to a TCP connection that a pathological demon will cut off at the worst possible moment?
The thing is, I'm using PostgreSQL as a queue, using asynchronous notifications and following the advice of Marko Tiikkaja in this post:
I'm using a stored procedure to reduce the round trips between the database and client, and then running it in a "bare transaction", that is, as "SELECT dequeue_element();" with an implicit BEGIN/COMMIT to mark a row in the queue as taken and return it.
My question is, would it be theoretically possible for an element of a queue to become marked but not delivered, or delivered and not marked, if the TCP connection between the backend and client was interrupted at the worst possible moment? Will the backend wait for the delivery of the row be acknowledged before the transaction is committed? Or should the truly paranoid use an explicit transaction block and not consider the row taken until confirmation that the transaction has committed has been received?
Best,
Leon
Leon Smith <leon.p.smith@gmail.com> writes:
> Out of (mostly idle) curiousity, when exactly does a transaction commit,
> especially with respect to a TCP connection that a pathological demon will
> cut off at the worst possible moment?
It's committed when the XLOG_XACT_COMMIT WAL record reaches disk,
if by "committed" you mean "is guaranteed to still be visible following
recovery from a subsequent database or operating system crash". If you
have some other definition of "committed" in mind, please say what.
> My question is, would it be theoretically possible for an element of a
> queue to become marked but not delivered, or delivered and not marked, if
> the TCP connection between the backend and client was interrupted at the
> worst possible moment?
The transaction would be committed before a command success report is
delivered to the client, so I don't think delivered-and-not-marked is
possible. The other direction is possible, if the connection drops
after the transaction is committed but before the completion report
can be delivered to the client.
regards, tom lane
On Tue, Jun 19, 2012 at 1:56 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > The transaction would be committed before a command success report is > delivered to the client, so I don't think delivered-and-not-marked is > possible. ...unless you have configured synchronous_commit=off, or fsync=off. Or unless your disk melts into a heap of slag and you have to restore from backup. You can protect against that last case using synchronous replication. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Tue, Jun 19, 2012 at 11:59 AM, Robert Haas <robertmhaas@gmail.com> wrote:
Best,
On Tue, Jun 19, 2012 at 1:56 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:...unless you have configured synchronous_commit=off, or fsync=off.
> The transaction would be committed before a command success report is
> delivered to the client, so I don't think delivered-and-not-marked is
> possible.
Or unless your disk melts into a heap of slag and you have to restore
from backup. You can protect against that last case using synchronous
replication.
But hard disk failure isn't in the failure model I was concerned about. =) To be perfectly honest, I'm not too concerned with either hard drive failure or network failure, as we are deploying on Raid 1+0 database server talking to the client over a redundant LAN, and using asynchronous (Slony) replication to an identical database server just in case. No single point of failure is a key philosophy of this app from top to bottom. Like I said, this is mostly idle curiosity.
But I'm also accustomed to trying to get work done on shockingly unreliable internet connections. As a result, network failure is something I think about quite a lot when writing networked applications. So this is not entirely idle curiosity either.
And thinking about this a bit more, it's clear that the database has to commit before the result is sent, on the off chance that the transaction fails and needs to be retried. And that an explicit transaction block isn't really a solution either, because a "BEGIN; SELECT dequeue_row()" would get the row to the client without marking it as taken, but the pathological TCP disconnect could then attack the following "COMMIT;", leaving the client to think that the row has not been actually taken when it in fact has.
It's not clear to me that this is even a solvable problem without modifying the schema to include both a "taken" and a "finished processing" state, and then letting elements be re-delievered after a period of time. But this would then allow a pathological demon with the power to cause TCP connects have a single element delivered and processed multiple times.
In any case, thanks for the responses...
Best,
Leon
I just realized this is essentially an instance of the Two General's Problem; which is something I feel should have been more obvious to me.
On Tue, Jun 19, 2012 at 5:50 PM, Leon Smith <leon.p.smith@gmail.com> wrote:
On Tue, Jun 19, 2012 at 11:59 AM, Robert Haas <robertmhaas@gmail.com> wrote:On Tue, Jun 19, 2012 at 1:56 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:...unless you have configured synchronous_commit=off, or fsync=off.
> The transaction would be committed before a command success report is
> delivered to the client, so I don't think delivered-and-not-marked is
> possible.
Or unless your disk melts into a heap of slag and you have to restore
from backup. You can protect against that last case using synchronous
replication.But hard disk failure isn't in the failure model I was concerned about. =) To be perfectly honest, I'm not too concerned with either hard drive failure or network failure, as we are deploying on Raid 1+0 database server talking to the client over a redundant LAN, and using asynchronous (Slony) replication to an identical database server just in case. No single point of failure is a key philosophy of this app from top to bottom. Like I said, this is mostly idle curiosity.But I'm also accustomed to trying to get work done on shockingly unreliable internet connections. As a result, network failure is something I think about quite a lot when writing networked applications. So this is not entirely idle curiosity either.And thinking about this a bit more, it's clear that the database has to commit before the result is sent, on the off chance that the transaction fails and needs to be retried. And that an explicit transaction block isn't really a solution either, because a "BEGIN; SELECT dequeue_row()" would get the row to the client without marking it as taken, but the pathological TCP disconnect could then attack the following "COMMIT;", leaving the client to think that the row has not been actually taken when it in fact has.It's not clear to me that this is even a solvable problem without modifying the schema to include both a "taken" and a "finished processing" state, and then letting elements be re-delievered after a period of time. But this would then allow a pathological demon with the power to cause TCP connects have a single element delivered and processed multiple times.In any case, thanks for the responses...
Best,Leon
Leon Smith <leon.p.smith@gmail.com> writes: > It's not clear to me that this is even a solvable problem without modifying > the schema to include both a "taken" and a "finished processing" state, > and then letting elements be re-delievered after a period of time. You maybe should have a look at PGQ from Skytools before reinventing it. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support