Thread: BUG #11637: SQL function volatility is ignored on index creation

The following bug has been logged on the website:

Bug reference:      11637
Logged by:          Will Glynn
Email address:      will@willglynn.com
PostgreSQL version: 9.3.5
Operating system:   Mac OS X
Description:

Steps to reproduce:
    CREATE FUNCTION immutable_but_marked_volatile() RETURNS integer AS $$
    SELECT floor(random() * 4)::integer;
    $$ LANGUAGE sql VOLATILE;
    CREATE TABLE foo (bar int);
    CREATE INDEX foo_index ON foo (immutable_but_marked_volatile());

Expected results:
    CREATE FUNCTION
    CREATE TABLE
    ERROR:  functions in index expression must be marked IMMUTABLE

Actual results:
    CREATE FUNCTION
    CREATE TABLE
    CREATE INDEX

The CREATE INDEX documentation and the wording of that error message
strongly imply that it's a function's volatility *marking* that is
important, but in at least some situations, PostgreSQL ignores
pg_proc.provolatile='v' and allows index creation anyway if the function is
in fact immutable. I suspect this qualifier is ignored because the SQL
function call is being inlined prior to the CheckMutability() in
ComputeIndexAttrs(); there isn't a volatile function call, just an immutable
expression.

Proposed solutions:
1. Prevent index creation on VOLATILE functions in all situations, i.e. even
if the function call is inlined away.
2. Update the CREATE INDEX documentation and error message to indicate that
certain VOLATILE functions are acceptable for use in indexes if PostgreSQL
can demonstrate that they are not, in fact, actually volatile.

will@willglynn.com writes:
> The CREATE INDEX documentation and the wording of that error message
> strongly imply that it's a function's volatility *marking* that is
> important, but in at least some situations, PostgreSQL ignores
> pg_proc.provolatile='v' and allows index creation anyway if the function is
> in fact immutable. I suspect this qualifier is ignored because the SQL
> function call is being inlined prior to the CheckMutability() in
> ComputeIndexAttrs(); there isn't a volatile function call, just an immutable
> expression.

That's correct, and it's intentional behavior, not a bug (cf commit
5a86e5e1930d95f495a134000512d6ca22064338).  Refusing the CREATE would
just be pedantry AFAICS.

            regards, tom lane

On Oct 10, 2014, at 12:58 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> will@willglynn.com writes:
>> The CREATE INDEX documentation and the wording of that error message
>> strongly imply that it's a function's volatility *marking* that is
>> important, but in at least some situations, PostgreSQL ignores
>> pg_proc.provolatile='v' and allows index creation anyway if the function is
>> in fact immutable. I suspect this qualifier is ignored because the SQL
>> function call is being inlined prior to the CheckMutability() in
>> ComputeIndexAttrs(); there isn't a volatile function call, just an immutable
>> expression.
>
> That's correct, and it's intentional behavior, not a bug (cf commit
> 5a86e5e1930d95f495a134000512d6ca22064338).  Refusing the CREATE would
> just be pedantry AFAICS.

Since this is a feature, my proposed solution #2 -- updating the
documentation to reflect this behavior -- seems appropriate.

The docs and my previous exposure to this error message made me think that
the IMMUTABLE marking was the key requirement, so when I saw a VOLATILE
function getting used in an index, it surprised me enough that I went digging
into the source to find out how that can happen.

Also, FWIW I've come to value that PostgreSQL errs on the side of pedantry.
I genuinely didn't expect CREATE INDEX to peer into a VOLATILE function and
silently deduce that it's actually okay to use anyway. I'm not arguing that
this is wrong, it's just... more clever than I thought.

Hmm... could CREATE FUNCTION do a similar analysis and notify you if you're
creating a VOLATILE function that doesn't actually need to be VOLATILE?

--Will Glynn