Re: truncate in combination with deferred triggers

On Mon, 21 Aug 2006, Tom Lane wrote:

> Markus Schiltknecht <markus(at)bluegap(dot)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.