Opened 10 years ago

Closed 9 years ago

Last modified 2 years ago

#852 closed defect (wontfix)

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

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

Description

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'

Change History (28)

comment:1 Changed 10 years ago by hugo

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

comment:2 Changed 10 years ago by adrian

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';

comment:3 Changed 10 years ago by adrian

  • priority changed from high to normal
  • Severity changed from major to normal

comment:4 Changed 9 years ago by adrian

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

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

comment:5 Changed 9 years ago by GomoX <gomo@…>

  • Cc gomo@… added
  • Resolution worksforme deleted
  • Status changed from closed to reopened
  • Summary changed from Error on insert into database - cursor.execute("SET TIME ZONE %s", [TIME_ZONE]) to psycopg.ProgrammingError exceptions leave the connection to the DB in an unusable state

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.

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

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

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

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

comment:10 Changed 9 years ago by rock@…

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

comment:11 Changed 9 years ago by jacob

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

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

comment:12 Changed 9 years ago by Gary Wilson <gary.wilson@…>

  • Cc gary.wilson@… added
  • Resolution fixed deleted
  • Status changed from closed to reopened

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'

comment:13 Changed 9 years ago by ubernostrum

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.

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

comment:15 Changed 9 years ago by jacob

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

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.

comment:16 Changed 9 years ago by 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)?

comment:17 Changed 9 years ago by model trains

  • Cc eminem.com added; gomo@… gary.wilson@… removed
  • Keywords eminem.com added
  • Summary changed from psycopg.ProgrammingError exceptions leave the connection to the DB in an unusable state to eminem.com

comment:18 Changed 9 years ago by cheap international health insurance

  • Cc kakalu.com added; eminem.com removed
  • Keywords kakalu.com added; eminem.com removed
  • Summary changed from eminem.com to kakalu.com

comment:19 Changed 9 years ago by case n70 nokia

  • Cc eminem.com added; kakalu.com removed
  • Keywords eminem.com added; kakalu.com removed
  • Summary changed from kakalu.com to eminem.com

comment:20 Changed 9 years ago by anonymous

  • Cc eminem.com removed
  • Keywords eminem.com removed
  • Summary changed from eminem.com to psycopg.ProgrammingError exceptions leave the connection to the DB in an unusable state

comment:21 follow-up: Changed 9 years ago by SmileyChris

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.

comment:22 Changed 8 years ago by 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)

comment:23 Changed 8 years ago by 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

comment:24 Changed 8 years ago by ubernostrum

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.

comment:25 Changed 8 years ago by 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

comment:26 in reply to: ↑ 21 Changed 8 years ago by anonymous

Replying to SmileyChris:

Current way to do transactions:

from django.db import transaction

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

comment:27 in reply to: ↑ description Changed 4 years ago by 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()

comment:28 Changed 2 years ago by aaugustin

  • Easy pickings unset
  • UI/UX unset

For the record, I just fixed this problem.

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