Transactions over pathological TCP connections

From: Leon Smith <leon(dot)p(dot)smith(at)gmail(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Transactions over pathological TCP connections
Date: 2012-06-18 19:33:02
Message-ID: CAPwAf1ksnfps0z=3VwsCojwX4ui-V1-u-u-TA9UH_7xgAvvFAw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Andres Freund 2012-06-18 19:41:01 Re: initdb and fsync
Previous Message Jeff Davis 2012-06-18 19:32:25 Re: initdb and fsync