Re: [SQL] trigger for TRUNCATE?
On 11/01/2008, Simon Riggs [EMAIL PROTECTED] wrote: On Fri, 2008-01-11 at 08:24 +, 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.
Re: [SQL] trigger for TRUNCATE?
On Jan 11, 2008, at 2:24 AM, Richard Huxton wrote: Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] 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® [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] trigger for TRUNCATE?
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 +, 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 [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] trigger for TRUNCATE?
On Fri, 2008-01-11 at 08:24 +, 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
Re: [SQL] trigger for TRUNCATE?
Pavel Stehule wrote:
On 08/01/2008, Chris Browne [EMAIL PROTECTED] wrote:
[EMAIL PROTECTED] (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
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] trigger for TRUNCATE?
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 Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] trigger for TRUNCATE?
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. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] trigger for TRUNCATE?
Tom Lane escribió: 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. 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 Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] trigger for TRUNCATE?
Tom Lane wrote: 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. 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] trigger for TRUNCATE?
Alvaro Herrera [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] trigger for TRUNCATE?
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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] trigger for TRUNCATE?
[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
Re: [SQL] trigger for TRUNCATE?
[EMAIL PROTECTED] (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
Re: [SQL] trigger for TRUNCATE?
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 [EMAIL PROTECTED] wrote:
[EMAIL PROTECTED] (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
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
