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.