To: Michael Paesold <mpaesold@gmx.at>
Cc: pgsql-sql@postgresql.org
Subject: Re: Getting current transaction id 
In-reply-to: <28749.1032797034@www55.gmx.net> 
References: <3D8F3775.B5DD079@nsd.ca> <28749.1032797034@www55.gmx.net>
Comments: In-reply-to Michael Paesold <mpaesold@gmx.at>
	message dated "Mon, 23 Sep 2002 18:03:54 +0200"
Date: Wed, 25 Sep 2002 13:12:03 -0400
Message-ID: <11493.1032973923@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Michael Paesold <mpaesold@gmx.at> writes:
> I just read it's possible to get the MVCC last version numbers. Is it also
> possible to get the current transaction id?

Well, there's the brute force way: insert a tuple in some table and look
at its xmin.  Offhand I don't think we provide a SQL function to read
current transaction id, though it'd surely be a trivial addition.

> Would it be possible to check
> later if that transaction has been commited? This would be nice for a distributed
> application to enforce an "exactly once" semantics for transactions (even if
> there are network related errors while the server sends ack for commiting a
> transaction).

Again, it's not an exported operation, though you could add a SQL function
that called TransactionIdDidCommit().

> And if it's possible, how long would that information be valid, i.e. when do
> transaction id's get reused?

That would be the tricky part.  The ID would be reused after 4 billion
transactions, which is long enough that you probably don't care ... but
the segment of the transaction log that has the associated commit bit
will be recycled as soon as the server has no internal use for it
anymore, which could be as early as the next database-wide VACUUM.
If you tried to call TransactionIdDidCommit() after that, you'd get the
infamous "can't open pg_clog/nnnn" error.

> If it's not working I will have to implement my own transactions table.

That's what I'd recommend.  Transaction IDs are internal to the database
and are not designed for users to rely on.

			regards, tom lane