From: | Pavan Deolasee <pavan(dot)deolasee(at)gmail(dot)com> |
---|---|
To: | pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Expression indexes and dependecies |
Date: | 2013-07-22 18:29:29 |
Message-ID: | CABOikdNLxn3UJ3zL1MAk-LYaybohiY0_dKxCxxGmvaJRg2kvOQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Greg Smith | 2013-07-22 18:31:56 | Re: Performance Improvement by reducing WAL for Update Operation |
Previous Message | Robert Haas | 2013-07-22 18:10:53 | Re: Foreign Tables as Partitions |