Use Truncate for Empty
klaxian opened this issue · 5 comments
When emptying a table using the phpPgAdmin 6 UI, a DELETE query is sent to the server. This can be very slow since each record is evaluated and deleted individually. I suggest replacing this with a TRUNCATE query instead.
You're right, it's using DELETE. I will replace it in the next release.
I suspect DELETE was originally used because TRUNCATE will not evaluate foreign key constraints (among other things). If you attempt to truncate a table that is reference by others, the truncate will fail unless you specify CASCADE, which will also truncate all other tables that reference the truncated table (recursively). Since TRUNCATE...CASCADE seems too dangerous, I suggest sending TRUNCATE ONLY...RESTRICT first. If the restrictive TRUNCATE fails, you can fall back to DELETE. Regardless, I would add the ONLY qualifier so that any descendant tables are not emptied along with the primary table. Thoughts?
RESTRICT is the default condition so you can safely omit it. Also you might want to restart the sequences adding RESTART IDENTITY, but the default behavior is to just let the sequence as it is, so you don't need to specify CONTINUE IDENTITY. I'm a bit concerned about the difference that makes including the ONLY option to avoid truncating child tables, if there are any. It is the default behavior, however phpPgAdmin doesn't offer this option when you, for example, drop a table.
I will dig through the code (you know, this is just a hard fork of phppgadmin and most of my work has been around refactoring the procedural scripts into controllers. I admit I haven't digged a lot into the database section and for sure there are other optimizations that can be implemented for newer versions (each version has its own class that inherits from the basic one, so you can override or add methods in the specific version class)
As I told you, I will implement your suggestion in the next release, perhaps today if my job allows for some spare time.
I was just trying to save you some time researching. Your work is much appreciated!
I released this improvements as release 6.0.0-beta.31. It offers the option to truncate with cascade, and defaults to restrict otherwise.