[Modeling-users] Warning: bug causing possible data loss w/ psycopg
Status: Abandoned
Brought to you by:
sbigaret
|
From: Sebastien B. <sbi...@us...> - 2003-12-01 17:10:41
|
Hi all,
Let me expose a bug in the framework that can cause data loss when
used w/ postgresql and psycopg (and maybe other adaptors as well, I
cannot be categorical).
There is a bug in the SQL generated for the database schema, involving
relationships to entities having subclasses: say you have classes A, B
and C, with A being the parent of B and B the parent of C.
Now if you design a relationship from A to B and you try to save two
objects 'a' and 'c' in relation, oracle and postgresql will fail
because the following constraint generated by
mdl_generate_DB_schema.py is wrong:
ALTER TABLE A ADD CONSTRAINT b FOREIGN KEY (FK_B) REFERENCES B(ID);
[in this case A.FK_B can reference either B(ID) or C(ID)]
Now the problem is that psycopg DISCARDS the error and does not raise
anything (in this case, an IntegrityError) --> nothing is actually
written in the database, but ec.saveChanges() returns successfully.
This problems happens w/ psycopg only, as far as I know. Other
adaptors will either fail (DCOracle2, pgdb, pypresql) with an
integrity error, or simply succeed because the underlying db does not
enforce the referential constraint (mysql, sqlite).
Immediate workaround: in your database schema, drop the constraints
coming from to-one relationships pointing to entities having subclasses.
Postgresql and Oracle:
ALTER TABLE A drop CONSTRAINT <constraint_name>;
This will ensure that your data are correctly saved w/ no "false"
integrity error, and additionally it will prevent data loss if you're
using postgresql and psycopg.
(You do not need this if you use sqlite or mysql, as far as I know)
-- S=E9bastien.
PS: the bug has been reported to the psycopg ml, you can find more
details here:
http://lists.initd.org/pipermail/psycopg/2003-December/002432.html
|