Thread: pg_locks view versus prepared transactions

Now that Heikki's two-phase-commit patch is in, we have a bit of a
problem in the pg_locks view: prepared transactions can hold locks,
but you can't tell which prepared transaction is holding which lock.
They all show with "pid 0".  (This should probably come out as a NULL
instead of 0, but I didn't change it yet.)

I think the minimum thing we ought to do about this is add an XID
column to pg_locks to show the transaction ID holding each lock.
Then you could join that to pg_prepared_xacts to see what's what.

I was also wondering about adding a current-XID column to
pg_stat_activity, and encouraging people to join pg_locks and
pg_stat_activity on XID instead of PID.

Ultimately we should maybe even remove PID from pg_locks, but probably
for backwards compatibility it'd have to be deprecated for a release
or two first.

Thoughts?
        regards, tom lane

> I think the minimum thing we ought to do about this is add an XID
> column to pg_locks to show the transaction ID holding each lock.
> Then you could join that to pg_prepared_xacts to see what's what.
> I was also wondering about adding a current-XID column to
> pg_stat_activity, and encouraging people to join pg_locks and
> pg_stat_activity on XID instead of PID.

That would be awesome.  Is there any performance penalty to do this?  (I
don't care about performance of pg_lock_status function execution, just
overall overhead).

> Ultimately we should maybe even remove PID from pg_locks, but probably
> for backwards compatibility it'd have to be deprecated for a release
> or two first.

It is interesting to note that systems with stats disabled are unable to
get lock owner information in this case (so what?).

Merlin

On Mon, Jun 20, 2005 at 03:55:45PM -0400, Merlin Moncure wrote:

> > Ultimately we should maybe even remove PID from pg_locks, but probably
> > for backwards compatibility it'd have to be deprecated for a release
> > or two first.
> 
> It is interesting to note that systems with stats disabled are unable to
> get lock owner information in this case (so what?).

We could make the pg_stat_activity view show information from the
ProcArray shared struct, when stats are disabled.

-- 
Alvaro Herrera (<alvherre[a]surnet.cl>)
"In fact, the basic problem with Perl 5's subroutines is that they're not
crufty enough, so the cruft leaks out into user-defined code instead, by
the Conservation of Cruft Principle."  (Larry Wall, Apocalypse 6)

"Merlin Moncure" <merlin.moncure@rcsonline.com> writes:
>> I was also wondering about adding a current-XID column to
>> pg_stat_activity, and encouraging people to join pg_locks and
>> pg_stat_activity on XID instead of PID.

> That would be awesome.  Is there any performance penalty to do this?

I gave up on the idea after I realized that current XID tends to change
a lot faster than the pg_stats mechanism is designed to track.
(Consider the half-second lag for starters...)  Turning pg_stats into a
realtime mechanism would be horridly expensive.
        regards, tom lane

Alvaro Herrera <alvherre@surnet.cl> writes:
> On Mon, Jun 20, 2005 at 03:55:45PM -0400, Merlin Moncure wrote:
>> It is interesting to note that systems with stats disabled are unable to
>> get lock owner information in this case (so what?).

> We could make the pg_stat_activity view show information from the
> ProcArray shared struct, when stats are disabled.

pg_stat_activity is not real time, and should not be because its
intended use is to help interpret the also-not-real-time other
statistics.

If people are concerned about this, my inclination would be to add a
separate view, or just add even more columns to pg_locks?

However, all you could get from ProcArray would be the database in
which the backend is running, and maybe the owning user's ID if we
cared to expend the extra space to store it there.  We're certainly
not going to add current_query or any such thing into that array.
        regards, tom lane

On Mon, Jun 20, 2005 at 04:18:03PM -0400, Tom Lane wrote:

> However, all you could get from ProcArray would be the database in
> which the backend is running, and maybe the owning user's ID if we
> cared to expend the extra space to store it there.  We're certainly
> not going to add current_query or any such thing into that array.

You could show the current transaction Id, which is also useful.
(Presently there's no way to know even a backend's own TransactionId,
and people is suggested to use hacks like insert a row in a table and
check its xmin.)

Maybe we could add an adittional view, with all the info from ProcArray,
which is useful sometimes.  Then you could join that to pg_locks, and it
would work even if the statistic collector is disabled.

-- 
Alvaro Herrera (<alvherre[a]surnet.cl>)
"La felicidad no es mañana. La felicidad es ahora"