> 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
> 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
> 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
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:
set timezone: http://code.djangoproject.com/ticket/3459