From: Chris Browne <cbbrowne@acm.org>
To: pgsql-sql@postgresql.org
Subject: Re: trigger for TRUNCATE?
Date: Thu, 10 Jan 2008 15:37:54 -0500
Message-ID: <601w8pxvq5.fsf@dba2.int.libertyrms.com> (raw)
References: <4783BEA9.3020507@fmed.uba.ar>
<60tzloyr0u.fsf@dba2.int.libertyrms.com>
<162867790801081338n237497efpecffaa255253274d@mail.gmail.com>
<47864A6B.3070902@fmed.uba.ar>
<20080110171027.GK6465@alvh.no-ip.org>
<8003.1199986734@sss.pgh.pa.us>
tgl@sss.pgh.pa.us (Tom Lane) writes:
> Alvaro Herrera <alvherre@commandprompt.com> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: cbbrowne@acm.org
Subject: Re: trigger for TRUNCATE?
In-Reply-To: <601w8pxvq5.fsf@dba2.int.libertyrms.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox