Hello, 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:
CREATE OR REPLACE FUNCTION foo(a integer) RETURNS integer AS $$ BEGIN RETURN $1 + 1; END; $$ 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
