psycopg.ProgrammingError exceptions leave the connection to the DB in an unusable state

[j.quigley@…]

I am getting the following error while trying to save an object. I was following the tutorial but creating my own structure. The database tables where all created okay with "django-admin.py sql".

I did get an insert error before as I didn't put any defaults in for the integer columns, but after fixing the model that went away.

u.save()

Traceback (most recent call last):

File "<stdin>", line 1, in ?
File "/usr/lib/python2.4/site-packages/Django-0.90-py2.4.egg/django/utils/functional.py", line 3, in _curried

return args[0](*(args[1:]+moreargs), dict(kwargs.items() + morekwargs.items()))

File "/usr/lib/python2.4/site-packages/Django-0.90-py2.4.egg/django/core/meta/init.py", line 782, in method_save

cursor = db.db.cursor()

File "/usr/lib/python2.4/site-packages/Django-0.90-py2.4.egg/django/core/db/backends/postgresql.py", line 35, in cursor

cursor.execute("SET TIME ZONE %s", [TIME_ZONE])

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

SET TIME ZONE 'Europe/Madrid'

[hugo]

Looks like a problem with your database setup - maybe your postgresql doesn't know about the Europe/Madrid timezone?

[Adrian Holovaty]

Yes, the problem is that Postgres doesn't know about Europe/Madrid.

Try executing the following query in the Postgres interactive program (psql) to verify this is the problem:

SET TIME ZONE 'Europe/Madrid';

[Adrian Holovaty]

Closing this ticket because we haven't heard anything back.

[GomoX <gomo@…>]

Actually that is a very typical problem. It isn't related to the Europe/Madrir TZ, it is related to Postgres transactions. In the python shell, when one of your interpreter-issued queries produces a Postgres error (psycopg.ProgrammingError), the problem above arises: "current transaction is aborted, commands ignored until end of transaction block". That is, the "current transaction" is aborted, and you are not allowed to execute any further queries until you end it, and start a new one. So, after executing a bad query and being given this error, you can't execute any further queries until you restart the interpreter (or somehow end the transaction and start a new one).

This is quite annoying, because people make mistakes all the time (that's why we use the interactive python shell!). I am reopening this and changing the subject so it reflects the real problem. Also, this will probably be dealt with when transaction support is added, but for now I think at least a FAQ entry should be added on this as it gets quite frustrating. I am not sure whether this happens in regular code files or just in the interpreter, but my guess is it does too. Obviously it's less of a problem there since the execution of the script is aborted by the exception too. I'll do some tests and then I'll let you know.

[igor@…]

It seems I experienced the same problem as you using time zone 'Europe/Moscow'. It was resolved after I upgraded psycopg from version 1.1.18 to 1.1.21.

[kkennedy@…]

I'm getting similar error when using admin site, with time zone 'America/New_York'. In psql, "SET TIME ZONE 'America/New_York';" (w/o double quotes) runs without error. The transaction eventually goes through and appears to complete (perhaps w/o setting TZ?) if I resubmit from the error page with Ctrl-R. I have psycopg version 1.1.21 installed (debian unstable).

Hmm...googling on gives me "this link":​http://django.pastebin.com/449611 , which appears to indicate that this is a result of an earlier error in the cursor. IOW, the SET TIME ZONE may be a red herring, being the first thing to run after the error. I'll try to track down further, perhaps via interactive python connection.

[rock@…]

I am getting Integrity Errors while loading data. I expect to get these occasionally in the rare cases where my system attempts to load redundant data. (That is why I specified the unique_together constraint in my Django model in the first place.)

When I targeted mysql I would see the Integrity Error, but I would catch that and everything would proceed. With psycopg 1.1.21, however, the same error causes the loading process to come screeching to a halt.

I guess it is back to mysql for me unless I get some insight on how to avoid this situation.

[rock@…]

Here is the workaround...

from dhango.core import db

try:

myobj.save()

except:

db.db.rollback()

Now your connection is free to perform additional work.
Of course I print out exception info first so I know what is happening.

[rock@…]

Sorry about the typo (django.core) and the ugly formatting, but the info is good.

[Jacob]

Transaction management (now on magic-removal) provides a fix for this problem. Use it. Love it.

[Gary Wilson <gary.wilson@…>]

I am still seeing this problem in [2977]. Connection gets hosed after a ProgrammingError.
Also, upgrading psycopg from 1.1.18 to 1.1.21 (as igor mentioned above) did not help.
My version of postgres is 8.0.4.

$ python manage.py shell
(InteractiveConsole)
>>> from django.contrib.auth.models import User
>>> u=User(username='gary')
>>> u.save()
Traceback (most recent call last):
  File "<console>", line 1, in ?
  File "/usr/lib/python2.4/site-packages/django/db/models/base.py", line 188, in save
    ','.join(placeholders)), db_values)
  File "/usr/lib/python2.4/site-packages/django/db/backends/util.py", line 12, in execute
    return self.cursor.execute(sql, params)
