Skip site navigation (1) Skip section navigation (2)

Re: Replacing IDENTITY with lastval()

From: "Ben Trewern" <ben(dot)trewern(at)mowlem(dot)com>
To: pgsql-odbc(at)postgresql(dot)org
Subject: Re: Replacing IDENTITY with lastval()
Date: 2007-06-01 15:00:28
Message-ID: f3pc9f$2lbv$1@news.hub.org (view raw or flat)
Thread:
Lists: pgsql-odbc
Shouldn't you be using INSERT ... RETURNING .. ;  for this?  Seems like the 
perfect useage.

Regards,

Ben

"Andreas" <maps(dot)on(at)gmx(dot)net> wrote in message news:463CB520(dot)90208(at)gmx(dot)net(dot)(dot)(dot)
>
> So - generally speaking - it is not advised to use lastval() at all?
> At least not, if one is not 110% sure what happens in the application now 
> and on later modifications of the client application.
>
> Thats a pitty, as I have only known lastval() for about 15 minutes when I 
> looked in the documentation for the sequence stuff.
>
> On the other hand I ran today in troubles with sequences when I was doing 
> a bit coding on a migration from MS-Access DAO + JET to DAO, ODBC + PG.
> With pure Access + DAO one can fetch the newly assigned AUTOVALUE by 
> simply reading it out of the newly created record right after AddNew and 
> before Update.
>
> With ODBC + PG. as backend the SERIAL is undefined between AddNew and 
> Update.
> And the recordset points not to the new record after Update but to the 
> first record in the recordset.
> I had to use PG's CurrVal respectivly LastVal which would be easier 
> without having to know the name of the sequence.
>
> I'm happy that I read your post before using LastVal all over the place. 
> :)
>
>
> Dmitry Samokhin schrieb:
>> Accordind to the release notes, "Use lastval() function to replace 
>> IDENTITY on 8.1 or later servers" was introduced in release 8.2.0205. 
>> This may cause incorrect results: "<...> assumes that your database does 
>> not expect any triggers to fire when the INSERT is executed. If a trigger 
>> does fire and if that trigger adds another row to a table, the @@IDENTITY 
>> global variable would be set to point to that new Identity valuenot the 
>> one your INSERT generated. <...> work for simple situations, but not when 
>> your database gets more sophisticated".
>> See "Managing an @@IDENTITY Crisis" on MSDN 
>> (http://msdn2.microsoft.com/en-us/library/ms971502.aspx) for more 
>> details.
>>
>> The PostgreSQL documentation states that the lastval() function returns 
>> the value most recently returned by nextval in the current session. It 
>> works the same way as the @@IDENTITY variable in MSSQL. The currval(...) 
>> function returns a value of the explicitly specified sequence, this is 
>> exactly what we need.
>>
>> Please consider to revert the code.
>>
>> Regards,
>> Dmitry.
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 2: Don't 'kill -9' the postmaster
>>
>>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
>                http://www.postgresql.org/about/donate
> 



In response to

Responses

pgsql-odbc by date

Next:From: Ben TrewernDate: 2007-06-01 15:10:13
Subject: Re: Replacing IDENTITY with lastval()
Previous:From: Rainer BauerDate: 2007-06-01 10:49:35
Subject: [psqlODBC 08.02.0401] Driver option socket buffer size is ignored

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group