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

[Chris S. <sto...@pc...>]

John Iodice in our group is working on a postgres instance of GUS so  
will ask him to help but if anyone else out there has some ideas that  
would be great to hear.
Chris

On Oct 7, 2006, at 10:08 AM, Steve Fischer wrote:

> 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
>>
>
> ---------------------------------------------------------------------- 
> ---
> 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