From: Jonathan Knopp <pgsql@delegated.net>
To: pgsql-sql@postgresql.org
Subject: Re: Way to stop recursion?
Date: Fri, 26 Nov 2004 15:56:52 -0800
Message-ID: <41A7C2C4.7010305@delegated.net> (raw)
In-Reply-To: <opsh3e2ph5cq72hf@musicbox>
References: <41A79A2A.6070606@delegated.net>
<20041126213448.GB2312@phlogiston.dyndns.org>
<opsh3e2ph5cq72hf@musicbox>
Pierre-Frédéric Caillaud wrote:
> - Change the name of your table to "hidden_table"
>
> - Create a view which is a duplicate of your table :
> CREATE VIEW visible_table AS SELECT * FROM hidden_table;
>
> -> Your application now accesses its data without realizing it goes
> through a view.
>
> Now create a rule on this view, to make it update the real
> hidden_table. As the rule does not apply to hidden_table, it won't
> recurse.
>
> Other solution (this similar to what Tom Lane proposed I think) :
>
> Create a field common_id in your table, with
> - an insert trigger which puts a SERIAL default value if there is no
> parent, or copies the parent's value if there is one
> - an update trigger to copy the new parent's common_id whenever a child
> changes parent (if this ever occurs in your design)
>
> Now create another table linking common_id to the 'common' value.
>
> Create a view which joins the two, which emulates your current behaviour.
> Create an ON UPDATE rule to the view which just changes one row in the
> link table.
>
> If you do a lot of selects, solution #1 will be faster, if you do a lot
> of updates, #2 will win...
The "hidden table" method should work just fine. Ingenius idea, thank you!
> Just out of curiosity, what is this for ?
The actual application has companies instead of parents, employees
instead of children, then emails as children of employees and/or
companies, and folders as parents of companies and employees. The
"common" field (in all 4 layers) are a pair of permissions flags.
May I humbly suggest two possible todo's for postgreSQL: a simple flag
to suppress recursion (easier/more powerful way of doing the above),
and/or more direct access to query rewriting. Seems right now rules
require you to rewrite queries while partially blind to them. Being able
to rewrite queries in statement triggers similar to what can be done
with row triggers would be very nice too.
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: pgsql@delegated.net
Subject: Re: Way to stop recursion?
In-Reply-To: <41A7C2C4.7010305@delegated.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