> When it comes to overhead... As far as I know PostgreSQL in autocommit > mode will wrap each statement (even SELECT) in an implicit transaction.
So what? Should we wrap it in 5 more just for giggles? Each one adds overhead. > > While you may think that you are not using transactions for singleton > > read-only SELECT statement, in fact every single statement in > > PostgreSQL is in a transaction. In the absence of an explicit > > transaction, the statement itself is an implicit transaction. I think the terminology is confused here. The "implicit transaction" i talked about in DB-API is that the database connector actually adds a BEGIN/END pair. What Josh is saying here is that each statement has significant round trip overhead, not that PostgreSQL adds BEGIN/END pairs. We actually know that it doesn't, since the error cases inside a BEGIN/COMMIT or BEGIN/END are different than when not inside a transaction. Of course the SQL specification requires that single statements be consistent, which is something like a transaction, but this is true of mysql as well. > So what Django does now (and what DB API suggests) by starting and > explicit transaction up to the first save(): > > BEGIN; > SELECT ... > UPDATE ... > COMMIT; > > is actually more efficient then equivalent in auto-commit mode: > > implicit begin; > SELECT ... > implicit commit; > implicit begin; > UPDATE ... > implicit commit; Incorrect. There is no implicit begin/commit in postgresql. PostgreSQL treats a single statement similarly to a transaction, but it does not add BEGIN/COMMIT which causes further overhead over single statements. Look, I"m not arguing from theory here. When we hit the wall on database performance we did a lot of analysis on where the hit came from. Some 30% or more of our database time was just in useless BEGIN and COMMIT statements that were wrapping SELECTS. This is a real problem, regardless of any extra behavior of PostgreSQL. > So if this ticket should be fixed then only for consistency with other > DBs, not for performance reasons. But I actually think that performance > is more important here. It's not hard to do explicit rollback anyway in > your view code if you're recovering from an exception. Django with #3460 is _significantly_ faster when the query load is high. If I ran stock Django on Chesspark, the database overhead would cripple the site. I didn't just poke around in the Django internals trying to fix supposed problems. I found these problems because I was explicitly trying to fix certain performance issues. I used a query analyzer to find them. You won't find them from the django side because it doesn't actually show you all the queries that happen (it masks all the overhead queries). jack. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Django developers" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/django-developers?hl=en -~----------~----~----~----~------~----~------~--~---
