Thread: Get current trasanction id

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

Thanks in advance for any help.

ML

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/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

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