| 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: | Whole Thread | Raw Message | 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 |