Re: BUG #9175: REINDEX on functional index fails - Mailing list pgsql-bugs

From Tom Lane
Subject Re: BUG #9175: REINDEX on functional index fails
Date
Msg-id 10815.1392047456@sss.pgh.pa.us
Whole thread Raw
In response to BUG #9175: REINDEX on functional index fails  (ia.shumilova@gmail.com)
List pgsql-bugs
ia.shumilova@gmail.com writes:
> -- function for tree structure assembling
> -- outputs something like 'level0/level1/level2/leaf'
> CREATE OR REPLACE FUNCTION taxonomy_string(tree_detail)
>   RETURNS text AS
> $BODY$
>   SELECT
>     COALESCE(string_agg(tax_entry, '/'), '')
>   FROM
>     (
>     SELECT
>       tax_entry
>     FROM
>       tree_detail
>     WHERE
>       _left <= $1._left
>       AND _right >= $1._right
>       AND tree_master_id = $1.tree_master_id
>     ORDER BY _level
>     ) u
> $BODY$
>   LANGUAGE sql IMMUTABLE;

I don't have a whole lot of sympathy for a bug report that involves
a function claimed to be IMMUTABLE when that marking is a lie.
This function selects from tree_detail, so it can at best be claimed
to be STABLE; which means you cannot use it in an index.

I think the proximate cause of the reported error is that once the
index exists, the query in the function tries to use it; and then
that usage will fail when the index is in mid-rewrite.  It might
be worth trying to make that fail more cleanly, if index functions
that depend on selecting from their table were a supported thing;
but they aren't and never will be.

You might be able to get the results you want by including the
taxonomy_string() function in a materialized view over the table.

            regards, tom lane

pgsql-bugs by date:

Previous
From: ia.shumilova@gmail.com
Date:
Subject: BUG #9175: REINDEX on functional index fails
Next
From: Tom Lane
Date:
Subject: Re: index scan is performed when index-only scan is possible (partial index)