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...7/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


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Markus Schiltknecht <markus (AT) bluegap (DOT) ch> writes:
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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

On Mon, 21 Aug 2006, Tom Lane wrote:

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.

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Stephan Szabo <sszabo (AT) megazone (DOT) bigpanda.com> writes:
Yuck :-(

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

On Mon, 21 Aug 2006, Tom Lane wrote:

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.

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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

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

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

Tom Lane wrote:
--
Bruce Momjian bruce (AT) momjian (DOT) us
EnterpriseDB http://www.enterprisedb.com

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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings