Replacing IDENTITY with lastval()

Accordind to the release notes, "Use lastval() function to replace IDENTI=
TY on 8.1 or later servers" was introduced in release 8.2.0205. This may =
cause incorrect results: "<...> assumes that your database does not expec=
t any triggers to fire when the INSERT is executed. If a trigger does fir=
e and if that trigger adds another row to a table, the @@IDENTITY global =
variable would be set to point to that new Identity value=97not the one y=
our 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/e=
n-us/library/ms971502.aspx) for more details.

The PostgreSQL documentation states that the lastval() function returns t=
he value most recently returned by nextval in the current session. It wor=
ks the same way as the @@IDENTITY variable in MSSQL. The currval(...) fun=
ction returns a value of the explicitly specified sequence, this is exact=
ly what we need.

Please consider to revert the code.

Regards,
Dmitry.

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Re: Replacing IDENTITY with lastval()

Dmitry Samokhin wrote:
> Accordind to the release notes, "Use lastval() function to replace IDEN=
TITY on 8.1 or later servers" was introduced in release 8.2.0205. This ma=
y cause incorrect results: "<...> assumes that your database does not exp=
ect any triggers to fire when the INSERT is executed. If a trigger does f=
ire and if that trigger adds another row to a table, the @@IDENTITY globa=
l variable would be set to point to that new Identity value=97not the one=
your INSERT generated. <...> work for simple situations, but not when yo=
ur database gets more sophisticated".
> See "Managing an @@IDENTITY Crisis" on MSDN (http://msdn2.microsoft.com=
/en-us/library/ms971502.aspx) for more details.
>=20
> The PostgreSQL documentation states that the lastval() function returns=
the value most recently returned by nextval in the current session. It w=
orks the same way as the @@IDENTITY variable in MSSQL. The currval(...) f=
unction returns a value of the explicitly specified sequence, this is exa=
ctly what we need.
>=20
> Please consider to revert the code.

OK I would take care of it in 8.2.0402.

regards,
Hiroshi Inoue

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Re: Replacing IDENTITY with lastval()

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=20
now and on later modifications of the client application.

Thats a pitty, as I have only known lastval() for about 15 minutes when=20
I looked in the documentation for the sequence stuff.

On the other hand I ran today in troubles with sequences when I was=20
doing a bit coding on a migration from MS-Access DAO + JET to DAO, ODBC=20
+ PG.
With pure Access + DAO one can fetch the newly assigned AUTOVALUE by=20
simply reading it out of the newly created record right after AddNew and=20
before Update.

With ODBC + PG. as backend the SERIAL is undefined between AddNew and=20
Update.
And the recordset points not to the new record after Update but to the=20
first record in the recordset.
I had to use PG's CurrVal respectivly LastVal which would be easier=20
without having to know the name of the sequence.

I'm happy that I read your post before using LastVal all over the=20
place. :)


Dmitry Samokhin schrieb:
> Accordind to the release notes, "Use lastval() function to replace IDEN=
TITY on 8.1 or later servers" was introduced in release 8.2.0205. This ma=
y cause incorrect results: "<...> assumes that your database does not exp=
ect any triggers to fire when the INSERT is executed. If a trigger does f=
ire and if that trigger adds another row to a table, the @@IDENTITY globa=
l variable would be set to point to that new Identity value=97not the one=
your INSERT generated. <...> work for simple situations, but not when yo=
ur 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 w=
orks the same way as the @@IDENTITY variable in MSSQL. The currval(...) f=
unction returns a value of the explicitly specified sequence, this is exa=
ctly what we need.
>
> Please consider to revert the code.
>
> Regards,
> Dmitry.
>
> ---------------------------(end of broadcast)--------------------------=
-
> TIP 2: Don't 'kill -9' the postmaster
>
> =20


---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Re: Replacing IDENTITY with lastval()

Shouldn't you be using INSERT ... RETURNING .. ; for this? Seems like the
perfect useage.

Regards,

Ben

"Andreas" wrote in message news:463CB520.90208@gmx.net...
>
> 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 value—not 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
>



---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Re: Replacing IDENTITY with lastval()

> Shouldn't you be using INSERT ... RETURNING .. ; for this? Seems like
> the perfect useage.
>

Sorry to reply to myself but I just looked this up and it is only supported
from 8.2 onwards. :-(

Ben



---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org