Issue Details (XML | Word | Printable)

Key: CORE-2479
Type: New Feature New Feature
Status: Open Open
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: K. A.
Votes: 13
Watchers: 12
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Add TRUNCATE TABLE

Created: 28/May/09 10:12 PM   Updated: 21/Feb/19 06:34 PM
Component/s: Engine
Affects Version/s: None
Fix Version/s: None


 Description  « Hide
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.


 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Sean Leyne added a comment - 28/May/09 11:00 PM
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)

K. A. added a comment - 28/May/09 11:48 PM
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.

Ann Harrison added a comment - 28/May/09 11:53 PM
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.

pabloj added a comment - 07/Jul/09 06:25 AM
Truncate is very important for datawarehouses, an example is when you have to quickly empty a staging table to reload it.

Vlad Khorsun added a comment - 09/Jul/09 04:26 AM
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

Milan Babuskov added a comment - 25/Dec/11 03:09 PM
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.

limingfeng added a comment - 16/Nov/18 08:22 AM
TRUNCATE TABLE command is very useful for our daily report. I am waiting for it|

Volker Rehn added a comment - 01/Jan/19 05:55 PM
please also backport truncate to version 3

limingfeng added a comment - 05/Jan/19 07:09 AM - edited
backporting to version 2.5 is much apprecialted!

Volker Rehn added a comment - 21/Feb/19 06:24 PM
Has TRUNCATE TABLE been postponed for Firebird 4.0 Beta 1?

Dmitry Yemanov added a comment - 21/Feb/19 06:34 PM
Yes, it was. It needs some discussion in fb-devel.