From: Peter <peterh@600ml.com.au>
To: Marco Colombo <marco@esi.it>
To: PostgreSQL PHP <pgsql-php@postgresql.org>
Subject: Re: Nextval
Date: Fri, 16 Nov 2001 16:37:40 +1100
Message-ID: <008501c16e60$cebe7a70$0300000a@600mlPeterPC1> (raw)
References: <Pine.LNX.4.33.0110121350120.28896-100000@Megathlon.ESI>
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@esi.it>
To: <beloshapka@mnogo.ru>
Cc: <pgsql-php@postgresql.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@ESI.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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-php@postgresql.org
Cc: peterh@600ml.com.au, marco@esi.it
Subject: Re: Nextval
In-Reply-To: <008501c16e60$cebe7a70$0300000a@600mlPeterPC1>
References: <Pine.LNX.4.33.0110121350120.28896-100000@Megathlon.ESI> <008501c16e60$cebe7a70$0300000a@600mlPeterPC1>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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