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 (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;