Re: Refreshing functional index - Mailing list pgsql-general

>>Grzegorz>>
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
checkingto see if your stuff is IMMUTABLE: it isn't, so of course it doesn't work. How about a trigger on the child
tablethat updates an indexed column on parent? merlin  

According to docs:
"An IMMUTABLE function 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
accordingto 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.

>>/Grzegorz>>


From before you wrote:

"When I insert new record to children table, select over parents with function gives wrong(outdated) results."

Which is not the same as what you are describing above.


Furthermore:

"I created functional index on parents with function, which selects max value of timestamp from child elements(for
givenparent_id)." 

Is not the same as:

"..items_ts returns the timestamp for the given item..."


If all "items_ts" did was return the timestamp of the provided child then when you update the "last_item" column on
"groups"(however you would decide to do that) a new index entry would be created that stores the timestamp for the
specifiedchild id.  As long as the child's timestamp doesn't change (or become deleted) then the index will maintain
thecorrect value. 

Given that you are seeing "outdated" results that means you are changing the "items" table without updating the
"groups"table in a corresponding manner but instead are expecting the index function to somehow magically update.  That
isnot how the system works. 

If you want to put forth a self-contained example with descriptions of exactly where you believe there is a problem
thenmaybe we can help you understand better.  As it stands now you have provided two different descriptions of your
situation. The first one seems to be the most accurate and based upon that description the advice you have been given
iscorrect.  The second example is incomplete but could indeed work (given specific assumptions).  The idea you are
suggestingis that you maintain the id of the most recent "item" on the "group" table then use a functional index to
cachethe timestamp of that child.  The question becomes how do you update the "item id" on the "groups" table when you
addnew records to "items". 

David J.