From: <an...@ne...> - 2006-10-07 08:37:27
|
Hello all, using ISF I've found a bug of the schema very similar to the one signalle= d here: https://www.cbil.upenn.edu/tracker/show_bug.cgi?id=3D177 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 sin= gle SQL instruction fails: sqlExec: INSERT INTO DoTSVer.SourceVer select v.*,215,now(),30 from DoTS.Source v where v.na_feature_id =3D ? 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 14= 7, <GEN39> line 368238. The SQL instruction is indeed incorrect: trying it manually in PostgreSQL dbgus=3D# INSERT INTO DoTSVer.SourceVer select v.*,215,now(),30 from DoTS.Source v where v.na_feature_id =3D 5 ; gives the very same error. The problem lies in the INSERT trigger for the view DoTSVer.SourceVer, wh= ich 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_transact= ion_id ) Now, the description for underlying table DoTS.NAFeatureimpVer starts wit= h dbgus=3D# \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 miss= es 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 likel= y 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. |
From: Steve F. <sfi...@pc...> - 2006-10-07 14:08:43
|
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 > |
From: Chris S. <sto...@pc...> - 2006-10-07 21:40:05
|
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 |
From: Michael S. <m...@sa...> - 2006-10-07 23:39:22
|
Antonio is correct here-- it looks like the postgres triggers aren't being correctly generated for this table. I had thought I had fixed this, but I guess not. These are in the db writer for postgres within the db admin package. Search for 'ON INSERT TO'. Antonio-- it may help if you forward along your install logs (that include all SQL used in generating the instance). Steve's also correct-- bug 177 appears to track a different issue. --Mike ----- Original Message ----- From: "Chris Stoeckert" <sto...@pc...> To: "Steve Fischer" <sfi...@pc...> Cc: <gus...@li...> Sent: Saturday, October 07, 2006 2:39 PM Subject: Re: [GUSDEV] bug in GUS schema,evidentiated by InsertSequenceFeatures > 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 > > > ------------------------------------------------------------------------- > 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 > |
From: <an...@ne...> - 2006-10-09 08:48:46
|
Quoting Michael Saffitz <m...@sa...>: > Antonio is correct here-- it looks like the postgres triggers aren't be= ing > correctly generated for this table. I had thought I had fixed this, but= I > guess not. These are in the db writer for postgres within the db admin > package. Search for 'ON INSERT TO'. Antonio-- it may help if you forw= ard > along your install logs (that include all SQL used in generating the > instance). > Steve's also correct-- bug 177 appears to track a different issue. I fear that while the database error does appear to be different, the underlying issue is exactly the same. Following Steve's advice to try suggesting a fix, I've been digging furth= er. If I got your code correctly, all this wreck is generated when the schema hierarchy gets converted, in GUS/DBAdmin/src/java/org/gusdb/dbadmin/util/GusClassHierarchyConverter.j= ava In function 'buildSuperClassView', (around line 440 in GUS 3.5; but 'buildSubClassView' exhibits the same issue) the code goes like this: -------------------------------------------------------------------------= --- // Housekeeping Columns and complete for the Version View if (impTable.isVersioned()) { superClassView.setVersioned(true); verSql =3D sql.concat(" "); for (Iterator i =3D verHousekeepingColumns.iterator(); i.hasNext();) { Column column =3D (Column)i.next(); verSql =3D verSql.concat(", " + column.getName()); superClassView.getVersionView().addColumn(new ColumnPair(column.getNa= me(), column.getName())); } verSql =3D verSql.concat( " FROM " + impTable.getVersionTable().getSchema().getName() + "." + impTable.getVersionTable().getName() + ";"); superClassView.getVersionView().setSql(verSql); } -------------------------------------------------------------------------= --- Now, the problem is that while 'verSql' is correctly generated (because i= t starts with 'sql'), the original view's colums are not copied at all. The reason is that GusView behaves differently from GusTable, since its 'setVersioned()' method does not copy the base view colums as one might h= ave expected after having looked at GusTable. As far as I can tell this has no relation to the actual database instance= in use, but I'd say that this is the core of the problem. HTH, Antonio ---------------------------------------------------------------- Net Wise webmail system - http://www.netwise.it This message was sent using IMP, the Internet Messaging Program. |
From: <an...@ne...> - 2006-10-16 07:54:56
|
Hello, did anyone have the time to look into the issue I submitted and confirm i= t? Thank you very much, Antonio Quoting an...@ne...: > Quoting Michael Saffitz <m...@sa...>: > > > Antonio is correct here-- it looks like the postgres triggers aren't = being > > correctly generated for this table. I had thought I had fixed this, b= ut I > > guess not. These are in the db writer for postgres within the db adm= in > > package. Search for 'ON INSERT TO'. Antonio-- it may help if you fo= rward > > along your install logs (that include all SQL used in generating the > > instance). > > Steve's also correct-- bug 177 appears to track a different issue. > > I fear that while the database error does appear to be different, the > underlying issue is exactly the same. > > Following Steve's advice to try suggesting a fix, I've been digging fur= ther. > If I got your code correctly, all this wreck is generated when the sche= ma > hierarchy gets converted, in > > GUS/DBAdmin/src/java/org/gusdb/dbadmin/util/GusClassHierarchyConverter= .java > > In function 'buildSuperClassView', (around line 440 in GUS 3.5; but > 'buildSubClassView' exhibits the same issue) the code goes like this: > > -----------------------------------------------------------------------= ----- > // Housekeeping Columns and complete for the Version View > if (impTable.isVersioned()) { > superClassView.setVersioned(true); > verSql =3D sql.concat(" "); > > for (Iterator i =3D verHousekeepingColumns.iterator(); i.hasNext();) = { > Column column =3D (Column)i.next(); > verSql =3D verSql.concat(", " + column.getName()); > superClassView.getVersionView().addColumn(new > ColumnPair(column.getName(), > column.getName())); > } > verSql =3D verSql.concat( > " FROM " + > impTable.getVersionTable().getSchema().getName() + > "." + impTable.getVersionTable().getName() + > ";"); > superClassView.getVersionView().setSql(verSql); > } > -----------------------------------------------------------------------= ----- > > Now, the problem is that while 'verSql' is correctly generated (because= it > starts with 'sql'), the original view's colums are not copied at all. > > The reason is that GusView behaves differently from GusTable, since its > 'setVersioned()' method does not copy the base view colums as one might= have > expected after having looked at GusTable. > > As far as I can tell this has no relation to the actual database instan= ce in > use, but I'd say that this is the core of the problem. > > HTH, > > Antonio > > > ---------------------------------------------------------------- > 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=3Djoin.php&p=3Dsourceforge&CID=3D= DEVDEV > _______________________________________________ > Gusdev-gusdev mailing list > Gus...@li... > https://lists.sourceforge.net/lists/listinfo/gusdev-gusdev > > !DSPAM:452a0cfd138065873587180! > > ---------------------------------------------------------------- Net Wise webmail system - http://www.netwise.it This message was sent using IMP, the Internet Messaging Program. |