How to find out PIDs of transactions older than the current?

From: Torsten Förtsch <torsten(dot)foertsch(at)gmx(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: How to find out PIDs of transactions older than the current?
Date: 2014-04-25 11:26:19
Message-ID: 535A465B.5050805@gmx.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

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.

Will it also work after the wraparound? I am worried because there is no
default cast from XID to INT or BIGINT.

Is there a better way?

Thanks,
Torsten

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Oleg Bartunov 2014-04-25 13:32:35 New GIN opclass for hstore (Faster and smaller) !
Previous Message matthias.trauffer 2014-04-25 10:54:37 unexpected data offset flag 0