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

[Chris Weiss]

On 2/1/06, Sigurd Nes <address@hidden> wrote:
> > 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.
> >
>
> How about insulating each insert statement which utilise get_last_insert_id 
> in transactions to use MAX safely?
> Or how about checking for the version of pgsql and use lastval() for 8.1+ ?
>
> My point is only to make phpgw somehow work with the current pgsql (where oid 
> won't work (by default))
>
> Please help me out here - I am (certainly) not an expert on this matters - 
> but I really would like phpgw to run on an out-of-the-box pgsql distribution.
>
> So if anybody have a sollution (and have read the manual...) - please make a 
> patch.
>
> Regards
>
> Sigurd
>

because that breaks our mysql implementation.  unless you code the
trasaction for every supported database, and do something completely
different for mysql/sqlite, this is not a feasable option.