Forum OpenACS Development: Re: Scalability of acs_objects and acs_object_context_index

(5 days without net access - sorry)

Damn, I should not have posted the "offending" query. I meant to start a discussion about application deficiencies, not about a particular query.

Nonetheless here the results for diff. queries with the same semantics:

 select site_node.url(node_id) as url, acs_object.name(object_id) as name
      from site_nodes
      where parent_id = site_node.node_id('/')
      and object_id is not null
      and acs_permission.permission_p(
          object_id,
          acs.magic_object_id('the_public'),
          'read') = 't';

Statistics
----------------------------------------------------------
        118  recursive calls
        136  db block gets
     167407  consistent gets
       2026  physical reads

 select site_node.url(node_id) as url, acs_object.name(object_id) as name
      from site_nodes
      where parent_id = site_node.node_id('/')
      and object_id is not null
      and exists(
         select 1
           from acs_object_party_privilege_map m
           where m.object_id = site_nodes.object_id
           and m.party_id = acs.magic_object_id('the_public')
           and m.privilege = 'read');

Statistics
----------------------------------------------------------
     166032  recursive calls
     663816  db block gets
     333325  consistent gets
       1984  physical reads

select site_node.url(node_id) as url, acs_object.name(object_id) as name
      from site_nodes
      where parent_id = site_node.node_id('/')
      and object_id is not null
      and exists(
         select 1
           from acs_object_party_privilege_map m
           where m.object_id = site_nodes.object_id
           and m.party_id = -1
           and m.privilege = 'read');

Statistics
----------------------------------------------------------
         80  recursive calls
          8  db block gets
     167324  consistent gets
       1959  physical reads

So unfortunately no advance with directly accessing acs_object_party_privilege_map. But note the price of acs.magic_object_id: "evils of PL/SQL" (my broken vinyl record) and not telling the database what I know. Maybe I should start there.

Proposal 1: Hardcode values that are hardcoded anyway.

the_public always has the id -1. Not using a function to retrieve this id made a *gigantic* difference for this particular query. It will make a gigantic difference for many other queries as well.

Replacing it with a join to the magic_objects table is not enough. I *do* know that I want -1. Why not tell the database right away? The optimizer may make a much better decision and I don't lose flexibility. If people dislike hardcoding values, then we should use PL/SQL global variables and the TCL function acs_magic_object_id.

Proposal 2: Gradually remove permission types.

Not only do they make the permissioning system less scalable, they make the packages much more complex. Why are the foo_* permissions there in the first place? I think they were a selling argument..."our permissioning system is extensible..." There's no real use for most of them. And forum_write and write is similar enough. As to having admin on news and not on subsite - you would grant a particular user admin on the news package, not on the subsite - or am I missing something here?

Proposal 3: Research which object_types in acs_objects and acs_object_context_index are being used.

There still is not a good policy on which data needs to go to acs_objects etc. It is not unlikely that some information in the central tables is write-only. We may be able to remove rows from central object tables without further ado...

Proposal 4: Come up with a good policy for acs_objects etc.

The delination of what needs to be an object and what not is still very fuzzy. https://openacs.org/doc/openacs-4-6-2/objects.html refers to a rule of thumb. We should find a "proper rule" and then have an eye on changes to central tables. Here's a fuzzy propsal: "Everything that has a URL to the user needs to be an acs_object", the rest goes to other meta_tables e. g. acs_audited_objects, acs_internal_objects

Proposal 5: Flatten out the privilege hierarchy into a table

The privilege hierarchy is sstill a view that performs an implicit connect-by. We should just flatten that out with triggers or - Oracle! - a materialized view.

Proposal 6:: Add a section "Permissions and site-wide metadata" to packages docs.

As using central tables inevitably affects other packages their use should be documented. Probably helps us get a grip on a "metadata table use".

I'll be throwing in my time once we have reached a agreement on any of above mentioned points...