> Since the effect of SET TIME ZONE is reverted when the transaction > aborts, you need to COMMIT after setting it.
I'll add that to the patch after I test this case. Certainly this is true if the default isolation mode isn't changed. > Regarding SET TRANSACTION ISOLATION LEVEL, this only works for the > current transaction and thus needs to be repeated. Of course, it > wouldn't be necessary at all if the default isolation level in the > database server has been set up to READ COMMITTED ... Postgresql doesn't have a transaction isolation level less than read committed. Read committed is the default isolation level, and the only other level is serializable which is only going to be used in special applications and would probably take a ton of work to get django to use (since you have to be willing to retry transactions that get serialization errors). So unless someone has set their database to serializable isolation mode by default this command is effectively a no-op. It still takes a few milliseconds to parse/execute, but those add up quickily over a few million calls. > > [ Regarding "transaction isolation level AUTOCOMMIT" ] > So AUTOCOMMIT means "don't create BEGIN/COMMIT" statements, which > proably leads to an autocommit mode, since without BEGIN, each > statement will immediately cause a COMMIT. But isn't it strange to > subsume it under the isolation levels? Well, never mind. I think so. That's a question for the psycopg2 developers though, not for django. > I don't know. Django can do transaction management (see e.g. the > transaction middleware), and to do this something needs to issue > BEGIN TRANSACTION statents. Django seems not to do this, so I guess > it relies on the psycopg backend. I doubt that this would work in > Autocommit mode. If you want to digg this up, try to use the > transaction middleware with Autocommit mode. If django's middleware is broken, then we need to fix it. Leaving this up to the underlying adapter is both asking for trouble (what if psycopg2 2.1 changes this behavior?) and makes django horribly inefficient using this adapter. The autocommit patch and the SET TIMEZONE patch gave us a factor of 2 performance increase. That's a lot of overhead to avoid fixing a bug elsewhere. That said, I don't think this breaks django out of the box, but maybe some people are relying on this broken behavior. None of our web apps which use the middleware were affected by this change, and the only instance I could find where there could be a problem was in the save() call, which as I already stated has the same issue in a transaction block or outside it. read committed isolation mode does not mean that each statement sees the same database. It only means that the rest of the database won't see any changes a transaction makes until it is finished. > I'm a bit cautious with the "works fine for me" argument in these > cases. This all might be no problem for *you*. If you don't use any > transaction management, your default isolation level is READ > COMMITTED, anyway, and the default timezone is the same as specified > in Django settings, then you don't need any statements to set them > at all. If you want to make sure, test it with transaction > management, set the default database isolaton level to SERIALIZABLE > and the TIMEZONE to something far away. Then make "problematic" > queries and see if it still works ... One would hope that calling SET TIMEZONE and SET TRANSACTION ISOLATION LEVEL once per connection would be plenty. I think that even if this needs to be called every connection, django needs to special case this somehow since serializable databases are (one assumes, especially reading postgresql documentation) in the vast minority. 50% overhead seems like an unfair price to pay. > An optimization that could find out if the default settings of the > database server are alright and then skip the settings would solve > the problems better, IMO. Agreed. I think you don't even need to test. Using autocommit mode and calling SET TIMEZONE and SET TRANSATION ISOLATION LEVEL once per connection is probably all you need. I will try and test this today to make sure. > There's nothing wrong with using custom queries, Django does not try > to put everything under the hood of its ORM. If you can propose a > way to extend Django so that you can use the features of psycopg2 > more directly without sacrificing usability for other backends, it > would be a nice contribution! The two line patch is here: http://code.djangoproject.com/ticket/3461 > You can control transactions in detail with Django's ORM, too, for > example with the @transaction.commit_manually decorator. See > > http://www.djangoproject.com/documentation/transactions/ > > Or have you already tried this? We're not using the ORM inside a web application in this instance. So the transaction middleware is not used and a view decorator would not be useful for us. However, I see now that the commit() and rollback() are just somewhere different now under transaction instead of connection. It's easy enough for us to change our code. Btw, my other patches are: autocommit: http://code.djangoproject.com/ticket/3460 set timezone: http://code.djangoproject.com/ticket/3459 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 -~----------~----~----~----~------~----~------~--~---
