Forum OpenACS Development: need of advice dealing with acs_permission__permission_p

Working on scalability issues in WP, as you will see WP is completely unusable after loading some thousands users, presentations, slides (in PG 7.2), even using oacs4.6.1, now I have fixed a few queries, instead of doing
things like this in the where clause:

acs_permission__permission_p(i.item_id, 2121, 'wp_view_presentation') = 't'

changed to direct use of the same tables that uses
acs_permission__permission_p, having something like:
....
from acs_permissions perm
.....
and  perm.object_id = i.item_id
and  (perm.grantee_id = :user_id or perm.grantee_id = -1)
and  (perm.privilege = 'wp_view_presentation' or perm.privilege =
'wp_edit_presentation')

the performance has improved by thousands using this, I thought that making acs_permission__permission_p iscacheble was not a good idea.

Now my main concern is that the extra lines added to the where in somes
queries doesn't have the full functionality of acs_permission__permission_p, basicly in two areas: Check group permmissions & relational segment approved
group

WP doesn't use group stuff directly, because always check (in all the pages) against the user_id.

What do you think? Am I missing something? Do I need to add also that group
permission checks?
Is another better way to work around this thing?

Thanks,

On Sloan's Sun E450 (not a terribly overwhelmingly fast machine by today's standards, a bit of a slug really), with 11,000 users, a couple of thousand groups, a half-million objects ... calls to permission_p are running from 6 to 10 milliseconds.  That's pretty minimal for random checks on permissions for a particular object.

Using the acs_object_party_privilege_map directly in the WHERE clause is a much faster way to find out the objects you have permission to view in a particular content folder or under a particular package.  So fast that I'd much rather see the bookmarks and similar packages continue to use permissions, switching to private_p wouldn't really be particularly faster.  The extra flexibility could be useful for someone customizing the package - for instance you could make the bookmarks group-private rather than individual-private just by fiddling the perms rather than fiddling code extensively.

Here's an answer I e-mailed Rocael earlier (he originally e-mailed his question to me and Jeff Davis spanked us for not having this discussion in public):

You are on the right track, very close to being right actually, you're only missing the fact that you should use the acs_object_party_privilege_map view for such checking.

This used to do the UNION of groups, parties, relational segments that you're describing as missing above.  My changes in 4.6.1 creates a (large) denormalized table that maintains the same state as an explicit UNION, vastly speeding up the view especially (but not only) in PostgreSQL.

Here's a query snippet (from a big gross query in file storage) that should get the idea across:

              select fs_urls_full.url_id as object_id,
                acs_permission__permission_p(fs_urls.url_id, :viewing_user_id, 'write') as write_p,
                acs_permission__permission_p(fs_urls.url_id, :viewing_user_id, 'delete') as delete_p,
                1 as sort_key
              from fs_urls_full
              where fs_urls_full.folder_id = :folder_id
                and exists (select 1
                            from acs_object_party_privilege_map m
                            where m.object_id = fs_urls_full.url_id
                              and m.party_id = :viewing_user_id
                              and m.privilege = 'read')) as fs_objects

Note the use of acs_permission__permission_p() to determine write and delete privileges on the rows that are actually returned but the use of
the acs_object_party_privilege_map in the WHERE clause to figure out which rows to process and return.  Switching to the use of the view in the WHERE clause sped things up by a factor of two or three even though the query had previously been optimized to minimize the calls to permission_p.

The EXISTS subquery you see there works regardless of whether the user has the permission directly, due to being a member of a group, or a relational segment.

In particular it is no longer necessary to ever check for "user_id = 0 and permisssion granted on group -1" or stuff like that again.  Just the direct check against this view is sufficient and covers all the cases.