Opened 18 years ago
Closed 18 years ago
#3459 closed (fixed)
SET TIME ZONE called too often for postgresql_psycopg2 backend
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.
Change History (7)
by , 18 years ago
Attachment: | settimezone.diff added |
---|
comment:1 by , 18 years ago
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.
comment:2 by , 18 years ago
Triage Stage: | Unreviewed → 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
comment:4 by , 18 years ago
Resolution: | → fixed |
---|---|
Status: | new → closed |
patch to call SET TIME ZONE only on new connections instead of for every cursor