Forum OpenACS Q&A: Strange behavior in acs_object__update_last_modified

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.
Dave,

The purpose of this function is specifically to trigger an update through the
context ID hierarchy. If you'd rather not trigger this update, it's best not to use
this trigger. The reason we implemented it is because it seemed like 90% of
the time it was the right behavior, and 10% of the time it didn't matter. We
haven't found an example yet where this is the *wrong* behavior. That said,
it's very possible that the function could use a bit of speedup.

examples of cases where this is very useful:

1) update a message or add a new message in a forum: the forum has new
information and thus updating its last_modified makes a lot of sense

2) update a file in a file storage folder, and its parent has been updated in the
logical sense, so why not do it in the data model, too.

This also amortizes the cost of figuring out "newness" by updating information
at insert/update time, and not having to do crazy queries at every selection.

Ben,

OK, I understand the concept. If a file is in a folder in file-storage, update the folder's last modified date. Same for a forum when a message is updated.

It seemed to cause a problem on postgresql that the query never ended.

Besides that, still isn't context_id only used for permissions and should not be used to represent a physical relationship such are forum ownership or folder contents?

Also,

In Forums there is an explicit call to acs_object__update_last_modified with the forum_id as a paramter. So there really isn't any need to go back up the hierarchy to find the forum_id. Or am I missing something here?

Dave,

Certainly there might be a bug in the PG version. Is it still there?

Btw, you're right about the theory: the context_id should really be used for
permissions only. That's why I said that our use of context_id is correct in 90%
of cases, and not so correct in 10% of cases (but in those cases, the result is
still totally acceptable).

If you have a better suggestion for solving this problem, I'm absolutely up for a
better way to look at this stuff.

It should really use the parent link and not depend on the context id.  After all I can walk in and use the general perms UI to break context-id. That's one very good reason not to depend on context id to point to a physical parent.

I know other packages do this too but personally I think they're all broken.

The CR may already provide something like this.  If not, one way of doing it would be to use tree_ancestor_keys to grab the set of ancestors - "in (select tree_ancestore_keys ..." rather than recurse.

It would be faster.  Regardless CR objects should use a standard CR way rather than depend on a context_id hack.

You could use the same trick for forums, too, since the message sortkeys *do* reflect physical reality, no?

Ben, there might be a bug in the postgresql version, I could not find it right away. I am too tired right now :)

We will be looking into it more as we migrate the site over. I took out the code that recurses on the context_id tree and the function does work in that limited capacity, but only updates the object_id that is passed in.

I've entered a bug and a small patch that may address this problem. At the very least, it works for some cases.