Consistent 'duplicate key' detection in core

First one could lead to some drift in multi-db config (master-slave replication)

True. Even in a single-db setup it would not be bullet-proof unless access to the table was restricted by semaphores.

I think we should use second approach and unify this all over core

I agree. I suggest we create a separate exception class for all 23xxx Integrity Constraint Violation errors.
http://www.postgresql.org/docs/8.1/static/errcodes-appendix.html

How about this?

I couldn't reproduce all the test errors reported by the test bot (it complained about fatal errors in line numbers that were containts the initial "function testFoo()" method declaration). But I fixed one bug in lock.inc that might have caused problems elsewhere.

I looked through the code (especially places where we catch PDOExceptions) but couldn't find any other places where we should catch the new exception (I wonder why our own database exceptions don't inherit from PDOException? Then changes like this would not break existing code - though of course we should still use the more specific exception class when appropriate).

This updated patch contains an update to the pgsql driver also. This has been tested on Postgres 8.4 (I ran all database tests — two tests were failing with and without the patch). I had to patch core in two places in order to even install Drupal on Postgres?! This is not included in the patch (I assume this problem is being tracked elsewhere, though I couldn't find the ticket).

The patch was also tested successfully with the sqlite driver (I ran all database tests — one test were failing with and without the patch). I fixed a test failure (database_test line 3186, “The whole transaction is rolled back when a duplicate key insert occurs.”) by adding a missing "use Exception" to core/lib/Drupal/Core/Database/Query/Insert.php.

I'm a bit surprised about the problems with the pgsql and sqlite drivers. Don't we have test bots running tests on with engines?

I created a separate issue for the Postgres install troubles: #1491526: Cannot install Drupal on PostgreSQL

Reroll (needed because of #1477446: Move lock backend to PSR-0 code).

Reroll.

Reroll.

Bad patch.

Bah, the last patch was also bad (it included a fix for some other issue also). This is better, I hope.

Looks great, it will make it a lot easier to catch these errors specifically.

This patch needs a re-roll. Asking for a re-test.

#14: integrity-constraint-violation-8.patch queued for re-testing.

Reroll.

#19: integrity-constraint-violation-9.patch queued for re-testing.

Not sure why this is listed as a feature. Committed/pushed to 8.x.

I'm guessing this is nice to have/necessary for drupal7 as well, unless this proves impossible/too big of a task.

Gonna mark this as fixed against 8.x dev because there's been no movement on the 7.x work for 7 years.