Re: FW: BUG in trigger and foreign keys

If try it with the same  script - but without the constraints - you will see the difference.
And there should not be any since the data is the same in the tables.
This causes the tree structure to be updated incorrectly ( the reseller table is a tree and the delete trigger should
updatethe tree structure). 
You should also note that all the trigger invocations other  that the first one are caused by the delete that is issued
inthe first invocation of the trigger. 
But before that the l_val and r_val are set to NULL for the records to be deleted. So the trigger should print out
NULL'sand not the old values that are valid only when trigger is in his first invocation. 

The tree article is :
http://archives.postgresql.org/pgsql-sql/2002-05/msg00039.php

    regards, jefim.

> Following sample demonstrates incorrect behavior of the trigger.
> The problem happens only when the table that has  the trigger also has foreign key constraints.

What makes you think this is a bug?  It looks to me like you simply
haven't thought about the interaction between your AFTER DELETE trigger
and the triggers that implement the foreign-key behavior (in particular,
the ON UPDATE CASCADE and ON DELETE CASCADE clauses).

            regards, tom lane