psycopg2 backend should use get_parameter_status to check if SET TIME ZONE is needed
|Reported by:||Anssi Kääriäinen||Owned by:||Aymeric Augustin|
|Component:||Database layer (models, ORM)||Version:|
|Cc:||Triage Stage:||Ready for checkin|
|Has patch:||yes||Needs documentation:||no|
|Needs tests:||no||Patch needs improvement:||no|
In ticket #17062 Aymeric Augustin suggested the idea for using get_parameter_status to check if the database default time zone is already correct. If it is, we can skip the SET TIME ZONE and associated isolation level handling for every new connection. Ticket #17062 has more details, but I will copy-paste the most relevant parts here. If this is not done, for every new connection we need to do these queries:
26639: LOG: connection authorized: user=akaj database=testdb1 26639: LOG: statement: SHOW default_transaction_isolation 26639: LOG: statement: SET default_transaction_isolation TO DEFAULT 26639: LOG: statement: SET TIME ZONE 'America/Chicago' 26639: LOG: statement: SET default_transaction_isolation TO 'read uncommitted' 26639: LOG: statement: BEGIN 26639: LOG: statement: first real query...
If we use get_parameter_status, we can get it down to this:
27163: LOG: connection authorized: user=akaj database=testdb1 27163: LOG: statement: SHOW default_transaction_isolation 27163: LOG: statement: BEGIN 27163: LOG: statement: first real query
The get_parameter_status was added in psycopg2 version 2.0.12 http://initd.org/psycopg/docs/connection.html#connection.get_parameter_status. I can't find a mention if we need to support versions prior to this. If we need to support versions prior to 2.0.12, we can just add a check for the psycopg2 version in use and not use get_parameter_status if the version doesn't support it.
I will wait until ticket #17062 gets fixed before working on this.
Change History (13)
comment:8 Changed 5 years ago by
|Patch needs improvement:||unset|
|Triage Stage:||Accepted → Ready for checkin|