Re: Transactions over pathological TCP connections

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