Vlad Khorsun wrote:

> All,
>
> One of the often task in ETL applications is to quick erase all data in some table(s). Often
> amount of data is big or huge (tens of millions records). In Firebird we have two choices
> currently - DELETE all rows or RECREATE TABLE. Both have its own drawbacks. DELETE
> produced a lot of record versions to clean up, its slow as works row-by-row basis and fire
> triggers which is almost always is not needed. RECREATE is free from this drawbacks but
> it will fail if there is dependencies and all related objects such as triggers, indices, constraints
> must be recreated too.
>
> SQL 2008 introduced "new" statement free from this drawbacks : TRUNCATE TABLE. This
> statement is long time present in commercial DBMS's and currently it is part of standard.
> I offer to implement it in Firebird too.
>
>

It's a can of worms from both theory and practice.

On the theory side, is it a DML or DDL operation? If it's DML, it has
to be transactional. If DDL, it doesn't. If it's DML, presumably
anyone who can do a delete can do a truncate, but from the database
integrity perspective, do you really want users to have the right to
bypass delete triggers? This creates the opportunity (and incentive)
for serious mischief. This suggests that perhaps (yet) another
privilege is required.

The interaction between truncate and other transactions is a mess,
though requiring an exclusive lock on the table is a simple solution.
Where this is useful in a production environment is a question, but one
I will defer to others.

MySQL has a TRUNCATE statement, defined somewhat ambiguously as a DDL
statement as a moral equivalent of dropping and recreating the table.
It isn't implemented that way, of course -- execution is pushed down to
storage engines.

Falcon did implement it, though the implementation has proven to be one
of the most difficult and fragile parts of the engine. The actual
table truncation was the easy part. The hard parts were locking
(deadlock avoidance) and transaction interaction. But, unlike Firebird,
Falcon does do two phase locking on tables (we actually don't do two
phase locking on anything), so our job was substantially more
difficult. Incidentally, in MySQL (and Falcon) a TRUNCATE cannot be
rolled back.

Personally, I believe that triggers are critical for logical database
integrity and an integral part of database semantics. All in all, I
think I'll give TRUNCATE a miss in Nimbus.