On Thu, Apr 3, 2014 at 5:04 PM, <bashtanov@imap.cc> wrote:
> However, there is no "transaction" column in pg_locks.
>
> Neither transactionid nor virtualtransaction could be the mentioned column
> as transactionid is "null if the target is not a transaction ID" and
> virtualtransaction has different column data type.
Yes, documentation is unclear, but even if virtualtransaction has text
as data type, it is the combination of backendID/TransactionID so you
could use that for a join with pg_prepared_xacts like that:
=# create table aa (a int);
CREATE TABLE
=# begin;
BEGIN
=# insert into aa values (1);
INSERT 0 1
=# prepare transaction 'toto';
PREPARE TRANSACTION
=# select locktype, mode, gid, relation
from pg_locks pl
join pg_prepared_xacts ppx on
ppx.transaction = split_part(pl.virtualtransaction, '/', 2)::xid;
locktype | mode | gid | relation
---------------+------------------+------+----------
relation | RowExclusiveLock | toto | 16385
transactionid | ExclusiveLock | toto | null
(2 rows)
At the same time, I am attaching a doc patch recommending using
virtualtransaction instead of transaction in pg_locks when doing a
join with pg_prepared_xacts.
Regards,
--
Michael