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