From: | Richard Huxton <dev(at)archonet(dot)com> |
---|---|
To: | Tony Cebzanov <tonyceb(at)andrew(dot)cmu(dot)edu> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Avoiding cycles in a directed graph |
Date: | 2010-03-16 20:17:22 |
Message-ID: | 4B9FE752.4080709@archonet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On 16/03/10 19:45, Tony Cebzanov wrote:
> I'm using the following table to represent an acyclic directed graph:
[snip]
> I see there is an example in the online docs for detecting cycles in
> recursive queries, and I've adapted the example query to the table above:
[snip]
> That's great to avoid looping forever on queries, but what about
> preventing anyone from inserting edges that would create cycles in the
> first place? I reckon I'll need a trigger of some sort, but nothing
> I've tried has enabled me to do the cycle check as part of the trigger
> to avoid inserting an edge that would create a cycle.
You've got the right idea.
If you know that the table doesn't contain any cycles at the moment,
then all the trigger has to do is:
1. Check "parent" <> "child"
2. Build the set of parents of "parent".
3. Check "child" isn't in that set.
4. If there is a cycle, raise an error (which will abort the insert or
update)
If you have a "before" trigger, then step 4 could return NULL rather
than raise an error. That would just skip the insert.
Also, step #1 could be done with a CHECK constraint which would be
checked before your trigger gets run.
--
Richard Huxton
Archonet Ltd
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2010-03-16 20:26:58 | Re: installing uuid generators |
Previous Message | Richard Huxton | 2010-03-16 20:11:13 | Re: installing uuid generators |