In includes/database, for both MySQL and SQLite implementations of TruncateQuery, we see overrides of TRUNCATE resembling this:

<?php
return $comments . 'DELETE FROM {' . $this->connection->escapeTable($this->table) . '}';
?>

But for the PostgreSQL implementation, there is no override of TruncateQuery, with normal object inheritance from includes/database/query.inc for the PostgreSQL database:

<?php
return $comments . 'TRUNCATE {' . $this->connection->escapeTable($this->table) . '} ';
?>

In PostgreSQL, TRUNCATE holds an exclusive access lock on the table until the transaction commits. This can cause problems where db_truncate is called, such as in _menu_router_save, when one clears the cache, for example. DELETE FROM queries don't hold exclusive access locks, and while they don't immediately reclaim the disk space like TRUNCATE would, autovacuum is enabled by default in PostgreSQL, and has been for 5+ years.

Access Exclusive locks are the most Draconian of table locks, thwarting SELECTs and all other queries while in the transaction block. On a high traffic site, this can mean timeouts, white screen, and all sorts of bad times. Is there a method to the madness, or can all TruncateQuerys' be overridden/replaced by DELETE FROM's instead? Drupal is already doing this for the other supported backends, so why not PostgreSQL?

Files: 
CommentFileSizeAuthor
#15 1839998-use-delete-in-transactions-15.patch4.33 KBdcam
PASSED: [[SimpleTest]]: [MySQL] 40,288 pass(es).
[ View ]
#12 1839998-use-delete-in-transactions-12.patch2.61 KBwiifm
PASSED: [[SimpleTest]]: [MySQL] 52,236 pass(es).
[ View ]
#8 1839998-use-delete-in-transactions.patch2.48 KBJosh Waihi
PASSED: [[SimpleTest]]: [MySQL] 52,205 pass(es).
[ View ]
#6 1839998-postgresql-truncate-replace-D8.patch1.03 KBwiifm
FAILED: [[SimpleTest]]: [MySQL] Unable to apply patch 1839998-postgresql-truncate-replace-D8.patch. Unable to apply patch. See the log in the details link for more information.
[ View ]
#6 1839998-postgresql-truncate-replace-D7.patch959 byteswiifm
FAILED: [[SimpleTest]]: [MySQL] Unable to apply patch 1839998-postgresql-truncate-replace-D7.patch. Unable to apply patch. See the log in the details link for more information.
[ View ]
#4 pg-truncate-replace-1839998-4.patch856 bytesmcm.guaba
PASSED: [[SimpleTest]]: [MySQL] 48,134 pass(es).
[ View ]

Comments

mcm.guaba’s picture

Issue summary:View changes

typo

mcm.guaba’s picture

Issue summary:View changes

autovacuum link

mcm.guaba’s picture

Issue summary:View changes

more typos

mcm.guaba’s picture

Issue summary:View changes

link for access exclusive locks

webchick’s picture

Version:7.x-dev» 8.x-dev
Category:feature» bug

This sounds like a bug, rather than a feature.

Does this problem exist in D8 as well? If so, we should fix it there first, and then backport.

Damien Tournoud’s picture

Ok, so we should do the same thing we do for MySQL: use DELETE if we are in a transaction, TRUNCATE otherwise.

mcm.guaba’s picture

Yes, that is how MySQL TRUNCATE in implemented, in D7 and D8.

mcm.guaba’s picture

StatusFileSize
new856 bytes
PASSED: [[SimpleTest]]: [MySQL] 48,134 pass(es).
[ View ]

le patch (D8)

webchick’s picture

Status:Active» Needs review

Thanks! Marking needs review.

wiifm’s picture

Status:Needs review» Reviewed & tested by the community
StatusFileSize
new959 bytes
FAILED: [[SimpleTest]]: [MySQL] Unable to apply patch 1839998-postgresql-truncate-replace-D7.patch. Unable to apply patch. See the log in the details link for more information.
[ View ]
new1.03 KB
FAILED: [[SimpleTest]]: [MySQL] Unable to apply patch 1839998-postgresql-truncate-replace-D8.patch. Unable to apply patch. See the log in the details link for more information.
[ View ]

