>>> 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 ? :)
>>
> No, and I don't object to TRUNCATE as long as it has the privileges and
> security of a DDL operation. I just don't like it as DML operation.

We reach agreement there to make TRUNCATE as DDL statement.
So, no problem at this side, i think.

> Incidentally, there is a related problem with MySQL auto increment
> fields. People expect TRUNCATE to set them back to 1. Falcon
> implements auto increment with a sequence. Something I learned from
> Interbase generators was that allowing users to reset them was a mistake
> that I learned to regret. So I have to build in a way to reset
> sequences after all. Oh, well...

Firebird have no autoincrement fields, so there we have one problem less.
Must note, that standard address this issue with <identity column restart option>.

>>> 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.
>>
> Not everything users ask for is wise, you know. I hope everyone
> understands that.

Of course. But TRUNCATE is good known to users of another DBMS's and
allow to eliminate garbage collecion pass after mass delete. Currently it is part
of SQL standard and this is another reason to implement it. And having it may
help users to migrate to Firebird from, for example, MySQL :)

>>> 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 :)
>>
> No, not lucky. Falcon does everything in three stage thread pipelines,
> which is how we can do 30,000 dbt2 transactions per minute on a four
> core machine. If Firebird were fine grained multi-threading, you'd have
> the problem, too.

Will see in v3.0 ;)

>>> 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.
>>
>>
> Will it be fired before the truncate?

I didn't look as existing implementations of DDL triggers. Adriano's recent
proposition allows to fire DDL triggers BEFORE or AFTER corresponding statement.

> And if it has to reference each
> record in the table, is it much better than a traditional delete that
> fires triggers? (Yes, because indexes are a great deal cheaper to zap
> than to empty by deletion).

This is per-statetement triggers, not per-row.

Regards,
Vlad