pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedlocking on database updates
21+ messages / 9 participants
[nested] [flat]
* locking on database updates
@ 1999-12-06 17:16 Gary Stainburn <gary.stainburn@ringways.co.uk>
1999-12-06 18:20 ` Re: [INTERFACES] locking on database updates Rich Shepard <rshepard@appl-ecosys.com>
1999-12-07 01:14 ` Re: [INTERFACES] locking on database updates Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 21+ messages in thread
From: Gary Stainburn @ 1999-12-06 17:16 UTC (permalink / raw)
To: pgsql-interfaces
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
-----------------------------------------
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: [INTERFACES] locking on database updates
1999-12-06 17:16 locking on database updates Gary Stainburn <gary.stainburn@ringways.co.uk>
@ 1999-12-06 18:20 ` Rich Shepard <rshepard@appl-ecosys.com>
1 sibling, 0 replies; 21+ messages in thread
From: Rich Shepard @ 1999-12-06 18:20 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; +Cc: pgsql-interfaces
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, OR 97060-1247 | U.S.A.
+ 1 503-667-4517 (voice) | + 1 503-667-8863 (fax) | rshepard@appl-ecosys.com
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: [INTERFACES] locking on database updates
1999-12-06 17:16 locking on database updates Gary Stainburn <gary.stainburn@ringways.co.uk>
@ 1999-12-07 01:14 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-12-07 02:59 ` Re: [INTERFACES] locking on database updates Joseph Shraibman <jks@p1.selectacast.net>
1 sibling, 1 reply; 21+ messages in thread
From: Tom Lane @ 1999-12-07 01:14 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; +Cc: pgsql-interfaces
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
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: [INTERFACES] locking on database updates
1999-12-06 17:16 locking on database updates Gary Stainburn <gary.stainburn@ringways.co.uk>
1999-12-07 01:14 ` Re: [INTERFACES] locking on database updates Tom Lane <tgl@sss.pgh.pa.us>
@ 1999-12-07 02:59 ` Joseph Shraibman <jks@p1.selectacast.net>
1999-12-07 03:58 ` Re: [INTERFACES] locking on database updates Douglas Thomson <dougt@mugc.cc.monash.edu.au>
0 siblings, 1 reply; 21+ messages in thread
From: Joseph Shraibman @ 1999-12-07 02:59 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Gary Stainburn <gary.stainburn@ringways.co.uk>; pgsql-interfaces
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?
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: [INTERFACES] locking on database updates
1999-12-06 17:16 locking on database updates Gary Stainburn <gary.stainburn@ringways.co.uk>
1999-12-07 01:14 ` Re: [INTERFACES] locking on database updates Tom Lane <tgl@sss.pgh.pa.us>
1999-12-07 02:59 ` Re: [INTERFACES] locking on database updates Joseph Shraibman <jks@p1.selectacast.net>
@ 1999-12-07 03:58 ` Douglas Thomson <dougt@mugc.cc.monash.edu.au>
1999-12-07 04:17 ` Re: [INTERFACES] locking on database updates Joseph Shraibman <jks@p1.selectacast.net>
1999-12-07 04:25 ` Re: [INTERFACES] locking on database updates Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 21+ messages in thread
From: Douglas Thomson @ 1999-12-07 03:58 UTC (permalink / raw)
To: jks@p1.selectacast.net; +Cc: tgl@sss.pgh.pa.us; gary.stainburn@ringways.co.uk; pgsql-interfaces
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.
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: [INTERFACES] locking on database updates
1999-12-06 17:16 locking on database updates Gary Stainburn <gary.stainburn@ringways.co.uk>
1999-12-07 01:14 ` Re: [INTERFACES] locking on database updates Tom Lane <tgl@sss.pgh.pa.us>
1999-12-07 02:59 ` Re: [INTERFACES] locking on database updates Joseph Shraibman <jks@p1.selectacast.net>
1999-12-07 03:58 ` Re: [INTERFACES] locking on database updates Douglas Thomson <dougt@mugc.cc.monash.edu.au>
@ 1999-12-07 04:17 ` Joseph Shraibman <jks@p1.selectacast.net>
1999-12-07 04:57 ` Re: [INTERFACES] locking on database updates Rich Shepard <rshepard@appl-ecosys.com>
1999-12-07 04:58 ` Re: [INTERFACES] locking on database updates Thomas Lockhart <lockhart@alumni.caltech.edu>
1 sibling, 2 replies; 21+ messages in thread
From: Joseph Shraibman @ 1999-12-07 04:17 UTC (permalink / raw)
To: dougt@mugc.cc.monash.edu.au; +Cc: Joseph Shraibman <jks@p1.selectacast.net>; tgl@sss.pgh.pa.us; gary.stainburn@ringways.co.uk; pgsql-interfaces
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.
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: [INTERFACES] locking on database updates
1999-12-06 17:16 locking on database updates Gary Stainburn <gary.stainburn@ringways.co.uk>
1999-12-07 01:14 ` Re: [INTERFACES] locking on database updates Tom Lane <tgl@sss.pgh.pa.us>
1999-12-07 02:59 ` Re: [INTERFACES] locking on database updates Joseph Shraibman <jks@p1.selectacast.net>
1999-12-07 03:58 ` Re: [INTERFACES] locking on database updates Douglas Thomson <dougt@mugc.cc.monash.edu.au>
1999-12-07 04:17 ` Re: [INTERFACES] locking on database updates Joseph Shraibman <jks@p1.selectacast.net>
@ 1999-12-07 04:57 ` Rich Shepard <rshepard@appl-ecosys.com>
1 sibling, 0 replies; 21+ messages in thread
From: Rich Shepard @ 1999-12-07 04:57 UTC (permalink / raw)
To: pgsql-interfaces
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, OR 97060-1247 | U.S.A.
+ 1 503-667-4517 (voice) | + 1 503-667-8863 (fax) | rshepard@appl-ecosys.com
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: [INTERFACES] locking on database updates
1999-12-06 17:16 locking on database updates Gary Stainburn <gary.stainburn@ringways.co.uk>
1999-12-07 01:14 ` Re: [INTERFACES] locking on database updates Tom Lane <tgl@sss.pgh.pa.us>
1999-12-07 02:59 ` Re: [INTERFACES] locking on database updates Joseph Shraibman <jks@p1.selectacast.net>
1999-12-07 03:58 ` Re: [INTERFACES] locking on database updates Douglas Thomson <dougt@mugc.cc.monash.edu.au>
1999-12-07 04:17 ` Re: [INTERFACES] locking on database updates Joseph Shraibman <jks@p1.selectacast.net>
@ 1999-12-07 04:58 ` Thomas Lockhart <lockhart@alumni.caltech.edu>
1999-12-07 19:36 ` Re: [INTERFACES] locking on database updates Joseph Shraibman <jks@p1.selectacast.net>
1 sibling, 1 reply; 21+ messages in thread
From: Thomas Lockhart @ 1999-12-07 04:58 UTC (permalink / raw)
To: Joseph Shraibman <jks@p1.selectacast.net>; +Cc: dougt@mugc.cc.monash.edu.au; tgl@sss.pgh.pa.us; gary.stainburn@ringways.co.uk; pgsql-interfaces
> 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
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: [INTERFACES] locking on database updates
1999-12-06 17:16 locking on database updates Gary Stainburn <gary.stainburn@ringways.co.uk>
1999-12-07 01:14 ` Re: [INTERFACES] locking on database updates Tom Lane <tgl@sss.pgh.pa.us>
1999-12-07 02:59 ` Re: [INTERFACES] locking on database updates Joseph Shraibman <jks@p1.selectacast.net>
1999-12-07 03:58 ` Re: [INTERFACES] locking on database updates Douglas Thomson <dougt@mugc.cc.monash.edu.au>
1999-12-07 04:17 ` Re: [INTERFACES] locking on database updates Joseph Shraibman <jks@p1.selectacast.net>
1999-12-07 04:58 ` Re: [INTERFACES] locking on database updates Thomas Lockhart <lockhart@alumni.caltech.edu>
@ 1999-12-07 19:36 ` Joseph Shraibman <jks@p1.selectacast.net>
0 siblings, 0 replies; 21+ messages in thread
From: Joseph Shraibman @ 1999-12-07 19:36 UTC (permalink / raw)
To: Thomas Lockhart <lockhart@alumni.caltech.edu>; +Cc: dougt@mugc.cc.monash.edu.au; tgl@sss.pgh.pa.us; gary.stainburn@ringways.co.uk; pgsql-interfaces
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'
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: [INTERFACES] locking on database updates
1999-12-06 17:16 locking on database updates Gary Stainburn <gary.stainburn@ringways.co.uk>
1999-12-07 01:14 ` Re: [INTERFACES] locking on database updates Tom Lane <tgl@sss.pgh.pa.us>
1999-12-07 02:59 ` Re: [INTERFACES] locking on database updates Joseph Shraibman <jks@p1.selectacast.net>
1999-12-07 03:58 ` Re: [INTERFACES] locking on database updates Douglas Thomson <dougt@mugc.cc.monash.edu.au>
@ 1999-12-07 04:25 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 21+ messages in thread
From: Tom Lane @ 1999-12-07 04:25 UTC (permalink / raw)
To: dougt@mugc.cc.monash.edu.au; +Cc: jks@p1.selectacast.net; gary.stainburn@ringways.co.uk; pgsql-interfaces
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
^ permalink raw reply [nested|flat] 21+ messages in thread
* RE: [INTERFACES] locking on database updates
@ 1999-12-07 12:21 Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 0 replies; 21+ messages in thread
From: Gary Stainburn @ 1999-12-07 12:21 UTC (permalink / raw)
To: 'dougt@mugc.cc.monash.edu.au' <dougt@mugc.cc.monash.edu.au>; +Cc: pgsql-interfaces
-----Original Message-----
From: Douglas Thomson [SMTP:dougt@mugc.cc.monash.edu.au]
Sent: Tuesday, December 07, 1999 11:16 AM
To: Gary Stainburn
Subject: Re: [INTERFACES] locking on database updates
!> Is there any way to easily retrieve the sequence number just created =
!> without having the same contension problems I originally wanted to =
!> avoid?
!
!Do you need to find out the next value that will be used before you
!use it? If so there may be a problem...
!
!However, if you only need to do inserts and find out afterwards what
!sequence number got used then there is no problem. Postgres looks
!after making sure multiple simultaneous backend processes don't
!duplicate sequence numbers. I think it actually reserves a few
!numbers in the sequence for each process, so it is possible to get
!small gaps in the sequence, or for the sequence numbers to be not
!strictly chronological...
!
!As for the how - well, I simply selected back the row I had just
!inserted to extract the sequence number column :-)
!
!Doug.
It's not important that I know the number beforehand. However, as
this is the only field that is guaranteed to be unique, getting the
sequence number using a select statement may not work.
It doesn't matter
-----------------------------------------
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
-----------------------------------------
^ permalink raw reply [nested|flat] 21+ messages in thread
* RE: [INTERFACES] locking on database updates
@ 1999-12-07 16:34 Gary Stainburn <gary.stainburn@ringways.co.uk>
1999-12-07 17:12 ` Re: [INTERFACES] locking on database updates Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>
1999-12-08 04:34 ` Re: [INTERFACES] locking on database updates Douglas Thomson <dougt@mugc.cc.monash.edu.au>
1999-12-09 08:00 ` Re: [INTERFACES] locking on database updates marten@feki.toppoint.de
0 siblings, 3 replies; 21+ messages in thread
From: Gary Stainburn @ 1999-12-07 16:34 UTC (permalink / raw)
To: pgsql-interfaces
Hi all,
I've just read the last message I sent and apologise for the poor English.
Let me put it a little clearer.
I agree that using a type 'serial' will give me what I want
regarding the key field for my table, and gets round the problem
of concurrent users trying to create records with the same number.
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.
Has anyone any ideas?
Thanks,
Gary
-----Original Message-----
From: Gary Stainburn [SMTP:gary.stainburn@ringways.co.uk]
Sent: Tuesday, December 07, 1999 12:21 PM
To: 'dougt@mugc.cc.monash.edu.au'
Cc: 'Pgsql Interfaces'
Subject: RE: [INTERFACES] locking on database updates
-----Original Message-----
From: Douglas Thomson [SMTP:dougt@mugc.cc.monash.edu.au]
Sent: Tuesday, December 07, 1999 11:16 AM
To: Gary Stainburn
Subject: Re: [INTERFACES] locking on database updates
!> Is there any way to easily retrieve the sequence number just created =
!> without having the same contension problems I originally wanted to =
!> avoid?
!
!Do you need to find out the next value that will be used before you
!use it? If so there may be a problem...
!
!However, if you only need to do inserts and find out afterwards what
!sequence number got used then there is no problem. Postgres looks
!after making sure multiple simultaneous backend processes don't
!duplicate sequence numbers. I think it actually reserves a few
!numbers in the sequence for each process, so it is possible to get
!small gaps in the sequence, or for the sequence numbers to be not
!strictly chronological...
!
!As for the how - well, I simply selected back the row I had just
!inserted to extract the sequence number column :-)
!
!Doug.
It's not important that I know the number beforehand. However, as
this is the only field that is guaranteed to be unique, getting the
sequence number using a select statement may not work.
It doesn't matter
-----------------------------------------
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
-----------------------------------------
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: [INTERFACES] locking on database updates
1999-12-07 16:34 RE: [INTERFACES] locking on database updates Gary Stainburn <gary.stainburn@ringways.co.uk>
@ 1999-12-07 17:12 ` Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>
1999-12-07 17:59 ` Re: [INTERFACES] locking on database updates Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 1 reply; 21+ messages in thread
From: Ross J. Reedstrom @ 1999-12-07 17:12 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; +Cc: pgsql-interfaces
On Tue, Dec 07, 1999 at 04:34:37PM -0000, Gary Stainburn wrote:
> Hi all,
>
> I've just read the last message I sent and apologise for the poor English.
> Let me put it a little clearer.
>
> I agree that using a type 'serial' will give me what I want
> regarding the key field for my table, and gets round the problem
> of concurrent users trying to create records with the same number.
>
> 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.
>
> Has anyone any ideas?
create table foo (bar serial, baz text);
insert into foo (baz) values ('wooble');
select currval('foo_bar_seq');
>
> Thanks,
>
> Gary
you're welcome ;-)
Ross
--
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: [INTERFACES] locking on database updates
1999-12-07 16:34 RE: [INTERFACES] locking on database updates Gary Stainburn <gary.stainburn@ringways.co.uk>
1999-12-07 17:12 ` Re: [INTERFACES] locking on database updates Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>
@ 1999-12-07 17:59 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-12-07 19:24 ` Re: [INTERFACES] locking on database updates Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>
0 siblings, 1 reply; 21+ messages in thread
From: Tom Lane @ 1999-12-07 17:59 UTC (permalink / raw)
To: Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>; +Cc: Gary Stainburn <gary.stainburn@ringways.co.uk>; pgsql-interfaces
"Ross J. Reedstrom" <reedstrm@wallace.ece.rice.edu> writes:
> create table foo (bar serial, baz text);
> insert into foo (baz) values ('wooble');
> select currval('foo_bar_seq');
I don't think this is safe in a multi-client environment;
what if someone else inserts at about the same time?
Better to do
select nextval('foo_bar_seq');
insert into foo values (just-retrieved-value, 'wooble');
which is safer and probably marginally faster (since the
sequence object is touched only once, not twice).
regards, tom lane
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: [INTERFACES] locking on database updates
1999-12-07 16:34 RE: [INTERFACES] locking on database updates Gary Stainburn <gary.stainburn@ringways.co.uk>
1999-12-07 17:12 ` Re: [INTERFACES] locking on database updates Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>
1999-12-07 17:59 ` Re: [INTERFACES] locking on database updates Tom Lane <tgl@sss.pgh.pa.us>
@ 1999-12-07 19:24 ` Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>
1999-12-08 06:53 ` Re: [INTERFACES] locking on database updates Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 21+ messages in thread
From: Ross J. Reedstrom @ 1999-12-07 19:24 UTC (permalink / raw)
To: pgsql-interfaces
Tom -
I'm surprised: that one's been beat to death in earlier incarnations
of this FAQ. The currval() function is part of backend state: it
always returns the last value sent to _this connection_. In fact, it's
undefined (and throws an error) in a connection until a nextval() has
been performed. Who ever implemented currval did it right.
============session 1=====================
idas_demo=> create table foo (bar serial, baz text);
NOTICE: CREATE TABLE will create implicit sequence 'foo_bar_seq' for SERIAL column 'foo.bar'
NOTICE: CREATE TABLE/UNIQUE will create implicit index 'foo_bar_key' for table 'foo'
CREATE
idas_demo=> insert into foo (baz) values ('wooble');
INSERT 692575 1
idas_demo=> insert into foo (baz) values ('wibble');
INSERT 692576 1
idas_demo=> select currval('foo_bar_seq');
currval
-------
2
(1 row)
============session 2=====================
idas_demo=> select currval('foo_bar_seq');
ERROR: foo_bar_seq.currval is not yet defined in this session
idas_demo=> insert into foo (baz) values ('wibble');
INSERT 692608 1
idas_demo=> insert into foo (baz) values ('wibble');
INSERT 692609 1
idas_demo=> select currval('foo_bar_seq');
currval
-------
4
(1 row)
idas_demo=>
============session 1=====================
idas_demo=> select currval('foo_bar_seq');
currval
-------
2
(1 row)
idas_demo=>
I'm not sure it's even slower: since currval is local to the backend,
it may be that it doesn't touch the sequence per se: I'd have to check
the code.
Of the sequence functions, one that's not multi-user safe, as far as
I can tell, is setval(). I think that sets the master sequence counter
for all clients.
Ross
On Tue, Dec 07, 1999 at 12:59:33PM -0500, Tom Lane wrote:
> "Ross J. Reedstrom" <reedstrm@wallace.ece.rice.edu> writes:
> > create table foo (bar serial, baz text);
> > insert into foo (baz) values ('wooble');
> > select currval('foo_bar_seq');
>
> I don't think this is safe in a multi-client environment;
> what if someone else inserts at about the same time?
>
> Better to do
> select nextval('foo_bar_seq');
> insert into foo values (just-retrieved-value, 'wooble');
> which is safer and probably marginally faster (since the
> sequence object is touched only once, not twice).
>
> regards, tom lane
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: [INTERFACES] locking on database updates
1999-12-07 16:34 RE: [INTERFACES] locking on database updates Gary Stainburn <gary.stainburn@ringways.co.uk>
1999-12-07 17:12 ` Re: [INTERFACES] locking on database updates Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>
1999-12-07 17:59 ` Re: [INTERFACES] locking on database updates Tom Lane <tgl@sss.pgh.pa.us>
1999-12-07 19:24 ` Re: [INTERFACES] locking on database updates Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>
@ 1999-12-08 06:53 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 21+ messages in thread
From: Tom Lane @ 1999-12-08 06:53 UTC (permalink / raw)
To: Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>; +Cc: pgsql-interfaces
"Ross J. Reedstrom" <reedstrm@wallace.ece.rice.edu> writes:
> Tom -
> I'm surprised: that one's been beat to death in earlier incarnations
> of this FAQ. The currval() function is part of backend state: it
> always returns the last value sent to _this connection_.
Yeah, sure, I knew that ... in one brain cell or another, but evidently
not the ones on call today ;-)
A fairly weak rejoinder: if you've got triggers or rules doing things
to your tables behind-your-back, you might still not be able to assume
that the sequence's nextval() will be called only once during any one
SQL query that you issue. So I still say the nextval-first approach
is sounder than currval-afterwards. But I must agree it wouldn't
become an issue unless you had some pretty convoluted database
programming going on.
regards, tom lane
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: [INTERFACES] locking on database updates
1999-12-07 16:34 RE: [INTERFACES] locking on database updates Gary Stainburn <gary.stainburn@ringways.co.uk>
@ 1999-12-08 04:34 ` Douglas Thomson <dougt@mugc.cc.monash.edu.au>
2 siblings, 0 replies; 21+ messages in thread
From: Douglas Thomson @ 1999-12-08 04:34 UTC (permalink / raw)
To: gary.stainburn@ringways.co.uk; +Cc: pgsql-interfaces
Gary Stainburn writes:
> I agree that using a type 'serial' will give me what I want
> regarding the key field for my table, and gets round the problem
> of concurrent users trying to create records with the same number.
>
> 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.
>
> Has anyone any ideas?
For what it is worth, I saved the OID returned by the INSERT (see
PQoidStatus - the details depend on what interface language you use),
and then selected the row I had just inserted using:
SELECT id FROM name_map WHERE oid=12345
Doug.
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: [INTERFACES] locking on database updates
1999-12-07 16:34 RE: [INTERFACES] locking on database updates Gary Stainburn <gary.stainburn@ringways.co.uk>
@ 1999-12-09 08:00 ` marten@feki.toppoint.de
2 siblings, 0 replies; 21+ messages in thread
From: marten@feki.toppoint.de @ 1999-12-09 08:00 UTC (permalink / raw)
To: gary.stainburn@ringways.co.uk; +Cc: pgsql-interfaces
>
> 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
^ permalink raw reply [nested|flat] 21+ messages in thread
* RE: [INTERFACES] locking on database updates
@ 1999-12-07 17:26 Nicolas Huillard <nhuillard@ghs.fr>
1999-12-07 17:45 ` Re: [INTERFACES] locking on database updates Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 21+ messages in thread
From: Nicolas Huillard @ 1999-12-07 17:26 UTC (permalink / raw)
To: 'Gary Stainburn' <gary.stainburn@ringways.co.uk>; pgsql-interfaces
This sounds like a question posted a few months ago (sept/oct 99) in this list. Look at the archive, I'm sure you'll have an positive answer (AFAIR) on that point.
Nicolas Huillard
-----Message d'origine-----
De: Gary Stainburn [SMTP:gary.stainburn@ringways.co.uk]
Date: mardi 7 décembre 1999 17:35
À: 'Pgsql Interfaces'
Objet: RE: [INTERFACES] locking on database updates
Hi all,
I've just read the last message I sent and apologise for the poor English.
Let me put it a little clearer.
I agree that using a type 'serial' will give me what I want
regarding the key field for my table, and gets round the problem
of concurrent users trying to create records with the same number.
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.
Has anyone any ideas?
Thanks,
Gary
-----Original Message-----
From: Gary Stainburn [SMTP:gary.stainburn@ringways.co.uk]
Sent: Tuesday, December 07, 1999 12:21 PM
To: 'dougt@mugc.cc.monash.edu.au'
Cc: 'Pgsql Interfaces'
Subject: RE: [INTERFACES] locking on database updates
-----Original Message-----
From: Douglas Thomson [SMTP:dougt@mugc.cc.monash.edu.au]
Sent: Tuesday, December 07, 1999 11:16 AM
To: Gary Stainburn
Subject: Re: [INTERFACES] locking on database updates
!> Is there any way to easily retrieve the sequence number just created =
!> without having the same contension problems I originally wanted to =
!> avoid?
!
!Do you need to find out the next value that will be used before you
!use it? If so there may be a problem...
!
!However, if you only need to do inserts and find out afterwards what
!sequence number got used then there is no problem. Postgres looks
!after making sure multiple simultaneous backend processes don't
!duplicate sequence numbers. I think it actually reserves a few
!numbers in the sequence for each process, so it is possible to get
!small gaps in the sequence, or for the sequence numbers to be not
!strictly chronological...
!
!As for the how - well, I simply selected back the row I had just
!inserted to extract the sequence number column :-)
!
!Doug.
It's not important that I know the number beforehand. However, as
this is the only field that is guaranteed to be unique, getting the
sequence number using a select statement may not work.
It doesn't matter
-----------------------------------------
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
-----------------------------------------
************
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: [INTERFACES] locking on database updates
1999-12-07 17:26 RE: [INTERFACES] locking on database updates Nicolas Huillard <nhuillard@ghs.fr>
@ 1999-12-07 17:45 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 21+ messages in thread
From: Tom Lane @ 1999-12-07 17:45 UTC (permalink / raw)
To: 'Gary Stainburn' <gary.stainburn@ringways.co.uk>; +Cc: pgsql-interfaces
> 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.
Easiest answer is to do
SELECT nextval('sequence_object');
and then INSERT the new row into your table using the value you just
obtained as an explicit value for the ID column, thus overriding the
default expression.
regards, tom lane
^ permalink raw reply [nested|flat] 21+ messages in thread
* RE: [INTERFACES] locking on database updates
@ 1999-12-07 19:36 Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 0 replies; 21+ messages in thread
From: Gary Stainburn @ 1999-12-07 19:36 UTC (permalink / raw)
To: 'Tom Lane' <tgl@sss.pgh.pa.us>; +Cc: pgsql-interfaces
-----Original Message-----
From: Tom Lane [SMTP:tgl@sss.pgh.pa.us]
Sent: Tuesday, December 07, 1999 6:00 PM
To: Ross J. Reedstrom
Cc: Gary Stainburn; 'Pgsql Interfaces'
Subject: Re: [INTERFACES] locking on database updates
|"Ross J. Reedstrom" <reedstrm@wallace.ece.rice.edu> writes:
|> create table foo (bar serial, baz text);
|> insert into foo (baz) values ('wooble');
|> select currval('foo_bar_seq');
|
|I don't think this is safe in a multi-client environment;
|what if someone else inserts at about the same time?
|
|Better to do
| select nextval('foo_bar_seq');
| insert into foo values (just-retrieved-value, 'wooble');
|which is safer and probably marginally faster (since the
|sequence object is touched only once, not twice).
|
| regards, tom lane
Perfect - exactly what I need. Another fine day at the office.
-----------------------------------------
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 ones are always taken - anon
-----------------------------------------
^ permalink raw reply [nested|flat] 21+ messages in thread
end of thread, other threads:[~1999-12-09 08:00 UTC | newest]
Thread overview: 21+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-12-06 17:16 locking on database updates Gary Stainburn <gary.stainburn@ringways.co.uk>
1999-12-06 18:20 ` Rich Shepard <rshepard@appl-ecosys.com>
1999-12-07 01:14 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-12-07 02:59 ` Joseph Shraibman <jks@p1.selectacast.net>
1999-12-07 03:58 ` Douglas Thomson <dougt@mugc.cc.monash.edu.au>
1999-12-07 04:17 ` Joseph Shraibman <jks@p1.selectacast.net>
1999-12-07 04:57 ` Rich Shepard <rshepard@appl-ecosys.com>
1999-12-07 04:58 ` Thomas Lockhart <lockhart@alumni.caltech.edu>
1999-12-07 19:36 ` Joseph Shraibman <jks@p1.selectacast.net>
1999-12-07 04:25 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-12-07 12:21 RE: [INTERFACES] locking on database updates Gary Stainburn <gary.stainburn@ringways.co.uk>
1999-12-07 16:34 RE: [INTERFACES] locking on database updates Gary Stainburn <gary.stainburn@ringways.co.uk>
1999-12-07 17:12 ` Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>
1999-12-07 17:59 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-12-07 19:24 ` Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>
1999-12-08 06:53 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-12-08 04:34 ` Douglas Thomson <dougt@mugc.cc.monash.edu.au>
1999-12-09 08:00 ` marten@feki.toppoint.de
1999-12-07 17:26 RE: [INTERFACES] locking on database updates Nicolas Huillard <nhuillard@ghs.fr>
1999-12-07 17:45 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-12-07 19:36 RE: [INTERFACES] locking on database updates Gary Stainburn <gary.stainburn@ringways.co.uk>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox