I am about to start working on finer grained permissions in the project manager application and so began by studying the permissions data model and becoming familiar with the most efficient way to use it.

I started by reading the documentation and some existing code and came to the conclusion that the preferred method within sql up to now has been:

select 1 from acs_object_party_privilege_map ppm where ppm.party_id=x and ppm.privilege='foo' and ppm.object_id=y;

I read a forum post in which it was noted that the PostgreSQL optimiser seems to be making different decisions in PostgreSQL 7.4.x from those made in earlier versions which have resulted in slower performance during permissions lookups.

For the purposes of this document I have performed all of the following queries on an x686 650Mhz laptop. The absolute times reported therefore do not reflect the real world performance of an OpenACS instance on an industrial grade server platform but rather provide an indication of relative speed. My intention here is to float some ideas which I hope will stimulate discussion and improve performance. I expect there to be flaws in some of my assumptions and I suspect that there may be some errors/bugs in my queries - please shoot me down in flames if I deserve it!

I began by running a benchmark:

TEST A-1)

explain analyse select 1 from acs_object_party_privilege_map ppm where ppm.party_id=448 and ppm.privilege='admin' and ppm.object_id=16631;

Total runtime: 459.076 ms

[The party_id 448 is site-wide-admin and so has many descendent privileges. This impacts performace because of the larger number of rows returned from the acs_object_party_privilege_map view.]

TEST B-1)

explain analyse select 1 from acs_object_party_privilege_map ppm where ppm.party_id=-1 and ppm.privilege='admin' and ppm.object_id=16631;

Total runtime: 173.728 ms

[The party_id -1 is 'the public' and has no aggregated permissions on the system. There are also no explicit permissions set for this party_id.]



As things stand it seems that the use of the acs_object_party_privilege_map view works best when there are simple cascading permissions. This is well suited to sites with a high volume of unregistered visitors but may be less than ideal in a tightly controlled corporate application where many explicit permissions are to be granted.


Based on this conclusion I became comcerned that if I proceed to implement permissions in the Project Manager at the level of projects and tasks, because of the potential number of explicitly granted permissions, I might create a significant performance issue.

I was interested in the design decision that led to the creation of acs_object_party_privilege_map as a view rather than as a table maintained by triggers. I have come to the conclusion that the reason that this works well is that the PostgreSQL optimiser is smart enough to only create enough of the total dataset to satisfy the requirements of the query so that the entire view does not ever have to be created or stored. However it is the behaviour of the optimiser that we do not have control over and this has led to frustrations in the past. I wondered whether we could change the structure of our request such that we guide the optimiser more firmly towards the execution plan that we believe is appropriate.

So I decided to attempt to replicate the functionality of the above without using the acs_object_party_privilege view at all. I decided that the following structure might be appropriate:

select 1 from acs_permissions
where object_id in (...)
and grantee_id in (...)
and privilege in (...);

The sub-selects would each return a small dataset (ideally by index scanning) to the main query which would then only need to join a modest number of rows. Gone would be the need for dynamically creating portions of the enormous flattened combination hierarchy.

Here is the full experimental query:

select 1 from acs_permissions
where privilege in (select descendant from acs_privilege_descendant_map where privilege='admin')
and object_id in (select ancestor_id from acs_object_context_index where object_id=16631)
and (grantee_id in (select group_id from group_member_index where member_id=448) or grantee_id=448);

[The 'or' clause is required because the group_member_index does not hold an object as an ancestor or itself as do the other flattened hierarchies.]


Here are the test results to solve the same problem as benchmarked above:

TEST A-2)

explain analyse select 1 from acs_permissions where privilege in (select descendant from acs_privilege_descendant_map where privilege='admin') and object_id in (select ancestor_id from acs_object_context_index where object_id=16631) and (grantee_id in (select group_id from group_member_index where member_id=448) or grantee_id=448);

Total runtime: 444.026 ms

TEST B-2)

explain analyse select 1 from acs_permissions where privilege in (select descendant from acs_privilege_descendant_map where privilege='admin') and object_id in (select ancestor_id from acs_object_context_index where object_id=16631) and (grantee_id in (select group_id from group_member_index where member_id=-1) or grantee_id=-1);

Total runtime: 104.635 ms


The result is a similar TEST A result but a significantly faster TESTB at a little over half the time.

I was disappointed by this but since it is the handling of views that seems to give rise to the variance between versions of PostgreSQL I was keen to find a way to improve things.

Since we only ever want a true or false result from a permissions request, it ocurred to me that we might make use of the 'LIMIT 1' construct. Perhaps the optimiser will be clever enough to do what Perl does when executing boolean statements and break of execution as soon as the condition has been fulfilled.

Here are the results using limit with my experimental query:

TEST A-3)

explain analyse select 1 from acs_permissions where privilege in (select descendant from acs_privilege_descendant_map where privilege='admin') and object_id in (select ancestor_id from acs_object_context_index where object_id=16631) and (grantee_id in (select group_id from group_member_index where member_id=448) or grantee_id=448) limit 1;

Total runtime: 11.749 ms

TEST B-3)

explain analyse select 1 from acs_permissions where privilege in (select descendant from acs_privilege_descendant_map where privilege='admin') and object_id in (select ancestor_id from acs_object_context_index where object_id=16631) and (grantee_id in (select group_id from group_member_index where member_id=-1) or grantee_id=-1) limit 1;

Total runtime: 104.808 ms


Aha!
No change to the time taken for a search when there are no permissions entries for obvious reasons but a very significant improvement for queries involving users with lots of aggregated permissions.

So now the results for using 'LIMIT 1' with the original query:

TEST A-4)

explain analyse select 1 from acs_object_party_privilege_map ppm where ppm.party_id=448 and ppm.privilege='admin' and ppm.object_id=16631 limit 1;

Total runtime: 0.891 ms

TEST B-4)

explain analyse select 1 from acs_object_party_privilege_map ppm where ppm.party_id=-1 and ppm.privilege='admin' and ppm.object_id=16631 limit 1;

Total runtime: 393.342 ms


Double WOW! ...the query for party_id 448 is another 10x faster, however the query for 'the public' is of course no quicker.

This seems to throw up the following thoughts which I have summarised below and would welcome comments on.

1) Perhaps we could employ these different query structures depending upon the anticipated situation. For example, in admin pages or in the project manager application (where documents are to be restricted to a small number of parties) we could use the query shown in example TEST 4-A, whilst for nodes more likely to be viewed publically and edited infrequently (such as a news item) it may be more appropriate to use the structure proposed in TEST B-2.

