SV: [Phpgroupware-developers] get_last_insert_id broken for PostgreSQL 8.x

[Sigurd Nes]

> From: Chris Weiss address@hidden
> Sent: 2006-01-31 22:11:16 CET
> To: address@hidden
> Subject: Re: [Phpgroupware-developers] get_last_insert_id broken for 
> PostgreSQL 8.x
> 
> On 1/31/06, Alan Langford <address@hidden> wrote:
> > Unless you're in a SQL transaction, you run a risk of getting the wrong ID
> > on a busy server unless you have a write lock on the table, which is
> > usually not a good thing. If you have another unique key, you can "select
> > max($field) from $table where unique_key=$keyvalue" but that's often not
> > possible.
> >
> > MySQL has a "select last insert id" query (that I could never get to
> > function), maybe Postgre has a similar query/function that works? Even then
> > you need to make sure you're the only thread using the database connection
> > or you could get an insert Id from some other transaction (highly unlikely
> > but possible).
> 
> 
> that's "select last_insert_id()", and the php function mentioned is an
> alias to this type of command in the postgresql API.
> 
> agree'd that getting the MAX is Bad Idea, and no suprise to me, the
> php manual explains the issue and solution in sufficient detail. 
> php.net/pg_last_oid
> 
> when in doubt, RTFM.
> 

new patch for get_last_insert_id()

http://savannah.gnu.org/bugs/download.php?item_id=15588&item_file_id=3353

This one checks the version - and if it is pre 8.1 - it uses the oid-method - 
otherwise it uses the lastval() function.
I still think the inserts should be protected inside transactions.
manual:
http://www.postgresql.org/docs/8.1/interactive/functions-sequence.html

Regards

Sigurd