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

Torsten Förtsch <torsten.foertsch@xxxxxxx> · Fri, 25 Apr 2014 13:26:19 +0200

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

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general