2) Analysis of the query execution plans reveals an opportunity for performance improvement by recreating the privilege hierarchy table to utilise integer keys. Because the acs_privilege_descendent_map table is currently in plain english, the query plan calls for a 'text filter' comparison on every row in the nested loop. In the documentation for PostgreSQL it states that Unicode support significantly impacts performance. Since the internationalisation support in recent versions of OpenACS requires Unicode to be compiled into PostgreSQL, it could be that there are gains to be made here by eliminating the need for unicode supporting text filters in the permissions infrastructure. The query execution result seems to support this conclusion.

3) At the moment there is no index on the privilege column of the acs_privilege_descendent_map table. Addition of an index on this field may provide an incremental improvement over 2) above.


If I think of anything else I will post it.

I'd be very grateful for input from others.

Regards
Richard

Rocael, why do we need the "select 1 from dual where exists". Maybe I'm just too blind to see the difference.

Your benchmarks are meaningless unless you use "exists", in other words ...

select exists(select 1 from ...)

in PG we don't need the "select 1 from dual where exists (select 1 ... " form because "exists()" is implemented as a generalized function rather than a special WHERE-clause operator as is true in Oracle.

The reason you see "from dual" queries in our code or as Roc did in his example is that our "dual" view only has one row, so performance shouldn't vary noticably (feel free to test that presumption if you have time!). Using "from dual" allows us to write a single query that works in both PG and ORACLE, and single queries rather than two RDBMS-depended queries are much easier to maintain (exactly twice as easy, actually!) when datamodels change for some reason.

Exists - in theory, at least, and HOPEFULLY in practice - stops when the subquery returns its first row. In other words, exists() and LIMIT are the same. That's why we use exists() in our queries.

If you have time, it would be interesting to see your various tests but modified to surround your existing "select 1" queries with "select 1 where exists (select 1 ...)" queries. If adding LIMIT 1 decreases the execution time, then something broke in PG.

BTW Jeff's tested permissions queries in PG 8.0 and they're running fast as expected. Also we don't see permissions queries running slowly on OpenACS with its thousands of users. I think there was something messed up in the testing done earlier, in the thread you refer to. Lingering PG handles in AOLserver after ANALYZE was run, something along those lines.

But more testing is good, it's been over two years since I did the testing that led to the current strategy.

Oh, the main reason for not exploding the entire view into a trigger-maintained table is size. I chose to explode the party privilege member map instead. It's quite huge, but not nearly as huge, and doing this rather than explode the main view seemed to give the best bang for the buck without causing table size to go through the roof. Running out of shared buffer space or disk cache space on a large installation would wreak havoc on performance ...

Also ... the current plan for increasing performance is to start weeding out custom permissions where they aren't needed. The ability to define custom permissions is very useful, but aD's paradigm of doing so for every package is expensive, adds no functionality in most cases, nor readability. In other words "forums_create" is the same as "create" (and indeed is a child of "create"). Adds no functionality, just adds a row to the privileges table. I estimate that for .LRN we can cut down the rows in the privileges table (which is sequentially scanned because it has something like 60 rows in .LRN) down to maybe a dozen or at worst two rows. That would lead to a 3x-4x increase in performance on average ...

I dont think your granular permissions checks on the PM will decrease performance, by no means ... and those aditions will be really good!

A few comments ...

1. I've never claimed my permissions work for 4.6 was the be-all and end-all in possible speedups. Previously, queries against the view in PG (always) and Oracle (sometimes) would runaway and take literally tens of minutes to run. Now we're down to worst cases taking a large fraction of a second. At that point I stopped. Just want to make clear that further improvements are welcome, of course.

2. until either version 7.3 or 7.4, PG implemented the construct "id in (select id from ...)" by building the entire rowset for the subquery, then sequentially searching it of the left operand ("id"). That was horribly slow and made such constructs unusable. Such constructs can be implemented as a form of "join", using the appropriate index. PG does that now (Oracle always did) so we can consider using constructs of this sort where before we could not. In many places in OpenACS, not just permissions, you'll see our queries bend over backwards to avoid "id in (select id from ...)" clauses because of this earlier flaw in PG. As time goes on and people work on the code we can take advantage of PG's enhanced intelligence and use this form.

3. Here are the two main problems with not using the view:

a. If the underlying datamodel changes again (as I changed it for 4.6) for enhanced performance, you need to chase down EVERY QUERY doing such permissions checks and rewrite them. Using the VIEW, we only have to drop and create the VIEW.

b. It's a constant struggle to get people to use the VIEW rather than call acs_permission__permission_p in their WHERE clauses. If you call the proc, in both Oracle and PG at times the proc will be called for EACH ROW being examined, rather than only rows that are qualified by other clauses in the WHERE clause. In general, calling PL/[pg]SQL functions in WHERE clauses is begging for a scalability disaster (as opposed to calling such functions in the rowset designator, i.e. the columns you specify following the SELECT keyword, PL/[pg]SQL is fine there). Getting people to write more complex WHERE clauses would be even harder, I'm afraid, especially when one considers point "a" above.

c. And point "b" is why we can't just bury your changes into permission_p and maintain abstraction by calling the function rather than use the view or do the query bits you're discussing manually. Oracle and PG will both build disasterous execution plans for queries with the permission_p call in the WHERE clause unpredictably.

So I think we need to concentrate on the VIEW. BTW both PG and Oracle are pretty good at generating the same plan for VIEWs as for the components, so you might consider trying to rewrite the VIEW using the "in (select ...)" approach and see if that helps.

Regarding your 2-integer table ... I'm surprised it helps so much. First of all, PG has a per-row (not table) overhead of 40+ bytes, so the size difference of a row compared to the group_element_index shouldn't be as large as you're probably imagining. Unlike Oracle, BTW, which has a very low per-row overhead (maybe even zero bytes).

So can you post EXPLAIN ANALYZE output on your 400ms query using LIMIT above? I want to see exactly what's going on in terms of execution, not just the plan but the measured times for the various bits of the query. Also the EXPLAIN ANALYZE output for your super-fast query.

Lastly, where the hell is that original query from, that doesn't do "exists()"? We need to fix it!

Yes, I'm curious about caching/disk reads, that's one of the reasons I want the EXPLAIN ANALYZE output ... to make sure we're seeing measurements from Richard that reflect an expensive plan, rather than a tuning (or lack thereof) artifact ...

