Re: 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(at)fmed(dot)uba(dot)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

On 08/01/2008, Chris Browne <cbbrowne(at)acm(dot)org> wrote:
> gherzig(at)fmed(dot)uba(dot)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(at)acm(dot)org> wrote:
>
>
>>gherzig(at)fmed(dot)uba(dot)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(at)commandprompt(dot)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(at)commandprompt(dot)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(at)commandprompt(dot)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(at)commandprompt(dot)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

tgl(at)sss(dot)pgh(dot)pa(dot)us (Tom Lane) writes:
> Alvaro Herrera <alvherre(at)commandprompt(dot)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

Tom Lane wrote:
> Alvaro Herrera <alvherre(at)commandprompt(dot)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(at)commandprompt(dot)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(at)myemma(dot)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(at)momjian(dot)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(at)2ndquadrant(dot)com> 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

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.