Re: Refreshing functional index

On Wed, Aug 29, 2012 at 4:01 PM, Grzegorz Tańczyk <goliatus(at)polzone(dot)pl> wrote:
> 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?

Well, the only reason what you're trying to do works at all is because
the database isn't stricter about double checking to see if your stuff
is IMMUTABLE: it isn't, so of course it doesn't work.

How about a trigger on the child table that updates an indexed column
on parent?

merlin