----- Original Message ----- 
From: "Jeroen T. Vermeulen" <jtv@xs4all.nl>

> I ran into the same need (Bruce, we discussed this at FOSDEM in Brussels
> this February) for libpqxx.  My code tries to compensate for the
> possibility that the backend connection is lost while waiting for a reply
> to a COMMIT.  The way I worked around it was to create a special record
> at the beginning of the transaction, in a dedicated table that's
> effectively a custom transaction log.  If the record is still there when
> I reconnect, the transaction committed.  If not, it didn't.
>
> Obviously this leaves some garbage collection issues, so I'd be really
> happy with a way to go back to the server after my connection is lost
> and find out whether my transaction committed or not.

I see a race condition in this approach: if you reconnect too fast, and the
backend which actually should commit is still in progress (assume it takes a
while to commit for whatever reasons) you get the impression that it did not
commit - and a short time later the backend will commit... (before noticing
that the client connection was lost).

A safe method would be to shut down all backends (or is there another method
to safely abort all transactions?), then start the backends again, and then
read the table with the special records. In this way you would be sure that
your transaction is not in progress while you're inspecting the table.
Ofcourse, this approach is not very fast and may abort alot of
transactions - but if consistency is more important for you than anything
else...

- Christian



Fri, 20 Jun 2003 09:35:08 +0200
"Christian Plattner" <postgresql@sioux.ch>
Re: Access to transaction status
On Fri, Jun 20, 2003 at 09:35:08AM +0200, Christian Plattner wrote:
> 
> I see a race condition in this approach: if you reconnect too fast, and the
> backend which actually should commit is still in progress (assume it takes a
> while to commit for whatever reasons) you get the impression that it did not
> commit - and a short time later the backend will commit... (before noticing
> that the client connection was lost).
 
Good point.  So far I assumed that a broken connection would take a while
to repair.  OTOH by the time TCP gives up due to a bad network connection,
wouldn't the server reach the same conclusion?


Jeroen


Fri, 20 Jun 2003 10:04:18 +0200
"Jeroen T. Vermeulen" <jtv@xs4all.nl>
Re: Access to transaction status

----- Original Message ----- 
From: "Jeroen T. Vermeulen" <jtv@xs4all.nl>

> > I see a race condition in this approach: if you reconnect too fast, and
the
> > backend which actually should commit is still in progress (assume it
takes a
> > while to commit for whatever reasons) you get the impression that it did
not
> > commit - and a short time later the backend will commit... (before
noticing
> > that the client connection was lost).
>
> Good point.  So far I assumed that a broken connection would take a while
> to repair.  OTOH by the time TCP gives up due to a bad network connection,
> wouldn't the server reach the same conclusion?
>

Well, I wouldn't rely solely on TCP when assuring consistency. Also, I don't
think that the backend will ever inspect its TCP socket while committing.

btw: There could be also other reasons for the client to loose the
connection (i.e. client process crashes).

- Christian


Fri, 20 Jun 2003 10:20:14 +0200
"Christian Plattner" <postgresql@sioux.ch>
Re: Access to transaction status
On Fri, Jun 20, 2003 at 10:20:14AM +0200, Christian Plattner wrote:
> 
> Well, I wouldn't rely solely on TCP when assuring consistency. Also, I don't
> think that the backend will ever inspect its TCP socket while committing.
 
No, but its underlying IP stack would.


> btw: There could be also other reasons for the client to loose the
> connection (i.e. client process crashes).

In that case the client would lose all its state as well, so not really 
a problem that can be handled client-side.


Jeroen


Fri, 20 Jun 2003 14:30:42 +0200
"Jeroen T. Vermeulen" <jtv@xs4all.nl>
Re: Access to transaction status

----- Original Message ----- 
From: "Jeroen T. Vermeulen" <jtv@xs4all.nl>

> > btw: There could be also other reasons for the client to loose the
> > connection (i.e. client process crashes).
>
> In that case the client would lose all its state as well, so not really
> a problem that can be handled client-side.

Well, my client (actually it is a middleware layer which routes transactions
to a set of replicas) keeps its own log, because it must be able to handle
arbitary failures. So it never looses its state.

- Christian


Fri, 20 Jun 2003 14:41:29 +0200
"Christian Plattner" <postgresql@sioux.ch>
Re: Access to transaction status
On Fri, Jun 20, 2003 at 02:41:29PM +0200, Christian Plattner wrote:
> 
> Well, my client (actually it is a middleware layer which routes transactions
> to a set of replicas) keeps its own log, because it must be able to handle
> arbitary failures. So it never looses its state.

In that case perhaps we should see if there's anything we can do for
each other.  At the current rate, libpqxx is growing towards a sort of
middleware product, but obviously it's not the right place to tackle
many of the typical middleware problems.


Jeroen


Fri, 20 Jun 2003 14:47:28 +0200
"Jeroen T. Vermeulen" <jtv@xs4all.nl>
Re: Access to transaction status