re different results: I did not get the same relative timings. Also I have some trouble understanding the times you get just because we are getting things that are 100x faster on similiar data and 50x faster on openacs with about 10-100x as much data (

In 7.4 geqo_threshold is used for two things, one is the threshold for the geqo optimizer but the other is whether or not to flatten the tables in a subquery or view when optimizing (in pg 8 that semi-secret function was broken out into an explicit parameter). And yes, it should not matter if there are only four tables in the query.

Your faster query does not return the correct answer it seems (member -1 does not show up in the rh_group_struct_view):

openacs.org=# select * from acs_permissions where 
privilege in (select descendant from acs_privilege_descendant_map where privilege='read') 
and object_id in (select ancestor_id from acs_object_context_index where object_id=179199) 
and grantee_id in (select group_id from rh_group_struct_view where element_id=-1);
 object_id | grantee_id | privilege
-----------+------------+-----------
(0 rows)

openacs.org=# select * from acs_object_party_privilege_map ppm where 
ppm.party_id=-1 and ppm.privilege='read' and ppm.object_id=179199;
 object_id | privilege | party_id
-----------+-----------+----------
    179199 | read      |       -1
(1 row)

And for reference here is the plan on openacs.org for a check for admin for public on a site node (which returns false):

openacs.org=# explain analyze select exists (select * from acs_object_party_privilege_map ppm where ppm.party_id=-1 and ppm.privilege='admin' and ppm.object_id=179199);
                                                                                    QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Result  (cost=119.70..119.71 rows=1 width=0) (actual time=1.332..1.333 rows=1 loops=1)
   InitPlan
     ->  Hash Join  (cost=15.82..119.70 rows=2 width=26) (actual time=1.322..1.322 rows=0 loops=1)
           Hash Cond: ("outer".grantee_id = "inner".party_id)
           ->  Hash Join  (cost=3.61..107.33 rows=11 width=26) (actual time=0.373..1.159 rows=30 loops=1)
                 Hash Cond: (("outer".privilege)::text = ("inner".privilege)::text)
                 ->  Nested Loop  (cost=0.00..102.88 rows=74 width=17) (actual time=0.070..0.542 rows=38 loops=1)
                       ->  Index Scan using acs_object_context_index_pk on acs_object_context_index c  (cost=0.00..22.95 rows=7 width=8) (actual time=0.031..0.054 rows=5 loops=1)
                             Index Cond: (object_id = 179199)
                       ->  Index Scan using acs_permissions_pk on acs_permissions p  (cost=0.00..11.39 rows=3 width=17) (actual time=0.017..0.053 rows=8 loops=5)
                             Index Cond: ("outer".ancestor_id = p.object_id)
                 ->  Hash  (cost=3.60..3.60 rows=3 width=31) (actual time=0.247..0.247 rows=0 loops=1)
                       ->  Seq Scan on acs_privilege_descendant_map pdm  (cost=0.00..3.60 rows=3 width=31) (actual time=0.074..0.240 rows=1 loops=1)
                             Filter: ((descendant)::text = 'admin'::text)
           ->  Hash  (cost=12.20..12.20 rows=4 width=8) (actual time=0.061..0.061 rows=0 loops=1)
                 ->  Index Scan using party_member_member_idx on party_approved_member_map pamm  (cost=0.00..12.20 rows=4 width=8) (actual time=0.047..0.053 rows=1 loops=1)
                       Index Cond: (member_id = -1)
 Total runtime: 1.582 ms

I think given this is <2ms for 8600 users and 140k objects I don't think it needs to be fixed. The plan produced here is quite a bit different than what you get so I think either you need to run vacuumdb -f -z YOURDB or your postgres conf is seriously out of whack.

"Your comment that "analyze isn't really necessary for a test system" is false ... "

Mmmm...............False will bells on I think!

After running a vacuumdb with full and analyse (four times for good measure!) the standard query returns results for both 448 and -1 in approximately 1.2ms - which is great news.

The alternative query runs slightly slower now at just under 2ms for both tests which clearly demonstrates that the current method is best. Thank you for your help - sorry to soak up your precious time.

Now I can get on with working on the PM without worrying about permissions calls!

Don,

I am now tryiung different ways to put permissions on projects at the query level. I would value your opinion as to which of these is the preferred method (or any other you can devise!).

A) QUERY WITH PERMISSIONS CALL IN SELECT STATEMENT

Reqires tcl code to control display of data. I tried adding:

 and permission='t'

to the where clause but postgres will not allow me to refer to the column using the affiliation name. Is there a way to do this (i.e. with column number as with an aggregate function).

explain analyse SELECT p.project_id, p.parent_id as folder_id, p.object_type as content_type, p.title as project_name, p.project_code, (select exists(select 1 from acs_object_party_privilege_map ppm where ppm.object_id = p.project_id and ppm.party_id = -1 and ppm.privilege = 'admin')) as permission
        FROM pm_projectsx p
             LEFT JOIN pm_project_assignment pa
                ON p.item_id = pa.project_id
             LEFT JOIN organizations o ON p.customer_id =
                o.organization_id
             LEFT JOIN (
                        select
                        om.category_id,
                        om.object_id,
                        t.name as category_name
                        from
                        category_object_map om,
                        category_translations t,
                        categories ctg
                        where
                        om.category_id = t.category_id and
                        ctg.category_id = t.category_id and
                        ctg.deprecated_p = 'f')
                 c ON p.item_id = c.object_id,
        cr_items i,
        pm_project_status s
        WHERE
        p.project_id = i.live_revision and
        s.status_id = p.status_id;

