Thread: trigger for TRUNCATE?
Hi all. Acording to the docs, TRUNCATE will not fire a DELETE trigger on the table being truncated. There is a way to capture a TRUNCATE in any way? Thanks! Gerardo
gherzig@fmed.uba.ar (Gerardo Herzig) writes:
> Hi all. Acording to the docs, TRUNCATE will not fire a DELETE trigger
> on the table being truncated.
> There is a way to capture a TRUNCATE in any way?
I think there's some sort of "to do" on that...
It ought to be not *too* difficult (I imagine!) to be able to
associate a trigger with the TRUNCATE action, and therefore run some
stored function any time TRUNCATE takes place.
For the Slony-I replication system, it would be attractive for this to
lead to attaching two functions: - One function would return an exception so that TRUNCATE against a subscriber node
wouldfail...
- Another would pretty much be as simple as submitting an event; perform createEvent('_ourcluster',
'TRUNCATE_TABLE',table_id);
A new event, TRUNCATE_TABLE, would do a TRUNCATE against the
subscribers.
This represents a pretty easy enhancement, given the new kind of
trigger.
--
(reverse (concatenate 'string "moc.enworbbc" "@" "enworbbc"))
http://www3.sympatico.ca/cbbrowne/nonrdbms.html
Frisbeetarianism: The belief that when you die, your soul goes up on
the roof and gets stuck...
Hello
theoretically you can have trigger on any statement, but I am not sure
about conformance with std. But, you can wrap TRUNCATE statement into
some procedure, and then call this procedure with some other actions.
Regards
Pavel Stehule
On 08/01/2008, Chris Browne <cbbrowne@acm.org> wrote:
> gherzig@fmed.uba.ar (Gerardo Herzig) writes:
> > Hi all. Acording to the docs, TRUNCATE will not fire a DELETE trigger
> > on the table being truncated.
> > There is a way to capture a TRUNCATE in any way?
>
> I think there's some sort of "to do" on that...
>
> It ought to be not *too* difficult (I imagine!) to be able to
> associate a trigger with the TRUNCATE action, and therefore run some
> stored function any time TRUNCATE takes place.
>
> For the Slony-I replication system, it would be attractive for this to
> lead to attaching two functions:
> - One function would return an exception so that TRUNCATE against
> a subscriber node would fail...
>
> - Another would pretty much be as simple as submitting an event;
> perform createEvent('_ourcluster', 'TRUNCATE_TABLE', table_id);
>
> A new event, TRUNCATE_TABLE, would do a TRUNCATE against the
> subscribers.
>
> This represents a pretty easy enhancement, given the new kind of
> trigger.
> --
> (reverse (concatenate 'string "moc.enworbbc" "@" "enworbbc"))
> http://www3.sympatico.ca/cbbrowne/nonrdbms.html
> Frisbeetarianism: The belief that when you die, your soul goes up on
> the roof and gets stuck...
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
Pavel Stehule wrote:
>On 08/01/2008, Chris Browne <cbbrowne@acm.org> wrote:
>
>
>>gherzig@fmed.uba.ar (Gerardo Herzig) writes:
>>
>>
>>>Hi all. Acording to the docs, TRUNCATE will not fire a DELETE trigger
>>>on the table being truncated.
>>>There is a way to capture a TRUNCATE in any way?
>>>
>>>
>>I think there's some sort of "to do" on that...
>>
>>It ought to be not *too* difficult (I imagine!) to be able to
>>associate a trigger with the TRUNCATE action, and therefore run some
>>stored function any time TRUNCATE takes place.
>>
>>For the Slony-I replication system, it would be attractive for this to
>>lead to attaching two functions:
>> - One function would return an exception so that TRUNCATE against
>> a subscriber node would fail...
>>
>> - Another would pretty much be as simple as submitting an event;
>> perform createEvent('_ourcluster', 'TRUNCATE_TABLE', table_id);
>>
>>A new event, TRUNCATE_TABLE, would do a TRUNCATE against the
>>subscribers.
>>
>>This represents a pretty easy enhancement, given the new kind of
>>trigger.
>>--
>>(reverse (concatenate 'string "moc.enworbbc" "@" "enworbbc"))
>>http://www3.sympatico.ca/cbbrowne/nonrdbms.html
>>Frisbeetarianism: The belief that when you die, your soul goes up on
>>the roof and gets stuck...
>>
>>Hello
>>
>>theoretically you can have trigger on any statement, but I am not sure
>>about conformance with std. But, you can wrap TRUNCATE statement into
>>some procedure, and then call this procedure with some other actions.
>>
>>Regards
>>Pavel Stehule
>>
>>
>>
Yes, the TRUNCATE statement is not sql ansi, maybe is a more low level
thing than i think.
Gerardo
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. Currently, Mammoth Replicator does replicate TRUNCATE commands. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
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.
regards, tom lane
Tom Lane escribió: > 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. My thinking is that a TRUNCATE trigger is a per-statement trigger which doesn't have access to the set of deleted rows (Replicator uses it that way -- we replicate the truncate action, and replay it on the replica). In that way it would be different from a per-statement trigger for DELETE. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Tom Lane wrote: >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. > > regards, tom lane > > > I 100% agree, i can live using delete instead, but i can't ensure the whole team i work with will not use TRUNCATE. It was my bad naming the thread with such a contradictory name, im just looking the way to capture it in any form. I would even consider the posibility of *ignoring* a TRUNCATE command, if thats possible. Thanks you all, dudes! Gerardo
Alvaro Herrera <alvherre@commandprompt.com> writes:
> My thinking is that a TRUNCATE trigger is a per-statement trigger which
> doesn't have access to the set of deleted rows (Replicator uses it that
> way -- we replicate the truncate action, and replay it on the replica).
> In that way it would be different from a per-statement trigger for
> DELETE.
Ah, right. I was thinking in terms of having TRUNCATE actually fire the
existing ON DELETE-type triggers, but that's not really helpful --- you'd
need a separate trigger-event type. So we could just say by fiat that
an ON TRUNCATE trigger doesn't get any rowset information, even after we
add that for the other types of statement-level triggers.
Never mind ...
regards, tom lane
Alvaro Herrera wrote: > My thinking is that a TRUNCATE trigger is a per-statement trigger which > doesn't have access to the set of deleted rows. > In that way it would be different from a per-statement trigger for > DELETE. Completely agree. A truncate trigger should run a different function to a delete trigger. This is an important feature for trigger-based replication systems. Not just slony, but bucardo and others too. It's an embarrassing hole in our high availability capabilities and we really need to fill the gap. We can't always control whether an application will issue truncates or not. Rather spookily that's what I've been working on this afternoon, though I didn't realise this thread was in progress until now, nor did I realise there might be possible objections. I do hope the importance of it is enough to overcome objections. Yes, it does look fairly straightforward. Should be ready for when 8.4 opens, assuming we agree. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com
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
tellother 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 =
$1and 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
Tom Lane wrote: > Alvaro Herrera <alvherre@commandprompt.com> writes: >> My thinking is that a TRUNCATE trigger is a per-statement trigger which >> doesn't have access to the set of deleted rows (Replicator uses it that >> way -- we replicate the truncate action, and replay it on the replica). >> In that way it would be different from a per-statement trigger for >> DELETE. > > Ah, right. I was thinking in terms of having TRUNCATE actually fire the > existing ON DELETE-type triggers, but that's not really helpful --- you'd > need a separate trigger-event type. So we could just say by fiat that > an ON TRUNCATE trigger doesn't get any rowset information, even after we > add that for the other types of statement-level triggers. I've always considered TRUNCATE to be DDL rather than DML. I mentally group it with DROP TABLE rather than DELETE> -- Richard Huxton Archonet Ltd
On Jan 11, 2008, at 2:24 AM, Richard Huxton wrote: > Tom Lane wrote: >> Alvaro Herrera <alvherre@commandprompt.com> writes: >>> My thinking is that a TRUNCATE trigger is a per-statement trigger >>> which >>> doesn't have access to the set of deleted rows (Replicator uses >>> it that >>> way -- we replicate the truncate action, and replay it on the >>> replica). >>> In that way it would be different from a per-statement trigger for >>> DELETE. >> Ah, right. I was thinking in terms of having TRUNCATE actually >> fire the >> existing ON DELETE-type triggers, but that's not really helpful >> --- you'd >> need a separate trigger-event type. So we could just say by fiat >> that >> an ON TRUNCATE trigger doesn't get any rowset information, even >> after we >> add that for the other types of statement-level triggers. > > I've always considered TRUNCATE to be DDL rather than DML. I > mentally group it with DROP TABLE rather than DELETE> Not that DDL statement triggers wouldn't be just as useful for replication. Erik Jones DBA | Emma® erik@myemma.com 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com
On Fri, 2008-01-11 at 08:24 +0000, Richard Huxton wrote: > I've always considered TRUNCATE to be DDL rather than DML. I mentally > group it with DROP TABLE rather than DELETE> DDL/DML probably isn't the right split, since its then arguable as to which group of commands it belongs in. I see we have 3 types of commands: 1. Commands that alter the rows in the table e.g. UPDATE, DELETE, INSERT + TRUNCATE is clearly part of this group 2. Commands that change the shape of a table e.g. ALTER TABLE add/drop column, change type, constraints etc 3. Commands that change the environment of a table e.g. foreign keys, indexes, grants, set fillfactor, ANALYZE, VACUUM, CLUSTER etc Type (1) commands need to be replicated always, sliding down the scale to the type (3) which might well be site dependent. Applications seldom issue type 3 commands anyway, so its easy for a DBA to arrange for them to be executed in multiple places and there isn't any timing requirement usually to making that work. In some cases some of these factors might be managed by replication controllers, so the DBA doesn't need to touch at least some of these aspects. Applications do issue some type 2 commands, but usually they are for TEMP tables. Type 2 commands do change replication, but might not need to be exactly replicated on both sites. Again, some utilities exist to ensure that DDL changes are correctly replicated, so there is slightly less need for triggers on this. In many cases the application is locked down completely anyway and almost no DDL is ever executed. If it is executed it needs to be done in coordination with a change of application version. Applications issue lots of type 1 commands and we can't always easily change the SQL they execute. It's very common for an application to have a single userid, so its not a problem for it to be the owner of the table as well and hence TRUNCATE is usable. It is often written without any thought for replication, which is usually an afterthought. (If we allowed RULEs to translate TRUNCATE into DELETEs it would at least plug the gap, but thats not a great planand I'm not suggesting it.) So the main gap in all of this is the lack of a TRUNCATE trigger, probably also the lack of a specific TRUNCATE privilege as well. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com
Added to TODO: > * Add ability to trigger on TRUNCATE > > http://archives.postgresql.org/pgsql-sql/2008-01/msg00050.php --------------------------------------------------------------------------- Simon Riggs wrote: > On Fri, 2008-01-11 at 08:24 +0000, Richard Huxton wrote: > > > I've always considered TRUNCATE to be DDL rather than DML. I mentally > > group it with DROP TABLE rather than DELETE> > > DDL/DML probably isn't the right split, since its then arguable as to > which group of commands it belongs in. > > I see we have 3 types of commands: > > 1. Commands that alter the rows in the table > e.g. UPDATE, DELETE, INSERT + TRUNCATE is clearly part of this group > > 2. Commands that change the shape of a table > e.g. ALTER TABLE add/drop column, change type, constraints etc > > 3. Commands that change the environment of a table > e.g. foreign keys, indexes, grants, set fillfactor, ANALYZE, VACUUM, > CLUSTER etc > > Type (1) commands need to be replicated always, sliding down the scale > to the type (3) which might well be site dependent. > > Applications seldom issue type 3 commands anyway, so its easy for a DBA > to arrange for them to be executed in multiple places and there isn't > any timing requirement usually to making that work. In some cases some > of these factors might be managed by replication controllers, so the DBA > doesn't need to touch at least some of these aspects. > > Applications do issue some type 2 commands, but usually they are for > TEMP tables. Type 2 commands do change replication, but might not need > to be exactly replicated on both sites. Again, some utilities exist to > ensure that DDL changes are correctly replicated, so there is slightly > less need for triggers on this. In many cases the application is locked > down completely anyway and almost no DDL is ever executed. If it is > executed it needs to be done in coordination with a change of > application version. > > Applications issue lots of type 1 commands and we can't always easily > change the SQL they execute. It's very common for an application to have > a single userid, so its not a problem for it to be the owner of the > table as well and hence TRUNCATE is usable. It is often written without > any thought for replication, which is usually an afterthought. (If we > allowed RULEs to translate TRUNCATE into DELETEs it would at least plug > the gap, but thats not a great planand I'm not suggesting it.) > > So the main gap in all of this is the lack of a TRUNCATE trigger, > probably also the lack of a specific TRUNCATE privilege as well. > > -- > Simon Riggs > 2ndQuadrant http://www.2ndQuadrant.com > > > ---------------------------(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> http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
On 11/01/2008, Simon Riggs <simon@2ndquadrant.com> wrote:
I'm not sure Truncate currently 100% fits into this group but I think it should, ought to, or even might.
Create table, drop table, foreign keys, unique indexes, and (currently) truncate (in that is currently the same as a drop followed by a create) also fit into this group
On Fri, 2008-01-11 at 08:24 +0000, Richard Huxton wrote:
> I've always considered TRUNCATE to be DDL rather than DML. I mentally
> group it with DROP TABLE rather than DELETE>
DDL/DML probably isn't the right split, since its then arguable as to
which group of commands it belongs in.
I see we have 3 types of commands:
1. Commands that alter the rows in the table
e.g. UPDATE, DELETE, INSERT + TRUNCATE is clearly part of this group
I'm not sure Truncate currently 100% fits into this group but I think it should, ought to, or even might.
2. Commands that change the shape of a table
e.g. ALTER TABLE add/drop column, change type, constraints etc
Create table, drop table, foreign keys, unique indexes, and (currently) truncate (in that is currently the same as a drop followed by a create) also fit into this group
3. Commands that change the environment of a table
e.g. foreign keys, indexes, grants, set fillfactor, ANALYZE, VACUUM,
CLUSTER etc
ie commands that don't effect the shape of the table or the data in the table only the speed and security or the table so foreign keys don't really fit in this class nor do unique indexes.
Peter.