From: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
---|---|
To: | Grzegorz Tańczyk <goliatus(at)polzone(dot)pl> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Refreshing functional index |
Date: | 2012-08-29 21:06:44 |
Message-ID: | CAHyXU0zPP=nyJRV4QrZ8-i2b32oAGXrq3M55Uc26u2BJS_orug@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | David Johnston | 2012-08-29 21:22:10 | Re: Refreshing functional index |
Previous Message | Grzegorz Tańczyk | 2012-08-29 21:01:43 | Refreshing functional index |