ProgrammingError: ERROR:  invalid input syntax for type boolean: ""

INSERT INTO "auth_user" ("username","first_name","last_name","email","password","is_staff","is_active","is_superuser","last_login","date_joined") VALUES ('gary','','','','','',True,'','2006-05-24 09:55:14.392491','2006-05-24 09:55:14.392550')
>>> User.objects.all()
Traceback (most recent call last):
  File "<console>", line 1, in ?
  File "/usr/lib/python2.4/site-packages/django/db/models/query.py", line 88, in __repr__
    return repr(self._get_data())
  File "/usr/lib/python2.4/site-packages/django/db/models/query.py", line 384, in _get_data
    self._result_cache = list(self.iterator())
  File "/usr/lib/python2.4/site-packages/django/db/models/query.py", line 158, in iterator
    cursor = connection.cursor()
  File "/usr/lib/python2.4/site-packages/django/db/backends/postgresql/base.py", line 42, 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 'America/Chicago'

[James Bennett]

Gary, the time zone issue is not the problem here; when doing anything in the Python shell which writes to the DB you will need to use the try/except syntax outlined above to ensure the transaction is rolled back and ended after the error.

[Gary Wilson <gary.wilson@…>]

Yes I know the time zone issue is not the problem here. It seems that this should be handled more elegently without having to wrap everything in a try/except block. The point of using the interpreter is to quickly test things. This becomes more of a nuisance if I have to do all the extra typing.

[Jacob]

It would actualy be a *very* bad idea to hack around this. Postgres is protecting you from yourself here -- imagine if you have a whole series of dependant operations, and something messes up halfway through. Postgres quite correctly won't let you do anything else inside the transaction so that you don't end up with your database in an unusable state.

That's the whole point of transactions -- either *everything* in the transaction gets committed, or nothing.

[russblau@…]

The workaround given earlier ("from django.core import db") no longer works, as the db transaction code appears to have changed since it was posted. How does one recover from a ProgrammingError now (without reinitializing the whole db)?

[Chris Beaven]

To close a connection now:

from django.db import connection  # You've probably done this to create the cursor anyway.
connection.connection.rollback()  # You should be able to do another transaction now.

[lbolognini@…]

I just hit this issue, as of today, here is my solution running the latest bleeding-edge django

from psycopg2 import IntegrityError
from django.db import transaction

@transaction.commit_manually
def add_academic_year(request, info_msg=""):
    info_msg = get_success_msg(request)
    
    if request.method == "POST":
        form = AcademicYearForm(request.POST)

        if form.is_valid():
            try:
                #check if it's a dupe
                ay = AcademicYear(**form.clean_data)
                ay.save()
                transaction.commit()
                
                request.session["success_msg"] = "Academic year %s added" % ay
                return HttpResponseRedirect("/change/academic_year/%s" % ay.id)
            except IntegrityError:
                transaction.rollback()
                error_msg = "An academic year has already been defined with the same start/end date"
                context = dict(form=form, error_msg=error_msg)
        else:
            context = dict(form=form, error_msg="Please correct the error(s) below")
    else:
        form = AcademicYearForm()
        context = dict(form=form, info_msg=info_msg)
    
    return render_to_response("academic_year.html", context)

#Funnily enough this doesnt need to be handled specially when updating data like in the method below

