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

On May 13, 2011, at 12:46 AM, Tom Lane wrote:

> Noah Misch <noah@leadboat.com> writes:
>>> Some initial debugging by RhodiumToad on #postgresql led to the followi=
ng observation: The error occurs only when the "SELECT ... WHERE i =3D a_ba=
r();" is being planned, not when it is being executed, with the snapshot be=
ing used to plan the query apparently being too old to see the result of th=
e preceding insert.
>=20
>> The simplest fix I can see is to have _SPI_prepare_plan() push/pop a new
>> snapshot when analyze_requires_snapshot() returns true on the raw parse =
tree.
>> That strategy can break down in the other direction if the caller is STA=
BLE;
>> consider this example:
>=20
> Yes.  I'm of the opinion that we should not change this.  In general,
> marking functions STABLE that have major side effects (such as throwing
> errors) is not a good idea, and putting such things into WHERE clauses
> is a worse one.  We explicitly do not guarantee anything about timing or
> order of evaluation in WHERE clauses, because to do so would cripple the
> planner's ability to optimize them.  So I think this is a "don't do
> that" case rather than "we should try to make planning happen with the
> same snapshot that will be used at execution" case.

First of all, thanks to both of you for your replies to my email; at least =
now I understand a bit more of what went wrong.

As I know almost nothing about the internals, I am not one to argue about w=
hether or not to change the current behavior. It would however perhaps be n=
ice to have the documentation of the volatility categories mention explicit=
ly that throwing an error is also considered a (major) side-effect. In part=
icular: apparently not only are modifications to the database itself consid=
ered side-effects, but so is "irregular" control flow within a procedural l=
anguage... Based on the current documentation, I thought that as my functio=
n made no changes to the database and returns the same results given the sa=
me arguments for all rows within a single statement, it could safely be mar=
ked as STABLE.

Regards,

Matthijs