Обсуждение: Replacing IDENTITY with lastval()
Accordind to the release notes, "Use lastval() function to replace IDENTITY on 8.1 or later servers" was introduced in release8.2.0205. This may cause incorrect results: "<...> assumes that your database does not expect any triggers to firewhen the INSERT is executed. If a trigger does fire and if that trigger adds another row to a table, the @@IDENTITY globalvariable would be set to point to that new Identity value—not the one your INSERT generated. <...> work for simplesituations, 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 thecurrent session. It works the same way as the @@IDENTITY variable in MSSQL. The currval(...) function returns a valueof the explicitly specified sequence, this is exactly what we need. Please consider to revert the code. Regards, Dmitry.
Dmitry Samokhin wrote: > Accordind to the release notes, "Use lastval() function to replace IDENTITY on 8.1 or later servers" was introduced inrelease 8.2.0205. This may cause incorrect results: "<...> assumes that your database does not expect any triggers to firewhen the INSERT is executed. If a trigger does fire and if that trigger adds another row to a table, the @@IDENTITY globalvariable would be set to point to that new Identity value—not the one your INSERT generated. <...> work for simplesituations, 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 inthe current session. It works the same way as the @@IDENTITY variable in MSSQL. The currval(...) function returns a valueof the explicitly specified sequence, this is exactly what we need. > > Please consider to revert the code. OK I would take care of it in 8.2.0402. regards, Hiroshi Inoue
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 inrelease 8.2.0205. This may cause incorrect results: "<...> assumes that your database does not expect any triggers to firewhen the INSERT is executed. If a trigger does fire and if that trigger adds another row to a table, the @@IDENTITY globalvariable would be set to point to that new Identity value—not the one your INSERT generated. <...> work for simplesituations, 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 inthe current session. It works the same way as the @@IDENTITY variable in MSSQL. The currval(...) function returns a valueof 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 > >
Shouldn't you be using INSERT ... RETURNING .. ; for this? Seems like the perfect useage. Regards, Ben "Andreas" <maps.on@gmx.net> 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 >
> 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