Thread: Access to transaction status

Hi all,

I am currently implementing an experimental middleware based replicator for
a set
of fully replicated databases.

Do be able to handle all sorts of failures I needed two functions:

- A function to get the current XID
- A function which I can use later to tell if a given XID
commited/aborted/whatever

I did a small implementation of this (see attachment).

Could one of you hackers tell me if you think this is

- an ugly way of  accessing the clog?
- totally wrong because I missed some point?
- or a good and correct idea :)

It would be very nice if someone had the time to have a short look into
this.

Greetings,
Christian

"Christian Plattner" <postgresql@sioux.ch> writes:
> Do be able to handle all sorts of failures I needed two functions:

> - A function to get the current XID
> - A function which I can use later to tell if a given XID
> commited/aborted/whatever

How much later?  clog is not kept forever.
        regards, tom lane

On Thu, Jun 19, 2003 at 05:16:10PM +0200, Christian Plattner wrote:
> 
> Do be able to handle all sorts of failures I needed two functions:
> 
> - A function to get the current XID
> - A function which I can use later to tell if a given XID
> commited/aborted/whatever

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.


Jeroen

----- Original Message ----- 
From: "Tom Lane" <tgl@sss.pgh.pa.us>

> How much later?  clog is not kept forever.

Due to my setup, I could assure, that for the XID I ask for always

(ShmemVariableCache->nextXid - XID) < C   (and C is in my case something
around 150).

holds. A possible solution could be to (dynamically) announce this constant
C to the clog code,
so that the information is kept for a while. Ofcourse one should not do a
VACUUM FULL while not being sure about the status of a transaction in the
past :)

Until now, I did not investigate what happens when
ShmemVariableCache->nextXid wraps around.

- Christian

"Christian Plattner" <postgresql@sioux.ch> writes:
> From: "Tom Lane" <tgl@sss.pgh.pa.us>
>> How much later?  clog is not kept forever.

> ... Ofcourse one should not do a
> VACUUM FULL while not being sure about the status of a transaction in the
> past :)

As long as you haven't done a cluster-wide VACUUM, clog status will not
get recycled.  For the application you're describing I think this will
work fine.

You might want to set up the API of the inquiry function to include
specified return codes for UNKNOWN (older than beginning of clog) and
FUTURE (greater than NextXid) as well as COMMITTED, ABORTED, and
INPROGRESS.  The current implementation can't easily give you UNKNOWN
(it'll error out instead) but any general-usage function of this kind
would have to offer that.
        regards, tom lane

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