BUG #8516: Calling VOLATILE from STABLE function

On Wed, Oct 9, 2013 at 08:58:46PM +0000, dwayne@docketnavigator.com wrote:

The following bug has been logged on the website:

Bug reference: 8516
Logged by: Dwayne Towell
Email address: dwayne@docketnavigator.com
PostgreSQL version: 9.2.4
Operating system: CentOS
Description:

Why doesn't PostgreSQL give a warning when calling a volatile function from
a stable function?

For example:
CREATE TABLE x (val double);

CREATE FUNCTION g() RETURNS boolean AS $$
INSERT INTO x SELECT rand() RETURNING val>0.5;
$$ LANGUAGE SQL VOLATILE;

CREATE FUNCTION f() RETURNS boolean AS $$
SELECT g(); -- this is where the stability-violation happens
$$ LANGUAGE SQL STABLE; -- this is a lie

According to the documentation, f() should be marked VOLATILE also, since
calling f() produces side effects. PostgreSQL does not give a warning (or
better yet, an error); I think it should.

I think the answer is that function authors are required to prevent
functions they mark as STABLE from calling VOLATILE functions.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

According to the documentation, f() should be marked VOLATILE also, since
calling f() produces side effects. PostgreSQL does not give a warning (or
better yet, an error); I think it should.

I think the answer is that function authors are required to prevent
functions they mark as STABLE from calling VOLATILE functions.

--

I understand it's an error (at least usually), my question/issue is why does
PostgreSQL NOT give at least a warning when a programmer (probably
accidentally) calls a VOLATILE function in one that he has specifically
tagged as STABLE? The compiler has all the information to notify the
programmer of a mistake, but isn't. This violates a fundamental principle of
software engineering--take every opportunity to prevent errors.

Dwayne

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

On Thu, Oct 10, 2013 at 04:10:35PM -0700, Dwayne Towell wrote:

According to the documentation, f() should be marked VOLATILE also, since
calling f() produces side effects. PostgreSQL does not give a warning (or
better yet, an error); I think it should.

I think the answer is that function authors are required to prevent
functions they mark as STABLE from calling VOLATILE functions.

--

I understand it's an error (at least usually), my question/issue is why does
PostgreSQL NOT give at least a warning when a programmer (probably
accidentally) calls a VOLATILE function in one that he has specifically
tagged as STABLE? The compiler has all the information to notify the
programmer of a mistake, but isn't. This violates a fundamental principle of
software engineering--take every opportunity to prevent errors.

Well, we can't walk the function tree to know all called functions, and
those they call, so we don't even try.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

'Bruce Momjian' <bruce@momjian.us> writes:

Well, we can't walk the function tree to know all called functions, and
those they call, so we don't even try.

Inter function dependencies is a hard topic indeed. I still would like
to see some kind of progress being made someday. The general case is
turing complete tho, because you can use EXECUTE against programatically
generated SQL.

You could even generate a CREATE FUNCTION command from within a PL
function and EXECUTE it then call the created function… and I think I've
seen people do that in the past.

Still some kind of limited in scope static analysis for the cases where
it's possible to do so would be great. With pg_depend tracking so that
you know you're doing something wrong at DROP FUNCTION time.

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

On Oct 11, 2013, at 9:21 AM, Dimitri Fontaine <dimitri@2ndQuadrant.fr> wrote:

Inter function dependencies is a hard topic indeed. I still would like
to see some kind of progress being made someday. The general case is
turing complete tho, because you can use EXECUTE against programatically
generated SQL.

You could even generate a CREATE FUNCTION command from within a PL
function and EXECUTE it then call the created function… and I think I've
seen people do that in the past.

Still some kind of limited in scope static analysis for the cases where
it's possible to do so would be great. With pg_depend tracking so that
you know you're doing something wrong at DROP FUNCTION time.

I'm not very familiar with PostgreSQL internals, so I might be way off here, and I'm asking as much out of curiousity as anything else…

Would it be possible (and make sense) to solve this in a completely different way, not walking the function tree or doing static analysis, but simply setting and checking a bit during execution?

That is, when you execute a STABLE function, set a bit (clear it when function is done), and always check it when executing any VOLATILE function?

If a volatile function checks the bit, and you're "inside" a stable function, you could then raise an exception for calling volatile inside stable?

Terje

Terje Elde <terje@elde.net> writes:

Would it be possible (and make sense) to solve this in a completely different way, not walking the function tree or doing static analysis, but simply setting and checking a bit during execution?

While it's possible that we could do something like that, I think it's
fairly unlikely that we would. The reason is that it would disable
constructs that some people find useful; that is, sometimes it's
intentional that a stable function calls a volatile one.

A couple of examples:

1. You might want to make some database updates but continue to do queries
with a pre-update snapshot. A single function can't accomplish that,
but the combination of a stable outer function with a volatile update
function can.

2. A security checking function (for use with Veil or the proposed row
security feature) might wish to log accesses without denying them. To
do that it'd have to be volatile, so if we had a restriction like this
the function would fail when invoked within a stable function.

You can imagine various ways around such issues, but it would add a lot
of complication.

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs