Re: [GENERAL] How to find out PIDs of transactions older than the current?

2014-04-25 Thread Torsten Förtsch 
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


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

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

2014-04-25 Thread Torsten Förtsch 
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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general