transaction.savepoint_rollback fails with postgresql DATABASE_ENGINE

[Andrew Badr]

Calls to transaction.rollback_savepoint fail because they try to create a new cursor, which executes "SET client_encoding to 'UNICODE'" inside the already failed transaction. This is in 1.1 and trunk (r11724).

To repro: create a project w/the "postgresql" database backend, and do python runtests.py get_or_create.

Example traceback:

 File "/var/www/yourworld/ywot/views.py", line 136, in send_edits
   tile, _ = Tile.objects.get_or_create(world=world, tileY=tileY, tileX=tileX)

 File "/usr/local/lib/python2.6/dist-packages/django/db/models/manager.py", line 123, in get_or_create
   return self.get_query_set().get_or_create(**kwargs)

 File "/usr/local/lib/python2.6/dist-packages/django/db/models/query.py", line 339, in get_or_create
   transaction.savepoint_rollback(sid)

 File "/usr/local/lib/python2.6/dist-packages/django/db/transaction.py", line 199, in savepoint_rollback
   connection._savepoint_rollback(sid)

 File "/usr/local/lib/python2.6/dist-packages/django/db/backends/__init__.py", line 67, in _savepoint_rollback
   self.cursor().execute(self.ops.savepoint_rollback_sql(sid))

 File "/usr/local/lib/python2.6/dist-packages/django/db/backends/__init__.py", line 81, in cursor
   cursor = self._cursor()

 File "/usr/local/lib/python2.6/dist-packages/django/db/backends/postgresql/base.py", line 127, in _cursor
   cursor.execute("SET client_encoding to 'UNICODE'")

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

SET client_encoding to 'UNICODE'

[Andrew Badr]

Eh, botched the title. :)

[Andrew Badr]

I mean it *has* tests. Am I missing something?

[Andrew Badr]

To clarify, this is with DATABASE_ENGINE = "postgresql", *not* "postgresql_psycopg2".

[Jonathan Leroy]

Hello Andrew,

This is a duplicate of #12087.
I have the same problem with postgresql, postgresql_psycopg2, on 1.1.1 and TRUNK versions.
FYI, I use postgresql 8.3

[Andrew Badr]

Jonathan, it looks like these are separate issues. The ticket you refer to is only about tests, and successfully repro's under psycopg2. Neither of those are true for this ticket. Also, I'd try changing your setup to isolate the bug because other people can run the tests fine without running into what you're seeing.

[Karen Tracey]

I can't figure out how you are even getting to the point where get_or_create tries to rollback the transaction. At the revision mentioned (and now), get_or_create only tries the rollback when it catches an IntegrityError (see ​http://code.djangoproject.com/browser/django/trunk/django/db/models/query.py?rev=11724#L311). Yet when I try to run the get_or_create testcase with the old postgres backend, the first problem encountered is that no IntegrityError is raised but rather a ProgrammingError is raised. (This is true even with 1.1 or trunk r11724). I have to change the except IntegrityError, e in get_or_create to except Exception, e and then I can see the subsequent problem that attempting to do a savepoint rollback will first try to set do that "SET client_encoding to 'UNICODE'":

> c:\u\kmt\django\django-1.1\django\test\_doctest.py(362)set_trace()->None
-> pdb.Pdb.set_trace(self)
(Pdb) n
> c:\u\kmt\django\django-1.1\django\db\models\query.py(341)get_or_create()
-> transaction.savepoint_rollback(sid)
(Pdb) l
336                     transaction.savepoint_commit(sid)
337                     return obj, True
338     #           except IntegrityError, e:
339                 except Exception, e:
340                     import pdb; pdb.set_trace()
341  ->                 transaction.savepoint_rollback(sid)
342                     try:
343                         return self.get(**kwargs), False
344                     except self.model.DoesNotExist:
345                         raise e
346
(Pdb) n
ProgrammingError: "ERROR:  current transaction is aborted, commands ignored until end of transaction block\n\nSET client
_encoding to 'UNICODE'"
> c:\u\kmt\django\django-1.1\django\db\models\query.py(341)get_or_create()
-> transaction.savepoint_rollback(sid)
(Pdb)

So, a question: have you done anything to your install to fix this IntegrityError/ProgrammingError mismatch I see? I'm also wondering why you are using the old backend instead of psycopg2?

The error I see is reported in #12766 but since this is a bit different I'm going to leave both open for now.

[Andrew Badr]

I really don't think I did anything to fix the exception mismatch you're seeing. I don't remember encountering it at all. Could it be that my test case was using a field with a unique constraint?

How do I install psycopg1 on Ubuntu? Not sure how I was able to do this before.

(I'm not using it anymore, and when I was it was only by accident. Maybe the BDFLs should consider dropping psycopg1 support for 1.2?)

[Russell Keith-Magee]

Replying to andrewbadr:

Maybe the BDFLs should consider dropping psycopg1 support for 1.2?)

We can't just drop support, but we have put the postgresql backend on the deprecation path: See [12510]

[Karen Tracey]

Thanks for responding. I don't know how to get it on Ubuntu, I've only managed to get binaries for it on Windows. Could be what I've managed to find for Windows is not quite a match for what you were running. (I don't actually use it myself, just tried to get a test setup with it working after seeing some problems reported against it.)

It's been put on the deprecation path (​http://docs.djangoproject.com/en/dev/releases/1.2/#postgresql-database-backend) but that's a mutli-release cycle thing before it's completely cut. In the meantime we've got a few things not working right with it. psycopg2 is definitely a better bet unless there's some reason you can't use it (which I have not heard anyone report yet).

[Andrew Badr]

Yeah, I know -- I was proposing to accelerate the process in a rash violation of policy.

I can't figure out how to install this without downgrading python.

[Russell Keith-Magee]

This was fixed (inadvertently) by r12848.

[Jacob]

Milestone 1.2 deleted