Forum OpenACS Development: PL/pgSQL concurrency and tree_sortkey questions

Posted by Andrew Piskorski on
Don, could you say more about Postgres's "locking within PL/pgSQL doesn't work right" problem? I hadn't heard of that before and would like to understand how/why it differs from Oracle, what problems it can cause, what the outlook might be for Postgres fixing it, etc.

Hm, is that concurrency problem related to the race condition Patrick M. was talking about back in Feb. 2003? (Which was fixed since then, I hope?)

Also, a basic OpenACS data model tutorial question: In the Postgres version the acs_objects table has this tree_sortkey column, while the Oracle version appears to have no equivalent column. Why? Does the Oracle version just use a connect by on the context_id column somewhere, while for Postgres the tree_sortkey provides the same information? Or something completely different? (This had probably been answered several times elsewhere, but if so I couldn't find it...)

Posted by Jeff Davis on
Andrew, No, it has not been fixed yet and it (and thanks for pointing out Patrick's solution). I think there is still a locking problem with Patrick's code (not based on anything but hearsay though) but the more commonly encountered problem, besides the performance one that got me looking into it, is the duped tree_sortkeys. There are some places where code like Patricks has been implemented (forums, dotlrn_communities_all, dotlrn_community_types all maintain a max_child_sortkey in the parent) but it's not in: sp_folders, bm_bookmarks, acs_messages, cr_keywords, cr_items, site_nodes, acs_privilege_hierarchy_index, acs_objects, and acs_object_types.

Yes, most of the oracle datamodel uses connect by for this rather than tree_sortkey but they are roughly equivalent. The tree_sortkey stuff has been implemented in oracle is used in dotlrn (for the dotlrn_communities_all and dotlrn_community_types tables). My gut tells me tree_sortkey is a better answer for heirarchies that are mostly static but I am not aware of anyone having benchmarked things.