Re: BUG #15060: Row in table not found when using pg function in an expression

>>>>> "PG" == PG Bug reporting form <noreply(at)postgresql(dot)org> writes:

PG> I tested this in postgresql versions 9.1, 10.1 and 10.2 on centOS.

PG> In short this is what happens (in a plpgsql function):
PG> 1.) An insert is done into 'bug' table
PG> 2.) A SELECT is done to make sure the INSERT was successful
PG> 3.) Another function (get_bug_id) is called which returns id based on
PG> value.
PG> When the function is called directly, it returns the id correctly.
PG> When it's called in an expression, it does not find the inserted
PG> row and an exception is raised.

So what's happening here is that the function get_bug_id, being stable,
is being called speculatively at plan time for the query where it
appears in the WHERE clause. For whatever reason, the snapshot it's
being run in at that time is not the same one actually used for the
later execution of the query, and the plan-time snapshot doesn't see the
just-inserted row.

It looks like what's going on here is that SPI does GetCachedPlan -
which is where planning will happen - _before_ establishing the new
snapshot in the non-read-only case (read_only is false here because the
calling function, test_bug(), is volatile).

--
Andrew (irc:RhodiumToad)