Re: Nextval - Mailing list pgsql-php

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 "<p>Drat!</p>";
}

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