While doing some tests, I observed that expression indexes can malfunction if the underlying expression changes. For example, say I define a function foo() as:
$$ LANGUAGE plpgsql IMMUTABLE;
I then create a table, an expression index on the table and insert a few rows:
CREATE TABLE test (a int, b char(20));
CREATE UNIQUE INDEX testindx ON test(foo(a));
INSERT INTO test VALUES (generate_series(1,10000), 'bar');
A query such as following would return result using the expression index:
SET enable_seqscan TO off;
SELECT * FROM test WHERE foo(a) = 100;
It will return row with a = 99 since foo() is defined to return (a + 1)
If I now REPLACE the function definition with something else, say to return (a + 2):
CREATE OR REPLACE FUNCTION foo(a integer) RETURNS integer AS $$
BEGIN
RETURN $1 + 2;
END;
$$ LANGUAGE plpgsql IMMUTABLE;
I get no error/warnings, but the index and the new function definition are now out of sync. So above query will still return the same result, though the row with (a = 99) no longer satisfies the current definition of function foo().
Perhaps this is a known behaviour/limitation, but I could not find that in the documentation. But I wonder if it makes sense to check for dependencies during function alteration and complain. Or there are other reasons why we can't do that and its a much larger problem than what I'm imagining ?
Thanks,
Pavan
--
Pavan Deolasee
http://www.linkedin.com/in/pavandeolasee