From: marten@feki.toppoint.de
Message-Id: <199912090800.JAA03573@feki.toppoint.de>
Subject: Re: [INTERFACES] locking on database updates
In-Reply-To: <01BF40D0.F4BA79A0@garys.ringways.co.uk> from Gary Stainburn at
	"Dec 7, 1999  4:34:37 pm"
To: gary.stainburn@ringways.co.uk (Gary Stainburn)
Date: Thu, 9 Dec 1999 09:00:09 +0100 (CET)
Cc: pgsql-interfaces@postgresql.org
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit


> 
> The problem is that this does not actually give me the number that
> was just created.  As there is no guarantee that the other fields in
> the table are unique (hence the serial field in the first place) I cannot
> simply do a select to retrieve it.

 When doing development with OO development tools and using wrappers
to handle the oo <-> rdbms wall one has to look at the unique identifier
problem.

 Here again, two possible ways are possible:

 - let the database handle the serial number stuff
 - let the software handle the serial number stuff

 In general it is said not to use special features of databases if they
are ot portable - the software way is very often very portable.

 For our wrapper in Smalltalk/X we decided NOT to use the Postgre support
for serial numbers, but do it on our own.

 Several papers are out in the internet, which tells one how to create
such number, which fits several points:

 - unique number in multiconnection environment
 - minimize the amount of communication between client and database
   while handling this stuff.

 The principle for the high-low algorithm is:

 - the client gets a session id on startup - asking the database for 
   the next valid session number.

 - the unique keys are created on the client side without any further
   interaction between client and database.

 - the unique key is based on a session id and a id within a session.

 - to get it into one column you may put these values together and perhaps
   convert them to a string.

 - if the client exits it writes its session value and the actual 
   id within the session back to the database - for further use by the
   next client.

 If anyone wishes further information about this you may send me an
e-mail.

 Marten