Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
[rearranging so results directly follow statements]
> select * from test;
> id | parent | data | nchildren
> ----+--------+--------------+-----------
> 2 | 1 | root child A | 1
> 4 | 2 | grandchild 1 | 0
> 3 | 1 | root child B | 1
> 5 | 3 | grandchild 2 | 0
> 1 | | root! | 2
> (5 rows)
> delete from test;
> DELETE 4
> select * from test;
> id | parent | data | nchildren
> ----+--------+-------+-----------
> 1 | | root! | 0
> (1 row)
And other minor updates to the data column can result in totally
different sets of rows left over after a DELETE from the table with
no WHERE clause. It makes me pretty queasy whenever the semantics
of a statement can depend on the order of tuples in the heap. It's
pretty hard to view this particular case as anything other than a
bug.
> the reason being that when the outer delete arrives at the last
> (root!) row, GetTupleForTrigger fails because the tuple is already
> self-updated, and we treat that as canceling the outer delete
> action.
>
> I'm not sure what to do about this. If we throw an error there,
> there will be no way that the trigger can override the error
> because it will never get to run. Possibly we could plow ahead
> with the expectation of throwing an error later if the trigger
> doesn't cancel the update/delete, but is it safe to do so if we
> don't hold lock on the tuple? In any case that idea doesn't help
> with the remaining caller, ExecLockRows.
I'm still trying to sort through what could be done at the source
code level, but from a user level I would much rather see an error
than such surprising and unpredictable behavior.
-Kevin