Re: BUG #9840: Documentation bug on pg_locks

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