Thread: 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 -----------------------------------------
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
"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 doselect 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
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
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.
"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
> > 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 handlingthis 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 furtheruse by the next client. If anyone wishes further information about this you may send me an e-mail. Marten