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

Hmm...

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

Yes, this is much more DDL than DML, as for me. Special privilege is also OK.

And will you complain that your ON DELETE triggers not fired when table is
DROPped ? :)

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

The feature often asked by users. It is not mine own wish.

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

It seems Firebird more lucky re. difficulty :)

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

Not firing triggers is one of the big benefits of TRUNCATE. It is intentional.

BTW, special DDL trigger may be implemented for TRUNCATE when\if
DDL triggers will be implemented.

Regards,
Vlad