I just had an in-depth look at the trigger causing problems (acs_objects_context_id_up_tr on acs_objects) and found the following:
the trigger starts with
<![CDATA[
if new.object_id = old.object_id and
new.context_id = old.context_id and
new.security_inherit_p = old.security_inherit_p then
return new;
end if;
Raise Notice ''Continuing 1'';
]]>
Doing the update on acs_objects mentioned above does not cause the trigger code to return "new". But i do not understand why? None of "object_id", "context_id" or "security_inherit_p" have changed.
The second part of the trigger code causes the problem. A delete statement using an "in" clause.
<![CDATA[
delete from acs_object_context_index
where object_id in (select object_id
from acs_object_contexts
where ancestor_id = old.object_id)
and ancestor_id in (select ancestor_id
from acs_object_contexts
where object_id = old.object_id);
Raise Notice ''Continuing 2'';
]]>
The rest of the trigger code does not really take much time. But the table acs_object_context_index has about 160.000 entries in my database, so this statement takes a looong time.
Analyzing a corresponding select statement shows the problem:
<![CDATA[
openacs-stage=# explain analyze select * from acs_object_context_index
openacs-stage-# where object_id in (select object_id
openacs-stage(# from acs_object_contexts
openacs-stage(# where ancestor_id = -3)
openacs-stage-# and ancestor_id in (select ancestor_id
openacs-stage(# from acs_object_contexts
openacs-stage(# where object_id = -3);
NOTICE: QUERY PLAN:
Seq Scan on acs_object_context_index (cost=0.00..12089439.77 rows=40624 width=12) (actual time=87654.15..174282.81 rows=167 loops=1)
SubPlan
-> Materialize (cost=45.67..45.67 rows=11 width=4) (actual time=0.00..0.47 rows=167 loops=162498)
-> Index Scan using acs_obj_ctx_idx_ancestor_idx on acs_object_context_index (cost=0.00..45.67 rows=11 width=4) (actual time=0.07..1.74 rows=167 loops=1)
-> Materialize (cost=28.71..28.71 rows=8 width=4) (actual time=0.00..0.01 rows=1 loops=729)
-> Index Scan using acs_object_context_index_pk on acs_object_context_index (cost=0.00..28.71 rows=8 width=4) (actual time=0.07..0.08 rows=1 loops=1)]]>
Total runtime: 174283.57 msec
I would be very happy about any help with this situtation.
