Re: [GUSDEV] bug in GUS schema, evidentiated by InsertSequenceFeatures

[Steve F. <sfi...@pc...>]

Antonio-

i don't think this is the same problem as bug 177.   in that bug, there 
was a uniqueness constraint violation, not a non-null constraint violation.

we don't have much postgres expertise in-house.  i'll see if anybody can 
help  on this. 

otherwise, you might need to propose a solution yourself.

steve

an...@ne... wrote:
> Hello all,
> using ISF I've found a bug of the schema very similar to the one signalled
> here: https://www.cbil.upenn.edu/tracker/show_bug.cgi?id=177
>
> When using ISF like this:
>
> ga GUS::Supported::Plugin::InsertSequenceFeatures \
>  --mapFile $GUS_HOME/config/genbank2gus.xml \
>  --inputFileOrDir CHR_I/NC_003279.gbk --fileFormat Genbank \
>  --extDbName "NCBI Genome data" --extDbRlsVer 2006.02.16 \
>  --veryVerbose --sqlVerbose --commit
>
> the procedure does complete the insert of all relevant data, but this single
> SQL instruction fails:
>
> sqlExec:
>  INSERT INTO DoTSVer.SourceVer select v.*,215,now(),30 from DoTS.Source v
>  where v.na_feature_id = ?
>  bindValues (5)
>
> DBD::Pg::st execute failed: ERROR:  null value in column "na_feature_id"
> violates not-null constraint at
> /hardmnt/mpa/gus/GUS/gus_home/lib/perl/GUS/ObjRelP/DbiDbHandle.pm line 147,
> <GEN39> line 368238.
>
> The SQL instruction is indeed incorrect: trying it manually in PostgreSQL
>
> dbgus=# INSERT INTO DoTSVer.SourceVer
>   select v.*,215,now(),30 from DoTS.Source v where v.na_feature_id = 5
> ;
>
> gives the very same error.
>
> The problem lies in the INSERT trigger for the view DoTSVer.SourceVer, which
> PostgreSQL spells out as follows:
>
> ON INSERT TO dotsver.sourcever DO INSTEAD
>   INSERT INTO dotsver.nafeatureimpver (
>     modification_date, user_read, user_write, group_read, group_write,
>     other_read, other_write, row_user_id, row_group_id, row_project_id,
>     row_alg_invocation_id, version_alg_invocation_id, version_date,
>     version_transaction_id
>   ) VALUES (
>     new.modification_date, new.user_read, new.user_write, new.group_read,
>     new.group_write, new.other_read, new.other_write, new.row_user_id,
>     new.row_group_id, new.row_project_id, new.row_alg_invocation_id,
>     new.version_alg_invocation_id, new.version_date, new.version_transaction_id
>   )
>
> Now, the description for underlying table DoTS.NAFeatureimpVer starts with
>
> dbgus=# \d dotsver.nafeatureimpver
>                     Table "dotsver.nafeatureimpver"
>             Column            |            Type             | Modifiers
> ------------------------------+-----------------------------+-----------
>  na_feature_id                | numeric(10,0)               | not null
>
> Clearly the DoTSVer.SourceVer trigger for INSERTs is wrong, since it misses
> to set the (provided) value to na_feature_id column.
>
> Bug #177 signalled in you Bugzilla is generated by the very same problem, but
> on view DoTSVer.TranslatedAAFeatureVer (and using Oracle), so quite likely
> this might a more general issue related to how DoTSVer views are created.
>
> Let me know if you need more information for tackling the problem,
>
> Antonio Galea
>
>
>
> ----------------------------------------------------------------
> Net Wise webmail system - http://www.netwise.it
> This message was sent using IMP, the Internet Messaging Program.
>
>
> -------------------------------------------------------------------------
> Take Surveys. Earn Cash. Influence the Future of IT
> Join SourceForge.net's Techsay panel and you'll get the chance to share your
> opinions on IT & business topics through brief surveys -- and earn cash
> http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
> _______________________________________________
> Gusdev-gusdev mailing list
> Gus...@li...
> https://lists.sourceforge.net/lists/listinfo/gusdev-gusdev
>