Forum OpenACS Development: Re: Bug in .LRN/openacs group memberships

Collapse
Posted by Dave Bauer on
Tom,

To print out the acs_rels with null rel_id, it does a left join. The point is, there is a row in party_approved_member_map but the user is not a member of the group according to acs_rels. The rel_id is null just filters out the ones that have a missing acs_rel.

I will post more when I look into how the rels are added and deleted and see if I can reproduce this.

Collapse
Posted by Dave Bauer on

Here is the function that is having the problem:

create or replace function party_approved_member__remove_one(integer, integer, \
integer) returns integer as '
declare
  p_party_id alias for $1;
  p_member_id alias for $2;
  p_rel_id alias for $3;
begin

  delete from party_approved_member_map
  where party_id = p_party_id
    and member_id = p_member_id
    and tag = p_rel_id;

  return 1;

end;' language 'plpgsql';

There are rows in party_approved_member_map where tag is 1 instead of the rel_id. I have not figured out where these came from yet.

Collapse
Posted by Gustaf Neumann on
Dave,

we just looked into the problem on our learn server. Although the original query (https://openacs.org/forums/message-view?message_id=1172094) returns a few thousand entries, we seem to have a different situation, since we have there a value for tag of 0 and not 1 as in your case. For us, all these entries have party_id = member_id and tag == 0. These entries come most probably from parties_in_tr(), which adds identity rows when parties are created. These parties are not users in our case. Since we delete only seldomly parties, these entries are there, they would be removed trough parties_del_tr(). So, I tend to believe that for our site, everything seems fine (but we have many differences from dotlrn, even in the data model).

Tom said

Offhand I would say that there is some confusion with tag.
.... the basic problem is that the field does not have a reference constraint ..... data model problem which was somehow abused by certain applications ...
In principle agreed. The name "tag" is bad, it is as well bad, that there is no fk. Others might know better than me (i am not a veteran from the ad days), but from looking at the code, i got the impression, the basic idea might have been, that "tag" is some arbitrary "client_data" (for maybe multiple purposes). In todays version it is used only for "0" or a rel_id.

Since the key of party_approved_member_map consists of (party_id, member_id, tag), tag can't be a NULL value, therefore a rel_id for the identity rows should be created (an identity rel) and added instead of the 0 when parties are added. Renaming tag to rel_id would help to avoid confusions. These are just my 2 cents.

This does most probably not help Dave with his original problem. Dave, do you have any idea, where the entries with tag=1 are added? Is there anything special about the party_ids and member_ids (are they equal, ancient, etc.)?

-gustaf

Collapse
Posted by Dave Bauer on
This query shows that pretty much every rel type can develop this problem.
select distinct object_type from acs_objects o, acs_rels r, party_approved_member_map m where m.tag = 1 and m.party_id=r.object_id_one
and m.member_id = r.object_id_two
and o.object_id = r.rel_id;

         object_type
------------------------------
 dotlrn_admin_profile_rel
 dotlrn_admin_rel
 dotlrn_ca_rel
 dotlrn_cadmin_rel
 dotlrn_external_profile_rel
 dotlrn_guest_rel
 dotlrn_instructor_rel
 dotlrn_member_rel
 dotlrn_non_guest_rel
 dotlrn_professor_profile_rel
 dotlrn_student_profile_rel
 dotlrn_student_rel
 dotlrn_ta_rel
 membership_rel
(14 rows)
Collapse
Posted by Dave Bauer on
I can't seem to (so far) reproduce a new entry into party_approved_member_map with tag==1, although if I check my database, I can see a new membersip_rel created today that has a tag of 1 in the party_approved_member_map.

Reviewing all the triggers they look ok to me. Everything passes around the actual rel_id from the membership_rel row the trigger runs on.

Collapse
Posted by Tom Jackson on
Offhand I would say that there is some confusion with tag. Why would a pl function take in something called a rel_id (p_rel_id), and equate that with a tag. In my mind, a tag is a string, but it must refer to something else, and the basic problem is that the field does not have a reference constraint. If it did, then only valid tags could be used, and then you could guarantee that the tag was unique. The pl function takes in a compound primary key, but the tag values are not enforced correctly to allow it to be part of a compound primary key.

So partly this looks like a data model problem which was somehow abused by certain applications. If the data model problem was removed, developers would have discovered that things didn't work very quickly, and would have fixed their code. But the API that was listed above is not necessarily in error. It might be the one correct piece in this, hard to say just yet.

I ran these queries with an old 5.0... version of OpenACS and didn't find any tags = 1, no object_id = 1. So I can't add anything to the meaning of the 1.