Django

Code

Ticket #3459 (closed: fixed)

Opened 1 year ago

Last modified 1 year ago

SET TIME ZONE called too often for postgresql_psycopg2 backend

Reported by: Jack Moffitt <metajack@gmail.com> Assigned to: adrian
Milestone: Component: Database wrapper
Version: SVN Keywords:
Cc: Triage Stage: Ready for checkin
Has patch: 1 Needs documentation: 0
Needs tests: 0 Patch needs improvement: 0

Description

Analyzing our database logs I found that 25% of all traffic were calls to SET TIME ZONE. This is currently called for every cursor when using the postgrsql_psycopg2 backend. This only needs to be called when the connection is established. All cursors will get the correct timezone if it is set in the connection.

I've attached a patch which calls SET TIME ZONE only on new connections. This eliminates a lot of wasted database traffic and can result in a large performance gain for loaded systems using this backend.

Attachments

settimezone.diff (1.1 kB) - added by Jack Moffitt <metajack@gmail.com> on 02/08/07 14:24:29.
patch to call SET TIME ZONE only on new connections instead of for every cursor
combined.diff (1.5 kB) - added by Jack Moffitt <metajack@gmail.com> on 02/08/07 16:00:46.
updated patch combined with fix for #3460
tztest.py (0.6 kB) - added by Jack Moffitt <metajack@gmail.com> on 02/08/07 16:01:43.
test script

Change History

02/08/07 14:24:29 changed by Jack Moffitt <metajack@gmail.com>

  • attachment settimezone.diff added.

patch to call SET TIME ZONE only on new connections instead of for every cursor

02/08/07 16:00:07 changed by Jack Moffitt <metajack@gmail.com>

  • needs_better_patch changed.
  • needs_tests changed.
  • needs_docs changed.

Note: According to postgresql 8.1 docs, this is correct: http://www.postgresql.org/docs/8.1/interactive/sql-set.html

The SET will take effect for the entire session unless something else calls set. This is regardless of whether it is inside a transaction or not. However, the patch needs to be changed to do a COMMIT otherwise the change will not take effect if this cursor's first transaction ends in a rollback. I've attached a modified patch which also combines the fix for #3460, which needs a similar setup.

I've also attached a test script to show that this works regardless of the isolation level used.

02/08/07 16:00:46 changed by Jack Moffitt <metajack@gmail.com>

  • attachment combined.diff added.

updated patch combined with fix for #3460

02/08/07 16:01:43 changed by Jack Moffitt <metajack@gmail.com>

  • attachment tztest.py added.

test script

02/08/07 16:19:48 changed by SmileyChris

  • stage changed from Unreviewed to Ready for checkin.

According to docs this looks right and the code looks good. There may be some gotchas that haven't been considered, but I'll let a committer mull over those and change the state back if required. :P

02/08/07 17:40:31 changed by Marc Fargas <telenieko@telenieko.com>

For the record: Discussion about that was taken in: there

02/25/07 10:18:46 changed by jacob

  • status changed from new to closed.
  • resolution set to fixed.

(In [4573]) Fixed #3459: Django no longer runs SET TIME ZONE for every query when using Postgres. This should result in a pretty noticible speedup for Postgres users, so many thanks to Jack Moffitt for the patch.

Add/Change #3459 (SET TIME ZONE called too often for postgresql_psycopg2 backend)




Change Properties
Action