This pl/pgsql function seems to walk back up the context_id tree to
update all parent objects last modified dates. This seems
inappropriate as well as slow, at least on Postgresql.
Here is the postgresql version of the function:
create function acs_object__update_last_modified (integer, timestamp)
returns integer as '
declare
acs_object__update_last_modified__object_id alias for $1;
acs_object__update_last_modified__last_modified alias for $2; --
default now()
v_parent_id integer;
v_last_modified timestamptz;
begin
if acs_object__update_last_modified__last_modified is null then
v_last_modified := now();
else
v_last_modified :=
acs_object__update_last_modified__last_modified;
end if;
update acs_objects
set last_modified = v_last_modified
where object_id = acs_object__update_last_modified__object_id;
select context_id
into v_parent_id
from acs_objects
where object_id = acs_object__update_last_modified__object_id;
if v_parent_id is not null and v_parent_id != 0 then
perform acs_object__update_last_modified(v_parent_id,
v_last_modified);
end if;
return acs_object__update_last_modified__object_id;
end;' language 'plpgsql';
and the Oracle version, which nicely uses a connect-by but still
probbaly should not be doing that
procedure update_last_modified (
object_id in acs_objects.object_id%TYPE,
last_modified in acs_objects.last_modified%TYPE default sysdate
)
is
v_parent_id acs_objects.context_id%TYPE;
begin
update acs_objects
set acs_objects.last_modified =
acs_object.update_last_modified.last_modified
where acs_objects.object_id in (select ao.object_id
from acs_objects ao
connect by prior ao.context_id
= ao.object_id
start with ao.object_id =
acs_object.update_last_modified.object_id)
and acs_objects.context_id is not null
and acs_objects.object_id != 0;
end update_last_modified;
Can we take this out? It seems that you should only be updating the
last_modified of the actualy object you are referring to. Context_id
is only to signify inheritance of permissions.
