Forum OpenACS Q&A: Duplicates in registered_users

Collapse
Posted by Samer Abukhait on
I added some groups, mapped some users to them
When querying registered_users, I found duplicate rows for them.

Is this a know bug or my system behaves strangely???

Group-User mapping process created more maps for the user in registered users group (with the container being the new group-user relation).
For every new mapping for the user in a group, a new duplicated row comes out for him in registered_users.

Any Advice?

Collapse
Posted by Samer Abukhait on
I solved this temporarily by adding

  and m.container_id = m.group_id

in registered_users.

But is this a correct thing to do?

Collapse
Posted by Don Baccus on
Can you give some more information?  Like the query and the output of the query?
Collapse
Posted by Samer Abukhait on
select user_id, count(*) from registered_users
group by user_id
having count(*) > 1

this should give no rows at all.

It is giving me a row for any user mapped to any group, the count being the number of groups he is mapped to.

Mapping a user to a group maps him to registered_users also?

Weird!

Collapse
Posted by Don Baccus on
How did you add the group?  Did you use a composition rel to relate the new group to registered users?
Collapse
Posted by Samer Abukhait on
No

I just added groups, using

/admin/group-types/one?group_type=group

no composition rels defined.

Collapse
Posted by Peter Marklund on
I can reproduce this problem by going to the start page of a clean OpenACS install, click the members link, click invite, and choose to create a new user. Make the new user an admin (the duplication doesn't seem to occur otherwise). Here is my psql debug output and workaround (different from Samer's workaround interestingly) from after having added the admin user (mailto:peter@collaboraid.biz is the new user I created):

select user_id, email from registered_users;
user_id |        email
---------+-----------------------
    584 | mailto:demo@demo.test
    653 | mailto:peter@collaboraid.biz
    653 | mailto:peter@collaboraid.biz
(3 rows)

select * from group_member_index;
group_id | member_id | rel_id | container_id |    rel_type
----------+-----------+--------+--------------+----------------
      -1 |        0 |      5 |          -1 | membership_rel
      -2 |      584 |    585 |          -2 | membership_rel
      -1 |      584 |    585 |          -2 | membership_rel
      -2 |      653 |    655 |          -2 | membership_rel
      -1 |      653 |    655 |          -2 | membership_rel
      -2 |      653 |    656 |          -2 | admin_rel
      -1 |      653 |    656 |          -2 | admin_rel

(note user is both in admin_rel and membership_rel).

simulation=# drop view registered_users;
DROP VIEW
simulation=# create view registered_users
as
  select p.email, p.url, pe.first_names, pe.last_name, u.*, mr.member_state
  from parties p, persons pe, users u, group_member_map m, membership_rels mr, acs_magic_objects amo
  where party_id = person_id
  and person_id = user_id
  and u.user_id = m.member_id
  and m.rel_id = mr.rel_id
  and amo.name = 'registered_users'
  and m.group_id = amo.object_id
  and mr.member_state = 'approved'
  and m.rel_type = 'membership_rel'
  and u.email_verified_p = 't';

select user_id, email from registered_users;
user_id |        email
---------+-----------------------
    584 | mailto:demo@demo.test
    653 | mailto:peter@collaboraid.biz
(2 rows)

Don, Lars and others, what do you make of this?

Collapse
Posted by Peter Marklund on
For clarification of my workaround, what I did was to add the where clause:

  and m.rel_type = 'membership_rel'

to registered_users (it would also need to be added to cc_users and potentially in other places).

Collapse
Posted by Dave Bauer on
The registered users view looks like it is broken. I never used it so I never noticed when I was doing work with permissions.

A party can be a member of a group with more than one relationship. If a user has an admin relationship, it is a subtype of membership_rel and the user will have an entry for both in the group_member_map.

I think using group_id=container_id is the right way to fix this.

When I created a group model all my queries used group_member_map and usually did need the where group_id=container_id clause.

When doing permissions queries you may also need to limit by the rel_type which is what Peter seems to be seeing in the query he tested.

Collapse
Posted by Dave Bauer on
I should have reread the original post.

If your group is a component of the main subsite applicaiton group (i think all groups created through the acs-subsite admin pages are) then your users will be a member of the group  and any other group the original group is a component of.

So if you want to see members that are direct members of a group and not due to a composite relationship you need to specify group_id=container_id.

I guess it depends on what the registered_users view should be used for if it should be changed or if the current behavior is correct.

Collapse
Posted by Peter Marklund on
I filed this bug in the Bug Tracker as #1144. We must fix this for 5.0.
Collapse
Posted by Don Baccus on
I will look into this.  It appears that we have an admin group instead of an admin relational segment?  If true, why? Is it too late to fix this?  This is a perfect example of where relational segments are useful.

group_element_index content is fine, there's no problem there.

I think either approach - filtering by "membership_rel" or by container_id=group_id is fine for fixing registered_users.

Collapse
Posted by Lars Pind on
I created the admin as a rel type and a rel segment. But maybe the problem is that I made admin_rel a subtype of membership_rel, the thinking being that admins would also be members.

/Lars

Collapse
Posted by Don Baccus on
No, that's fine, Lars, I forgot that rel_segs show up in that denormalized table.  Had to so existing code would work.

Peter - checking for membership_rel (rather than group_id = container_id) is the conceptually correct thing to do, because the "registered users" view is meant to be "all members of the registered users group" which is defined as those with membership_rel.

The other works but is more obscure IMO.

Collapse
Posted by Don Baccus on
OK something's messed up and I'm not quite sure what.  There shouldn't be two rows in the rel_case, that's for sure ... and my 5.0 install doesn't have this problem.  I add a "test" user make them an admin and I only have one row for "test" in "registered_users", with the rel type being "admin_rel".

And checking for "membership_rel" is wrong BTW, I now realize that ...