[Biopython-dev] [Bug 2833] Features insertion on previous bioentry_id

[Cymon Cox]

2009/5/21 <bugzilla-daemon at portal.open-bio.org>

> http://bugzilla.open-bio.org/show_bug.cgi?id=2833
>
>
>
>
>
> ------- Comment #11 from andrea at biodec.com  2009-05-21 18:22 EST -------
> So the problem is related to the different behaviur adopted by postgres
> loaded
> with the biosql schema, with respect to mysql.
>
> Sorry because i thought the problem was due to BioSQL because i didn't know
> wich was the "expected database behaviour".
>
> Since we expect an error during insertion of a "duplicate" or "quite
> duplicate"
> record... we have only to focus on the postgres biosql schema, and
> why/where it
> differs from the mysql one.
>
> I didn't have time to have a look to the difference between the various
> "duplicate record tests". I will do.
>
> [i've tried postgres 8.4... and it's exactly the same]


Hi Andrea,

The problem appears to be related to the BioSQL schema/PostGreSQL.

As you indicated, adding a duplicate entry to bioentry returns a "INSERT 0
0" and doesnt throw an IntegrityError which is what the code is looking from
and presumably what MySQL throws.

The reason it doesnt throw an error is because of one (or both) of the RULES
in the schema:

rule_bioentry_i1
and/or
rule_bioentry_i2

If you delete these two rules, load the schema and try to do a duplicate
entry:

mytest=# insert into bioentry(bioentry_id, biodatabase_id, name, accession,
version) values (2, 1, 'blah1', 'test4', 1);
INSERT 0 1
mytest=# select * from bioentry;
 bioentry_id | biodatabase_id | taxon_id | name  | accession | identifier |
division | description | version
-------------+----------------+----------+-------+-----------+------------+----------+-------------+---------
           2 |              1 |          | blah1 | test4     |
|          |             |       1
(1 row)

mytest=# insert into bioentry(bioentry_id, biodatabase_id, name, accession,
version) values (2, 1, 'blah1', 'test4', 1);
ERROR:  duplicate key value violates unique constraint "bioentry_pkey"

we have an error rather than a "INSERT 0 0"

I'm going to assume that psycopg2 would pick-up this error and throw an
IntegrityError, but I havent taken it any further to check.

Cheers, C.