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
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