Refreshing functional index

From: Grzegorz Tańczyk <goliatus(at)polzone(dot)pl>
To: pgsql-general(at)postgresql(dot)org
Subject: Refreshing functional index
Date: 2012-08-29 20:50:17
Message-ID: 18109421.652061346273278893.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 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 in my app, so reindexing will
be less painful, but perhaps there is some other easier way to solve
tihs problem?

Thanks

--
Regards,
Grzegorz

Browse pgsql-general by date

  From Date Subject
Next Message Grzegorz Tańczyk 2012-08-29 21:01:43 Refreshing functional index
Previous Message Scott Marlowe 2012-08-29 20:32:35 Re: C locale versus en_US.UTF8. (Was: String comparision in PostgreSQL)