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

[Sigurd Nes]

Chris Weiss wrote:
> 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.
>
>   
Ah - that is a big misunderstanding.

look up:
$GLOBALS['phpgw']->db->transaction_begin();
$GLOBALS['phpgw']->db->transaction_commit();
$GLOBALS['phpgw']->db->transaction_abort();

You will find that transactions is handled by the corresponding 
db-object to each supported DBMS.
That is - only mssql,sapdb and pgsql is acutally handling transations 
correctly (and is why only these db-types should be used for production 
systems)
If you look in class.db.inc.php you will find the functions only 
returning TRUE for mysql - while for the others - the call for the 
function will dive into class.db_[db-type].inc.php

I still think that the latest patch solves the issue when testing for 
db-version - and then apply the appropriate method.
In worst case - it is potentially unstable for postgresql 8.1+ - which 
without this patch will not work at all.
If there is a better solution (there always is...) - please come forward.

Regards

Sigurd