Thread: BUG #9840: Documentation bug on pg_locks

The following bug has been logged on the website:

Bug reference:      9840
Logged by:          Alexey Bashtanov
Email address:      bashtanov@imap.cc
PostgreSQL version: 9.3.4
Operating system:   -
Description:

Hello!
Documentation
http://www.postgresql.org/docs/current/static/view-pg-locks.html states that
"if you are using prepared transactions, the transaction column can be
joined to the transaction column of the pg_prepared_xacts view to get more
information on prepared transactions that hold locks."

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.

Please could you clarify the docs.

Regards,
  Alexey Bashtanov

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

Michael Paquier <michael.paquier@gmail.com> writes:
> 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.

That change doesn't really seem good enough to me, since exactly how to do
the join remains just as unclear as before.  I think we'd better give an
explicit example.  I'd be inclined to write it as

select * from
  pg_locks pl join pg_prepared_xacts ppx
    on pl.virtualtransaction = '-1/' || ppx.transaction;

            regards, tom lane