From: | Grzegorz Tańczyk <goliatus(at)polzone(dot)pl> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Refreshing functional index |
Date: | 2012-08-29 21:01:43 |
Message-ID: | 5010372.654641346273963189.JavaMail.root@Polzone |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Merlin Moncure | 2012-08-29 21:06:44 | Re: Refreshing functional index |
Previous Message | Grzegorz Tańczyk | 2012-08-29 20:50:17 | Refreshing functional index |