From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | ia(dot)shumilova(at)gmail(dot)com |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #9175: REINDEX on functional index fails |
Date: | 2014-02-10 15:50:56 |
Message-ID: | 10815.1392047456@sss.pgh.pa.us |
Views: | Whole Thread | Raw Message | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
ia(dot)shumilova(at)gmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2014-02-10 16:04:36 | Re: index scan is performed when index-only scan is possible (partial index) |
Previous Message | Alexey Bashtanov | 2014-02-10 12:33:43 | index scan is performed when index-only scan is possible (partial index) |