Hello,
I have a problem with functional index feature in Postgres 8.3
There are two tables, lets call them: PARENTS and CHILDREN(with timestamp column)
I created functional index on parents with function, which selects max value of timestamp from child elements(for given parent_id).
The problem is that plpgsql function, which returns the value is IMMUTABLE and it works like a cache. When I insert new record to children table, select over parents with function gives wrong(outdated) results.
So far I figured out only one way to flush this "cache". It's by calling REINDEX on my index. I guess I should call it after every insert to children table. It's not good for me since it locks the table.
I'm thinking about partitioning the index by my app, so reindexing will be less painful, but perhaps there is some other easier way to solve tihs problem?
Thanks
--
Regards,
Grzegorz