Re: Nextval

Everybody appears to be using nextval() to get next val but there is issues
with people accessing same record at the same time etc, etc...
obviously if you are using serials and you are just creating a new record in
the same table you just let postgres add the next value by default but if
for example you need the id of a newly created record to insert into a new
order record what I do is:
start a transaction
do an insert into customers
use pg_getlastoid() to get last oid and write a little function to do a
select on that oid and return the corresponding id which I insert into the
order table
cofim transaction
as far as I know thats the most reliable way because there wont be problems
with concurrent users plus the whole thing is safeguarded by the transaction
anyway
seems to work nicely

regards, Peter

----- Original Message -----
From: "Marco Colombo" <marco(at)esi(dot)it>
To: <beloshapka(at)mnogo(dot)ru>
Cc: <pgsql-php(at)postgresql(dot)org>
Sent: Friday, October 12, 2001 10:56 PM
Subject: Re: [PHP] Nextval

> On Thu, 11 Oct 2001, Zavier Sheran wrote:
>
> > I try to do the following:
> >
> > Fetch a SERIAL field (ie. record_id) and get the
> > highest value currently stored (ie. 1000), increment
> > the value by 1 and store a record that will have the
> > same value (1001) in record_id. So there must be a way
> > with concurrency control.
> >
> > I went through the manuals and found the solution with
> > NEXTVAL('serial'), but you have to create a sequence
> > for that, and I don't know if it is the right way to
> > do for what I want.
> >
> > It is my first PHP project involving a Database, so
> > it's a newbie question. Have mercy...
> >
> > Thanks
> >
>
> marco=# create table test_serial ( id serial );
> NOTICE: CREATE TABLE will create implicit sequence 'test_serial_id_seq'
for SERIAL column 'test_serial.id'
> NOTICE: CREATE TABLE/UNIQUE will create implicit index
'test_serial_id_key' for table 'test_serial'
>
> The serial type automagically creates both a sequence and an index:
> the sequence is named <table>_<column>_seq and the index
> <table>_<column>_key.
>
> You can use nextval() on the sequence if you like, as in:
> insert into test_serial values ( nextval('test_serial_id_seq') );
>
> or have the column default to that so that you don't even bother on
> inserts.
>
> .TM.
> --
> ____/ ____/ /
> / / / Marco Colombo
> ___/ ___ / / Technical Manager
> / / / ESI s.r.l.
> _____/ _____/ _/ Colombo(at)ESI(dot)it
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>