Skip site navigation (1) Skip section navigation (2)

BUG #1705: nextval being evaluated more than once

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 (view raw or flat)
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();

pgsql-bugs by date

Next:From: Tom LaneDate: 2005-06-08 03:32:13
Subject: Re: pg_ctl inappropriately timing out?
Previous:From: Alvaro HerreraDate: 2005-06-08 00:27:12
Subject: Re: pg_ctl inappropriately timing out?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group