Thread: 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 havelearned from part experience (although not on pgsql) not to use auto-generated numbers as it makes it a nightmare torebuild corrupted databases. I then have a parameter table with a single row in it containing the next available number for each table. There will eventuallybe approx 140 users on this database accessing from web browsers (apache->perl using CGI.pm and DBI/DBD::Pg) andthe 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 samenumber 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 -----------------------------------------
On Mon, 6 Dec 1999, Gary Stainburn wrote:
> Pseudo Code
>
> Select & lock record
> increment number
> Update record
> Unlock record
What I learned from writing databases in Paradox/DOS is to change the
sequence so that steps 2 and 3 are switched. That is, wait until the record
is committed to be added, then grab the next number and assign it.
The advantage is that if the record is not added (user changed her mind or
had to go do something else right away), the ID table is untouched. Also,
assigning the unique number just before writing the record means that the ID
table is locked, incremented and unlocked very quickly. No one is slowed
down by this process.
HTH,
Rich
Dr. Richard B. Shepard, President
Applied Ecosystem Services, Inc. (TM) Making environmentally-responsible mining
happen.(SM) -------------------------------- 2404 SW 22nd Street | Troutdale,
OR97060-1247 | U.S.A.+ 1 503-667-4517 (voice) | + 1 503-667-8863 (fax) | rshepard@appl-ecosys.com
Gary Stainburn <gary.stainburn@ringways.co.uk> writes:
> 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.
Use a SEQUENCE object for each "parameter table". This gives you
a well-tested, efficient generator mechanism. It may be "auto
generated" but you do have the option of resetting the sequence's
nextval and so forth, so I don't see that there is a good reason
to build a SEQUENCE substitute by hand.
regards, tom lane
Tom Lane wrote: > Gary Stainburn <gary.stainburn@ringways.co.uk> writes: > > 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. > > Use a SEQUENCE object for each "parameter table". This gives you > a well-tested, efficient generator mechanism. It may be "auto > generated" but you do have the option of resetting the sequence's > nextval and so forth, so I don't see that there is a good reason > to build a SEQUENCE substitute by hand. > > regards, tom lane > > ************ Can you give an example of sql that creates a table with that?
Joseph Shraibman <jks@p1.selectacast.net> writes:
> Tom Lane wrote:
> > Gary Stainburn <gary.stainburn@ringways.co.uk> writes:
> > > 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.
> >
> > Use a SEQUENCE object for each "parameter table". This gives you
> > a well-tested, efficient generator mechanism. It may be "auto
> > generated" but you do have the option of resetting the sequence's
> > nextval and so forth, so I don't see that there is a good reason
> > to build a SEQUENCE substitute by hand.
>
> Can you give an example of sql that creates a table with that?
I created my sequence using code like:
CREATE SEQUENCE name_map_seq START 1
and then used it as the default in another table:
CREATE TABLE name_map (
id INT DEFAULT nextval('name_map_seq'),
name TEXT,
info TEXT
)
I also added a unique index to avoid possible mistakes:
CREATE UNIQUE INDEX name_map_unq ON name_map (id)
If I just insert into the name_map table without supplying any
id:
INSERT INTO name_map (name, info) VALUES ('some name', 'some info')
then I get the next sequence number filled in for the id attribute.
On the other hand, if I need to rebuild a table using the same id
values as before, I can simply provide a value explicitly, and then
the default is ignored:
INSERT INTO name_map (id, name, info) VALUES (24, 'name', 'info')
If I need to find out what value my sequence is up to, I can extract
it using:
SELECT last_value FROM name_map_seq
Finally, if I need to prime the sequence when reconstructing tables,
I use:
SELECT setval('name_map_seq', 24)
(looks weird modifying a table with a SELECT, but it works!).
Hope this helps. Criticism welcome; I make no claim to be an expert
on either PostgreSQL or SQL in general, I just wanted to contribute
something by way of thanks for all the assistance I have received
from this list!
Doug.
Douglas Thomson wrote:
> Joseph Shraibman <jks@p1.selectacast.net> writes:
> > Tom Lane wrote:
> > > Gary Stainburn <gary.stainburn@ringways.co.uk> writes:
> > > > 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.
> > >
> > > Use a SEQUENCE object for each "parameter table". This gives you
> > > a well-tested, efficient generator mechanism. It may be "auto
> > > generated" but you do have the option of resetting the sequence's
> > > nextval and so forth, so I don't see that there is a good reason
> > > to build a SEQUENCE substitute by hand.
> >
> > Can you give an example of sql that creates a table with that?
>
> I created my sequence using code like:
> CREATE SEQUENCE name_map_seq START 1
> and then used it as the default in another table:
> CREATE TABLE name_map (
> id INT DEFAULT nextval('name_map_seq'),
> name TEXT,
> info TEXT
> )
> I also added a unique index to avoid possible mistakes:
> CREATE UNIQUE INDEX name_map_unq ON name_map (id)
>
> If I just insert into the name_map table without supplying any
> id:
> INSERT INTO name_map (name, info) VALUES ('some name', 'some info')
> then I get the next sequence number filled in for the id attribute.
>
> On the other hand, if I need to rebuild a table using the same id
> values as before, I can simply provide a value explicitly, and then
> the default is ignored:
> INSERT INTO name_map (id, name, info) VALUES (24, 'name', 'info')
>
> If I need to find out what value my sequence is up to, I can extract
> it using:
> SELECT last_value FROM name_map_seq
>
> Finally, if I need to prime the sequence when reconstructing tables,
> I use:
> SELECT setval('name_map_seq', 24)
> (looks weird modifying a table with a SELECT, but it works!).
>
> Hope this helps. Criticism welcome; I make no claim to be an expert
> on either PostgreSQL or SQL in general, I just wanted to contribute
> something by way of thanks for all the assistance I have received
> from this list!
>
> Doug.
Thanks. I just wish the postgres documentation were a little better. Does
anyone know of a SQL refrence on the web *anywhere*? I've just started
programming in sql, and I can't even find one in a big Barnes & Noble.
Douglas Thomson <dougt@mugc.cc.monash.edu.au> writes:
> Joseph Shraibman <jks@p1.selectacast.net> writes:
>> Can you give an example of sql that creates a table with that?
A couple footnotes on Doug's fine example:
> I created my sequence using code like:
> CREATE SEQUENCE name_map_seq START 1
> and then used it as the default in another table:
> CREATE TABLE name_map (
> id INT DEFAULT nextval('name_map_seq'),
> name TEXT,
> info TEXT
> )
> I also added a unique index to avoid possible mistakes:
> CREATE UNIQUE INDEX name_map_unq ON name_map (id)
Declaring a column as "SERIAL" is a handy shortcut for exactly these
declarations: a sequence, a default value of nextval('sequence'), and
a unique index on the column. (Plus a NOT NULL constraint, which you
might perhaps not want.) You can reach in and inspect/modify the
sequence object for a SERIAL column just as if you'd made the sequence
by hand.
> On the other hand, if I need to rebuild a table using the same id
> values as before, I can simply provide a value explicitly, and then
> the default is ignored:
> INSERT INTO name_map (id, name, info) VALUES (24, 'name', 'info')
Right. Dumping and restoring the table with COPY commands works the
same way. In fact, if you dump the database with pg_dump, you'll find
that the resulting script not only restores all the values of the "id"
column via COPY, but also recreates the current state of the sequence
object.
regards, tom lane
On Mon, 6 Dec 1999, Joseph Shraibman wrote:
> Thanks. I just wish the postgres documentation were a little better. Does
> anyone know of a SQL refrence on the web *anywhere*? I've just started
> programming in sql, and I can't even find one in a big Barnes & Noble.
Joe,
I recommend Joe Celko's book, "Instant SQL Programming", published by Wrox
Press.
Joe is a SQL expert who wrote (still writes?) columns for the database
magazines. He writes clearly and comprehensively.
Highly recommended.
Rich
Dr. Richard B. Shepard, President
Applied Ecosystem Services, Inc. (TM) Making environmentally-responsible mining
happen.(SM) -------------------------------- 2404 SW 22nd Street | Troutdale,
OR97060-1247 | U.S.A.+ 1 503-667-4517 (voice) | + 1 503-667-8863 (fax) | rshepard@appl-ecosys.com
> Thanks. I just wish the postgres documentation were a little better.
Um, if you were a bit more specific that would be helpful ;)
The topic you have asked about is in the docs somewhere; now that you
know the solution perhaps you can go back and suggest where in the
docs this should appear to make it clearer. Without looking, I'd
predict you will find something about the SERIAL type in the User's
Guide in the chapter on data types...
Good luck.
- Thomas
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California
Thomas Lockhart wrote: > > Thanks. I just wish the postgres documentation were a little better. > > Um, if you were a bit more specific that would be helpful ;) > > The topic you have asked about is in the docs somewhere; now that you > So it is. I was looking under 'tutorial' and it was in 'postgres'