Re: [HACKERS] userlock changes for 8.1/8.2
[ yawn... ] Create a table with a name column, put some rows in it, lock the rows. What would guarantee that the OIDs of those rows don't conflict with some other OIDs in the system? BTW, this becomes a real issue if you're trying to write code that is meant to be incorporated into other PostgreSQL applications, which might also be using user_lock. Having a text-based means to identify locks greatly reduces the odds of conflicting with a userlock being used by an existing application. I prefer sequences to OIDs in virtually every aspect, including this one. However, it would be nice to have system generated unique tuple identifier. There isn't one currently that would fit in the userlock restriction of 48 bits. Any identifier derived from system columns probably would take 96-128 bits (for example ctid/tableoid combo). IMO, the current behavior is ok, meaning I don't necessarily feel that there should be a candidate system key to use for them. However I would discourage the use of OIDs with them. Merlin ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] userlock changes for 8.1/8.2
Merlin Moncure [EMAIL PROTECTED] writes: However, it would be nice to have system generated unique tuple identifier. There isn't one currently that would fit in the userlock restriction of 48 bits. Sure there is: the ctid of a row in an agreed-on table works fine. The reason it's system-wide unique is that user_locks.c forcibly includes your database OID in the lock tag. It would be reasonable to allow user control of the lock's relId field and maybe even dbId field, but that just takes an expansion of the API for user_locks.c. There's no need to put overhead on the rest of Postgres for this. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] userlock changes for 8.1/8.2
Tom Lane wrote: Merlin Moncure [EMAIL PROTECTED] writes: However, it would be nice to have system generated unique tuple identifier. There isn't one currently that would fit in the userlock restriction of 48 bits. Sure there is: the ctid of a row in an agreed-on table works fine. The reason it's system-wide unique is that user_locks.c forcibly includes your database OID in the lock tag. It would be reasonable to allow user control of the lock's relId field and maybe even dbId field, but that just takes an expansion of the API for user_locks.c. There's no need to put overhead on the rest of Postgres for this. Ok, you answered my next question. Part of my confusion here is the comments in front of LockAcquire() which explains how userlocks are supposed to be mapped to the lock tag. In the case of userlocks, the locktag is basically a hash key, right? so we can recover the other fields and have enough room to play with to generate a 'real' identifier, but... ctid is useless for user locks because an update from a non-cooperating client will change it unless the locks become non-cooperative, and now we are talking about classic row level locks...not worth considering IMO. IOW, a 'user lock' candidate identifier generated by the database must be both unique and persistent for the lifetime of the lock. Without using ctid (and don't want to use oid), there is nothing that can be pulled from the tuple that persistently identifies it until the oid is extended to 64 bits or beyond which doesn't seem likely at this point, so we are back to the classic approach of using an application managed sequence. So the only changes to the current sources in the core project outside of documentation are: 1. update comments to LockAcuire() in lock.c 2. (proposing) new system type that covers the maximum bitspace allowed inside locktag structure, and add a union here to reduce confusion (encompassing offsetnum but not lockmethodid). 3. pg_lock_status(): I prefer to alter this function to simply return the follwing: lockdatum locktype -- bits of locktag structure except for type pid integer locktypeinteger granted boolean 'lock type' mentioned above as a single datum per lock. This can be parsed at the view stage for different types of locks. So, select * from pg_locks --pulls from pg_lock_status filtering on default lock method select * from pg_user_locks --pulls from pg_lock_status filtering on user lock method IMO, this is a good setup because it clearly defines the different types. This will become even more important if and when more lock types get added to this system...shared locks for example. To make this work, though at the very least a built in cast for int-xid would have to be added, since there are no casts to or from xid currently. If doesn't fly, of course an alternative is to just add more functions to lockfuncs.c for various locktypes (as well as new lock views). And finally, 4. a new function in lock.c allowing a lock owned by another process to be killed. Merlin ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] userlock changes for 8.1/8.2
On Tue, Jan 25, 2005 at 01:51:27PM -0500, Merlin Moncure wrote: Merlin, 2. (proposing) new system type that covers the maximum bitspace allowed inside locktag structure, and add a union here to reduce confusion (encompassing offsetnum but not lockmethodid). Please search this message in the archives: To: Alvaro Herrera [EMAIL PROTECTED] Cc: Patches pgsql-patches@postgresql.org Subject: Re: [PATCHES] LockObject patch Date: Mon, 20 Dec 2004 19:22:30 -0500 From: Tom Lane [EMAIL PROTECTED] -- Alvaro Herrera ([EMAIL PROTECTED]) Oh, great altar of passive entertainment, bestow upon me thy discordant images at such speed as to render linear thought impossible (Calvin a la TV) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] userlock changes for 8.1/8.2
Merlin Moncure [EMAIL PROTECTED] writes: Ok, you answered my next question. Part of my confusion here is the comments in front of LockAcquire() which explains how userlocks are supposed to be mapped to the lock tag. In the case of userlocks, the locktag is basically a hash key, right? Those are just comments ;-). You have a hash tag and you can do anything you like with it ... except make it bigger ... the lockmethod field is sufficient to keep it from colliding with any system-generated tags. ctid is useless for user locks because an update from a non-cooperating client will change it unless the locks become non-cooperative, [ shrug... ] Since userlocks are only advisory, a non-cooperating client can break anything in sight anyway. I don't find the above argument convincing. But in any case, you can use an OID or serial sequence identifier if you prefer that to CTID. They're just integers and it's really up to the user application to define the interpretation of a userlock tag. IOW, a 'user lock' candidate identifier generated by the database must be both unique and persistent for the lifetime of the lock. See above. The database isn't defining anything here. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] userlock changes for 8.1/8.2
Tgl wrote: [ shrug... ] Since userlocks are only advisory, a non-cooperating client can break anything in sight anyway. I don't find the above argument convincing. But in any case, you can use an OID or serial sequence identifier if you prefer that to CTID. They're just integers and it's really up to the user application to define the interpretation of a userlock tag. Right. My point is that use of CTIDs just too easy to screw up from the user's perspective, because they change with updates. I was briefly toying with a 'auto lock' mode which built the lock from ctid + tableoid. So, I'd suggest discouraging the use of ctid, just like the use of OIDs is discouraged (in fact there is already a disclaimer about using ctid as a logical identifier in the docs). Certainly we can't provide a tighter integration between the user locks and the system columns via extensions to the grammar, etc. So it becomes a documentation issue. Merlin ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] userlock changes for 8.1/8.2
On Tue, Jan 25, 2005 at 08:19:05AM -0500, Merlin Moncure wrote: [ yawn... ] Create a table with a name column, put some rows in it, lock the rows. What would guarantee that the OIDs of those rows don't conflict with some other OIDs in the system? BTW, this becomes a real issue if you're trying to write code that is meant to be incorporated into other PostgreSQL applications, which might also be using user_lock. Having a text-based means to identify locks greatly reduces the odds of conflicting with a userlock being used by an existing application. I prefer sequences to OIDs in virtually every aspect, including this one. However, it would be nice to have system generated unique tuple identifier. There isn't one currently that would fit in the userlock restriction of 48 bits. Any identifier derived from system columns probably would take 96-128 bits (for example ctid/tableoid combo). IMO, the current behavior is ok, meaning I don't necessarily feel that there should be a candidate system key to use for them. However I would discourage the use of OIDs with them. I believe if there was a way to specify in userlock what table you were working with that the current 48 bit lock size should be OK for almost any situation. With the current scheme you could have locks on 65k tables, any of which could have up to 2^32 rows (has anyone actually ever exceeded 2^32 rows?). Granted, it's not perfect, but it's better than what we have now. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] 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? ---(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] userlock changes for 8.1/8.2
Merlin Moncure [EMAIL PROTECTED] writes: Is it possible for one backend (with superuser privs) to release a lock held by anotether? As of 8.0 this is not possible for regular locks, because there'd be no way to update the other backend's internal data structure that shows what locks it holds. I think though that there is no corresponding structure for userlocks and so in principle it could be done for userlocks. Fair enough. Doesn't seem right to do this for userlocks and not other locks. Whether it's a good idea is a whole 'nother question. It strikes me as a foot-gun with no evident redeeming social value. In particular, there would most likely be some state inside the client app showing that it holds this userlock, and so the inability-to-update-state problem comes right back at that level. I'll take you up on this one: Firstly this is application defined, so who knows what the app is doing. Now for the more selfish reasons :-) I'm using the user locks to provide ISAM style pessimistic locks for postgresql...and they work stupendously good for this, better than any other commercial isam-rdbms implemention that I've seen...they generally rely on lock tables (yuck) and/or triggers (double yuck) to provide equivalent functionality. Anyways, a lot of these ISAM vendors (like AcuCorp AcuCOBOL for example) provide service monitoring tools that allow you to disconnect a user or his locks as an administrative function. Since users have been know to do silly things lock a record and then go to lunch, COBOL admins like the power of being able to relieve them of their locks without having to resort to killing their connection. So, from the ISAM point of view, it's an attractive feature for pg. I make no claims to merit on technical grounds however. Merlin ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] userlock changes for 8.1/8.2
Alvaro wrote: Please search this message in the archives: right. heh. Well, moving on... tgl wrote: Since subids and offnums are only 16 bits, we could pack all of these cases into 64 bits with a 16-bit type identifier to distinguish the cases. That would mean that LOCKTAG doesn't get any bigger than it is now, and we'd have plenty of room for expansion still with more types. Ok, this makes perfect sense, kind of what I was saying only better. The only thing I can add to it at this point is to reorganize the lock view(s) correspondingly...should be 1 view for each specific lock type plus 1 generic one for all locks. A new datum for the generic lock type (plus some casts) might be worth considering. Is it possible for one backend (with superuser privs) to release a lock held by anotether? Merlin ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] userlock changes for 8.1/8.2
Merlin Moncure [EMAIL PROTECTED] writes: Is it possible for one backend (with superuser privs) to release a lock held by anotether? As of 8.0 this is not possible for regular locks, because there'd be no way to update the other backend's internal data structure that shows what locks it holds. I think though that there is no corresponding structure for userlocks and so in principle it could be done for userlocks. Whether it's a good idea is a whole 'nother question. It strikes me as a foot-gun with no evident redeeming social value. In particular, there would most likely be some state inside the client app showing that it holds this userlock, and so the inability-to-update-state problem comes right back at that level. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] userlock changes for 8.1/8.2
Speaking of other tricks and things missing; I'd like to see support for named locks. If you're using locks for something other than row-level locking, it's awkward at best to have to come up with an OID to identify your lock with, and even that doesn't guarantee uniqueness. You're also out of luck if the table you're doing row level locking on doesn't have OIDs. You also can't do direct OID locking on more than one table in an application. I think two different naming facilities would be of use: 1) A namespace for single locks, where each lock has it's own name. 2) A namespace for a set of locks. Another way to look at this would be a namespace for group IDs. I realize that this conflicts with the existing userlock implementation, so it might be necessary to have a second set of locks to support this. On Mon, Jan 24, 2005 at 01:31:34PM -0500, Merlin Moncure wrote: Ok, With 8.0 out the door, I'd like to reopen discussion re: userlock contrib. module and propose to get it moved into the core database sources. This was discussed a bit a few months back but it never made it officially to the todo list. I'm not sure what the final consensus was on the shared locks discussion, but it might be a good idea to consider user locks at the same time (and vise versa), since they might invoke related changes to the documentation, etc. At the very least, I'd like to know that if properly documented, userlocks stand a chance of being upgraded to the core project. First, a little about the current userlock module: 1. It is GPL. However, the module is nothing more than a couple of wrappers to the backend functions LockAcquire, LockRelease, etc. and some documentation. I'm suggesting to recode the wrappers and redo the documentation as well in the BSD license. 2. userlocks provide a very powerful and high performance method of row level locking. With a little bit of clever coding, they can do other tricks... 3. The current system view, pg_locks, does not display enough information about user locks...I'd suggest either expanding the current view and/or adding a new view, pg_user_locks (along with new function in lockfuncs.c) 4. Current user locks implementation is missing (at least 2) important features...1. ability of superuser to kill a lock owned by another user (corollary: does lockrelease support this?), and 2. the ability to deal with the full 48 bit lock as a single datum (new system type?). Would also be nice to have a lockmode that waits for a lock for a period of time. 5. Need lots of documentation changes...would like to beef up section covering lock module, plus better description of runtime setting 'max_locks_per_transation' including a possible rename. Merlin ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] 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? ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] userlock changes for 8.1/8.2
Jim C. Nasby [EMAIL PROTECTED] writes: Speaking of other tricks and things missing; I'd like to see support for named locks. [ yawn... ] Create a table with a name column, put some rows in it, lock the rows. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] userlock changes for 8.1/8.2
On Mon, Jan 24, 2005 at 10:43:40PM -0500, Tom Lane wrote: Jim C. Nasby [EMAIL PROTECTED] writes: Speaking of other tricks and things missing; I'd like to see support for named locks. [ yawn... ] Create a table with a name column, put some rows in it, lock the rows. What would guarantee that the OIDs of those rows don't conflict with some other OIDs in the system? BTW, this becomes a real issue if you're trying to write code that is meant to be incorporated into other PostgreSQL applications, which might also be using user_lock. Having a text-based means to identify locks greatly reduces the odds of conflicting with a userlock being used by an existing application. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] 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? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
