id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
16622	django.test.TestCase slow using PostgreSQL 8.4.4	valhallasw	nobody	"Using very simple tests, such as
{{{#!python
class To_confirm_the_stable_database_contains_the_right_data(TestCase):
    def test_adressen_that_should_not_exist_do_not_exist(self):
        adressen = [1409, 1410, 1411, 4094]
        for adres in adressen:
            self.failUnlessEqual(
                Adres.objects.using('stable').filter(pk=4095).count(),
                0
            )
}}}

results in very slow tests:
{{{
$ ./manage.py test
Creating test database for alias 'default'...
.....
----------------------------------------------------------------------
Ran 5 tests in 6.890s

OK
}}}

This is caused by `TRUNCATE` commands, which is clear when logging all queries using pdb:
{{{
b /lib/python2.6/site-packages/Django-1.3-py2.6.egg/django/db/backends/postgresql_psycopg2/base.py:43
commands 1
silent
import time
print ""QUERY"", time.time(), query
continue
continue
}}}

yields
{{{#!sql
QUERY 1313096004.8
            SELECT c.relname
            FROM pg_catalog.pg_class c
            LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
            WHERE c.relkind IN ('r', 'v', '')
                AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
                AND pg_catalog.pg_table_is_visible(c.oid)
QUERY 1313096004.83 TRUNCATE ""models_opdracht"", ""models_dataholder"", (...47 more...)
QUERY 1313096006.59 SELECT setval(pg_get_serial_sequence('""models_dataholder_audit""','_audit_id'), 1, false);
(..)
QUERY 1313096006.63 SELECT setval(pg_get_serial_sequence('""models_groepeninfo""','id'), 1, false);
}}}

I thought I read somewhere that PostgreSQL did not support transactioned `TRUNCATE` before 9.1, but I am unable to find the source. The documentation suggests neither 8.4 nor 9.1 fully support transactions.

I can think of two ways to approach this problem -- one: don't use `TRUNCATE`, use `DELETE` instead, which should work fine inside the transaction; or two: instead of `TRUNCATE`ing in each transaction, `TRUNCATE` before the first transaction, and roll back all further transactions.

I was uncertain whether to file this under 'testing framework' or under 'database layer'. As the most direct effect is on testing, I chose the former.

"	Cleanup/optimization	closed	Database layer (models, ORM)	1.3	Normal	invalid		valhallasw	Accepted	0	0	0	0	0	0