From: | "Nick Farrell" <nick(at)farrell(dot)name> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #1705: nextval being evaluated more than once |
Date: | 2005-06-08 03:18:03 |
Message-ID: | 20050608031803.3F6CDF0B13@svr2.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged online:
Bug reference: 1705
Logged by: Nick Farrell
Email address: nick(at)farrell(dot)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();
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2005-06-08 03:32:13 | Re: pg_ctl inappropriately timing out? |
Previous Message | Alvaro Herrera | 2005-06-08 00:27:12 | Re: pg_ctl inappropriately timing out? |