Message-ID: <468E54D7.8020807@andromeiberica.com>
Date: Fri, 06 Jul 2007 16:42:31 +0200
From: Arnau <arnaulist@andromeiberica.com>
Reply-To: arnaulist@andromeiberica.com
User-Agent: Thunderbird 2.0.0.4 (Windows/20070604)
MIME-Version: 1.0
To: PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: Advice about how to delete
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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