Re: Nextval - Mailing list pgsql-php
From | Peter |
---|---|
Subject | Re: Nextval |
Date | |
Msg-id | 008501c16e60$cebe7a70$0300000a@600mlPeterPC1 Whole thread Raw |
In response to | Re: Nextval (Marco Colombo <marco@esi.it>) |
Responses |
Re: Nextval
|
List | pgsql-php |
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 >