def change_academic_year(request, academic_year_id, info_msg=""):
    try:
        #get the success message and reset it
        info_msg = request.session.get("success_msg")
        request.session["success_msg"] = None
    except KeyError: pass
        
    if request.method == "POST":
        form = AcademicYearForm(request.POST)
        
        if form.is_valid():
            try:
                ay = AcademicYear(id=request.POST["id"], **form.clean_data)
                ay.save()
                
                info_msg = "%s updated" % ay
                context = dict(form=form, info_msg=info_msg, academic_year_id=ay.id, show_delete_button=True)
            except IntegrityError:
                error_msg = "An academic year already exists with the same start/end date"
                context = dict(form=form, error_msg=error_msg, academic_year_id=request.POST["academic_year_id"], show_delete_button=True)
        else:
            error_msg = "Please correct the error(s) below"
            context = dict(form=form, error_msg=error_msg, show_delete_button=True, academic_year_id=request.POST["academic_year_id"])
    else:    
        ay = AcademicYear.objects.get(pk=academic_year_id)
        form = AcademicYearForm(dict(start_date=ay.start_date,
                                     end_date=ay.end_date,
                                     friendly_name=ay.friendly_name))
        
        context = dict(form=form, academic_year_id=academic_year_id, info_msg=info_msg, show_delete_button=True)
        
    return render_to_response("academic_year.html", context)

[lbolognini@…]

I think this is due to TIMESTAMP WITH TIME ZONE field (used in the django_session table where the cookie information is stored). I believe this is more of a psycopg2 problem (verified both on Windows XP SP2/Py2.4 and Win2k3/Py2.4) than django's.

Traceback follows:

Traceback (most recent call last):

  File "C:\Python24\lib\site-packages\django\core\servers\basehttp.py", line 272, in run
    self.result = application(self.environ, self.start_response)

  File "C:\Python24\lib\site-packages\django\core\servers\basehttp.py", line 614, in __call__
    return self.application(environ, start_response)

  File "C:\Python24\lib\site-packages\django\core\handlers\wsgi.py", line 193, in __call__
    response = middleware_method(request, response)

  File "C:\Python24\lib\site-packages\django\contrib\sessions\middleware.py", line 89, in process_response
    datetime.datetime.now() + datetime.timedelta(seconds=settings.SESSION_COOKIE_AGE))

  File "C:\Python24\lib\site-packages\django\contrib\sessions\models.py", line 29, in save
    s.save()

  File "C:\Python24\lib\site-packages\django\db\models\base.py", line 166, in save
    cursor = connection.cursor()

  File "C:\Python24\lib\site-packages\django\db\backends\postgresql_psycopg2\base.py", line 48, in cursor
    cursor.execute("SET TIME ZONE %s", [settings.TIME_ZONE])

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

[James Bennett]

Please be sure to read the previous discussion; it seems clear that the error has nothing whatsoever to do with time zones, timestamps or anything of that sort. Here is what is actually happening:

1. A bad query is being sent, and Postgres immediately aborts the current transaction.
2. For some reason, the exception which should be generated from the bad query is never happening.
3. Before doing the next query, Django is executing SET TIME ZONE (Django does this on pretty much every query).
4. When that happens, Postgres responds that it has stopped accepting statements in the current transaction, because of the error back in step 1.
5. This time, for some reason, the exception actually propagates.

To reiterate once again: the error is not coming from SET TIME ZONE, it's coming from whichever statement executed just before the SET TIME ZONE, and because an error is getting trapped or silenced somewhere, the original error never propagates. But after the original error has happened, any statement other than ROLLBACK will generate the "current transaction is aborted" message.

[rahul.pilani@…]

Since the only way to exit the bad state is to do a rollback, shouldnt Django do a rollback by default if it encounters a programming error by psycopg? This would eliminate the need to do everything in a try: except: .

My 2c.

Rahul Pilani

[anonymous]

Replying to SmileyChris:

Current way to do transactions:

from django.db import transaction

try:
    phone = Phone.objects.create(primary=True)
except:
    transaction.rollback()

[doerr@…]

Replying to j.quigley@serviomatic.com:

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

The only thing that worked for me (on the python command prompt):

import django; django.db.connection.close()

[Aymeric Augustin]

For the record, I just fixed this problem.