On Wed, Aug 29, 2012 at 4:01 PM, Grzegorz Tańczyk <goliatus@xxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
