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 <[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