Re: Retrieve the postgres transaction id

Hi,

Is there a way to retrieve the postgres transaction id using JDBC?

Bye,
Cédric.

--
Cédric Coulon cedric(dot)coulon(at)lina(dot)univ-nantes(dot)fr
Atlas group, INRIA
LINA - Université de Nantes voice: +33 (0)2 51 12 59 63
2, Rue de la Houssinière BP 92208 fax : +33 (0)2 51 12 58 97
44322 Nantes Cedex 03 France

Cédric Coulon wrote:
> Hi,
>
> Is there a way to retrieve the postgres transaction id using JDBC?

There's no special JDBC path for doing this.

(later) A bit of experimentation yields this (I'm sure there's a better
way):

select transaction from pg_locks where pid = pg_backend_pid();

You should be able to run that as a normal query through JDBC.

-O

Oliver Jowett <oliver(at)opencloud(dot)com> writes:
> (later) A bit of experimentation yields this (I'm sure there's a better
> way):
> select transaction from pg_locks where pid = pg_backend_pid();

You'd need to qualify that more, since as-is it will also show rows for
other locks the transaction holds. Possibly "where transaction is not
null" will be sufficient.

regards, tom lane

Thx for yours helps.
But Isn't there a possibility that a backend has more than one
transaction with locks?

Cedric.

Tom Lane wrote:

> Oliver Jowett <oliver(at)opencloud(dot)com> writes:
>
>>(later) A bit of experimentation yields this (I'm sure there's a better
>>way):
>> select transaction from pg_locks where pid = pg_backend_pid();
>
>
> You'd need to qualify that more, since as-is it will also show rows for
> other locks the transaction holds. Possibly "where transaction is not
> null" will be sufficient.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

--
Cédric Coulon cedric(dot)coulon(at)lina(dot)univ-nantes(dot)fr
Atlas group, INRIA
LINA - Université de Nantes voice: +33 (0)2 51 12 59 63
2, Rue de la Houssinière BP 92208 fax : +33 (0)2 51 12 58 97
44322 Nantes Cedex 03 France

Cédric Coulon wrote:
> Thx for yours helps.
> But Isn't there a possibility that a backend has more than one
> transaction with locks?

As I understand it, no. There is exactly one connection per backend, and
one transaction per connection, and there is no way to change the
transaction associated with a connection without committing/aborting the
existing transaction.

-O

That won't be true soon, nested transactions are coming ...

Just a heads up.
Dave
On Tue, 2004-05-11 at 19:39, Oliver Jowett wrote:
> Cédric Coulon wrote:
> > Thx for yours helps.
> > But Isn't there a possibility that a backend has more than one
> > transaction with locks?
>
> As I understand it, no. There is exactly one connection per backend, and
> one transaction per connection, and there is no way to change the
> transaction associated with a connection without committing/aborting the
> existing transaction.
>
> -O
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>
>
> !DSPAM:40a167fc177361515062736!
>
>
--
Dave Cramer
519 939 0336
ICQ # 14675561

Oliver Jowett <oliver(at)opencloud(dot)com> writes:
> Cdric Coulon wrote:
>> But Isn't there a possibility that a backend has more than one
>> transaction with locks?

> As I understand it, no. There is exactly one connection per backend, and
> one transaction per connection,

This is true at the moment. Alvaro is hard at work on nested
transactions, which will imply more than one active XID per connection.
It's not decided yet how that will reflect into pg_locks, but I'd expect
there will be some visible consequence...

Probably the question that should have been asked at the outset of this
thread is "why do you want to know"? I can't offhand think of a good
reason for clients to be interested in transaction numbers --- they are
surely just an internal implementation detail.

regards, tom lane