integrity error silently failing with postgres and loaddata

[sandro@…]

this is a simple example where a Model.save() that should raise
integrity/operational errors is not:

example

class one(models.Model):
    fk_field = models.ForeignKey('two')

class two(models.Model):
    description = models.CharField(maxlength=10)

In [1]: from webfi.sd.models import *

In [2]: One(fk_field_id=1)
Out[2]: <One: One object>
In [3]: One(fk_field_id=1).save()

pg logs

The last line should have raised an integrity error but it does not. Db log
(PostgreSQL) shows:

STATEMENT: INSERT INTO "sd_one" ("fk_field_id") VALUES (1)
2007-06-05 16:55:38 [30666] LOG: duration: 0.345 ms statement: SELECT CURRVAL('"sd_one_id_seq"')
STATEMENT: SELECT CURRVAL('"sd_one_id_seq"')
2007-06-05 16:55:38 [30666] ERROR: insert or update on table "sd_one" violates foreign key constraint "$1"
DETAIL: Key (fk_field_id)=(1) is not present in table "sd_two".

a second save

a second 'save' results in:

In [4]: One(fk_field_id=1).save()
---------------------------------------------------------------------------
psycopg2.OperationalError                          Traceback (most recent call last)

/home/sandro/src/fossati/webfi/<ipython console>

/misc/src/django/trunk/django/db/models/base.py in save(self)
    241                 cursor.execute("INSERT INTO %s (%s) VALUES (%s)" % \
    242                     (backend.quote_name(self._meta.db_table), ','.join(field_names),
--> 243                     ','.join(placeholders)), db_values)
    244             else:
    245                 # Create a new record with defaults for everything.

/misc/src/django/trunk/django/db/backends/util.py in execute(self, sql, params)
     10         start = time()
     11         try:
---> 12             return self.cursor.execute(sql, params)
     13         finally:
     14             stop = time()

OperationalError:   insert or update on table "sd_one" violates foreign key constraint "$1"
DETAIL:  Key (fk_field_id)=(1) is not present in table "sd_two".

And that's ok, but too late...

It seems to me too big a mistake so I hope I'm just missing something, if
not I'll file a new ticket for this. I think in no way a db error should be
ignored...

I tried with a fresh svn co of rel 5427.

[Jacob]

I *think* this is because Django declares FKs in Postgres as "initially deferred", which means integrity checks don't run until the transaction is committed. You should get the error *later* when you commit the transaction.

[sandro@…]

My understanding of object.save() is that if I'm not managing directly the transaction,
the commit happens as a last step of .save(), just before signal dispatching. Moreover the database
logs the error that makes me think it already has found the error.

Anyhow I realized the error when I realized that no data where saved and no error
where raised, so there is no such a *later* raise of the error.

[giorgio.salluzzo@…]

Replying to sandro@e-den.it:

this is a simple example where a Model.save() that should raise
integrity/operational errors is not:

I got the same problem loading a fixture using "manage.py loaddata test_ticket.json".

This is the Django reply:

Loading 'test_ticket' fixtures...
Installing json fixture 'test_ticket' from '/usr/lib/python2.4/site-packages/jungle/apps/ticket/fixtures'.
Installed 1 object(s) from 1 fixture(s)

...and this is the Postgresql log:

2007-06-19 15:08:47 CEST ERROR:  insert or update on table "ticket_project_organization" violates foreign key constraint "ticket_project_organization_organization_id_fkey"
2007-06-19 15:08:47 CEST DETAIL:  Key (organization_id)=(1) is not present in table "organization_organization".
2007-06-19 15:08:47 CEST STATEMENT:  END
2007-06-19 15:08:47 CEST WARNING:  non c'è nessuna transazione in corso
2007-06-19 15:08:47 CEST LOG:  duration: 0.579 ms  statement: ABORT

This happened because I forgot to load the fixture needed by the one I executed and, of course, the db table is empty.

[Keith Bussell]

I can't seem to reproduce this problem. Using the model structure defined in the example in the description, I try loaddata on:

[{"pk": 1, "model": "bug4499.one", "fields": {"fk_field": 1}}]

and I properly get an operational error. I've tried it with both psycopg and psycopg2, on python v2.5.1/postgresql v8.2.4.

[Russell Keith-Magee]

#6724 is a duplicate of this, but with a complete out-of-the-box test case.

I saw this problem today at work, but when I got home, I couldn't reproduce it. Keith has ruled out Psycopg that as a source of difference. I run OSX at home but Windows XP at work, so that could be a contributing cause. However, some of the comments on this ticket seem to be from Linux systems, so it obviously isn't as simple as the WinXP psycopg being broken.

Can anyone add to this list of working/non-working configurations?

SILENT FAIL: Python 2.4, Psycopg2, Postgres 8.1, Windows XP

ERROR RAISED: Python 2.4, Psycopg, Postgres 8.1, MacOSX 10.4

[Anssi Kääriäinen]

Anybody seen anything related to this in a while? To me it seems this one could be closed.

[Anssi Kääriäinen]

Closed, as I don't believe this ticket to be valid anymore. Maybe this was fixed in #6724?