Get current trasanction id

O Marek Lewczuk οΏ½οΏ½οΏ½οΏ½οΏ½οΏ½ οΏ½οΏ½οΏ½οΏ½ Dec 27, 2004 :

Hello,
is there any way to get current transaction id using plpgsql or sql ?

Maybe write a C function which calls GetCurrentTransactionId().

Thanks in advance for any help.

ML

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

--
-Achilleus

I'm sure there's many tricky ways, but one simple way would be to
insert a row into a table and then grab its XMIN value...

Hope this helps,

On Mon, Dec 27, 2004 at 09:52:57AM +0100, Marek Lewczuk wrote:

Hello,
is there any way to get current transaction id using plpgsql or sql ?

Thanks in advance for any help.

ML

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

On Mon, Dec 27, 2004 at 09:52:57AM +0100, Marek Lewczuk wrote:

is there any way to get current transaction id using plpgsql or sql ?

A couple of people have posted suggestions but I'll ask a question:

Why do you want the transaction ID? What problem are you trying
to solve?

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Michael Fuhr napisał(a):

On Mon, Dec 27, 2004 at 09:52:57AM +0100, Marek Lewczuk wrote:

is there any way to get current transaction id using plpgsql or sql ?

A couple of people have posted suggestions but I'll ask a question:

Why do you want the transaction ID? What problem are you trying
to solve?

Michael,
I've already solved the problem - I found somewhere on the web a very
simple C function, which returns transaction id. I need transaction id
for plperl functions setVar, getVar (using $_SHARED array). Functions
can write/read variables which are available either for connection or
transaction.

Regards,

ML

Am Mo, den 27.12.2004 schrieb Marek Lewczuk um 20:54:

Michael Fuhr napisał(a):

Why do you want the transaction ID? What problem are you trying
to solve?

I've already solved the problem - I found somewhere on the web a very
simple C function, which returns transaction id. I need transaction id
for plperl functions setVar, getVar (using $_SHARED array). Functions
can write/read variables which are available either for connection or
transaction.

It would be nice to have that in pgplsql. I once needed a transaction Id
in oracle for a historisation trigger. It saves before copies of the
modified records on a per transaction basis.

Sincerely,
Joachim

--

"... ein Geschlecht erfinderischer Zwerge, die fuer alles gemietet werden
koennen." - Bertolt Brecht - Leben des Galilei

Joachim Zobel napisał(a):

Am Mo, den 27.12.2004 schrieb Marek Lewczuk um 20:54:

Michael Fuhr napisał(a):

Why do you want the transaction ID? What problem are you trying
to solve?

I've already solved the problem - I found somewhere on the web a very
simple C function, which returns transaction id. I need transaction id
for plperl functions setVar, getVar (using $_SHARED array). Functions
can write/read variables which are available either for connection or
transaction.

It would be nice to have that in pgplsql. I once needed a transaction Id
in oracle for a historisation trigger. It saves before copies of the
modified records on a per transaction basis.

As I wrote before you can have it in plpgsql - you just need to install
special c function as e.g getCurrentTransactionId() and you will be able
to get id from within plpgsql - but I agree that this function should be
in base SQL functions or at least in contrib modules.

C function is very simple:

getcurrenttransactionid.c:
#include "postgres.h"
#include "access/xact.h"

Datum getcurrenttransactionid(PG_FUNCTION_ARGS)
{
TransactionId xid = GetCurrentTransactionId();
PG_RETURN_INT32((int32) xid);
}

getcurrenttransactionid.sql:
SET search_path = public;

CREATE FUNCTION getCurrentTransactionId()
RETURNS integer
AS 'MODULE_PATHNAME'
LANGUAGE 'c';

Regards,

Marek Lewczuk