Re: trigger for TRUNCATE?

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