locking on database updates

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(dot)stainburn(at)ringways(dot)co(dot)uk
REVCOM: http://www.revcom.dhs.org http://www.revcom.org.uk gary(dot)stainburn(at)revcom(dot)org(dot)uk
-----------------------------------------
The nice thing about standards is that there are so many of them to choose from. -- Andrew S. Tanenbaum
-----------------------------------------