Re: [HACKERS] pg_locks view versus prepared transactions
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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] pg_locks view versus prepared transactions
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) ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] pg_locks view versus prepared transactions
Merlin Moncure [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] pg_locks view versus prepared transactions
Alvaro Herrera [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pg_locks view versus prepared transactions
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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
