Обсуждение: Bug #729: replacing a function used in an index yields incorrect query results.
Bug #729: replacing a function used in an index yields incorrect query results.
От
pgsql-bugs@postgresql.org
Дата:
Kris Jurka (jurka@ejurka.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 systemprevents dropping the function because it realizes the index depends on the function, but it does allow the functionto be replaced. There are a number of checks in src/backend/catalog/pg_proc.c ProcedureCreate to guard against in improper replacement (can'tchange return type, aggregate status), but there are no checks against changing the return result or the provolatileflag. 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