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

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

From: Matthijs Bomhoff <matthijs(at)quarantainenet(dot)nl>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Bug with STABLE function using the wrong snapshot (probably during planning)
Date: 2011-03-22 15:31:47
Message-ID: DF84581C-FFCF-42D7-AB80-50872A677BC6@quarantainenet.nl (view raw or flat)
Thread:
Lists: pgsql-bugs
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;

Responses

pgsql-bugs by date

Next:From: Dave PageDate: 2011-03-22 15:45:33
Subject: Re: BUG #5938: PostgreSQL Installer outputs log file with superuser password in clear text
Previous:From: Alvaro HerreraDate: 2011-03-22 14:51:38
Subject: Re: BUG #5937: initdb: FATAL error: could not open relation with OID 2608

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