Bug #729: replacing a function used in an index yields incorrect query results.

Kris Jurka (jurka(at)ejurka(dot)com) reports a bug with a severity of 3
The lower the number the more severe it is.

Short Description
replacing a function used in an index yields incorrect query results.

Long Description
Using current sources a functional index contains inaccurate data when a function is replaced. The new dependency system prevents dropping the function because it realizes the index depends on the function, but it does allow the function to be replaced.

There are a number of checks in src/backend/catalog/pg_proc.c ProcedureCreate to guard against in improper replacement (can't change return type, aggregate status), but there are no checks against changing the return result or the provolatile flag.

Clearly the function result cannot be checked, so perhaps marking the functional index invalid or rebuilding it is possible.

For the provolatile flag, I would suggest functions can move up in stability (volatile -> stable or stable-> immutable), but not down (stable->volatile).

Sample Code
CREATE TABLE t (a int);

CREATE FUNCTION f(int) RETURNS int AS 'SELECT 1' LANGUAGE SQL IMMUTABLE;

CREATE UNIQUE INDEX t_fa_idx ON t (f(a));

INSERT INTO t VALUES (1);

CREATE OR REPLACE FUNCTION f(int) RETURNS int AS 'SELECT 2' LANGUAGE SQL IMMUTABLE;

INSERT INTO t VALUES (2);

SET enable_indexscan = 1;
SELECT * FROM t WHERE f(a) = 2;

SET enable_indexscan = 0;
SELECT * FROM t WHERE f(a) = 2;

DROP INDEX t_fa_idx;
DROP FUNCTION f(int);
DROP TABLE t;

No file was uploaded with this report