truncate in combination with deferred triggers

From: Markus Schiltknecht <markus(at)bluegap(dot)ch>
To: pgsql-bugs(at)postgresql(dot)org
Cc: tgl(at)sss(dot)pgh(dot)pa(dot)us
Subject: truncate in combination with deferred triggers
Date: 2006-08-21 18:24:12
Message-ID: 44E9FA4C.9060005@bluegap.ch
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2006-08-21 18:56:54 Re: truncate in combination with deferred triggers
Previous Message Josh Berkus 2006-08-21 17:48:35 Re: Handling of \ in array data display