News for hackers. Stuff that matters.
Re:PostgreSQL Rising by einhverfr on Friday July 20, 2012 @01:19AM (#4269533)
1) can applications set the SQL_MODE themselves? Can an admin configure the server so applications cannot specify mode? If not, what good is it since it won't guarantee your data?

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.

Re:PostgreSQL Rising by epimenov on Friday July 20, 2012 @06:00AM (#4270117)
There definitely was braindead locking behavior on PostgreSQL at least once: http://www.mail-archive.com/[email protected]/msg...
Re:PostgreSQL Rising by einhverfr on Friday July 20, 2012 @06:16AM (#4270147)
That certainly is odd behavior however reading through the whole email thread that seems to be one complicated issue with a two processes obtaining different locks to the same row. Probably not ideal, but maybe I need to recalibrate my definition of braindead because in real-world examples, that would be highly annoying.
Re:PostgreSQL Rising by epimenov on Friday July 20, 2012 @06:34AM (#4270180)
It happened to us in the real world. And was fixed in 9.2
Re:PostgreSQL Rising by jeltz on Friday July 20, 2012 @08:41PM (#4273526)
Actually it is not fixed yet, the patch was pushed to 9.3 and Alvaro is still working on it.

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.

Re:PostgreSQL Rising by einhverfr on Friday July 20, 2012 @08:00AM (#4270339)
ouch. Reminds me of the (one) data corruption bug I found in PostgreSQL 7.3, allowing me to create write-only tables....
Re:PostgreSQL Rising by AaronBBrown on Friday July 20, 2012 @10:10AM (#4270863)
What version of MySQL? Sounds like the old AUTOINC lock mode behavior. http://insatiabledemand.ideeli.com/post/18850015294/diagnosi...
Re:PostgreSQL Rising by einhverfr on Friday July 20, 2012 @11:15AM (#4271136)
I don't think so. First ID's were being assigned manually, so I don't think a table lock would be pulled out, and secondly only one statement was executing at a time. Only one connection issuing sequential statements, and nobody else on the server.

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.

Re:PostgreSQL Rising by AaronBBrown on Friday July 20, 2012 @04:38PM (#4272787)
1. Yes, applications can set sql_mode themselves at the session level. An admin cannot prevent this. http://dev.mysql.com/doc/refman/5.5/en/server-sql-mode.html
Re:PostgreSQL Rising by einhverfr on Friday July 20, 2012 @09:54PM (#4273698)
So, what good is setting a sensible default as a DBA if the dev guys can override it?

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.

Re:PostgreSQL Rising by AaronBBrown on Saturday July 21, 2012 @08:10AM (#4274616)
A dev can only override it for their session, not at the global level.