Re: Determin if cascade is being used - Mailing list pgsql-novice

From Martin Foster
Subject Re: Determin if cascade is being used
Date
Msg-id 4353F9B5.708@ethereal-realms.org
Whole thread Raw
In response to Re: Determin if cascade is being used  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-novice
Tom Lane wrote:
> Martin Foster <martin@ethereal-realms.org> writes:
>
>>Is there a way to tell in PL/PgSQL if the delete coming down the line is
>>from a cascade delete?   I have a trigger, which when called will throw
>>an error because the row its trying to change is now linked with an item
>>which no longer exists.   The foreign key constraint fails since it was
>>removed before underlaying components were.
>
>
> This sounds like you're trying to do manual enforcement of a foreign key
> relationship.  Wouldn't it be better to let the standard FK mechanism
> handle it for you?
>
> (But the short answer is no, I don't think you can tell that from
> plpgsql, or even very reasonably from a C trigger.)
>
>             regards, tom lane

Not trying to enforce anything, really.    Lets do a quick example to
show what I mean, with the following three tables:

   Gallery
   Layer
   Work

In the above a gallery holds layers who in turn hold works.   Now when I
remove a gallery it will remove all layers and works associated with it.
  Pretty simple and straight forward.

Now what happens is that every time a work is updated or removed it will
make sure that the parent layers and gallery do not use it as a
highlight.   Meaning that the trigger upon update/delete of Works will
update Layer and Gallery.

What happens however is that an error is thrown when I delete the
gallery because when it attempts to change the gallery and layer it
can't because they have already been removed.

I figured, if I could tell that this was a CASCADE removal, then I could
change the trigger to take no action on such deletes because there is no
sense in updating rows that will be removed in a short order of time.
The foreign-key constraint is kicked in because the Gallery row is
removed before any row from Work and Layer are.

Does this make sense?

    Martin Foster
    Creator/Designer Ethereal Realms
    martin@ethereal-realms.org


pgsql-novice by date:

Previous
From: Tom Lane
Date:
Subject: Re: Determin if cascade is being used
Next
From:
Date:
Subject: FATAL: missing or erroneous pg_hba.conf file