Обсуждение: Expression indexes and dependecies
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;
--
Pavan Deolasee
http://www.linkedin.com/in/pavandeolasee
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
Pavan Deolasee escribió: > Hello, > > While doing some tests, I observed that expression indexes can malfunction > if the underlying expression changes. [...] > 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 ? This is a tough problem. The dependency mechanism has no way to keep track of this kind of dependency; all it does is prevent the function from being dropped altogether, but preventing it from acquiring a conflicting definition is outside its charter. One way to attack this would be registering dependencies of a new kind on functions used by index expressions. Then CREATE OR REPLACE function could reject alteration for such functions. I don't know if we care enough about this case. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
On Mon, Jul 22, 2013 at 6:04 PM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote: > Pavan Deolasee escribió: >> Hello, >> >> While doing some tests, I observed that expression indexes can malfunction >> if the underlying expression changes. > > [...] > >> 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 ? > > This is a tough problem. The dependency mechanism has no way to keep > track of this kind of dependency; all it does is prevent the function > from being dropped altogether, but preventing it from acquiring a > conflicting definition is outside its charter. > > One way to attack this would be registering dependencies of a new kind > on functions used by index expressions. Then CREATE OR REPLACE function > could reject alteration for such functions. I don't know if we care > enough about this case. What about a warning and leave it to the dba to reindex?
On 2013-07-22 17:04:06 -0400, Alvaro Herrera wrote: > Pavan Deolasee escribió: > > Hello, > > > > While doing some tests, I observed that expression indexes can malfunction > > if the underlying expression changes. > > [...] > > > 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 ? > > This is a tough problem. The dependency mechanism has no way to keep > track of this kind of dependency; all it does is prevent the function > from being dropped altogether, but preventing it from acquiring a > conflicting definition is outside its charter. > > One way to attack this would be registering dependencies of a new kind > on functions used by index expressions. Then CREATE OR REPLACE function > could reject alteration for such functions. I don't know if we care > enough about this case. I think changing the results of a immutable function violates the contract enough to make this the user's fault. Also the other solutions seem hard to achieve ;) Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services
Andres Freund <andres@2ndquadrant.com> writes:
> On 2013-07-22 17:04:06 -0400, Alvaro Herrera wrote:
>> One way to attack this would be registering dependencies of a new kind
>> on functions used by index expressions. Then CREATE OR REPLACE function
>> could reject alteration for such functions. I don't know if we care
>> enough about this case.
> I think changing the results of a immutable function violates the
> contract enough to make this the user's fault. Also the other solutions
> seem hard to achieve ;)
Yeah. Prohibiting any change at all would be a cure worse than the
disease, likely, but we don't have the tools to analyze more finely than
that. And what if the index uses function A which calls function B,
and you change function B?
I'd be in favor of adding a note to the CREATE INDEX man page pointing
out that if you change the behavior of an immutable function, any bad
consequences for indexes are on your own head, and a REINDEX would be
advisable.
regards, tom lane
On Tue, Jul 23, 2013 at 4:51 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
--
Pavan Deolasee
http://www.linkedin.com/in/pavandeolasee
Andres Freund <andres@2ndquadrant.com> writes:
> On 2013-07-22 17:04:06 -0400, Alvaro Herrera wrote:>> One way to attack this would be registering dependencies of a new kindYeah. Prohibiting any change at all would be a cure worse than the
>> on functions used by index expressions. Then CREATE OR REPLACE function
>> could reject alteration for such functions. I don't know if we care
>> enough about this case.
> I think changing the results of a immutable function violates the
> contract enough to make this the user's fault. Also the other solutions
> seem hard to achieve ;)
disease, likely, but we don't have the tools to analyze more finely than
that. And what if the index uses function A which calls function B,
and you change function B?
Right. I was gonna suggest that if can mark the index invalid if a dependent immutable function is being changed, but that clearly does not solve the case of nested function calls and we don't have any mechanism to track that either.
I'd be in favor of adding a note to the CREATE INDEX man page pointing
out that if you change the behavior of an immutable function, any bad
consequences for indexes are on your own head, and a REINDEX would be
advisable.
Ok. I will write up something and submit a patch. Constraints probably also suffer from the same issue. Whats surprising is we don't mandate that the functions used in CHECK constraint are immutable (like we do for indexes). What that means is, even if a row was satisfying a constraint while insertion, it may not once its there. Is that intentional ?
Thanks,
Pavan
Pavan Deolasee
http://www.linkedin.com/in/pavandeolasee
Pavan Deolasee <pavan.deolasee@gmail.com> writes:
> Ok. I will write up something and submit a patch. Constraints probably also
> suffer from the same issue. Whats surprising is we don't mandate that the
> functions used in CHECK constraint are immutable (like we do for indexes).
> What that means is, even if a row was satisfying a constraint while
> insertion, it may not once its there. Is that intentional ?
Well, it's probably somewhat historical, but I doubt we'd want to
tighten it up now. Here's an example of a sensible CHECK that's
only stable:
create ... last_update timestamptz check (last_update <= now()) ...
More generally, I think the argument was that the behavior of a
non-immutable CHECK would at least be easy to understand, assuming you
know that the check will only be applied at row insertion or update.
Non-immutable indexes could misbehave in much less obvious ways, for
instance causing the results of a query to differ depending on whether
the planner chose to use that index.
regards, tom lane
On Thu, Jul 25, 2013 at 6:43 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
This constraint would most likely fail if someone was to restore the table from a dump.
Well, it's probably somewhat historical, but I doubt we'd want to
tighten it up now. Here's an example of a sensible CHECK that's
only stable:
create ... last_update timestamptz check (last_update <= now()) ...
Agree. That looks like a very sensible use case and something not possible without support for mutable functions.
More generally, I think the argument was that the behavior of a
non-immutable CHECK would at least be easy to understand, assuming you
know that the check will only be applied at row insertion or update.
But they are also prone to unexpected behaviour, no ? For example, a slight variation of the above example is:
create ... last_update timestamptz check (last_update <= now() and last_update >= now() - '1 week'::interval) ...
Given that we haven't seen any complaints may mean I am imagining a problem that does not exist in practice, though I thought the example looks quite sensible too.
Thanks,
Pavan Deolasee <pavan.deolasee@gmail.com> writes:
> On Thu, Jul 25, 2013 at 6:43 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> More generally, I think the argument was that the behavior of a
>> non-immutable CHECK would at least be easy to understand, assuming you
>> know that the check will only be applied at row insertion or update.
> But they are also prone to unexpected behaviour, no ? For example, a slight
> variation of the above example is:
> create ... last_update timestamptz check (last_update <= now() and
> last_update >= now() - '1 week'::interval) ...
> This constraint would most likely fail if someone was to restore the table
> from a dump.
Sure, but the reason for the failure would be entirely obvious. It
might be annoying, but it'd still be obvious --- and not too hard to
fix, either. The prohibition on mutable index functions is because you
might waste a great deal of time on diagnosing the reason for a problem.
Now, I grant that that argument could also be used to justify trying
harder than we do now to detect not-really-immutable index functions,
or for trying harder than we do now to prevent you from changing an
index function's behavior. I'm not opposed in principle to tightening
those checks more; I'm just doubtful that we can easily make things
better there.
regards, tom lane