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), this leads me to believe there must be some reason why this "obvious" mistake is allowed to exist.

Solution

This is quite an interesting question and I would encourage you to take it up on the PostgreSQL email lists.

The short answer is that these are enforced by what they do, not by what functions they call.

This allows you to do some rather interesting things. For example, suppose I want a value that will create exactly one random number per query:

create function rand_im() returns double precision language sql immutable as 
$$ select random(); $$;

Now I have an immutable random number function, and it can be optimized into a single call which occurs before the query plan is formed and therefore can be used to determine if indexes are helpful:

chris=# explain analyse select * from profile_matches where id > (4 * rand_im());
                                                 QUERY PLAN                     

--------------------------------------------------------------------------------
-----------------------------
 Seq Scan on profile_matches  (cost=0.00..39.10 rows=647 width=12) (actual time=
0.009..0.010 rows=1 loops=1)
   Filter: ((id)::double precision > 3.24490546248853::double precision)
 Total runtime: 0.022 ms
(3 rows)

So here we have an "immutable" which calls a clearly volatile function and this is actually useful, because it allows us to create a function which will be flattened, and can provide exactly one value for a query.

Now, as per the other link I do not really know what happens regarding stable functions calling volatile functions. As far as I can tell this is just an optimization thing and the protections are pretty minimal but I wouldn't guarantee that db side effects will be always allowed.

Further, a simple thing to consider is that the functions are planner-opaque, and it isn't possible to prevent this sort of thing on all procedural languages. Consider you could have a Java function that could be labelled as immutable but could run queries (which is probably not a good thing). So it may seem like an obvious mistake but it is actually a potentially useful one.