[EMAIL PROTECTED] (Tom Lane) writes: > Alvaro Herrera <[EMAIL PROTECTED]> writes: >> Gerardo Herzig escribió: >>> Yes, the TRUNCATE statement is not sql ansi, maybe is a more low level >>> thing than i think. > >> TRUNCATE currently does not fire triggers, but that doesn't mean it's >> impossible to do it. I think it would be fairly easy to add support >> for that. > > The entire point of TRUNCATE is to not do a table scan, so making it > fire per-row triggers seems pretty misguided to me. > > We could maybe make it fire per-statement ON DELETE triggers, but > there's a future-proofing pitfall in that: someday it'd be nice > for statement-level triggers to have access to the set of deleted rows, > and then you'd be stuck either scanning the table or having TRUNCATE > act differently from plain DELETE. > > My feeling is that if you want to know what was deleted, you shouldn't > use TRUNCATE.
No, what would be nice to have is NOT per-row triggering, but rather simply the ability to run a stored function ON TRUNCATE. This would be useful for Slony-I: - On replica nodes, we might add a trigger: create trigger t_trunc before truncate on my_table for each statement execute _sl_cluster.deny_truncate(); which would raise the error: "Slony-I: Cannot TRUNCATE on subscriber node!" - On the "master" we might add a trigger: create trigger t_trunc before truncate on my_table for each statement execute _sl_cluster.createEvent('sl_cluster', 'TRUNCATE_TABLE', 14); which would generate a 'TRUNCATE_TABLE' event that would tell other nodes to truncate table #14, that is, my_table. For the case where people want to track "COUNT(*)" on a table using triggers, TRUNCATE presently throws that off. With a truncate trigger, we might implement the following: create trigger t_trunc before truncate on my_table for each statement execute purge_table('public', 'my_table'); create or replace function purge_table (text,text) returns null as $$ delete from count_summary_table where nspname = $1 and tabname = $2 $$ language sql; That's three use cases, so far, none of which expect to have access to the data that is being truncated. -- "cbbrowne","@","acm.org" http://linuxfinances.info/info/rdbms.html Security-wise, NT is a server with a "Kick me" sign taped to it. -- Peter Gutmann in the Scary Devil Monastery ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org