From: | Torsten Förtsch <torsten(dot)foertsch(at)gmx(dot)net> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: How to find out PIDs of transactions older than the current? |
Date: | 2014-04-25 14:09:14 |
Message-ID: | 535A6C8A.1080001@gmx.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 25/04/14 13:26, Torsten Förtsch wrote:
> I think I can find out the transaction ids of concurrent transactions
> older than the current one by:
>
> select * from txid_snapshot_xip(txid_current_snapshot())
> union
> select * from txid_snapshot_xmax(txid_current_snapshot());
>
> Now, I want to map these transaction ids to backend process ids.
> pg_stat_activity does not provide the transaction id. So, I turned to
> pg_locks.
>
> select l.pid
> from (
> select * from txid_snapshot_xip(txid_current_snapshot())
> union
> select * from txid_snapshot_xmax(txid_current_snapshot())) tx(id)
> join pg_locks l
> on ( l.locktype='transactionid'
> and l.transactionid::TEXT::BIGINT=tx.id);
>
> This works. But my transaction ids are still far less than 2^32.
I think I got it. pg_locks.transactionid is a 4-byte quantity. But
I can safely ignore the upper half of the BIGINT that comes out of
txid_snapshot_xip(). So, the query becomes:
select l.pid
from (
select * from txid_snapshot_xip(txid_current_snapshot())
union
select * from txid_snapshot_xmax(txid_current_snapshot())) tx(id)
join pg_locks l
on ( l.locktype='transactionid'
and l.transactionid::TEXT::BIGINT=tx.id & (1::BIGINT<<32)-1)
Torsten
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2014-04-25 14:15:32 | Re: unexpected data offset flag 0 |
Previous Message | Oleg Bartunov | 2014-04-25 13:32:35 | New GIN opclass for hstore (Faster and smaller) ! |