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

Issue summary:View changes

typo

Issue summary:View changes

autovacuum link

Issue summary:View changes

more typos

Issue summary:View changes

link for access exclusive locks

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.

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

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

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

le patch (D8)

Status:Active» Needs review

Thanks! Marking needs review.

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.

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.

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

Status:Needs review» Reviewed & tested by the community

Happy with the patch in #8 marking as RTBC

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.

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.

Status:Needs review» Reviewed & tested by the community

That makes sense to me.

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.

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

Backported #12 to D7.

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).

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

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

Issue summary:View changes

bad times