Have just experienced this with PostgreSQL and entitycache (with D7), from the logs:

2013-02-27 11:27:33 NZDT [drupal-site-mts] ERROR: duplicate key value violates unique constraint "semaphore_pkey"
2013-02-27 11:27:33 NZDT [drupal-site-mts] DETAIL: Key (name)=(variable_init) already exists.
2013-02-27 11:27:33 NZDT [drupal-site-mts] STATEMENT: INSERT INTO semaphore (name, value, expire) VALUES ('variable_init', '789523542512d36ddca6d74.68027500', '1361917662.8285')
2013-02-27 11:27:59 NZDT [drupal-site-mts] ERROR: deadlock detected
2013-02-27 11:27:59 NZDT [drupal-site-mts] DETAIL: Process 6749 waits for AccessExclusiveLock on relation 2236771 of database 1055284; blocked by process 6712.
Process 6712 waits for AccessExclusiveLock on relation 2236771 of database 1055284; blocked by process 6749.
Process 6749: TRUNCATE cache_entity_node
Process 6712: TRUNCATE cache_entity_node
2013-02-27 11:27:59 NZDT [drupal-site-mts] HINT: See server log for query details.
2013-02-27 11:27:59 NZDT [drupal-site-mts] STATEMENT: TRUNCATE cache_entity_node

This caused a integrity issue between the node table and the node_reivision table, leaving the content inaccessible to end users. Obviously less than ideal.

Have reviewed the patch in #4 and have made comment only changes so it matches what I wrote for the D7 patch (which in turn was copied from the MySQL truncate command).

Happy to mark this as RTBC for the D8 patch, wondering if someone can also review the D7 patch for me though?

Status:Reviewed & tested by the community» Needs work

The last submitted patch, 1839998-postgresql-truncate-replace-D7.patch, failed testing.

Josh Waihi’s picture

Status:Needs work» Needs review
StatusFileSize
new2.48 KB
PASSED: [[SimpleTest]]: [MySQL] 52,205 pass(es).
[ View ]

It makes sense to me that we use the MySQL implementation as the global default. As SQLite implements its own version of Truncate, all core databases will work fine like this.

acbramley’s picture

Had the same issue @wiifm explains, marked #1865238: PDO Deadlock causing node table vid inconsistencies as duplicate of this

wiifm’s picture

Status:Needs review» Reviewed & tested by the community

Happy with the patch in #8 marking as RTBC

Damien Tournoud’s picture

Status:Reviewed & tested by the community» Needs work

Let's update the comment. The problem on PostgreSQL is not that the DDL statement is transaction unsafe, it's that it locks the whole table, strongly reducing the concurrency with other transactions.

wiifm’s picture

Status:Needs work» Needs review
StatusFileSize
new2.61 KB
PASSED: [[SimpleTest]]: [MySQL] 52,236 pass(es).
[ View ]

Updated comment in a new patch attached. Hope this is clear now.

Josh Waihi’s picture

Status:Needs review» Reviewed & tested by the community

That makes sense to me.

catch’s picture

Version:8.x-dev» 7.x-dev
Status:Reviewed & tested by the community» Patch (to be ported)
Issue tags:+needs backport to D7

Me too. Committed/pushed to 8.x.

Looks like this needs 7.x backport.

dcam’s picture

Status:Patch (to be ported)» Needs review
StatusFileSize
new4.33 KB
PASSED: [[SimpleTest]]: [MySQL] 40,288 pass(es).
[ View ]

Backported #12 to D7.

wiifm’s picture

Status:Needs review» Reviewed & tested by the community

Patch looks good, there is some extra whitespace removals, but I think this is a good thing (and no doubt simply done by your editor automatically when saving the file).

David_Rothstein’s picture

Status:Reviewed & tested by the community» Fixed
Issue tags:+7.23 release notes

Automatically closed -- issue fixed for 2 weeks with no activity.

Anonymous’s picture

Issue summary:View changes

bad times