Thread: truncate in combination with deferred triggers

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

Markus Schiltknecht <markus@bluegap.ch> writes:
> 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'

Hm.  At least for this case, it seems the nicest behavior would be for
TRUNCATE to scan the deferred-triggers list and just throw away any
pending trigger firings for the target table(s).  I wonder however
whether there are cases where that would be a bad idea.  It might be
safer for the TRUNCATE to error out if there are any pending triggers.
Stephan, any thoughts about it?

            regards, tom lane

On Mon, 21 Aug 2006, Tom Lane wrote:

> Markus Schiltknecht <markus@bluegap.ch> writes:
> > 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'
>
> Hm.  At least for this case, it seems the nicest behavior would be for
> TRUNCATE to scan the deferred-triggers list and just throw away any
> pending trigger firings for the target table(s).  I wonder however
> whether there are cases where that would be a bad idea.  It might be
> safer for the TRUNCATE to error out if there are any pending triggers.
> Stephan, any thoughts about it?

Yeah, I think there are a few possibilities around truncate inside a
savepoint that's rolledback that we have to be careful of.

I think

BEGIN;
 INSERT INTO category (id, name) VALUES (0, 'test');
 INSERT INTO category_todo (cat_id) VALUES (0);
 SAVEPOINT foo;
  TRUNCATE category_todo;
 ROLLBACK TO SAVEPOINT foo;
COMMIT;

needs to check the values on the commit.

I'd then thought we could postpone removing them to the commit before
checking, but then SET CONSTRAINTS ALL IMMEDIATE would still fail in
something like

BEGIN;
 INSERT INTO category (id, name) VALUES (0, 'test');
 INSERT INTO category_todo (cat_id) VALUES (0);
 TRUNCATE category_todo;
 SET CONSTRAINTS ALL IMMEDIATE;
COMMIT;

If we could mark the entries in some way so we knew whether or not they
were made obsolete by a truncate of our own tranasaction or a committed or
rolled back past subtransaction of ours, we could probably make both of
these work nicely.

Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> Yeah, I think there are a few possibilities around truncate inside a
> savepoint that's rolledback that we have to be careful of.

Yuck :-(

> If we could mark the entries in some way so we knew whether or not they
> were made obsolete by a truncate of our own tranasaction or a committed or
> rolled back past subtransaction of ours, we could probably make both of
> these work nicely.

That seems much more trouble than it's worth, unless someone can
convince me that this isn't a corner case with little real-world value.

Furthermore, this still doesn't address the worry about whether there
are cases where dropping the trigger calls would be inappropriate.

I propose just having TRUNCATE check for pending triggers on the
target tables, and throw an error if there are any.

            regards, tom lane

On Mon, 21 Aug 2006, Tom Lane wrote:

> Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> > Yeah, I think there are a few possibilities around truncate inside a
> > savepoint that's rolledback that we have to be careful of.
>
> Yuck :-(
>
> > If we could mark the entries in some way so we knew whether or not they
> > were made obsolete by a truncate of our own tranasaction or a committed or
> > rolled back past subtransaction of ours, we could probably make both of
> > these work nicely.
>
> That seems much more trouble than it's worth, unless someone can
> convince me that this isn't a corner case with little real-world value.
>
> Furthermore, this still doesn't address the worry about whether there
> are cases where dropping the trigger calls would be inappropriate.

I don't believe there are for foreign keys on the referring side since the
post-truncate case is trivially satisfying the constraint, but I can
imagine that there might exist other uses for deferred triggers for which
one might care.

> I propose just having TRUNCATE check for pending triggers on the
> target tables, and throw an error if there are any.

That sounds reasonable to me, although I don't much use truncate in the
first place.

Is this a TODO or is it going to be fixed for 8.2?

---------------------------------------------------------------------------

Tom Lane wrote:
> Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> > Yeah, I think there are a few possibilities around truncate inside a
> > savepoint that's rolledback that we have to be careful of.
>
> Yuck :-(
>
> > If we could mark the entries in some way so we knew whether or not they
> > were made obsolete by a truncate of our own tranasaction or a committed or
> > rolled back past subtransaction of ours, we could probably make both of
> > these work nicely.
>
> That seems much more trouble than it's worth, unless someone can
> convince me that this isn't a corner case with little real-world value.
>
> Furthermore, this still doesn't address the worry about whether there
> are cases where dropping the trigger calls would be inappropriate.
>
> I propose just having TRUNCATE check for pending triggers on the
> target tables, and throw an error if there are any.
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match

--
  Bruce Momjian   bruce@momjian.us
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +