Robert Treat wrote:
> On Wednesday 05 September 2007 12:56, Tom Lane wrote:
>> "Florian G. Pflug" <fgp@phlo.org> writes:
>>> However, none of these are very strong reasons - certainly weaker than
>>> doing what ensures to cause the least confusion. I'm therefore
>>> starting to think that we should remove transaction, and keep the name
>>> virtualtransaction for the VXID. That will ensure that clients who
>>> *do* rely on pg_locks and the "transaction" column (which will be few,
>>> I guess) at least fail early and visibly, instead of producing bogus
>>> results...
>
> Reading the docs, it says "Every transaction holds an exclusive lock on its
> virtual transaction ID for its entire duration. If a permanent ID is assigned
> to the transaction (which normally happens only if the transaction changes
> the state of the database), it also holds an exclusive lock on its permanent
> transaction ID until it ends."
>
> ISTM that by removing the transaction column, there is no way to see the XID
> for relations thats have been updated (which by definition will have locks on
> them). Am I mis-reading the docs, or have we lost that functionality?
I'm sure sure if that is what you mean - but there were two columns carrying
transaction ids in pg_locks - the first was called transaction*id*, and held
a transaction that either *is* locked or *is* being waited for. The second
was called just transaction, and held the xid of the transaction *holding*
the lock or waiting *for* the lock. Of course, for exclusive locks on xids,
the first and the second xid where always the same, because nobody apart
from the transaction itself ever requests an exclusive lock on it's xid.
Now, the second column is replaced by virtualtransaction, holding the vxid
of the transaction holding the lock. To get the real xid for the *holding*
transaction, you'd have to join pg_locks to self, using the vxid as a join key.
So, in essence, you get the old pg_locks format back by doing
select l1.*, l2.transactionid as "transaction" from pg_locks l1, pg_locks l2
where l1.vxid = l2.vxid and l2.locktype = 'transaction'
and l2.mode='exclusive' and l2.granted=true.
Hm.. Maybe we should put that into the docs or into the release notes?
greetings, Florian Pflug