Re: BUG #9840: Documentation bug on pg_locks

On Thu, Apr 3, 2014 at 5:04 PM, <bashtanov(at)imap(dot)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