Thread: Determin if cascade is being used

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 would also greatly speed up the trigger since it could skip any
updates at all!   Thanks in advance.


    Martin Foster
    martin@ethereal-realms.org

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

Martin Foster <martin@ethereal-realms.org> writes:
> 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.

Couldn't you do that as an ON DELETE SET NULL foreign key reference,
instead of using a custom trigger?

            regards, tom lane

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

Tom Lane wrote:
>>>Couldn't you do that as an ON DELETE SET NULL foreign key reference,
>>>instead of using a custom trigger?
>
>
>>How simple would it be to have the three tables linked in a circular
>>fashion?
>
>
> People do it.  I dunno if there are any gotchas ... but if there are,
> you could complain about a bug instead of asking for a feature we're
> unlikely to provide ;-)
>
>             regards, tom lane

Would it be a bug however?   Is it a bug to remove elements required for
referential-integrity before the cascade is complete?

Personally I'd prefer to just know if this is a CASCADE and disable the
trigger for such purposes.   May seem odd, but it can certainly speed
things up.

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