Re: 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:

http://johtopg.blogspot.com/2010/12/queues-in-sql.html

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(dot)p(dot)smith(at)gmail(dot)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(at)sss(dot)pgh(dot)pa(dot)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(at)gmail(dot)com> wrote:

> On Tue, Jun 19, 2012 at 1:56 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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.
>

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(dot)p(dot)smith(at)gmail(dot)com> wrote:

> On Tue, Jun 19, 2012 at 11:59 AM, Robert Haas <robertmhaas(at)gmail(dot)com>wrote:
>
>> On Tue, Jun 19, 2012 at 1:56 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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.
>>
>
>
> 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(dot)p(dot)smith(at)gmail(dot)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