From: Aaron Bono <postgresql@aranya.com>
To: tv@fuzzy.cz <tv@fuzzy.cz>
Cc: pgsql-sql@postgresql.org
Subject: Re: storing access rights in a postgres database
Date: Fri, 13 Oct 2006 16:16:56 -0500
Message-ID: <bf05e51c0610131416t695282e6u67c52fec36109bf3@mail.gmail.com> (raw)
In-Reply-To: <1160487514.452ba25a936ea@mail.fuzzy.cz>
References: <1160487514.452ba25a936ea@mail.fuzzy.cz>
On 10/10/06, tv@fuzzy.cz <tv@fuzzy.cz> wrote:
>
>
> SELECT id, (
> SELECT allowed FROM rights WHERE user_id = 1 AND (
> (firm_id = projects.firm_id AND project_id = projects.id AND
> subproject_id IS NULL)
> OR (firm_id = projects.firm_id AND project_id IS NULL)
> )
> ORDER BY firm_id, project_id, subproject_id, module_id, allowed DESC
> LIMIT 1
> ) as allowed
> FROM projects;
>
> The problem is in the 'LIMIT 1' clause - that's the reason I can't write
> that as
> a join.
>
> Does someone else has an idea how to solve this? If needed I can send more
> complex examples and some testing data, explain plans, etc.
>
> I've been thinking about some 'intermediate table' with results of the
> subselect, updated by a set of triggers, but maybe there's some better
> solution.
I think your problem is NOT the LIMIT, it is the fact that you are putting a
select inside the select block (your correlated subquery). You should try
left outer joins instead:
SELECT
projects.id,
rights.allowed
FROM projects
LEFT OUTER JOIN rights ON (
(
rights.firm_id = projects.firm_id
AND rights.project_id = projects.id
AND rights.subproject_id IS NULL
) OR (
rights.firm_id = projects.firm_id
AND rights.project_id IS NULL
)
)
GROUP BY
projects.id,
rights.allowed
Using correlated subqueries is really bad (IMHO) because it causes your
query to perform a select for each row returned. I have never seen a
correlated subquery that cannot be refactored into a join (this is a
challenge for any of you who disagree - I would love to see a reason to use
a correlated subquery).
You can get more information about correlated subqueries and performance at
http://www.bcarter.com/sap29.htm .
Also, I would consider putting a conditional unique constraint to enforce
your rule on which fields must be null/not null to help preserve your data
integrity.
Hope this helps!
--
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.comhttp://codeelixir.com
==================================================================
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-sql@postgresql.org
Cc: postgresql@aranya.com, tv@fuzzy.cz
Subject: Re: storing access rights in a postgres database
In-Reply-To: <bf05e51c0610131416t695282e6u67c52fec36109bf3@mail.gmail.com>
* 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