Re: ON DELETE SET NULL clauses do error when more than two columns are referenced to one table

Pavel Stehule wrote:
> One question on www.abclinuxu.cz signalise bug in PostgreSQL RI
> implementation. Detected on 8.0.x and verified on 8.3.

What seems to happen under the hood is:

1. The row in users is deleted
2. The setnull trigger on owner is fired, which executes "UPDATE ONLY
users SET owner = NULL WHERE owner = 3"
3. That update fires the update-triggers on tasks-table, to check that
the referenced row exists in users. Because owner is now null, it's not
checked. Worker and checked_by are not checked, because
AfterTriggerSaveEvent sees that those columns were not modified.
4. The setnull trigger on worker is fired, which executes "UPDATE ONLY
users SET worker = NULL where worker = 3".
5. That update again fires the update-triggers on tasks, to check that
the referenced row exists in users. Owner and worker are now null, so
they're not checked. However, checked_by is not null, so a the trigger
to check that the referenced row in the users table exists ("SELECT 1
FROM users WHERE id=3 FOR SHARE"), which fails.

The ON UPDATE trigger is not fired in step 2, because of the
optimization in AfterTriggerSaveEvent to skip UPDATE triggers if the FK
column was not changed. However, we don't do the optimization if the old
tuple was created in the same transaction:

case RI_TRIGGER_FK:
/*
* Update on FK table
*
* There is one exception when updating FK tables: if the
* updated row was inserted by our own transaction and the
* FK is deferred, we still need to fire the trigger. This
* is because our UPDATE will invalidate the INSERT so the
* end-of-transaction INSERT RI trigger will not do
* anything, so we have to do the check for the UPDATE
* anyway.
*/
if (HeapTupleHeaderGetXmin(oldtup->t_data) !=
GetCurrentTransactionId() &&
RI_FKey_keyequal_upd_fk(trigger, rel, oldtup, newtup))
{
continue;
}
break;

which is why we do fire the trigger in step 5.

ISTM the real problem is that the RI triggers are fired in wrong order.
The above optimization saves the day in simple scenarios, but it seems
to be an unintended side-effect.

In fact, you can trigger the problem with a child table with just two ON
DELETE SET NULL columns, if you do a dummy update of the child row
before the delete of the parent, in the same transaction:

CREATE TABLE users (
id integer NOT NULL,
name VARCHAR NOT NULL,
PRIMARY KEY (id)
);

INSERT INTO users VALUES (3, 'Samko');

CREATE TABLE tasks (
id integer NOT NULL,
a INT REFERENCES users (id) ON DELETE SET NULL,
b INT REFERENCES users (id) ON DELETE SET NULL
);
INSERT INTO tasks VALUES (3,3,3);

BEGIN;
UPDATE tasks set id=id WHERE id=3;
DELETE FROM users WHERE id = 3; -- doesn't work, why?
COMMIT;

I'm not sure what to do about this. We could change the order the
triggers are fired to breadth-first. If all the setnull triggers were
executed first, there would be no problem. But that seems like a pretty
big change, and I'm afraid it might have other unintended consequences.

Ideas?

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com