Re: storing access rights in a postgres database

On 10/10/06, tv(at)fuzzy(dot)cz <tv(at)fuzzy(dot)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.com
http://codeelixir.com
==================================================================