Обсуждение: 6.4.1 contrib/spi/
Hi all Just a follow up, did that datetime stuff I submitted ever become part of the soon to be 6.4.1 tree? I just never seen any reply one way or the other, and wanted to make sure it did not just fall into a balck hole :-) It seemed to me that it may also be usefull to others. For those who don't know or forgot, it is a function to be called from a triger befor update, and will set the specified datetime field to the current datetime, thus implimenting a modification time stamp. I have re-attached the tgz file just incase it got lost. It should be unpacked in ..../pgsql/contrib/spi/ and the Makefile patched with the supplied patch. Thanks Terry Mackintosh <terry@terrym.com> http://www.terrym.com sysadmin/owner Please! No MIME encoded or HTML mail, unless needed. Proudly powered by R H Linux 4.2, Apache 1.3, PHP 3, PostgreSQL 6.3 ------------------------------------------------------------------- Success Is A Choice ... book by Rick Patino, get it, read it!
Hello: I noticed the locking code in the backend/storage/lmgr directory has had a lot of modifications between 6.3.2 vs. 6.4. I know that Vadim is working on changing the table-level locking scheme of 6.3.2 towards a multi-version concurrency control scheme. I'm wondering how much along these modifications are -- it looks like there were changes made to the existing locking scheme but no additional features were added. This is based on a very cursory look at the locking code in 6.4 (the locking code is a lot more complicated than I had initially thought it was going to be). I'm curious as to how the multi-version scheme will be implemented. Vadim said that Postgres has a non-overwriting storage manager which can be exploited for this concurrency control scheme. I'm not sure I understand him -- values that are updated in a table are written to the database in such a fashion that the old value remains accessible? This is accomplished without a recovery log? Also, there is some user-level locking code in the contrib directory by Massimo that (if I am correct in my understanding of it), seems to be providing row-level locking capabilities through query selects. Is this something that will be added to the Postgresql core at a future date? Thanks in advance for any information you can provide. --------------< LINUX: The choice of a GNU generation. >-------------- Steve Frampton <3srf@qlink.queensu.ca> http://qlink.queensu.ca/~3srf
> Hello: > > I noticed the locking code in the backend/storage/lmgr directory has had a > lot of modifications between 6.3.2 vs. 6.4. I know that Vadim is working > on changing the table-level locking scheme of 6.3.2 towards a > multi-version concurrency control scheme. I'm wondering how much along > these modifications are -- it looks like there were changes made to the > existing locking scheme but no additional features were added. This is > based on a very cursory look at the locking code in 6.4 (the locking code > is a lot more complicated than I had initially thought it was going to > be). That may have been me. I renamed a lot of the structures at one point, because they were so misnamed as to add to the confusion. No real code changed in that pass, though we have made incremental improvements to the code in this release. I don't think Vadim has started making changes for LLL there yet, but he can tell us. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Hi everyone: Thanks for the helpful responses. Are you folks getting sick of me yet? I'm hoping somebody could help me understand a bit better the way locking protocols are used in PostGreSQL 6.4, including how a query is parsed, executed, etc. I understand that there are two locks available: one for reads and one for writes. They are called by RelationSetLockForRead() and RelationSetLockForWrite(), respectively, which are both implemented in backend/storage/lmgr.c. These functions are called by the query parser, trigger handler, and indexing subsystem. The query parser is responsible for parsing a given expression in backend/parser/parse_expr.c and actually grabbing tuples in backend/parser/parse_func.c which are passed as a heap array to the backend which in turn passes the information to the client. Am I still okay? I'm interested in the locking protocols as used for query processing so I guess I can ignore the trigger and indexing for now. Locking is not accomplished with calls to the operating system but instead is managed by the locking manager through a lock hash table which lives in shared memory. The table contains information on locks such as the type of lock (read/write), number of locks currently held, an array of bitmasks showing lock conflicts, and lock priority level (used to prevent starvation). In addition, each relation has its own data structure which includes some locking information. Here's where things get fuzzy -- there's a lot of code here so please be patient with me if I really screwed up in my interpretation. :-) When the RelationSetLockFor...() function is called, it ensures that the relation and lock information for the relation are both valid. It then calls MultiLockReln() with a pointer to the relation's lock information and the appropriate lock type. MultiLockReln() initializes a lock tag which is passed to MultiAcquire(). I'm a little vague on MultiAcquire(). It seems to search through the lock hash table to see if a lock should be allowed? And if so it calls LockAcquire(). But LockAcquire() itself checks for conflicts, sleeps if one exists, or sets the appropriate lock, adding it to the lock table. So I'm a bit confused here... Unlocks are accomplished in much the same fashion. RelationUnsetLockFor...() is called which in turn calls MultiRelease() which searches the lock table using the same algorithm as in MultiAcquire(). MultiRelease() calls LockRelease() which performs two functions. First, it removes the lock information from the lock table. Second, this function will awaken any transaction which had blocked waiting for the same lock. This is done here because if it was not, a new process could come along and request the lock causing a race condition. So...did I even come *close* to understanding this behemoth? -_-; Corrections would be appreciated. Sorry again to be such a pain. --------------< LINUX: The choice of a GNU generation. >-------------- Steve Frampton <3srf@qlink.queensu.ca> http://qlink.queensu.ca/~3srf
> > Hello: > > I noticed the locking code in the backend/storage/lmgr directory has had a > lot of modifications between 6.3.2 vs. 6.4. I know that Vadim is working > on changing the table-level locking scheme of 6.3.2 towards a > multi-version concurrency control scheme. I'm wondering how much along > these modifications are -- it looks like there were changes made to the > existing locking scheme but no additional features were added. This is > based on a very cursory look at the locking code in 6.4 (the locking code > is a lot more complicated than I had initially thought it was going to > be). > > I'm curious as to how the multi-version scheme will be implemented. Vadim > said that Postgres has a non-overwriting storage manager which can be > exploited for this concurrency control scheme. I'm not sure I understand > him -- values that are updated in a table are written to the database in > such a fashion that the old value remains accessible? This is > accomplished without a recovery log? > > Also, there is some user-level locking code in the contrib directory by > Massimo that (if I am correct in my understanding of it), seems to be > providing row-level locking capabilities through query selects. Is this > something that will be added to the Postgresql core at a future date? No, this isn't row-level locking, it is a non-blocking mechanism which can be used by applications to signal that some entities should not be modified by other users because they are user-locked by one application instance. This is totally transparent and orthogonal with respect to standard locks. -- Massimo Dal Zotto +----------------------------------------------------------------------+ | Massimo Dal Zotto email: dz@cs.unitn.it | | Via Marconi, 141 phone: ++39-0461534251 | | 38057 Pergine Valsugana (TN) www: http://www.cs.unitn.it/~dz/ | | Italy pgp: finger dz@tango.cs.unitn.it | +----------------------------------------------------------------------+
> Hi all > > Just a follow up, did that datetime stuff I submitted ever become part of > the soon to be 6.4.1 tree? > > I just never seen any reply one way or the other, and wanted to make sure > it did not just fall into a balck hole :-) > > It seemed to me that it may also be usefull to others. For those who > don't know or forgot, it is a function to be called from a triger befor > update, and will set the specified datetime field to the current datetime, > thus implimenting a modification time stamp. > > I have re-attached the tgz file just incase it got lost. > It should be unpacked in ..../pgsql/contrib/spi/ and the Makefile patched > with the supplied patch. Done. But in 6.5 tree, not 6.4.1. You mentioned it has not been completely tested, so only in 6.5. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Hi Bruce and all On Sat, 12 Dec 1998, Bruce Momjian wrote: > > Just a follow up, did that datetime stuff I submitted ever become part of > > the soon to be 6.4.1 tree? > > Done. But in 6.5 tree, not 6.4.1. You mentioned it has not been > completely tested, so only in 6.5. > Thats fine, thanks. I have now tested it more, all seems very well, not even one glitch so far. Maybe I finally got some thing right ?-) I did notice later that I forgot to add a section to the README file, if you would like I can do that and submit a patch for the README file? Have a great night Terry Mackintosh <terry@terrym.com> http://www.terrym.com sysadmin/owner Please! No MIME encoded or HTML mail, unless needed. Proudly powered by R H Linux 4.2, Apache 1.3, PHP 3, PostgreSQL 6.4 ------------------------------------------------------------------- Success Is A Choice ... book by Rick Patino, get it, read it!
> Hi Bruce and all > > On Sat, 12 Dec 1998, Bruce Momjian wrote: > > > > Just a follow up, did that datetime stuff I submitted ever become part of > > > the soon to be 6.4.1 tree? > > > > Done. But in 6.5 tree, not 6.4.1. You mentioned it has not been > > completely tested, so only in 6.5. > > > > Thats fine, thanks. I have now tested it more, all seems very well, not > even one glitch so far. Maybe I finally got some thing right ?-) > > I did notice later that I forgot to add a section to the README file, if > you would like I can do that and submit a patch for the README file? > Sure. Sounds good. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
> Hi everyone: > > Thanks for the helpful responses. Are you folks getting sick of me yet? > I'm hoping somebody could help me understand a bit better the way locking > protocols are used in PostGreSQL 6.4, including how a query is parsed, > executed, etc. > > I understand that there are two locks available: one for reads and one for > writes. They are called by RelationSetLockForRead() and > RelationSetLockForWrite(), respectively, which are both implemented in > backend/storage/lmgr.c. > > These functions are called by the query parser, trigger handler, and > indexing subsystem. The query parser is responsible for parsing a given > expression in backend/parser/parse_expr.c and actually grabbing tuples in > backend/parser/parse_func.c which are passed as a heap array to the > backend which in turn passes the information to the client. Am I still > okay? > > I'm interested in the locking protocols as used for query processing > so I guess I can ignore the trigger and indexing for now. > > Locking is not accomplished with calls to the operating system but instead > is managed by the locking manager through a lock hash table which lives in > shared memory. The table contains information on locks such as the type of > lock (read/write), number of locks currently held, an array of bitmasks > showing lock conflicts, and lock priority level (used to prevent > starvation). In addition, each relation has its own data structure which > includes some locking information. > > Here's where things get fuzzy -- there's a lot of code here so please be > patient with me if I really screwed up in my interpretation. :-) > > When the RelationSetLockFor...() function is called, it ensures that the > relation and lock information for the relation are both valid. It then > calls MultiLockReln() with a pointer to the relation's lock information > and the appropriate lock type. MultiLockReln() initializes a lock tag > which is passed to MultiAcquire(). > > I'm a little vague on MultiAcquire(). It seems to search through the > lock hash table to see if a lock should be allowed? And if so it calls > LockAcquire(). But LockAcquire() itself checks for conflicts, sleeps if > one exists, or sets the appropriate lock, adding it to the lock table. So > I'm a bit confused here... > > Unlocks are accomplished in much the same fashion. > RelationUnsetLockFor...() is called which in turn calls MultiRelease() > which searches the lock table using the same algorithm as in > MultiAcquire(). MultiRelease() calls LockRelease() which performs two > functions. First, it removes the lock information from the lock table. > Second, this function will awaken any transaction which had blocked > waiting for the same lock. This is done here because if it was not, a new > process could come along and request the lock causing a race condition. Sounds pretty close. I assume you have studied the backend flowcart on the web support page and in src/tools/backend? -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Hi Bruce and all OK, here is a diff for the README file in /usr/src/pgsql/contrib/spi/. For the 6.5 tree. Have a great night. Terry On Sat, 12 Dec 1998, Bruce Momjian wrote: > > Hi Bruce and all > > > > On Sat, 12 Dec 1998, Bruce Momjian wrote: > > > > > > Just a follow up, did that datetime stuff I submitted ever become part of > > > > the soon to be 6.4.1 tree? > > > > > > Done. But in 6.5 tree, not 6.4.1. You mentioned it has not been > > > completely tested, so only in 6.5. > > > > > > > Thats fine, thanks. I have now tested it more, all seems very well, not > > even one glitch so far. Maybe I finally got some thing right ?-) > > > > I did notice later that I forgot to add a section to the README file, if > > you would like I can do that and submit a patch for the README file? > > > > Sure. Sounds good. > > > -- > Bruce Momjian | http://www.op.net/~candle > maillist@candle.pha.pa.us | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup. | Drexel Hill, Pennsylvania 19026 > Terry Mackintosh <terry@terrym.com> http://www.terrym.com sysadmin/owner Please! No MIME encoded or HTML mail, unless needed. Proudly powered by R H Linux 4.2, Apache 1.3, PHP 3, PostgreSQL 6.4 ------------------------------------------------------------------- Success Is A Choice ... book by Rick Patino, get it, read it! *** README Fri Oct 17 05:55:29 1997 --- README Sun Dec 13 23:31:35 1998 *************** *** 135,137 **** --- 135,149 ---- To CREATE FUNCTION use insert_username.sql (will be made by gmake from insert_username.source). + + + 5. moddatetime.c - function for maintaining a modification datetime stamp. + + You have to create a BEFORE UPDATE trigger using the function moddatetime(). + One argument must be given, that is the name of the field that is of type + datetime that is to be used as the modification time stamp. + + There is an example in moddatetime.example. + + To CREATE FUNCTION use moddatetime.sql ( will be made by gmake from + moddatetime.source).
Applied. > Hi Bruce and all > > OK, here is a diff for the README file in /usr/src/pgsql/contrib/spi/. > For the 6.5 tree. > > Have a great night. > Terry > On Sat, 12 Dec 1998, Bruce Momjian wrote: > > > > Hi Bruce and all > > > > > > On Sat, 12 Dec 1998, Bruce Momjian wrote: > > > > > > > > Just a follow up, did that datetime stuff I submitted ever become part of > > > > > the soon to be 6.4.1 tree? > > > > > > > > Done. But in 6.5 tree, not 6.4.1. You mentioned it has not been > > > > completely tested, so only in 6.5. > > > > > > > > > > Thats fine, thanks. I have now tested it more, all seems very well, not > > > even one glitch so far. Maybe I finally got some thing right ?-) > > > > > > I did notice later that I forgot to add a section to the README file, if > > > you would like I can do that and submit a patch for the README file? > > > > > > > Sure. Sounds good. > > > > > > -- > > Bruce Momjian | http://www.op.net/~candle > > maillist@candle.pha.pa.us | (610) 853-3000 > > + If your life is a hard drive, | 830 Blythe Avenue > > + Christ can be your backup. | Drexel Hill, Pennsylvania 19026 > > > > Terry Mackintosh <terry@terrym.com> http://www.terrym.com > sysadmin/owner Please! No MIME encoded or HTML mail, unless needed. > > Proudly powered by R H Linux 4.2, Apache 1.3, PHP 3, PostgreSQL 6.4 > ------------------------------------------------------------------- > Success Is A Choice ... book by Rick Patino, get it, read it! Content-Description: > *** README Fri Oct 17 05:55:29 1997 > --- README Sun Dec 13 23:31:35 1998 > *************** > *** 135,137 **** > --- 135,149 ---- > > To CREATE FUNCTION use insert_username.sql (will be made by gmake from > insert_username.source). > + > + > + 5. moddatetime.c - function for maintaining a modification datetime stamp. > + > + You have to create a BEFORE UPDATE trigger using the function moddatetime(). > + One argument must be given, that is the name of the field that is of type > + datetime that is to be used as the modification time stamp. > + > + There is an example in moddatetime.example. > + > + To CREATE FUNCTION use moddatetime.sql ( will be made by gmake from > + moddatetime.source). > -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026