#17266: psycopg2 backend should use get_parameter_status to check if SET TIME
ZONE
is needed
-------------------------------------+-------------------------------------
Reporter: akaariai | Owner: nobody
Type: | Status: new
Cleanup/optimization | Version:
Component: Database layer | Resolution:
(models, ORM) | Triage Stage: Accepted
Severity: Normal | Needs documentation: 0
Keywords: | Patch needs improvement: 0
Has patch: 1 | UI/UX: 0
Needs tests: 0 |
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Changes (by akaariai):
* has_patch: 0 => 1
* type: Uncategorized => Cleanup/optimization
Comment:
Provided is a patch that does use get_parameter_status to sniff the
connection's default time zone. I did a little testing of this. Queries
generated for a new connection with patch (using psycopg2 version 2.4.2):
{{{
21335: LOG: connection authorized: user=akaj database=testdb1
21335: LOG: statement: SHOW default_transaction_isolation
21335: LOG: statement: BEGIN
21335: LOG: statement: first real query
}}}
Without patch:
{{{
21355: LOG: connection authorized: user=akaj database=testdb1
21355: LOG: statement: SHOW default_transaction_isolation
21355: LOG: statement: SET default_transaction_isolation TO DEFAULT
21355: LOG: statement: SET TIME ZONE 'America/Chicago'
21355: LOG: statement: SET default_transaction_isolation TO 'read
committed'
21355: LOG: statement: BEGIN
21355: LOG: statement: first real query...
}}}
The precondition for this is that the connections default time zone is
actually the wanted timezone (settings TIME_ZONE if not USE_TZ, else UTC).
Attached patch contains some documentation mentioning that it is a good
idea to have the database or user defaults as mentioned above. Also, I
think you will need psycopg2 version 2.4.2 or later
([http://initd.org/psycopg/articles/2011/06/12/psycopg-242-released/|see
release notes]) or you will get more queries than in the examples posted
above. The patch should work with all versions of psycopg2, though.
The speed difference for making single query and then disconnecting is
about 10% on a localhost database. It would be likely more if the database
was on another machine, as there is no TCP/IP overhead for localhost
connections. The test was:
{{{
start = datetime.now()
for i in range(0, 1):
list(SomeModel.objects.all()) # no rows in the DB, so this is very
lightweight query.
connection.close()
print datetime.now() - start
}}}
So, there is some gain from this.
I have guarded against psycopg2 versions not having get_parameter_status
in the patch (versions prior to 2.0.12).
I don't see any easy way to test if you actually get less queries -
psycopg2 will not report all the internal queries made. Manually it is
easy to test: just set log_statement to 'all' and log_connections to on in
postgresql.conf (or for the db user) and see PostgreSQL's log to check
what queries are made.
I haven't build the documentation, dunno if it really works. The
reviewer/committer might want to check the language used in the
documentation, my English isn't too good. Also, I haven't tested this on
any other psycopg version than 2.4.2.
--
Ticket URL: <https://code.djangoproject.com/ticket/17266#comment:2>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" 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-updates?hl=en.
