Hi all,

I have the following scenario, I have users and groups where a user can belong to n groups, and a group can have n users. A user must belogn at least to a group. So when I delete a group I must check that there isn't any orphan. To do this I have something like that:

  CREATE TABLE users
  (
    user_id    SERIAL8 PRIMARY KEY
    user_name  VARCHAR(50)
  )

  CREATE TABLE groups
  (
    group_id    SERIAL8 PRIMARY KEY,
    group_name  VARCHAR(50)
  )

  CREATE TABLE user_groups
  (
    user_id   INT8 REFERENCES users(user_id),
    group_id  INT8 REFERENCE groups(group_id),
    CONSTRAINT pk PRIMARY_KEY ( user_id, group_id)
  )

  CREATE INDEX idx_user_id ON user_groups( user_id );
  CREATE INDEX idx_group_id ON user_groups( group_id );

  FUNCTION delete_group( INT8 )
  DECLARE
    p_groupid ALIAS FOR $1;
    v_deleted INTEGER;
    v_count   INTEGER;
    result    RECORD;

  BEGIN
    v_deleted = 0;

FOR result IN SELECT user_id FROM user_groups WHERE group_id = p_groupid
    LOOP

SELECT INTO v_count COUNT(user_id) FROM user_groups WHERE user_id = result.user_id LIMIT 2;

      IF v_count = 1 THEN
        DELETE FROM users WHERE user_id = result.user_id;
        v_deleted = v_deleted + 1;
      END IF;

    END LOOP;

    DELETE FROM groups WHERE group_id = p_groupid;

    RETURN v_deleted;
  END;


This works quite fast with small groups but when the group has an important number of users, it takes too much time. The delete_group action is fired from the user interface of the application.

  Do you have any idea about how I could improve the performance of this?

Thanks all
--
Arnau

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

              http://archives.postgresql.org

Reply via email to