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
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
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
Attachments:
20140403_pg_locks_docfix.patchtext/plain; charset=US-ASCII; name=20140403_pg_locks_docfix.patchDownload+1-1
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
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs