Hi,
The bit of SQL below does not behave the way it should on postgres 8.4.4 (t=
ested by me) and 9.0.3 (verified independently on #postgresql).
The third statement in the quux() function calls the a_bar() function that =
should find a single row in the 'bar' table and return its value. This sing=
le row is INSERTed into the 'bar' table on the previous line. However, the =
SELECT statement in the a_bar() function throws the following error: "ERROR=
: query returned no rows". It thus appears not to see the INSERTed value i=
n the 'bar' table. (The expected behavior is that the a_bar() function retu=
rns the value 500 instead of throwing an error.)
Removing the STABLE attribute from a_bar() works around the problem, as doe=
s moving the "INSERT INTO bar ..." statement out of the quux() function and=
executing it before calling the quux() function itself.
Some initial debugging by RhodiumToad on #postgresql led to the following o=
bservation: The error occurs only when the "SELECT ... WHERE i =3D a_bar();=
" is being planned, not when it is being executed, with the snapshot being =
used to plan the query apparently being too old to see the result of the pr=
eceding insert.
By the way: the EXECUTE around the SELECT in the a_bar() function is probab=
ly not required to trigger the bug, but this is the version we tested.
Regards,
Matthijs Bomhoff
BEGIN;
CREATE TABLE foo(i INTEGER);
CREATE TABLE bar(i INTEGER);
CREATE OR REPLACE FUNCTION a_bar() RETURNS INTEGER AS $EOF$
DECLARE
result INTEGER;
BEGIN
EXECUTE 'SELECT i FROM bar' INTO STRICT result;
RETURN result;
END
$EOF$ LANGUAGE plpgsql STABLE;
CREATE OR REPLACE FUNCTION quux() RETURNS INTEGER AS $EOF$
DECLARE
result INTEGER;
BEGIN
INSERT INTO foo(i) SELECT s.a FROM generate_series(1,1000,1) s(a);
INSERT INTO bar(i) VALUES(500);
SELECT INTO STRICT result COUNT(*) FROM foo WHERE i =3D a_bar();
RETURN result;
END
$EOF$ LANGUAGE plpgsql;
SELECT quux();
ROLLBACK;
