Thread: pg_locks needs a facelift
There was some previous discussion in a thread starting at http://archives.postgresql.org/pgsql-hackers/2005-01/msg00750.php about expanding the pg_locks view to provide more useful support for contrib/userlock locks. Also, the changes Alvaro and I just finished for sharable row locks mean that the core system itself is using lock types that aren't adequately displayed by pg_locks. So we need to do something. In the earlier thread there was talk of separate views for system and user locks, but on reflection I think that's the wrong approach; principally because it will be impossible to get exactly-simultaneous snapshots of the system and user lock states if there are two views involved. And that's something you tend to want when studying lock behavior ;-). So I think we have to maintain the current arrangement of one view, and add enough columns to it to handle all the requirements. As things are set up in CVS tip, LOCKTAG is defined to support these kinds of locks: LOCKTAG_RELATION, /* whole relation */ /* ID info for a relation is DB OID + REL OID; DB OID = 0 if shared*/ LOCKTAG_RELATION_EXTEND, /* the right to extend a relation */ /* same ID info as RELATION */ LOCKTAG_PAGE, /* one page of a relation */ /* ID info for a page is RELATION info + BlockNumber */ LOCKTAG_TUPLE, /* one physical tuple */ /* ID info for a tuple is PAGE info + OffsetNumber */ LOCKTAG_TRANSACTION, /* transaction (for waiting for xact done) */ /* ID info for a transaction is its TransactionId*/ LOCKTAG_OBJECT, /* non-relation database object */ /* ID info for an object is DB OID + CLASSOID + OBJECT OID + SUBID */ /* * Note: object ID has same representation as in pg_depend and * pg_description,but notice that we are constraining SUBID to 16 bits. * Also, we use DB OID = 0 for shared objects suchas tablespaces. */ LOCKTAG_USERLOCK /* reserved for contrib/userlock */ /* ID info for a userlock isdefined by user_locks.c */ and the physical struct provides four ID fields that are mapped in various ways for the given purposes: typedef struct LOCKTAG { uint32 locktag_field1; /* a 32-bit ID field */ uint32 locktag_field2; /* a 32-bit IDfield */ uint32 locktag_field3; /* a 32-bit ID field */ uint16 locktag_field4; /* a16-bit ID field */ uint8 locktag_type; /* see enum LockTagType */ uint8 locktag_lockmethodid; /* lockmethod indicator */ } LOCKTAG; One way we could go is to just expose the four ID fields more or less as-is, plus the type field, and let the user worry about interpreting them. I'm not very excited about that though, mainly because it would be very non-backwards-compatible. I would prefer to maintain the existing columns of pg_locks with the existing definitions as much as possible, and solve our problem by adding columns. So what I'm thinking about is pg_locks having the following columns: locktype text one of "relation", "relation_extend", "page", "tuple","transaction", "object", "user", or "unknown" database oid * the database OID (possibly zero) when relevant to the locktype,else null relation oid * the relation OID when relevant to the locktype, else null page int4 the block number when relevant (PAGE and TUPLE locks), else null offset int2 the tuple offset when relevant (TUPLE locks only), else null transaction xid * the transaction ID when relevant (only for TRANSACTION locks),else null classid oid the class OID when relevant (only for OBJECT locks), else null objid oid the object OID when relevant (only for OBJECT locks), else null objsubid int2 the object sub-id when relevant (only for OBJECT locks), else null pid int4 * mode text * granted bool * same as in current definition The columns marked * are in the current definition, the others are new. This still leaves us with the issue of what to do with user locks. I am inclined to display them as if they were OBJECT locks, ie, fill the database, classid, objid, and objsubid columns. An alternative that would also expose all the info is to display them as if they were TUPLE locks. Or we could add still more columns, but I'm not real enthused about that idea. Note that I'm not thinking of exposing lockmethodid --- that's redundant with locktype in the current system, and this isn't likely to change. Thoughts anyone? regards, tom lane
> In the earlier thread there was talk of separate views for system > and user locks, but on reflection I think that's the wrong approach; > principally because it will be impossible to get exactly-simultaneous > snapshots of the system and user lock states if there are two views > involved. And that's something you tend to want when studying lock > behavior ;-). So I think we have to maintain the current arrangement > of one view, and add enough columns to it to handle all the > requirements. This seems perfectly ok...as long as there is 1:1 correspondence between locktag and lock for all present and future types of locks. I'd like to point out though that when querying for user locks it's kind of nice not to wade through transaction locks, etc. One nice things about the generic types (int4) is that they can be easily casted...if a column is displaying an xid that is not really an xid (user lock block offset), this can be annoying if you want to do some post query processing on the field, like bit shift it back into a 64 bit variable...especially since a dump/restore will drop all casts between two system provided columns. What about having a view with all the generic columns and one specialized view (pg_locks) for backwards compatibility? Merlin
"Merlin Moncure" <merlin.moncure@rcsonline.com> writes: >> So I think we have to maintain the current arrangement >> of one view, and add enough columns to it to handle all the >> requirements. > This seems perfectly ok...as long as there is 1:1 correspondence between > locktag and lock for all present and future types of locks. I'd like to > point out though that when querying for user locks it's kind of nice not > to wade through transaction locks, etc. Well, sure, but that's what "SELECT ... WHERE" is for ;-) > One nice things about the generic types (int4) is that they can be > easily casted...if a column is displaying an xid that is not really an > xid (user lock block offset), this can be annoying if you want to do > some post query processing on the field, like bit shift it back into a > 64 bit variable...especially since a dump/restore will drop all casts > between two system provided columns. The proposal I made was to display all fields of a USER lock as either OID or int2, so you can certainly cast the OIDs to int4 if you want to do some kind of arithmetic on them. regards, tom lane
Tom Lane wrote: > > This seems perfectly ok...as long as there is 1:1 correspondence between > > locktag and lock for all present and future types of locks. I'd like to > > point out though that when querying for user locks it's kind of nice not > > to wade through transaction locks, etc. > > Well, sure, but that's what "SELECT ... WHERE" is for ;-) yeah, I misread your earlier post...being able to filter on lock type (or not) is an ideal solution. So, pg_locks it is. I'd also like to make one more comment: > This still leaves us with the issue of what to do with user locks. > I am inclined to display them as if they were OBJECT locks, ie, fill > the database, classid, objid, and objsubid columns. An alternative > that would also expose all the info is to display them as if they > were TUPLE locks. Or we could add still more columns, but I'm not > real enthused about that idea. I don't like the idea of listing user locks with 'tuple' locks for no other reason than this might confuse what user locks are. Even though they will be used as tuple locks 99% of the time, user locks are only loosely coupled with tuples in part because there is no sytem generated column which is persistent and > 32 bits. IMO, this is a problem with the current user lock module...it encourages locking over oid which is a bad practice. A properly implemented user lock system would likely maintain a global sequence shared by all lockable objects, tuple or otherwise. Listing them as object locks seems ok. Merlin
On Mon, May 02, 2005 at 01:12:06PM -0400, Merlin Moncure wrote: > I don't like the idea of listing user locks with 'tuple' locks for no > other reason than this might confuse what user locks are. Even though > they will be used as tuple locks 99% of the time, user locks are only > loosely coupled with tuples in part because there is no sytem generated > column which is persistent and > 32 bits. IMO, this is a problem with > the current user lock module...it encourages locking over oid which is a > bad practice. Another way would be to allow user locks to use the four fields of LOCKTAG. So the user would be able to establish more powerful conventions: say the relation's Oid, and a related sequence value if there is one; or a blocknumber/offset (ctid) if there isn't, etc. > A properly implemented user lock system would likely > maintain a global sequence shared by all lockable objects, tuple or > otherwise. That'd just be equivalent to require that user tables are created WITH OIDS, only the counter wouldn't be shared with system tables ... how is that any better? -- Alvaro Herrera (<alvherre[@]dcc.uchile.cl>) "Ellos andaban todos desnudos como su madre los parió, y también las mujeres, aunque no vi más que una, harto moza, y todos los que yo vi eran todos mancebos, que ninguno vi de edad de más de XXX años" (Cristóbal Colón)
"Merlin Moncure" <merlin.moncure@rcsonline.com> writes: > I don't like the idea of listing user locks with 'tuple' locks for no > other reason than this might confuse what user locks are. Fair enough, although I think that at least one major application of user locks would be equivalent to tuple locks. Somebody was asking for named user locks in the previous thread, and the easiest way to get that is to make a table containing just lock names, and then lock on the CTIDs of that table. Since there would be no reason to allow UPDATE or DELETE in such a table, the putative instability of CTID doesn't really matter. However, displaying them as object locks certainly works, and you'd have to put some intelligence in front of the view anyway about what meaning you were assigning to user locks in your installation. So you can always cast to whatever you need. > IMO, this is a problem with the current user lock module...it > encourages locking over oid which is a bad practice. A properly > implemented user lock system would likely maintain a global sequence > shared by all lockable objects, tuple or otherwise. Certainly the current contrib/userlock code could stand a rewrite. Or more likely, addition of new functions --- we should deprecate the old ones, but I see no need to remove 'em right away. regards, tom lane
> > A properly implemented user lock system would likely > > maintain a global sequence shared by all lockable objects, tuple or > > otherwise. > > That'd just be equivalent to require that user tables are created WITH > OIDS, only the counter wouldn't be shared with system tables ... how is > that any better? Well, oid is 32 bit and not guaranteed to be unique...therefore useless. However by properly defined, I meant by the application. The server is agnostic about user locks, a.k.a. 'application defined locks'. Merlin
> "Merlin Moncure" <merlin.moncure@rcsonline.com> writes: > Fair enough, although I think that at least one major application of > user locks would be equivalent to tuple locks. Somebody was asking > for named user locks in the previous thread, and the easiest way to > get that is to make a table containing just lock names, and then lock > on the CTIDs of that table. Since there would be no reason to allow > UPDATE or DELETE in such a table, the putative instability of CTID > doesn't really matter. This is fine, but relying on structures outside of shared memory is a fairly hefty price. User locks are very fast and tight and incur zero maintenance overhead...with a table you have to consider vacuuming strategies + possible reindex for the unique constraint...bleh. If the lock table was not synced and auto-vacuumed, then maybe it could work. I also wonder if there would be a race condition if someone tried to acquire ctid based named lock at the same time a user lock with the same value, unless ctid locks were maintained in a separate hash table. Interesting aside: you can get very similar functionality by abusing pg_listener...not that I'd suggest doing that however ;). > Certainly the current contrib/userlock code could stand a rewrite. > Or more likely, addition of new functions --- we should deprecate > the old ones, but I see no need to remove 'em right away. well, the old ones are GPL. I've made a few attempts to contact the original author...he's MIA. Since 95% of the implementation is in the backend, it seems odd to have a GPL interface. Merlin
"Merlin Moncure" <merlin.moncure@rcsonline.com> writes: >> "Merlin Moncure" <merlin.moncure@rcsonline.com> writes: >> Fair enough, although I think that at least one major application of >> user locks would be equivalent to tuple locks. Somebody was asking >> for named user locks in the previous thread, and the easiest way to >> get that is to make a table containing just lock names, and then lock >> on the CTIDs of that table. Since there would be no reason to allow >> UPDATE or DELETE in such a table, the putative instability of CTID >> doesn't really matter. > This is fine, but relying on structures outside of shared memory is a > fairly hefty price. User locks are very fast and tight and incur zero > maintenance overhead...with a table you have to consider vacuuming > strategies + possible reindex for the unique constraint...bleh. What vacuuming strategy? It's a constant table, at least in my view of the usage. I see no reason for the table lookups to be part of the performance critical path, either --- if you're grabbing and releasing a particular lock a lot, you could read the needed CTID and cache it on the application side. In any case, we are certainly *not* expanding LOCKTAG to the point where it can hold random user-defined strings ;-) > I also wonder if there would be a race condition if someone tried to > acquire ctid based named lock at the same time a user lock with the same > value, unless ctid locks were maintained in a separate hash table. This would be a matter of making sure you didn't use conflicting LOCKTAG bit patterns for different purposes. In practice the easiest way to do that would be to add more LockTagType enum values, which is trivial enough now. (I'll probably fix pg_locks so that if it doesn't recognize a particular LockTagType value, it prints the locktag as the numeric value of the tag, rather than falling back to something unhelpful like "unknown". This way you could make some use of freshly-invented tag values without any changes at all in the core backend.) > well, the old ones are GPL. I've made a few attempts to contact the > original author...he's MIA. Since 95% of the implementation is in the > backend, it seems odd to have a GPL interface. I agree. Wasn't it you that was proposing to rewrite the module from scratch to eliminate the GPL restriction? regards, tom lane
> > well, the old ones are GPL. I've made a few attempts to contact the > > original author...he's MIA. Since 95% of the implementation is in the > > backend, it seems odd to have a GPL interface. > > I agree. Wasn't it you that was proposing to rewrite the module from > scratch to eliminate the GPL restriction? > > regards, tom lane Yep. Actually, the biggest part of this was figuring out what to do about the pg_locks view. Since that's basically decided, all that remains is to decide what if anything to do about the max_locks_per_transaction GUC variable. User locks at the very least are extra-transactional so this could perhaps be renamed. This could possibly hinge on how Alvaro's 'spill to disk' scenario plays out. FWIW, I'm a huge fan of the current behavior which is to drop transactions when running out of lock-space. In any event, I'll rewrite the interface and the documentation for user-locking with minimal changes except to expose more of the locktag structure and remove references to the deprecated and conceptually confusing oid. Merlin
FWIW, I've asked previously for a means to name userlocks. The reason for this is that if you're not locking on some kind of object with an OID then you're stuck picking some random value and hoping that no one else using userlock ever picks the same value. If instead there was a means to name userlocks, it's easy to use a name like "My application: some thing I want to block on". Putting the 'My application:' in there pretty much ensures that you won't conflict with anything else, and the randomness of whatever you call what you're locking on should be plenty to handle the rest. On Mon, May 02, 2005 at 01:30:49PM -0400, Tom Lane wrote: > "Merlin Moncure" <merlin.moncure@rcsonline.com> writes: > > I don't like the idea of listing user locks with 'tuple' locks for no > > other reason than this might confuse what user locks are. > > Fair enough, although I think that at least one major application of > user locks would be equivalent to tuple locks. Somebody was asking > for named user locks in the previous thread, and the easiest way to > get that is to make a table containing just lock names, and then lock > on the CTIDs of that table. Since there would be no reason to allow > UPDATE or DELETE in such a table, the putative instability of CTID > doesn't really matter. > > However, displaying them as object locks certainly works, and you'd have > to put some intelligence in front of the view anyway about what meaning > you were assigning to user locks in your installation. So you can > always cast to whatever you need. > > > IMO, this is a problem with the current user lock module...it > > encourages locking over oid which is a bad practice. A properly > > implemented user lock system would likely maintain a global sequence > > shared by all lockable objects, tuple or otherwise. > > Certainly the current contrib/userlock code could stand a rewrite. > Or more likely, addition of new functions --- we should deprecate > the old ones, but I see no need to remove 'em right away. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > -- Jim C. Nasby, Database Consultant decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"
On Mon, May 02, 2005 at 02:12:33PM -0400, Merlin Moncure wrote: > > "Merlin Moncure" <merlin.moncure@rcsonline.com> writes: > > Fair enough, although I think that at least one major application of > > user locks would be equivalent to tuple locks. Somebody was asking > > for named user locks in the previous thread, and the easiest way to > > get that is to make a table containing just lock names, and then lock > > on the CTIDs of that table. Since there would be no reason to allow > > UPDATE or DELETE in such a table, the putative instability of CTID > > doesn't really matter. > > This is fine, but relying on structures outside of shared memory is a > fairly hefty price. User locks are very fast and tight and incur zero > maintenance overhead...with a table you have to consider vacuuming > strategies + possible reindex for the unique constraint...bleh. If the > lock table was not synced and auto-vacuumed, then maybe it could work. > I also wonder if there would be a race condition if someone tried to > acquire ctid based named lock at the same time a user lock with the same > value, unless ctid locks were maintained in a separate hash table. Well, there's nothing that says you have to actually refer to locks by name. When I proposed this what I proposed is that the userlock module provide a dedicated means to map a lock name to a lock number, and reserve one of the 'lock spaces' (the 16 bit number) for this use, just as one of them is currently reserved for locks based on OID. But I also can't think of any reason why lock names need to be persistent, so I imagine you could store a list of lock names in shared memory with no backing storage. -- Jim C. Nasby, Database Consultant decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"
On Mon, May 02, 2005 at 04:34:50PM -0400, Merlin Moncure wrote: > Yep. Actually, the biggest part of this was figuring out what to do > about the pg_locks view. Since that's basically decided, all that > remains is to decide what if anything to do about the > max_locks_per_transaction GUC variable. User locks at the very least > are extra-transactional so this could perhaps be renamed. This could > possibly hinge on how Alvaro's 'spill to disk' scenario plays out. Oh, I don't plan to work on locking issues anymore for now. Basically I wanted spill to disk only to serve the tuple locking, but since we solve that in a different way, I pretty much abandoned that idea. -- Alvaro Herrera (<alvherre[@]dcc.uchile.cl>) "Por suerte hoy explotó el califont porque si no me habría muertode aburrido" (Papelucho)
"Merlin Moncure" <merlin.moncure@rcsonline.com> writes: > Yep. Actually, the biggest part of this was figuring out what to do > about the pg_locks view. Since that's basically decided, all that > remains is to decide what if anything to do about the > max_locks_per_transaction GUC variable. User locks at the very least > are extra-transactional so this could perhaps be renamed. I'm not in favor of renaming the variable unless a really significantly more descriptive name is proposed. I can't think of any short names that are markedly better than max_locks_per_transaction. To me the main shortcoming of that name has nothing to do with user locks: it's that it suggests that we enforce a hard limit on each transaction individually, when in fact we do no such thing (the limit is on the total number of locks in existence, not how many are owned by whom). > FWIW, I'm a huge fan of the current behavior which is to drop > transactions when running out of lock-space. I can't quite tell if that was supposed to have a smiley or not ... regards, tom lane
> On Mon, May 02, 2005 at 02:12:33PM -0400, Merlin Moncure wrote: > Well, there's nothing that says you have to actually refer to locks by > name. When I proposed this what I proposed is that the userlock module > provide a dedicated means to map a lock name to a lock number, and > reserve one of the 'lock spaces' (the 16 bit number) for this use, just > as one of them is currently reserved for locks based on OID. But I also > can't think of any reason why lock names need to be persistent, so I > imagine you could store a list of lock names in shared memory with no > backing storage. Well, actually, as currently implemented the userlock module provides 48 bits of lock space but none of the bits are reserved for anything...interface functions which assign the lower 32 bits to oid are provided as a convenience. IIRC userlocks were first implemented in 1998 when the oid played a larger role, it is now quite rightly deprecated and my intention is to remove it from the userlock module. The new userlocks should be able to take advantage of refinements in the locktag structure and provide a full 64 bits to resolve the lock at the least. 64 bits is the magic number because it now works quite nicely with sequences. Could you be more specific about how a string based user lock system would be implemented? Merlin
On Tue, May 03, 2005 at 10:22:08AM -0400, Merlin Moncure wrote: > > On Mon, May 02, 2005 at 02:12:33PM -0400, Merlin Moncure wrote: > > Well, there's nothing that says you have to actually refer to locks by > > name. When I proposed this what I proposed is that the userlock module > > provide a dedicated means to map a lock name to a lock number, and > > reserve one of the 'lock spaces' (the 16 bit number) for this use, > just > > as one of them is currently reserved for locks based on OID. But I > also > > can't think of any reason why lock names need to be persistent, so I > > imagine you could store a list of lock names in shared memory with no > > backing storage. > > Well, actually, as currently implemented the userlock module provides 48 > bits of lock space but none of the bits are reserved for > anything...interface functions which assign the lower 32 bits to oid are > provided as a convenience. IIRC userlocks were first implemented in If I remember the docs correctly, it specifically mentions that one of the 16 bit values is used for the OID mapping. That value could be 0, I don't know, but the point is that anyone using userlock would naturally stay away from that range. > 1998 when the oid played a larger role, it is now quite rightly > deprecated and my intention is to remove it from the userlock module. I wish you wouldn't since http://rrs.decibel.org uses it. > The new userlocks should be able to take advantage of refinements in the > locktag structure and provide a full 64 bits to resolve the lock at the > least. 64 bits is the magic number because it now works quite nicely > with sequences. Could you be more specific about how a string based > user lock system would be implemented? My thought is to include a lookup table in the module that would correlate text names to lock numbers. For those who don't care about performance, they could just aquire and release locks with a function that accepts a text name. If performance was an issue, they could lookup the lock number/ID for a text name and store that value. I also don't see any reason not to set aside a range of numbers as being intended for general use, and specifying that named locks would never use a number in that range. If you're going to go to 64 bit locks I suggest setting aside the range of 0 to 0x00ff ffff (that is 48 bits, right? :) for backwards compatability, and also carving out a small chunk for use by other defined access methods (such as named locks and OIDs). That way if someone else thinks of an interesting way to refer to locks it can be added without worrying about colliding with locks used by existing software. BTW, the reason I'm so worried about lock number collisions is that the application I use them in is designed to run in an existing PostgreSQL database, which could easily be using userlocks of it's own. I want to do everything possible to ensure I don't conflict with anything else in the cluster (locks are cluster-wide, right?), so I use the OID of the function that aquires the lock. But of course that OID could end up duplicated, so I'd much rather be able to use a named lock which is almost guaranteed to be unique (unless someone else decides 'rrs.decibel.org: update()' makes a great lock name...) -- Jim C. Nasby, Database Consultant decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"
"Jim C. Nasby" <decibel@decibel.org> writes: > On Tue, May 03, 2005 at 10:22:08AM -0400, Merlin Moncure wrote: >> 1998 when the oid played a larger role, it is now quite rightly >> deprecated and my intention is to remove it from the userlock module. > I wish you wouldn't since http://rrs.decibel.org uses it. Don't worry, I'll veto any immediate removal of functionality ;-) The correct way to handle this is to add some better userlock functionality and deprecate what's there. We can remove the crufty stuff in a release or three after it's been officially deprecated ... but there is no reason to remove it immediately. It won't conflict with a better version, just exist alongside. regards, tom lane
On Tue, May 03, 2005 at 11:43:41PM -0400, Tom Lane wrote: > "Jim C. Nasby" <decibel@decibel.org> writes: > > I wish you wouldn't since http://rrs.decibel.org uses it. > > Don't worry, I'll veto any immediate removal of functionality ;-) Yes, but will core (or worse, that Bruce guy) over-ride your veto? ;P > The correct way to handle this is to add some better userlock > functionality and deprecate what's there. We can remove the crufty > stuff in a release or three after it's been officially deprecated > ... but there is no reason to remove it immediately. It won't conflict > with a better version, just exist alongside. Hopefully by then I'll have come up with a reason not to support pre-8.whenever_userlock_is_improved. :) -- Jim C. Nasby, Database Consultant decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"
Tom wrote: > Don't worry, I'll veto any immediate removal of functionality ;-) > The correct way to handle this is to add some better userlock > functionality and deprecate what's there. We can remove the crufty > stuff in a release or three after it's been officially deprecated > ... but there is no reason to remove it immediately. It won't conflict > with a better version, just exist alongside. hm. how about this: leave the userlock contrib module completely alone and call them 'application locks' (what is the 'user' in userlock?). Basic points: 1. tweak sources replacing 'user' with 'application' in various places 2. application locks interface is in core project and properly documented 3. provide 64 bit (or more?) lock space...oid plays no direct role 4. deprecate userlock module but leave it otherwise unchanged. 5. add string based locking to the interface? Merlin