Vlad Khorsun wrote:

>>> 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 ? :)
>

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.

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

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

>
>
>> 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 :)
>

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.

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