Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-bugs by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group