Re: Defer a functional index calculation?

On Fri, Mar 20, 2009 at 05:37:33PM -0700, Randall Lucas wrote:
> I added a functional index.
>
> create table example (id serial primary key, stuff text, parent_id int);
> create index example_root_idx on example (get_root_id(id));
>
> (get_root_id(id) pulls an example row and recurses onto parent_id until it
> hits a root)

I don't think you can do that; are you lying in the function's
definition that it's "immutable"? As far as I know, and a quick check
of the manual[1] seems to confirm it, all functions used in functional
indexes must be immutable and the execution of an immutable function
can't depend on any data in the database. If you're creating this
function as "immutable" then this is lying to the database and it's
guaranteed to go wrong at some point (as you seem to be finding out).

What about having some trigger to "cache" the entry's root "parent_id"
in another column?

--
Sam http://samason.me.uk/

[1] http://www.postgresql.org/docs/current/static/sql-createindex.html