pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Andrew McMillan <andrew@catalyst.net.nz>
To: Peter <peterh@600ml.com.au>
Cc: Marco Colombo <marco@esi.it>
Cc: PostgreSQL PHP <pgsql-php@postgresql.org>
Subject: Re: Nextval
Date: 16 Nov 2001 22:52:38 +1300
Message-ID: <1005904358.4336.0.camel@kant.mcmillan.net.nz> (raw)
In-Reply-To: <008501c16e60$cebe7a70$0300000a@600mlPeterPC1>
References: <Pine.LNX.4.33.0110121350120.28896-100000@Megathlon.ESI>
	<008501c16e60$cebe7a70$0300000a@600mlPeterPC1>

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




view thread (7+ messages)

Message-ID: <1005904358.4336.0.camel@kant.mcmillan.net.nz>
Permalink:  ../1005904358.4336.0.camel@kant.mcmillan.net.nz/
Also on:    postgresql.org/message-id/1005904358.4336.0.camel@kant.mcmillan.net.nz

 · 

reply

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: andrew@catalyst.net.nz, peterh@600ml.com.au, marco@esi.it
  Subject: Re: Nextval
  In-Reply-To: <1005904358.4336.0.camel@kant.mcmillan.net.nz>
  References: <Pine.LNX.4.33.0110121350120.28896-100000@Megathlon.ESI> <008501c16e60$cebe7a70$0300000a@600mlPeterPC1> <1005904358.4336.0.camel@kant.mcmillan.net.nz>

* 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