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