Hello Tom,
in June I've complained about a 'failed to fetch new tuple for AFTER
trigger' error and you requested a test case here:
http://archives.postgresql.org/pgsql-general/2006-07/msg00855.php
I finally got around to strip down the problem. The error first occurred
to me using a 8.2devel of May 11, testing with the current code still
reveals the error. The greatly simplified test case I came up with is:
CREATE TABLE category (
id INT PRIMARY KEY,
name TEXT);
CREATE TABLE category_todo (
cat_id INT REFERENCES category(id)
DEFERRABLE INITIALLY DEFERRED
);
BEGIN;
INSERT INTO category (id, name) VALUES (0, 'test');
INSERT INTO category_todo (cat_id) VALUES (0);
TRUNCATE category_todo;
COMMIT;
-- COMMIT fails with: 'failed to fetch new tuple for AFTER trigger'
The combination of the DEFERRED trigger (for foreign key checking)
together with TRUNCATE seems to be the killer here. You can either use
DELETE FROM instead of TRUNCATE or remove the 'DEFERRABLE INITIALLY
DEFERRED' of the foreign key and the problem disappears.
The manual only states that: "TRUNCATE cannot be used on a table that
has foreign-key references from other tables..." and that "TRUNCATE will
not run any user-defined ON DELETE triggers". My understanding is that
this constraint is a deferred ON INSERT trigger, not an ON DELETE trigger.
Couldn't all the deferred triggers for a table be dropped on truncation?
Or does that need a table scan? Could there be a better error message in
that case?
Regards
Markus
