[Modeling-users] Warning: bug causing possible data loss w/ psycopg

[Sebastien B. <sbi...@us...>]

        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