tradeadev#                                                            QUERY PLAN                                                                                                                          
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=4010.03..4129.91 rows=2 width=65) (actual time=37.805..37.805 rows=0 loops=1)
   ->  Hash Join  (cost=4010.03..4016.69 rows=2 width=73) (actual time=37.799..37.799 rows=0 loops=1)
         Hash Cond: ("outer".status_id = "inner".status_id)
         ->  Hash Left Join  (cost=4009.01..4015.63 rows=2 width=77) (actual time=37.680..37.680 rows=0 loops=1)
               Hash Cond: ("outer".item_id = "inner".object_id)
               ->  Nested Loop Left Join  (cost=4008.98..4015.59 rows=2 width=81) (actual time=37.621..37.621 rows=0 loops=1)
                     ->  Merge Left Join  (cost=4008.98..4009.22 rows=2 width=85) (actual time=37.618..37.618 rows=0 loops=1)
                           Merge Cond: ("outer".item_id = "inner".project_id)
                           ->  Sort  (cost=4006.38..4006.39 rows=2 width=85) (actual time=37.613..37.613 rows=0 loops=1)
                                 Sort Key: cr.item_id
                                 ->  Nested Loop  (cost=0.00..4006.37 rows=2 width=85) (actual time=37.583..37.583 rows=0 loops=1)
                                       ->  Seq Scan on cr_text  (cost=0.00..1.01 rows=1 width=0) (actual time=0.009..0.013 rows=1 loops=1)
                                       ->  Nested Loop  (cost=0.00..4005.34 rows=2 width=85) (actual time=37.550..37.550 rows=0 loops=1)
                                             ->  Nested Loop  (cost=0.00..4001.84 rows=1 width=81) (actual time=37.548..37.548 rows=0 loops=1)
                                                   ->  Nested Loop  (cost=0.00..3996.09 rows=1 width=61) (actual time=37.545..37.545 rows=0 loops=1)
                                                         ->  Seq Scan on pm_projects  (cost=0.00..3992.51 rows=1 width=26) (actual time=37.543..37.543 rows=0 loops=1)
                                                               Filter: ((subplan) = true)
                                                               SubPlan
                                                                 ->  Result  (cost=53.18..53.19 rows=1 width=0) (actual time=0.490..0.491 rows=1 loops=75)
                                                                       InitPlan
                                                                         ->  Hash Join  (cost=51.11..53.18 rows=1 width=0) (actual time=0.480..0.480 rows=0 loops=75)
                                                                               Hash Cond: (("outer".privilege)::text = ("inner".privilege)::text)
                                                                               ->  Seq Scan on acs_privilege_descendant_map pdm  (cost=0.00..2.05 rows=3 width=13) (actual time=0.122..0.194 rows=1 loops=75)
                                                                                     Filter: ((descendant)::text = 'admin'::text)
                                                                               ->  Hash  (cost=51.11..51.11 rows=1 width=9) (actual time=0.266..0.266 rows=0 loops=75)
                                                                                     ->  Hash Join  (cost=3.46..51.11 rows=1 width=9) (actual time=0.263..0.263 rows=0 loops=75)
                                                                                           Hash Cond: ("outer".grantee_id = "inner".party_id)
                                                                                           ->  Nested Loop  (cost=0.00..47.59 rows=6 width=13) (actual time=0.049..0.242 rows=8 loops=75)
                                                                                                 ->  Index Scan using acs_object_context_index_pk on acs_object_context_index c  (cost=0.00..17.93 rows=5 width=4) (actual time=0.021..0.048 rows=6 loops=75)
                                                                                                       Index Cond: (object_id = $0)
                                                                                                 ->  Index Scan using acs_permissions_pk on acs_permissions p  (cost=0.00..5.92 rows=1 width=17) (actual time=0.014..0.020 rows=1 loops=450)
                                                                                                       Index Cond: ("outer".ancestor_id = p.object_id)
                                                                                           ->  Hash  (cost=3.45..3.45 rows=2 width=4) (actual time=0.083..0.083 rows=0 loops=1)
                                                                                                 ->  Index Scan using party_member_member_idx on party_approved_member_map pamm  (cost=0.00..3.45 rows=2 width=4) (actual time=0.057..0.063 rows=1 loops=1)
                                                                                                       Index Cond: (member_id = -1)
                                                         ->  Index Scan using cr_revisions_pk on cr_revisions cr  (cost=0.00..3.57 rows=1 width=35) (never executed)
                                                               Index Cond: ("outer".project_id = cr.revision_id)
                                                   ->  Index Scan using acs_objects_pk on acs_objects  (cost=0.00..5.74 rows=1 width=20) (never executed)
                                                         Index Cond: (acs_objects.object_id = "outer".project_id)
                                             ->  Index Scan using cr_items_pk on cr_items i  (cost=0.00..3.49 rows=1 width=8) (never executed)
                                                   Index Cond: ("outer".item_id = i.item_id)
                           ->  Sort  (cost=2.60..2.70 rows=43 width=4) (never executed)
                                 Sort Key: pa.project_id
                                 ->  Seq Scan on pm_project_assignment pa  (cost=0.00..1.43 rows=43 width=4) (never executed)
                     ->  Index Scan using organization_id_pk on organizations o  (cost=0.00..3.17 rows=1 width=4) (never executed)
                           Index Cond: ("outer".customer_id = o.organization_id)
               ->  Hash  (cost=0.03..0.03 rows=1 width=4) (actual time=0.010..0.010 rows=0 loops=1)
                     ->  Nested Loop  (cost=0.00..0.03 rows=1 width=4) (actual time=0.008..0.008 rows=0 loops=1)
                           Join Filter: ("inner".category_id = "outer".category_id)
                           ->  Nested Loop  (cost=0.00..0.01 rows=1 width=8) (actual time=0.005..0.005 rows=0 loops=1)
                                 Join Filter: ("inner".category_id = "outer".category_id)
                                 ->  Seq Scan on category_translations t  (cost=0.00..0.00 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=1)
                                 ->  Seq Scan on categories ctg  (cost=0.00..0.00 rows=1 width=4) (never executed)
                                       Filter: (deprecated_p = 'f'::bpchar)
                           ->  Seq Scan on category_object_map om  (cost=0.00..0.00 rows=1 width=8) (never executed)
         ->  Hash  (cost=1.02..1.02 rows=2 width=4) (actual time=0.060..0.060 rows=0 loops=1)
               ->  Seq Scan on pm_project_status s  (cost=0.00..1.02 rows=2 width=4) (actual time=0.028..0.036 rows=2 loops=1)
   ->  Index Scan using cr_items_by_live_revision on cr_items i  (cost=0.00..3.40 rows=1 width=4) (never executed)
         Index Cond: (i.live_revision = "outer".revision_id)
   SubPlan
     ->  Result  (cost=53.18..53.19 rows=1 width=0) (never executed)
           InitPlan
             ->  Hash Join  (cost=51.11..53.18 rows=1 width=0) (never executed)
                   Hash Cond: (("outer".privilege)::text = ("inner".privilege)::text)
                   ->  Seq Scan on acs_privilege_descendant_map pdm  (cost=0.00..2.05 rows=3 width=13) (never executed)
                         Filter: ((descendant)::text = 'admin'::text)
                   ->  Hash  (cost=51.11..51.11 rows=1 width=9) (never executed)
                         ->  Hash Join  (cost=3.46..51.11 rows=1 width=9) (never executed)
                               Hash Cond: ("outer".grantee_id = "inner".party_id)
                               ->  Nested Loop  (cost=0.00..47.59 rows=6 width=13) (never executed)
                                     ->  Index Scan using acs_object_context_index_pk on acs_object_context_index c  (cost=0.00..17.93 rows=5 width=4) (never executed)
                                           Index Cond: (object_id = $0)
                                     ->  Index Scan using acs_permissions_pk on acs_permissions p  (cost=0.00..5.92 rows=1 width=17) (never executed)
                                           Index Cond: ("outer".ancestor_id = p.object_id)
                               ->  Hash  (cost=3.45..3.45 rows=2 width=4) (never executed)
                                     ->  Index Scan using party_member_member_idx on party_approved_member_map pamm  (cost=0.00..3.45 rows=2 width=4) (never executed)
                                           Index Cond: (member_id = -1)
 Total runtime: 39.795 ms
