Opened 9 years ago

Closed 9 years ago

Last modified 9 years ago

#2755 closed defect (invalid)

Bug in postgresql connector

Reported by: djangoproject.com@… Owned by: adrian
Component: Database layer (models, ORM) Version: master
Severity: major Keywords:
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:

Description

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

Change History (6)

comment:1 Changed 9 years ago by ubernostrum

  • Resolution set to invalid
  • Status changed from new to closed

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.

comment:2 Changed 9 years ago by ubernostrum

  • Resolution invalid deleted
  • Status changed from closed to reopened

Meant to close this "worksforme".

comment:3 Changed 9 years ago by ubernostrum

  • Resolution set to worksforme
  • Status changed from reopened to closed

comment:4 Changed 9 years ago by djangoproject.com@…

  • Resolution worksforme deleted
  • Status changed from closed to reopened

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!!! ;-)

comment:5 Changed 9 years ago by ubernostrum

  • Resolution set to invalid
  • Status changed from reopened to closed

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.

comment:6 Changed 9 years ago by 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.

Note: See TracTickets for help on using tickets.
Back to Top