Message-ID: <01BF400D.C8C88440@garys.ringways.co.uk>
From: Gary Stainburn <gary.stainburn@ringways.co.uk>
To: "'Pgsql Interfaces'" <pgsql-interfaces@postgresql.org>
Subject: locking on database updates
Date: Mon, 6 Dec 1999 17:16:27 -0000
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 8bit

Hi All,

I have a number of tables in my database where the key is an int4 into which I place a unique sequential number.  I have learned from part experience (although not on pgsql) not to use auto-generated numbers as it makes it a nightmare to rebuild corrupted databases.

I then have a parameter table with a single row in it containing the next available number for each table.  There will eventually be approx 140 users on this database accessing from web browsers (apache->perl using CGI.pm and DBI/DBD::Pg) and the main table will be hit quite hard.

My question is this.  Is there a safe way of retrieving the next number from the parameter table in such a way that the same number cannot be retrieved twice

Pseudo Code

Select & lock record
increment number
Update record
Unlock record
.......

-----------------------------------------
Gary Stainburn.
Work: http://www.ringways.co.uk gary.stainburn@ringways.co.uk
REVCOM: http://www.revcom.dhs.org http://www.revcom.org.uk gary.stainburn@revcom.org.uk
-----------------------------------------
The nice thing about standards is that there are so many of them to choose from. -- Andrew S. Tanenbaum
-----------------------------------------