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!
Sun, 15 Nov 1998 13:37:07 -0500 (EST)
Terry Mackintosh <terry@terrym.com>
6.4.1 contrib/spi/
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




Sun, 15 Nov 1998 18:04:10 -0500 (EST)
Steve Frampton <frampton@mail.flarc.edu.on.ca>
Concurrency control questions 6.3.2 vs. 6.4
> 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, Pennsylvania 19026

Sun, 15 Nov 1998 22:13:15 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Concurrency control questions 6.3.2 vs. 6.4
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


Tue, 17 Nov 1998 20:02:14 -0500 (EST)
Steve Frampton <frampton@mail.flarc.edu.on.ca>
Re: [HACKERS] Concurrency control questions 6.3.2 vs. 6.4
> 
> 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  |
+----------------------------------------------------------------------+

Wed, 18 Nov 1998 11:42:36 +0100 (MET)
Massimo Dal Zotto <dz@cs.unitn.it>
Re: [HACKERS] Concurrency control questions 6.3.2 vs. 6.4
> 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, Pennsylvania 19026

Sat, 12 Dec 1998 17:51:37 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] 6.4.1 contrib/spi/
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!


Sat, 12 Dec 1998 19:04:16 -0500 (EST)
Terry Mackintosh <terry@terrym.com>
Re: [HACKERS] 6.4.1 contrib/spi/
> 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

Sat, 12 Dec 1998 19:37:39 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] 6.4.1 contrib/spi/
> 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, Pennsylvania 19026

Sat, 12 Dec 1998 21:30:30 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Concurrency control questions 6.3.2 vs. 6.4
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!
Mon, 14 Dec 1998 00:03:18 -0500 (EST)
Terry Mackintosh <terry@terrym.com>
Re: [HACKERS] 6.4.1 contrib/spi/
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, Pennsylvania 19026

Mon, 14 Dec 1998 00:13:38 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] 6.4.1 contrib/spi/