Received: from deborah.paradise.net.nz (deborah.paradise.net.nz [203.96.152.32]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fAG9r0m19650 for ; Fri, 16 Nov 2001 04:53:00 -0500 (EST) (envelope-from andrew@catalyst.net.nz) Received: from heidegger.catalyst.net.nz (203-96-145-99.adsl.paradise.net.nz [203.96.145.99]) by deborah.paradise.net.nz (Postfix) with ESMTP id 0A3B0D1C2E; Fri, 16 Nov 2001 22:53:00 +1300 (NZDT) Received: from 127.0.0.1 (ident=unknown) by heidegger.catalyst.net.nz with esmtp (MasqMail 0.1.15) id 164ffq-4Ae-00; Fri, 16 Nov 2001 22:52:38 +1300 Subject: Re: Nextval From: Andrew McMillan To: Peter Cc: Marco Colombo , PostgreSQL PHP In-Reply-To: <008501c16e60$cebe7a70$0300000a@600mlPeterPC1> References: <008501c16e60$cebe7a70$0300000a@600mlPeterPC1> Content-Type: text/plain Content-Transfer-Encoding: 7bit X-Mailer: Evolution/0.99.0 (Preview Release) Date: 16 Nov 2001 22:52:38 +1300 Message-Id: <1005904358.4336.0.camel@kant.mcmillan.net.nz> Mime-Version: 1.0 X-Archive-Number: 200111/110 X-Sequence-Number: 751 On Fri, 2001-11-16 at 18:37, Peter wrote: > 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 Be _real_ careful with dealing with OID in this manner as it is not going to have the same value after a dump -> reload cycle. Personally I _never_ use OID for anything. Where's the benefit? There isn't any high-speed access to records by using OID - you still need to create an index on it (e.g.) if you have a large table that you are using it as a key for. The reason everyone is using: $result = pg_Exec( $dbconn, 'SELECT nextval('my_seq');' ); if ( $result && $pg_NumRows($result) > 0 ) { $my_new_id = pg_Fetch_Result( $result, 0, 0); $result = pg_Exec( $dbconn, 'INSERT INTO mytable( myid ) VALUES( $my_new_id );'); } else { echo "

Drat!

"; } Is because it is _THE_ right way to do it. A sequence is dealt with slightly specially in PostgreSQL so that it _does_ work. For example you can't roll back a 'nextval' - each one will be one more than the last. This is critical to the process of guaranteeing a different value is passed to each concurrent transaction. Another important point is that you don't need to vacuum a sequence - no matter how many nextval()s you do, you won't get deleted tuples. If you implemented that with a record in your own table you would get a deleted tuple every time you updated the sequence to say what the last used value was. Regards, Andrew. -- -------------------------------------------------------------------- Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St DDI: +64(4)916-7201 MOB: +64(21)635-694 OFFICE: +64(4)499-2267