From: Michael Glaesemann <grzm@seespotcode.net>
To: arnaulist@andromeiberica.com
Cc: PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: Re: Advice about how to delete
Date: Fri, 6 Jul 2007 10:04:54 -0500
Message-ID: <9583C488-0CE8-46FE-A58F-9D2DC8B6E201@seespotcode.net> (raw)
In-Reply-To: <468E54D7.8020807@andromeiberica.com>
References: <468E54D7.8020807@andromeiberica.com>
On Jul 6, 2007, at 9:42 , Arnau wrote:
> 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:
> IF v_count = 1 THEN
> DELETE FROM users WHERE user_id = result.user_id;
> v_deleted = v_deleted + 1;
> END IF;
Am I right in reading that you're deleting any users that would be
orphans? If so, you can just delete the orphans after rather than
delete them beforehand (untested):
-- delete user_group
DELETE FROM user_groups
WHERE user_group_id = p_group_id;
-- delete users that don't belong to any group
DELETE FROM users
WHERE user_id IN (
SELECT user_id
LEFT JOIN user_groups
WHERE group_id IS NULL);
This should execute pretty quickly. You don't need to loop over any
results. Remember, SQL is a set-based language, so if you can pose
your question in a set-based way, you can probably find a pretty
good, efficient solution.
Michael Glaesemann
grzm seespotcode net
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: grzm@seespotcode.net, arnaulist@andromeiberica.com
Subject: Re: Advice about how to delete
In-Reply-To: <9583C488-0CE8-46FE-A58F-9D2DC8B6E201@seespotcode.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox