#852 closed defect (wontfix)
psycopg.ProgrammingError exceptions leave the connection to the DB in an unusable state
Reported by: | Owned by: | Adrian Holovaty | |
---|---|---|---|
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 by , 19 years ago
comment:2 by , 19 years ago
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 by , 19 years ago
priority: | high → normal |
---|---|
Severity: | major → normal |
comment:4 by , 19 years ago
Resolution: | → worksforme |
---|---|
Status: | new → closed |
Closing this ticket because we haven't heard anything back.
comment:5 by , 19 years ago
Cc: | added |
---|---|
Resolution: | worksforme |
Status: | closed → reopened |
Summary: | Error on insert into database - cursor.execute("SET TIME ZONE %s", [TIME_ZONE]) → 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 by , 19 years ago
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 by , 19 years ago
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 by , 19 years ago
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 by , 19 years ago
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 by , 19 years ago
Sorry about the typo (django.core) and the ugly formatting, but the info is good.
comment:11 by , 19 years ago
Resolution: | → fixed |
---|---|
Status: | reopened → closed |
Transaction management (now on magic-removal) provides a fix for this problem. Use it. Love it.
comment:12 by , 18 years ago
Cc: | added |
---|---|
Resolution: | fixed |
Status: | closed → 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 by , 18 years ago
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 by , 18 years ago
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 by , 18 years ago
Resolution: | → wontfix |
---|---|
Status: | reopened → 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 by , 18 years ago
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 by , 18 years ago
Cc: | added; removed |
---|---|
Keywords: | eminem.com added |
Summary: | psycopg.ProgrammingError exceptions leave the connection to the DB in an unusable state → eminem.com |
comment:18 by , 18 years ago
Cc: | added; removed |
---|---|
Keywords: | kakalu.com added; eminem.com removed |
Summary: | eminem.com → kakalu.com |
comment:19 by , 18 years ago
Cc: | added; removed |
---|---|
Keywords: | eminem.com added; kakalu.com removed |
Summary: | kakalu.com → eminem.com |
comment:20 by , 18 years ago
Cc: | removed |
---|---|
Keywords: | eminem.com removed |
Summary: | eminem.com → psycopg.ProgrammingError exceptions leave the connection to the DB in an unusable state |
follow-up: 26 comment:21 by , 18 years ago
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 by , 18 years ago
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 by , 18 years ago
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 by , 18 years ago
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:
- A bad query is being sent, and Postgres immediately aborts the current transaction.
- For some reason, the exception which should be generated from the bad query is never happening.
- Before doing the next query, Django is executing
SET TIME ZONE
(Django does this on pretty much every query). - When that happens, Postgres responds that it has stopped accepting statements in the current transaction, because of the error back in step 1.
- 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 by , 18 years ago
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 by , 17 years ago
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 by , 14 years ago
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 by , 12 years ago
Easy pickings: | unset |
---|---|
UI/UX: | unset |
For the record, I just fixed this problem.
Looks like a problem with your database setup - maybe your postgresql doesn't know about the Europe/Madrid timezone?