Re: 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 +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. +