From: Bruce Momjian <bruce@momjian.us>
Message-Id: <200801111649.m0BGnWs25383@momjian.us>
Subject: Re: trigger for TRUNCATE?
In-Reply-To: <1200069700.4266.1156.camel@ebony.site>
To: Simon Riggs <simon@2ndquadrant.com>
Date: Fri, 11 Jan 2008 11:49:32 -0500 (EST)
CC: Richard Huxton <dev@archonet.com>, Tom Lane <tgl@sss.pgh.pa.us>,
	Alvaro Herrera <alvherre@commandprompt.com>,
	Gerardo Herzig <gherzig@fmed.uba.ar>,
	Pavel Stehule <pavel.stehule@gmail.com>, Chris Browne <cbbrowne@acm.org>,
	pgsql-sql@postgresql.org
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset="US-ASCII"


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