(78 rows)

B) WRAP ABOVE IN AND OUTER SELECT TO EXTRACT ONLY REQUIRED ROWS

explain analyse SELECT * from (SELECT p.project_id, p.parent_id as folder_id, p.object_type as content_type, p.title as project_name, p.project_code, (select exists(select 1 from acs_object_party_privilege_map ppm where ppm.object_id = p.project_id and ppm.party_id = -1 and ppm.privilege = 'admin')) as permission
        FROM pm_projectsx p
             LEFT JOIN pm_project_assignment pa
                ON p.item_id = pa.project_id
             LEFT JOIN organizations o ON p.customer_id =
                o.organization_id
             LEFT JOIN (
                        select
                        om.category_id,
                        om.object_id,
                        t.name as category_name
                        from
                        category_object_map om,
                        category_translations t,
                        categories ctg
                        where
                        om.category_id = t.category_id and
                        ctg.category_id = t.category_id and
                        ctg.deprecated_p = 'f')
                 c ON p.item_id = c.object_id,
        cr_items i,
        pm_project_status s

        WHERE
        p.project_id = i.live_revision and
        s.status_id = p.status_id) as project_list where permission='t';

                                                                    QUERY PLAN                                                                                                                          
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=4010.03..4129.91 rows=2 width=65) (actual time=45.897..45.897 rows=0 loops=1)
   ->  Hash Join  (cost=4010.03..4016.69 rows=2 width=73) (actual time=45.891..45.891 rows=0 loops=1)
         Hash Cond: ("outer".status_id = "inner".status_id)
         ->  Hash Left Join  (cost=4009.01..4015.63 rows=2 width=77) (actual time=45.768..45.768 rows=0 loops=1)
               Hash Cond: ("outer".item_id = "inner".object_id)
               ->  Nested Loop Left Join  (cost=4008.98..4015.59 rows=2 width=81) (actual time=45.710..45.710 rows=0 loops=1)
                     ->  Merge Left Join  (cost=4008.98..4009.22 rows=2 width=85) (actual time=45.708..45.708 rows=0 loops=1)
                           Merge Cond: ("outer".item_id = "inner".project_id)
                           ->  Sort  (cost=4006.38..4006.39 rows=2 width=85) (actual time=45.703..45.703 rows=0 loops=1)
                                 Sort Key: cr.item_id
                                 ->  Nested Loop  (cost=0.00..4006.37 rows=2 width=85) (actual time=45.669..45.669 rows=0 loops=1)
                                       ->  Seq Scan on cr_text  (cost=0.00..1.01 rows=1 width=0) (actual time=0.009..0.013 rows=1 loops=1)
                                       ->  Nested Loop  (cost=0.00..4005.34 rows=2 width=85) (actual time=45.637..45.637 rows=0 loops=1)
                                             ->  Nested Loop  (cost=0.00..4001.84 rows=1 width=81) (actual time=45.634..45.634 rows=0 loops=1)
                                                   ->  Nested Loop  (cost=0.00..3996.09 rows=1 width=61) (actual time=45.632..45.632 rows=0 loops=1)
                                                         ->  Seq Scan on pm_projects  (cost=0.00..3992.51 rows=1 width=26) (actual time=45.629..45.629 rows=0 loops=1)
                                                               Filter: ((subplan) = true)
                                                               SubPlan
                                                                 ->  Result  (cost=53.18..53.19 rows=1 width=0) (actual time=0.495..0.496 rows=1 loops=75)
                                                                       InitPlan
                                                                         ->  Hash Join  (cost=51.11..53.18 rows=1 width=0) (actual time=0.484..0.484 rows=0 loops=75)
                                                                               Hash Cond: (("outer".privilege)::text = ("inner".privilege)::text)
                                                                               ->  Seq Scan on acs_privilege_descendant_map pdm  (cost=0.00..2.05 rows=3 width=13) (actual time=0.123..0.195 rows=1 loops=75)
                                                                                     Filter: ((descendant)::text = 'admin'::text)
                                                                               ->  Hash  (cost=51.11..51.11 rows=1 width=9) (actual time=0.269..0.269 rows=0 loops=75)
                                                                                     ->  Hash Join  (cost=3.46..51.11 rows=1 width=9) (actual time=0.266..0.266 rows=0 loops=75)
                                                                                           Hash Cond: ("outer".grantee_id = "inner".party_id)
                                                                                           ->  Nested Loop  (cost=0.00..47.59 rows=6 width=13) (actual time=0.052..0.244 rows=8 loops=75)
                                                                                                 ->  Index Scan using acs_object_context_index_pk on acs_object_context_index c  (cost=0.00..17.93 rows=5 width=4) (actual time=0.023..0.050 rows=6 loops=75)
                                                                                                       Index Cond: (object_id = $0)
                                                                                                 ->  Index Scan using acs_permissions_pk on acs_permissions p  (cost=0.00..5.92 rows=1 width=17) (actual time=0.014..0.020 rows=1 loops=450)
                                                                                                       Index Cond: ("outer".ancestor_id = p.object_id)
                                                                                           ->  Hash  (cost=3.45..3.45 rows=2 width=4) (actual time=0.085..0.085 rows=0 loops=1)
                                                                                                 ->  Index Scan using party_member_member_idx on party_approved_member_map pamm  (cost=0.00..3.45 rows=2 width=4) (actual time=0.060..0.067 rows=1 loops=1)
                                                                                                       Index Cond: (member_id = -1)
                                                         ->  Index Scan using cr_revisions_pk on cr_revisions cr  (cost=0.00..3.57 rows=1 width=35) (never executed)
                                                               Index Cond: ("outer".project_id = cr.revision_id)
                                                   ->  Index Scan using acs_objects_pk on acs_objects  (cost=0.00..5.74 rows=1 width=20) (never executed)
                                                         Index Cond: (acs_objects.object_id = "outer".project_id)
                                             ->  Index Scan using cr_items_pk on cr_items i  (cost=0.00..3.49 rows=1 width=8) (never executed)
                                                   Index Cond: ("outer".item_id = i.item_id)
                           ->  Sort  (cost=2.60..2.70 rows=43 width=4) (never executed)
                                 Sort Key: pa.project_id
                                 ->  Seq Scan on pm_project_assignment pa  (cost=0.00..1.43 rows=43 width=4) (never executed)
                     ->  Index Scan using organization_id_pk on organizations o  (cost=0.00..3.17 rows=1 width=4) (never executed)
                           Index Cond: ("outer".customer_id = o.organization_id)
               ->  Hash  (cost=0.03..0.03 rows=1 width=4) (actual time=0.010..0.010 rows=0 loops=1)
                     ->  Nested Loop  (cost=0.00..0.03 rows=1 width=4) (actual time=0.008..0.008 rows=0 loops=1)
                           Join Filter: ("inner".category_id = "outer".category_id)
                           ->  Nested Loop  (cost=0.00..0.01 rows=1 width=8) (actual time=0.005..0.005 rows=0 loops=1)
                                 Join Filter: ("inner".category_id = "outer".category_id)
                                 ->  Seq Scan on category_translations t  (cost=0.00..0.00 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=1)
                                 ->  Seq Scan on categories ctg  (cost=0.00..0.00 rows=1 width=4) (never executed)
                                       Filter: (deprecated_p = 'f'::bpchar)
                           ->  Seq Scan on category_object_map om  (cost=0.00..0.00 rows=1 width=8) (never executed)
         ->  Hash  (cost=1.02..1.02 rows=2 width=4) (actual time=0.061..0.061 rows=0 loops=1)
               ->  Seq Scan on pm_project_status s  (cost=0.00..1.02 rows=2 width=4) (actual time=0.030..0.039 rows=2 loops=1)
   ->  Index Scan using cr_items_by_live_revision on cr_items i  (cost=0.00..3.40 rows=1 width=4) (never executed)
         Index Cond: (i.live_revision = "outer".revision_id)
   SubPlan
     ->  Result  (cost=53.18..53.19 rows=1 width=0) (never executed)
           InitPlan
             ->  Hash Join  (cost=51.11..53.18 rows=1 width=0) (never executed)
                   Hash Cond: (("outer".privilege)::text = ("inner".privilege)::text)
                   ->  Seq Scan on acs_privilege_descendant_map pdm  (cost=0.00..2.05 rows=3 width=13) (never executed)
                         Filter: ((descendant)::text = 'admin'::text)
                   ->  Hash  (cost=51.11..51.11 rows=1 width=9) (never executed)
                         ->  Hash Join  (cost=3.46..51.11 rows=1 width=9) (never executed)
                               Hash Cond: ("outer".grantee_id = "inner".party_id)
                               ->  Nested Loop  (cost=0.00..47.59 rows=6 width=13) (never executed)
                                     ->  Index Scan using acs_object_context_index_pk on acs_object_context_index c  (cost=0.00..17.93 rows=5 width=4) (never executed)
                                           Index Cond: (object_id = $0)
                                     ->  Index Scan using acs_permissions_pk on acs_permissions p  (cost=0.00..5.92 rows=1 width=17) (never executed)
                                           Index Cond: ("outer".ancestor_id = p.object_id)
                               ->  Hash  (cost=3.45..3.45 rows=2 width=4) (never executed)
                                     ->  Index Scan using party_member_member_idx on party_approved_member_map pamm  (cost=0.00..3.45 rows=2 width=4) (never executed)
                                           Index Cond: (member_id = -1)
 Total runtime: 47.817 ms
