Re: hidden errors calling a volatile function inside a stable function

Merlin Moncure wrote:
> On Thu, Nov 20, 2008 at 6:21 AM, Sabin Coanda
> <sabin.coanda@deuromedia.ro> wrote:
>> Hi,
>>
>> I have "PostgreSQL 8.2.4 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC)
>> 3.4.2 (mingw-special)" on Windows OS , but I experienced the same problem on
>> "PostgreSQL 8.3.5, compiled by Visual C++ build 1400" too.
>>
>> I attach the demo database here, to be available to test according with the
>> following scenario.
>>
>> I found in a STABLE function, for instance "TEST_0"(), it is not allowed to
>> use INSERT statement. Trying this will give me the error:
>> ERROR: INSERT is not allowed in a non-volatile function
>
> Why did you lie when your declared your function stable? :-)

I suspect the point here is that PostgreSQL tries to stop you violating
your claims about the function's stability by preventing DML operations.
However, it doesn't also attempt to prevent invocation of volatile
functions. When you annotate a function as STABLE you are making a
guarantee to PostgreSQL that the function is stable, rather than telling
PostgreSQL to force the function to behave as STABLE.

This is probably reasonable enough - after all, a volatile function
could even be invoked via an ON SELECT ... DO INSTEAD rule that
transforms a statement, so the function doesn't even know about it. It
could also be invoked dynamically via EXECUTE, which would probably be
tricky to trap. There are more issues than DML when it comes to STABLE
functions, too.

I do wonder if making STABLE functions execute as if `SET
transaction_read_only = 1' had been issued is possible/practical. Would
that work, and would it provide a way to trap at least most unsafe
operations without undue overhead?

--
Craig Ringer