| Nick Farrell 2005-06-12, 1:24 pm |
|
The following bug has been logged online:
Bug reference: 1705
Logged by: Nick Farrell
Email address: nick@farrell.name
PostgreSQL version: 8.0.3
Operating system: ubuntu linux (warty warthog)
Description: nextval being evaluated more than once
Details:
If I use embed nextval in a string which is EVALUATEd, the insert rule below
does not get the correct primary key value, but the next one! ie: NEW.p1 is
not the actual value of p1, but is the result of re-executing nextval().
Workaround is to evaluate nextval in the function, and EVALUATE with a
literal key value. This is shown in the good_fn below.
Apologies in advance if you already know about this one, or this is someone
intended behaviour.
Nick.
--------------- snip ---------------------
create table a (
p1 integer primary key,
v1 integer
);
create sequence s;
create table b (
p2 serial,
fk integer not null references a
);
create rule a_ins AS ON INSERT TO a DO INSERT INTO b (fk) values (NEW.p1);
create or replace function bad_fn() returns integer AS '
DECLARE
result INTEGER;
BEGIN
EXECUTE '' insert into a values (nextval(''''s''''),
2); '';
result := 0;
return result;
END;
' language plpgsql;
create or replace function good_fn() returns integer AS '
DECLARE
result INTEGER;
BEGIN
result := 0;
EXECUTE '' insert into a values ('' || nextval(''s'') || '', 2); '';
return result;
END;
' language plpgsql;
select good_fn();
select good_fn();
select bad_fn();
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly
|