(78 rows)

C)

tradeadev=# explain analyse SELECT p.project_id, p.parent_id as folder_id, p.object_type as content_type, p.title as project_name, p.project_code 
        FROM pm_projectsx p
             LEFT JOIN pm_project_assignment pa
                ON p.item_id = pa.project_id
             LEFT JOIN organizations o ON p.customer_id =
                o.organization_id
             LEFT JOIN (
                        select
                        om.category_id,
                        om.object_id,
                        t.name as category_name
                        from
                        category_object_map om,
                        category_translations t,
                        categories ctg
                        where
                        om.category_id = t.category_id and
                        ctg.category_id = t.category_id and
                        ctg.deprecated_p = 'f')
                 c ON p.item_id = c.object_id,
        cr_items i,
        pm_project_status s
        WHERE
        p.project_id = i.live_revision and
        s.status_id = p.status_id and (select exists(select 1 from acs_object_party_privilege_map ppm where ppm.object_id = p.project_id and ppm.party_id = -1 and ppm.privilege = 'admin'));


                                                                 QUERY PLAN                                                                                                                                   
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=4289.71..4304.48 rows=32 width=65) (actual time=41.532..41.532 rows=0 loops=1)
   Hash Cond: ("outer".status_id = "inner".status_id)
   ->  Hash Join  (cost=4288.68..4302.97 rows=32 width=69) (actual time=41.410..41.410 rows=0 loops=1)
         Hash Cond: ("outer".live_revision = "inner".revision_id)
         ->  Seq Scan on cr_items i  (cost=0.00..11.98 rows=398 width=4) (actual time=0.006..0.856 rows=398 loops=1)
         ->  Hash  (cost=4288.58..4288.58 rows=39 width=77) (actual time=39.722..39.722 rows=0 loops=1)
               ->  Hash Left Join  (cost=4255.16..4288.58 rows=39 width=77) (actual time=39.719..39.719 rows=0 loops=1)
                     Hash Cond: ("outer".item_id = "inner".object_id)
                     ->  Merge Right Join  (cost=4255.13..4288.35 rows=39 width=81) (actual time=39.667..39.667 rows=0 loops=1)
                           Merge Cond: ("outer".organization_id = "inner".customer_id)
                           ->  Index Scan using organization_id_pk on organizations o  (cost=0.00..29.92 rows=1201 width=4) (actual time=0.033..0.033 rows=1 loops=1)
                           ->  Sort  (cost=4255.13..4255.23 rows=39 width=85) (actual time=39.630..39.630 rows=0 loops=1)
                                 Sort Key: pm_projects.customer_id
                                 ->  Merge Left Join  (cost=4027.44..4254.10 rows=39 width=85) (actual time=39.588..39.588 rows=0 loops=1)
                                       Merge Cond: ("outer".item_id = "inner".project_id)
                                       ->  Nested Loop  (cost=4024.84..4270.01 rows=39 width=85) (actual time=39.585..39.585 rows=0 loops=1)
                                             ->  Nested Loop  (cost=4023.83..4268.22 rows=39 width=85) (actual time=39.583..39.583 rows=0 loops=1)
                                                   ->  Merge Join  (cost=4023.83..4043.93 rows=39 width=65) (actual time=39.579..39.579 rows=0 loops=1)
                                                         Merge Cond: ("outer".item_id = "inner".item_id)
                                                         ->  Index Scan using cr_items_pk on cr_items i  (cost=0.00..18.52 rows=398 width=8) (actual time=0.016..0.016 rows=1 loops=1)
                                                         ->  Sort  (cost=4023.83..4023.93 rows=38 width=61) (actual time=39.559..39.559 rows=0 loops=1)
                                                               Sort Key: cr.item_id
                                                               ->  Hash Join  (cost=3992.42..4022.83 rows=38 width=61) (actual time=39.510..39.510 rows=0 loops=1)
                                                                     Hash Cond: ("outer".revision_id = "inner".project_id)
                                                                     ->  Seq Scan on cr_revisions cr  (cost=0.00..27.69 rows=469 width=35) (actual time=0.007..1.031 rows=469 loops=1)
                                                                     ->  Hash  (cost=3992.32..3992.32 rows=38 width=26) (actual time=37.637..37.637 rows=0 loops=1)
                                                                           ->  Seq Scan on pm_projects  (cost=0.00..3992.32 rows=38 width=26) (actual time=37.634..37.634 rows=0 loops=1)
                                                                                 Filter: (subplan)
                                                                                 SubPlan
                                                                                   ->  Result  (cost=53.18..53.19 rows=1 width=0) (actual time=0.492..0.494 rows=1 loops=75)
                                                                                         InitPlan
                                                                                           ->  Hash Join  (cost=51.11..53.18 rows=1 width=0) (actual time=0.482..0.482 rows=0 loops=75)
                                                                                                 Hash Cond: (("outer".privilege)::text = ("inner".privilege)::text)
                                                                                                 ->  Seq Scan on acs_privilege_descendant_map pdm  (cost=0.00..2.05 rows=3 width=13) (actual time=0.122..0.195 rows=1 loops=75)
                                                                                                       Filter: ((descendant)::text = 'admin'::text)
                                                                                                 ->  Hash  (cost=51.11..51.11 rows=1 width=9) (actual time=0.268..0.268 rows=0 loops=75)
                                                                                                       ->  Hash Join  (cost=3.46..51.11 rows=1 width=9) (actual time=0.264..0.264 rows=0 loops=75)
                                                                                                             Hash Cond: ("outer".grantee_id = "inner".party_id)
                                                                                                             ->  Nested Loop  (cost=0.00..47.59 rows=6 width=13) (actual time=0.052..0.243 rows=8 loops=75)
                                                                                                                   ->  Index Scan using acs_object_context_index_pk on acs_object_context_index c  (cost=0.00..17.93 rows=5 width=4) (actual time=0.023..0.050 rows=6 loops=75)
                                                                                                                         Index Cond: (object_id = $0)
                                                                                                                   ->  Index Scan using acs_permissions_pk on acs_permissions p  (cost=0.00..5.92 rows=1 width=17) (actual time=0.014..0.020 rows=1 loops=450)
                                                                                                                         Index Cond: ("outer".ancestor_id = p.object_id)
                                                                                                             ->  Hash  (cost=3.45..3.45 rows=2 width=4) (actual time=0.078..0.078 rows=0 loops=1)
                                                                                                                   ->  Index Scan using party_member_member_idx on party_approved_member_map pamm  (cost=0.00..3.45 rows=2 width=4) (actual time=0.051..0.057 rows=1 loops=1)
                                                                                                                         Index Cond: (member_id = -1)
                                                   ->  Index Scan using acs_objects_pk on acs_objects  (cost=0.00..5.74 rows=1 width=20) (never executed)
                                                         Index Cond: (acs_objects.object_id = "outer".project_id)
                                             ->  Materialize  (cost=1.01..1.02 rows=1 width=0) (never executed)
                                                   ->  Seq Scan on cr_text  (cost=0.00..1.01 rows=1 width=0) (never executed)
                                       ->  Sort  (cost=2.60..2.70 rows=43 width=4) (never executed)
                                             Sort Key: pa.project_id
                                             ->  Seq Scan on pm_project_assignment pa  (cost=0.00..1.43 rows=43 width=4) (never executed)
                     ->  Hash  (cost=0.03..0.03 rows=1 width=4) (actual time=0.010..0.010 rows=0 loops=1)
                           ->  Nested Loop  (cost=0.00..0.03 rows=1 width=4) (actual time=0.008..0.008 rows=0 loops=1)
                                 Join Filter: ("inner".category_id = "outer".category_id)
                                 ->  Nested Loop  (cost=0.00..0.01 rows=1 width=8) (actual time=0.005..0.005 rows=0 loops=1)
                                       Join Filter: ("inner".category_id = "outer".category_id)
                                       ->  Seq Scan on category_translations t  (cost=0.00..0.00 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=1)
                                       ->  Seq Scan on categories ctg  (cost=0.00..0.00 rows=1 width=4) (never executed)
                                             Filter: (deprecated_p = 'f'::bpchar)
                                 ->  Seq Scan on category_object_map om  (cost=0.00..0.00 rows=1 width=8) (never executed)
   ->  Hash  (cost=1.02..1.02 rows=2 width=4) (actual time=0.057..0.057 rows=0 loops=1)
         ->  Seq Scan on pm_project_status s  (cost=0.00..1.02 rows=2 width=4) (actual time=0.024..0.032 rows=2 loops=1)
 Total runtime: 43.013 ms
