Обсуждение: 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