Re: Refreshing functional index

Hello,

W dniu 2012-08-29 23:06, Merlin Moncure pisze:
> 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

According to docs:
"AnIMMUTABLEfunction cannot modify the database and is guaranteed to
return the same results given the same arguments forever."

My tables look like this:
CREATE TABLE groups (
id serial PRIMARY KEY,
last_item integer REFERENCES items
) WITHOUT OIDS;

CREATE TABLE items (
id serial PRIMARY KEY,
group integer NOT NULL REFERENCES groups,
ts timestamp DEFAULT now()
) WITHOUT OIDS;

The index:
CREATE INDEX groups_last_ts
ON groups
USING btree
(items_ts(last_post));

Plpgsql function items_ts returns timestamp for given item, which will
never change(that's my assumption), so in fact according to definition
IT IS immutable fuction.

Unfortunately, whenever I update last_item column in groups, I get wrong
results, so I query like this:

SELECT * FROM groups WHERE items_ts(last_item) > now() - interval '1 week'

returns "outdated" results

I do realize about other ways for solving this problem, however I would
prefer if it worked in the way described above.

Thanks!

--
Regards,
Grzegorz