Thread: BUG #4684: lastval in function
The following bug has been logged online:
Bug reference: 4684
Logged by: andreas
Email address: postgresql@elbrief.de
PostgreSQL version: 8.3.6
Operating system: linux
Description: lastval in function
Details:
create table bla ( id serial primary key , name text not null unique ) ;
create table bla2 ( id serial primary key , blaid int references bla , name
text not null unique ) ;
create or replace function blaa( text ) returns int as $$
my $name = shift ;
my $q = spi_exec_query( "select id from bla where name = '$name'" ) ;
if ( not $q->{ rows }->[ 0 ]->{ id } ) {
spi_exec_query( "insert into bla ( name ) values ( '$name' )" ) ;
$q = spi_exec_query( "select lastval() as id" ) ;
}
return $q->{ rows }->[ 0 ]->{ id } ;
$$ language plperl security definer ;
select blaa( 'test' ) ;
insert into bla2 ( blaid , name ) values ( blaa( 'muster' ) , 'muster' ) ;
select lastval() ;
lastval
---------
2
i expected lastval() should be 1, because this is the id from the
insertstatement.
insert into bla2 ( blaid , name ) values ( blaa( 'muster2' ) , blaa(
'muster3' ) ) ;
select lastval() ;
lastval
---------
4
if nextval is used inside a function in a insertstatement, you get always
the value from inside the last function. but i expected, that lastval()
deliver the value from the insertstatement. i think, this should clearify in
the documentation, or better fixed that the nextval from an insertstatement
is called after the functioncalls.
Andreas
"andreas" <postgresql@elbrief.de> writes:
> select lastval() ;
> lastval
> ---------
> 2
> i expected lastval() should be 1, because this is the id from the
> insertstatement.
Well, you can't really rely on that when the statement you're executing
contains two different nextval() calls, as this does. The order of
evaluation of those calls is unspecified.
Personally I'm of the opinion that anyone who uses lastval() deserves to
lose, precisely because of the risk of this type of interaction. Use
currval() on one or the other of those sequences, and you'll be at least
a little bit safer. Even better is to use INSERT RETURNING or some
other alternative so that you can avoid currval() too.
regards, tom lane
andreas wrote:
> if nextval is used inside a function in a insertstatement, you get always
> the value from inside the last function. but i expected, that lastval()
> deliver the value from the insertstatement. i think, this should clearify in
> the documentation, or better fixed that the nextval from an insertstatement
> is called after the functioncalls.
Well, others might expect the opposite, like existing applications. Want
to suggest wording for the documentation?
To get the id of the row inserted, use INSERT RETURNING. Or currval('bla2').
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
Heikki Linnakangas schrieb: > andreas wrote: >> if nextval is used inside a function in a insertstatement, you get always >> the value from inside the last function. but i expected, that lastval() >> deliver the value from the insertstatement. i think, this should >> clearify in >> the documentation, or better fixed that the nextval from an >> insertstatement >> is called after the functioncalls. > > Well, others might expect the opposite, like existing applications. Want > to suggest wording for the documentation? Yes, existing applications might be involved. But i think, this is a very rare situation. But if someone use an insertstatement with a functioncall, but the function do NOT use nextval, and he use lastval to estimate the last inserted value he has no problems. But if then someone change the function so the function use nextval then the application crashes. So i think it is much more better to change the behavior, because this is what i expect. And i think, this is what others expect too. Andreas
Andreas <postgresql@elbrief.de> writes: > Heikki Linnakangas schrieb: >> andreas wrote: >>> if nextval is used inside a function in a insertstatement, you get always >>> the value from inside the last function. but i expected, that lastval() >>> deliver the value from the insertstatement. i think, this should >>> clearify in >>> the documentation, or better fixed that the nextval from an >>> insertstatement >>> is called after the functioncalls. >> >> Well, others might expect the opposite, like existing >> applications. Want to suggest wording for the documentation? > > Yes, existing applications might be involved. But i think, this is a > very rare situation. But if someone use an insertstatement with a > functioncall, but the function do NOT use nextval, and he use lastval to > estimate the last inserted value he has no problems. But if then someone > change the function so the function use nextval then the application > crashes. So i think it is much more better to change the behavior, > because this is what i expect. And i think, this is what others > expect too. Someone recently reported this issue as a possible bug in Slony-I; they had written their application to use lastval() to capture sequence values, and then, when they introduced replication, they started capturing values of a sequence Slony-I uses to control *its* operations. You'd experience the same problem with any similar sort of "after" trigger that was added to do logging; any kind of logging system that uses sequences is liable to break usage of lastval(). This actually feels like it's a global versus dynamic/lexical scope problem <http://en.wikipedia.org/wiki/Scope_(programming)>. PostgreSQL is capturing *all* the sequence updates for the connection, where the application would apparently prefer to only see those that it *wants* to see. I'm not sure whether lexical or dynamic scope better reflect what might be desired. However, the notion of there being some kind of more-local scope is the big deal, something which PostgreSQL does not support. I'm not sure I'd *want* to have a more-sophisticated scoping mechanism for this. -- output = reverse("moc.enworbbc" "@" "enworbbc") http://cbbrowne.com/info/nonrdbms.html Rules of the Evil Overlord #177. "If a scientist with a beautiful and unmarried daughter refuses to work for me, I will not hold her hostage. Instead, I will offer to pay for her future wedding and her children's college tuition." <http://www.eviloverlord.com/>
Chris Browne <cbbrowne@acm.org> writes:
> I'm not sure I'd *want* to have a more-sophisticated scoping mechanism
> for this.
We already have an appropriate language-level solution for this: it's
called INSERT RETURNING.
The fact is that lastval was invented to aid in porting brain-dead
mysql applications that weren't going to do anything as complicated
as execute commands that might involve more than one nextval call.
If there is any remote possibility that your app might have to deal
with such a thing, you shouldn't be using lastval.
Before considering complicating the definition of lastval, I'd vote
for removing it entirely. It's a foot-gun and will never be anything
but.
regards, tom lane
On Mon, Mar 2, 2009 at 12:21 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > Before considering complicating the definition of lastval, I'd vote > for removing it entirely. =A0It's a foot-gun and will never be anything > but. > +1 --=20 Atentamente, Jaime Casanova Soporte y capacitaci=F3n de PostgreSQL Asesor=EDa y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157