Re: Re: Bug with STABLE function using the wrong snapshot (probably during planning) - Mailing list pgsql-bugs

From Matthijs Bomhoff
Subject Re: Re: Bug with STABLE function using the wrong snapshot (probably during planning)
Date
Msg-id AAE5B068-2945-4D51-84BC-4248B526B657@quarantainenet.nl
Whole thread Raw
In response to Re: Re: Bug with STABLE function using the wrong snapshot (probably during planning)  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-bugs
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

pgsql-bugs by date:

Previous
From: Tom Lane
Date:
Subject: Re: BUG #6021: There is no difference between default and empty access privileges with \dp
Next
From: Pascal Borschneck
Date:
Subject: Re: BUG #5984: Got FailedAssertion("!(opaque->btpo_prev == target)", File: "nbtpage.c", Line: 1166)