(65 rows)

tradeadev=# 

Thanks Jeff, I wasn't sure if the idea was to avoid such things in the WHERE clause of other queries.

The SELECT was a stupid mistake!

Are there any plans to re-write the code in acs_permission__permission_p to utilise the acs_object_party_privilege_map view rather than do the table joins?

So, this thread demonstrates that the display width of posts in openacs.org Forums brokenyet again. Somebody with access please fix this. It's a bit difficult to follow a discussion when all posts above are forced into being incredibly wide, solely because the 12th post in this thread, above, is very poorly formatted.

(And we've only been repeatedly fixing and re-breaking this obvious feature for years now, people...)

Thanks Richard. I appreciate that you try to find best practices of this important issue in the open.

Jeff, did you try to replace the CENTER-tag with a DIV. AFAIK CENTER is depreciated.

Jeff, your tweak is an improvement in that it seems to place an upper bound on how wide this bug will force un-related posts to be. However, resizing your browser window smaller than this window demonstrates that the bug does still exist.

What solution was used on the openacs.org Forums before the recent upgrade? If I recall right, it used a (separate) table to make the the headers of each post look nice, but did not have this "one wide post forces all the rest to be super wide also" bug, and it looked nicer overall anyway.

I am ignorant of the details of how the current Forums HTML has been hacked up. I know simply that this bug has been fixed before, therefore it can be fixed again. I also suggest that any graphic design changes which cause this bug, are by definition bad graphic design changes.

Don, pre-formatted wide text most certainly should not be wrapped on display. That is the whole point of what "pre-formatted" or "fixed width" text means - display it exactly as I give it to you, line breaks and all. The point is that super wide pre-formatted text must not affect the display of other posts in the same thread. Right now it does, and this is a bug.

In fact any one post, regardless of whether a particular post is well or poorly formatted, must never break the display or formatting of all the other posts in a thread.

IMO this is an important correctness and usability criteria, the sort of thing that OpenACS is usually good about getting right, and much more important than getting the particular background color or whatever that someone prefers. But people making look and feel changes to OpenACS appear to sometimes completely ignore all such UI correctness concerns, and repeatedly re-introduce this bug. I'd say that's rather embarassing to the project.

If a user mis-formats a post, it would be very nice to have any easy way for a Forums admin (or better yet, any one of many trusted and designated "power users" or "sub-admins" in the community) to fix that one post. But that's a separate issue.

OpenACS.org is using the stock forums package. Anyone can submit a patch or commit fixes to CVS.

What I have done in the past is take the content out of the big table that surrounds all the posts. I have done this just in the local checkout for openacs.org. Since we want to run OpenACS.org on the same code everyone else has, it should be fixed in OpenACS CVS.

Don, you are not currently seeing the bug because Jeff's change above papers over the most egregious forms of the problem, and your browser window is fairy wide. Narrow your browser window a lot, and you will still see the problem - at least, I would be quite surprised if you do not. I'm using Mozilla 1.7.2 on Linux, and AFAIK the problem is not browser specific.

Jeff's tweak makes things much, much better than before, and is good enough that a real fix is no longer critical, but it's not really the right fix. The old openacs.org site had a correct fix.

Well, I don't see any "edit" link myself. But if it's made available to Admins, that's good.

In IE 6 there is no change with the inline styles Jeff added. Text is still impossible wide. In Firefox text obeys max-width, but not text within pre tags. Table width follows the pre tags.

What works in IE6 and FF1 is another inline style.

pre { white-space: normal }

Does that work for other browsers?

I don't want pre wrapped as I won't be able to read the
analyze output then.

Jeff, looks like you have fixed this the right way now, pushing the tables down to per-post was exactly what was need, outstanding!

In fact, if anything, this combination of fixes now looks even better than the old pre-upgrade openacs.org Forums behavior, which you can see by looking at the problematic poorly-formatted 12th post above - not only does it no longer screw up the display of the rest of the thread, but at least in Mozilla-derived browsers, it itself is now much more readable. (In IE 6.0.x, the text at the top of the 12th post is still too wide, but that's the fault of the way that user submitted that post; it is not a Forums bug. All the other posts look look good in IE.)

So, thank you very much, Jeff! (Hopefully this bug has now been finally slain for good, and won't come back again.)

Hi Richard,

I don't know if you have seem them, but we had quite some issues with OpenACS permissions when designing the Project/Open permission system. Everything could basicly be boiled down to the fact that we didn't need permission on a per-user level for most of the objects in P/O. Such a per-user and per-object granularity directly leads to a N*M storage complexity (number of users * number of objects) if the matrix matrix is more or less filled (i.e. if it's not a particularly sparse matrix).

So we decided to "tweak" the OpenACS permissions system in order to reduce the storage complexity to N, using a single "magic" object and a number of "privileges". Then we use a fixed number of groups to make the management of the privileges more easy. That did work out very well. We simply don't have to consider optimizing the performance.

The problem are the objects which _do_ need per-user access control, such as projects. These objects required us to introduce a N * M type of permissions. However, we didn't need inheritance here (neither hierarchical groups nor security contexts), so we can use plain optimized query with a _time_ complexity of log(N * M).

Finally, we had to qualify the relationships between member and projects a bit more, because a project manager should have more rights then a plain project member, particularly in terms of filestorage and discussion groups. So we used the acs_rels table to store the N * M matrix and added a custom relationship holding a relationship qualifier such as "project_member" or "full_member" or "software_tester" etc. so that we didn't have to multiply the N * M matrix with a constant factor (for various possible categories).

I know that this design isn't 100% compliant with the best programming practices @ OpenACS, but atleast we're not having any trouble with performance...

Good luck!
Frank

Hi Don,

thanks for the blessing!

> using ad hoc roles which extend acs-rels, could
> easily be solved in the permissions system IF object
> types themselves were objects.

Well, that would be a bit more difficult, because these ad-hoc roles depend on the project. So one guy can be a Project Manager of one project (and see everything) and be a tester in another one.

There are two other aspects that proved useful:

- We have integrated these ad-hoc roles with our filestorage
module. So we can set file access permissions for a
"tester" or a "full member".

- We also use acs_rels for connecting other business
objects, such companies with 1 or more offices, users
with their companies, invoices with the customer and
the provider etc. So it's a uniform way of connecting
objects, and one guy at La Salle is working on a
graphical object browser (a la "The Brain"):
http://www.kurzweilai.net/brain/frame.html?startThought=Artificial%20Intelligence%20(AI)

Bests,
Frank