It has happened to us too, though it was very easily fixed in our case due to the excellent deadlock reporting. And I can also confirm that the case for Joel (the original reporter) is also a real world case.
Link to talk about it http://www.pgcon.org/2012/schedule/events/483.en.html
Edit: Not sure if I would call it braindead though. Highly annoying, yes. Alvaro has spent a lot of time into solving this, it is much harder than it initially seems.
I think that this is thread/mutex based because of the fact that I could try to run the same series over and over and get different sets failing. So it seems to me the issue might be something environmental affecting timing.
Again the really braindead thing here was that it was only one statement, only one concurrent transaction at any point in time.
I mean it's fine if you only allow one app to write to your db, but once you have more than one, the fact that the option to ignore invalid data even exists ought to make one very nervous if the data stored is worth anything.
2) My larger frustration with MySQL is I have run into cases of single transactions deadlocking against themselves. These always happen when the following is true:
* Executing an insert statement in the form of INSERT foo (bar) VALUES (1), (2), (3), (4);
* Only one connection/session active at a time (for example during a data migration to MySQL)
* Frequency goes up when more rows are inserted per statement
* Which inserts trigger the deadlocks are not reproducible
I believe this is an issue with race conditions and threads, perhaps a lock contention that isn't being handled properly between index and table writes or the like. I can reproduce it by inserting a couple million rows into a table, a few thousand at a time, but the statements where this occurs varies from one run to the next.
I have never seen braindead locking behavior on PostgreSQL.