Add TRUNCATE TABLE [CORE2479]

[firebird-automations]

Submitted by: K. A. (parshua)

Votes: 14

DELETE is both costly and slow. DROP TABLE frees all pages and is not always possible due to dependencies. Using TEMPORARY TABLES is not desired because in many situations the data must be persisted.

Proposal: TRUNCATE TABLE TABLE_NAME [REUSE SPACE];

This will have all the aforementioned features without the cons.

[firebird-automations]

Commented by: Sean Leyne (seanleyne)

What logic would you propose for handling the validation of any Foreign Key relationships which are dependent on the table? (It is very likely for a table to have FK relationships)

[firebird-automations]

Commented by: K. A. (parshua)

Block if the table is referenced by foreign keys.

TRUNCATE marks/deallocates the pages, and does not use transaction log, so it is considered as a DDL command. TRUNCATE does not run triggers, and deallocates indexes too. AFAICU, it does not lock the table either.

By using the REUSE SPACE keyword, the pages will not return to page pool, otherwise, the pages will be deallocated.

It's worth mentioning that the kind of tables which this command is used for, are tables which contain some kind of prolonged temp data in nature, like log tables and calculation tables.

These kind of tables are not referenced by foreign keys and most of the time, they do not refer to other tables. They may be populated several times in a short period, and remain so for a longer period.

In our case, we calculate results for quiz data taken by some hundred thousands of students, and we may erase and recalc several times in the quiz day (Due to some feedback about quiz questions). There are two tables: One for overall quiz result, and another for some analysis for every single quiz answer (a quiz consists of \~250 questions).Then after the final calculation and publishing the results (That same evening), the system will remain so until the next quiz which will be in a week or two.

[firebird-automations]

Commented by: Ann Harrison (awharrison)

Be really careful with this one - it's been a significant problem in the Falcon storage engine in MySQL. Truncate is not the same as delete - it's more like a DDL operation than a DML operation. It can't be rolled back and it doesn't preserve a consistent view for other running transactions. Nor does it fire triggers. That can be a problem when the relationship between two tables is managed by triggers.

Firebird has mechanisms for recognizing that a transaction (and connection?) has touched a table. That will allows the truncate to wait until anybody who has read the table has exited before the truncation - much like dropping a table. That still leaves messy situations where there's an implicit relationship between two tables and a transaction has read one while the other was truncated.

What we (Falcon/MySQL) decided to do about foreign key relationships was to allow truncation of the child table but not the parent - unless parent and child are the same table.

Truncate is very handy when you're developing an application, but its utility in a live application is questionable. Be careful.

[firebird-automations]

Modified by: @dyemanov

Version: 3\.0 Initial \[ 10301 \] =\>

[firebird-automations]

Commented by: pabloj (pabloj)

Truncate is very important for datawarehouses, an example is when you have to quickly empty a staging table to reload it.

[firebird-automations]

Commented by: @hvlad

Feature was already discussed in fb-architect and we have an agreement that a) its is in standard and we have no questions what it should do b) it is DDL from the Firebird POV c) it could be implemented after v2.5

[firebird-automations]

Modified by: @hvlad

assignee: Vlad Khorsun \[ hvlad \]

[firebird-automations]

Commented by: Milan Babuskov (milan.babuskov)

Perhaps the first implementation of this feature would only allow truncate tables that:

- are not referenced by foreign keys - do not have any triggers defined

I would gladly give up FK or triggers to have this feature.

[firebird-automations]

Modified by: @dyemanov

Fix Version: 3\.0 Beta 2 \[ 10586 \]

[firebird-automations]

Modified by: @dyemanov

assignee: Vlad Khorsun \[ hvlad \] =\> Dmitry Yemanov \[ dimitr \] Fix Version: 3\.0 Beta 2 \[ 10586 \] =\>

[firebird-automations]

Modified by: @dyemanov

Fix Version: 4\.0 Beta 1 \[ 10750 \]

[firebird-automations]

Commented by: limingfeng (lmf1967)

TRUNCATE TABLE command is very useful for our daily report. I am waiting for it|

[firebird-automations]

Commented by: Volker Rehn (vr2_s18)

please also backport truncate to version 3

[firebird-automations]

Commented by: limingfeng (lmf1967)

backporting to version 2.5 is much apprecialted!

[firebird-automations]

Modified by: @dyemanov

Fix Version: 4\.0 Beta 1 \[ 10750 \] =\>

[firebird-automations]

Commented by: Volker Rehn (vr2_s18)

Has TRUNCATE TABLE been postponed for Firebird 4.0 Beta 1?

[firebird-automations]

Commented by: @dyemanov

Yes, it was. It needs some discussion in fb-devel.

[fdcastel]

There are any plans to see this one in Firebird 5?

[fperana]

...or in Firebird 6?

[dyemanov]

It's in the roadmap for Firebird 6