Bug in postgresql connector

[djangoproject.com@…]

Programming error in django/db/backends/postgresql/base.py setting the TIME_ZONE.

--- base.py     (revision 3768)
+++ base.py     (working copy)
@@ -43,7 +43,7 @@
             self.connection = Database.connect(conn_string)
             self.connection.set_isolation_level(1) # make transactions transparent to all cursors
         cursor = self.connection.cursor()
-        cursor.execute("SET TIME ZONE %s", [settings.TIME_ZONE])
+        cursor.execute("SET TIME ZONE %s", (settings.TIME_ZONE,))
         if settings.DEBUG:
             return util.CursorDebugWrapper(cursor, self)
         return cursor

[James Bennett]

A ProgrammingError on a SET TIME ZONE is almost never because of setting the time zone -- Django executes the SET TIME ZONE at the start of every query, and the syntax is correct. If it weren't, we'd know pretty quickly because there are more than a few really big Django installations that run solely on Postgres.

99.9% of the time when you see a ProgrammingError complaining about SET TIME ZONE, it's because some code somewhere has caused an exception at the database level and never rolled back its transaction; in this case the error you'll see is

psycopg.ProgrammingError: ERROR: current transaction is aborted, commands ignored until end of transaction block

The solution is to grab the database cursor and execute a rollback.

[James Bennett]

Meant to close this "worksforme".

[djangoproject.com@…]

Quote taken from: ​http://www.python.org/dev/peps/pep-0249/ (Python Database API Specification v2.0)

---

.execute(operation[,parameters])

Prepare and execute a database operation (query or
command). Parameters may be provided as sequence or
mapping and will be bound to variables in the operation.
Variables are specified in a database-specific notation
(see the module's paramstyle attribute for details). ....

---

Which clearly states that a sequence is required and not a list, which also means we have Singelton here, so we need to add a , after the one and only argument. This is also implemented by the psycopg connector. "Pure Luck" that this works in some installations!

My error looks like this:

>>> k.save()
Traceback (most recent call last):
  File "<console>", line 1, in ?
  File "/usr/lib/python2.4/site-packages/django/db/models/base.py", line 166, in save
    cursor = connection.cursor()
  File "/usr/lib/python2.4/site-packages/django/db/backends/postgresql/base.py", line 46, in cursor
    cursor.execute("SET TIME ZONE %s", [settings.TIME_ZONE])
ProgrammingError: ERROR:  current transaction is aborted, commands ignored until end of transaction block

SET TIME ZONE 'Europe/Berlin'
>>> 

Fixing the list to a sequence makes my k.save() work without any complaints!!! ;-)

[James Bennett]

A list is a sequence type in Python: ​http://docs.python.org/lib/typesseq.html.

Your error is due to an un-rolled-back transaction which failed, not due to the TIME ZONE query, and the solution is to roll back in the current session, or close the current session and start a new one.

[djangoproject.com@…]

You are correct, which still leaves me with the question why my psycopg connector has a problem here. Changeing vom list to tuple fixes the problem, changing it back results in the error. Even on very simple models with only one CharField for example.