Bug with STABLE function using the wrong snapshot (probably during planning)

Hi,

The bit of SQL below does not behave the way it should on postgres 8.4.4 (tested 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 single 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 in the 'bar' table. (The expected behavior is that the a_bar() function returns the value 500 instead of throwing an error.)

Removing the STABLE attribute from a_bar() works around the problem, as does 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 observation: The error occurs only when the "SELECT ... WHERE i = 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 preceding insert.

By the way: the EXECUTE around the SELECT in the a_bar() function is probably 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 = a_bar();
RETURN result;
END
$EOF$ LANGUAGE plpgsql;

SELECT quux();

ROLLBACK;