Postgresql and transactions, revisited
Nis Jørgensen
postgresql/psycopg2.
I had the problem that any db error in any of my tests (including
setups) would cause all following tests to fail (because the transaction
was not rolled back). This was made even worse by the fact that I used
the db in tearDown, which did not work very well either (I have since
started using django.test.TestCase, so I don't use tearDown much).
I also ran into this problem outside of tests, but do not remember
details. Probably I was trying to use try ... except to catch a
database error.
My original solution was to edit base.py to enable autocommit (thus, as
far as I can tell, disabling Django's transaction handling). This worked
well for a while ... until I started using fixtures. Forward references
don't work unless loaded within a transaction.
I think the problem at the base of all this is that postgresql's
transaction/error handling semantics are different from that of the
other backends - while Django treats it as if it was the same. I would
like to take a stab at fixing this (at the appropriate level, which I am
not too sure I have identified). But before I do that, I would like to
hear if anyone has any reasons why this should not be done ...
I will post some test cases with suggested behavior later (in a ticket).
If someone could give me a clue to how I can easily run the django test
suite, I would be glad.
Nis
Russell Keith-Magee
> I had the problem that any db error in any of my tests (including
> setups) would cause all following tests to fail (because the transaction
> was not rolled back). This was made even worse by the fact that I used
> the db in tearDown, which did not work very well either (I have since
> started using django.test.TestCase, so I don't use tearDown much).
I can see how this problem could arise. The test framework doesn't
have any special handling for errors, so if one test pushes the
database into an error state, subsequent tests may fail.
Ideally, test cases should be completely independent. It would be much
nicer to have a single test fail with a clean error than have one test
fail due to an error, and then all subsequent tests failing as a
consequence of the first failure.
> I think the problem at the base of all this is that postgresql's
> transaction/error handling semantics are different from that of the
> other backends - while Django treats it as if it was the same. I would
> like to take a stab at fixing this (at the appropriate level, which I am
> not too sure I have identified). But before I do that, I would like to
> hear if anyone has any reasons why this should not be done ...
I doubt this is a postgres-specific problem. The exact exception that
is thrown will be postgres specific, but all backends will throw
exceptions, and will require transaction rollback (or some other
handling) to allow the test suite to continue.
This does raise the larger issue of generic error handling for the
database backends; It has been suggested that the various backend
exceptions should be wrapped by a generic Django database failure
exception. If database exceptions were normalized in this way,
catching the type of errors you describe would become much cleaner -
both in tests, and in the general case.
> I will post some test cases with suggested behavior later (in a ticket).
> If someone could give me a clue to how I can easily run the django test
> suite, I would be glad.
Most certainly log this issue; test cases are most welcome; fixes even
more welcome.
I suspect the best approach will be to extend the django TestCase,
overriding one of the test run methods to provide transaction
checking. It may be helpful to define this behaviour as a decorator;
this would also allow users with tests based on unittest.TestCase,
rather than django.test.TestCase.
Yours,
Russ Magee %-)
Nis Jørgensen
> On 7/31/07, Nis Jørgensen <n...@superlativ.dk> wrote:
>
>> I had the problem that any db error in any of my tests (including
>> setups) would cause all following tests to fail (because the transaction
>> was not rolled back). This was made even worse by the fact that I used
>> the db in tearDown, which did not work very well either (I have since
>> started using django.test.TestCase, so I don't use tearDown much).
>>
>
> I can see how this problem could arise. The test framework doesn't
> have any special handling for errors, so if one test pushes the
> database into an error state, subsequent tests may fail.
>
> Ideally, test cases should be completely independent. It would be much
> nicer to have a single test fail with a clean error than have one test
> fail due to an error, and then all subsequent tests failing as a
> consequence of the first failure.
>
>
>> I think the problem at the base of all this is that postgresql's
>> transaction/error handling semantics are different from that of the
>> other backends - while Django treats it as if it was the same. I would
>> like to take a stab at fixing this (at the appropriate level, which I am
>> not too sure I have identified). But before I do that, I would like to
>> hear if anyone has any reasons why this should not be done ...
>>
>
> I doubt this is a postgres-specific problem. The exact exception that
> is thrown will be postgres specific, but all backends will throw
> exceptions, and will require transaction rollback (or some other
> handling) to allow the test suite to continue.
>
As far as I can tell, mySQL does not invalidate the current transaction
when it encounters an error. Thus you can do
"""
mysql> create table foo (bar varchar(20) primary key) engine innodb;
Query OK, 0 rows affected (0.28 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into foo values ('foo');
Query OK, 1 row affected (0.19 sec)
mysql> insert into foo values ('foo');
ERROR 1062 (23000): Duplicate entry 'foo' for key 1
mysql> insert into foo values ('bar');
Query OK, 1 row affected (0.00 sec)
"""
While in postgres you get:
"""
spider=> create table foo (bar varchar(20) primary key) ;
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"foo_pkey" for table "foo"
CREATE TABLE
spider=> begin;
BEGIN
spider=> insert into foo values ('foo');
INSERT 0 1
spider=> insert into foo values ('foo');
ERROR: duplicate key violates unique constraint "foo_pkey"
spider=> insert into foo values ('bar');
ERROR: current transaction is aborted, commands ignored until end of
transaction block
"""
For the record, I think mySQL is the one making sense here. sqlite
behave the same way. I don't have an Oracle installation to test on (nor
do I ever wish to get one).
> This does raise the larger issue of generic error handling for the
> database backends; It has been suggested that the various backend
> exceptions should be wrapped by a generic Django database failure
> exception. If database exceptions were normalized in this way,
> catching the type of errors you describe would become much cleaner -
> both in tests, and in the general case.
>
This sounds very much along the lines that I have been looking at for
solving the issue.
Out of interest, which versions of postgres are we supporting? I am
asking because I think the mySQL behavior can only be mimicked using
savepoints, which became available in 8.0.
I see that there are in fact no mention of version numbers of any of the
db products on the installation page. IMO, this should be fixed as well.
I guess I should log an issue ...
>> I will post some test cases with suggested behavior later (in a ticket).
>> If someone could give me a clue to how I can easily run the django test
>> suite, I would be glad.
>>
>
> Most certainly log this issue; test cases are most welcome; fixes even
> more welcome.
>
> I suspect the best approach will be to extend the django TestCase,
> overriding one of the test run methods to provide transaction
> checking. It may be helpful to define this behaviour as a decorator;
> this would also allow users with tests based on unittest.TestCase,
> rather than django.test.TestCase.
>
Thanks for the comments. I will log two issues:
1. Different backends have differen transaction semantics (and one of
them makes error handling distinctly hard to use, ie pg).
2. Testcases give problems because of this.
I am separating them since the second one is relatively easier to fix..
Nis