Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Add TRUNCATE TABLE [CORE2479] #2892

Open
firebird-automations opened this issue May 29, 2009 · 18 comments
Open

Add TRUNCATE TABLE [CORE2479] #2892

firebird-automations opened this issue May 29, 2009 · 18 comments

Comments

@firebird-automations
Copy link
Collaborator

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

Modified by: @dyemanov

Version: 3.0 Initial [ 10301 ] =>

@firebird-automations
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

Modified by: @hvlad

assignee: Vlad Khorsun [ hvlad ]

@firebird-automations
Copy link
Collaborator Author

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
Copy link
Collaborator Author

Modified by: @dyemanov

Fix Version: 3.0 Beta 2 [ 10586 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Vlad Khorsun [ hvlad ] => Dmitry Yemanov [ dimitr ]

Fix Version: 3.0 Beta 2 [ 10586 ] =>

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Fix Version: 4.0 Beta 1 [ 10750 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: limingfeng (lmf1967)

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

@firebird-automations
Copy link
Collaborator Author

Commented by: Volker Rehn (vr2_s18)

please also backport truncate to version 3

@firebird-automations
Copy link
Collaborator Author

Commented by: limingfeng (lmf1967)

backporting to version 2.5 is much apprecialted!

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Fix Version: 4.0 Beta 1 [ 10750 ] =>

@firebird-automations
Copy link
Collaborator Author

Commented by: Volker Rehn (vr2_s18)

Has TRUNCATE TABLE been postponed for Firebird 4.0 Beta 1?

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

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

@fdcastel
Copy link

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

3 participants