Re: [Gusdev-gusdev] everything works except commit!

[Michael S. <msa...@pc...>]

Hi Michael,

Your sequences are out of sync with the corresponding tables.  You 
should definitely continue to use the sequences (you'll avoid many 
problems down the road), but you'll need to alter sequences so that 
they start with the proper value.  For example, for the similarity 
table:

(Get the maximum value of the primary key:)

select max(SIMILARITY_ID) from dots.similarity;

You must then drop and recreate the sequence, setting the sequence to 
start with the value you got in the query above, plus 5 or so to be 
safe.  Let's say this is 5067:

drop sequence dots.similarity_SQ;
create sequence dots.similarity_SQ START WITH 5073;

Using the count(*) as the primary key generation is not supported and 
is likely to produced unexpected results.

--Mike
CBIL DBA

On Apr 19, 2004, at 11:58 AM, MICHAEL LUCHTAN wrote:

> Hello everyone-
> I have been attempting to load blast similarities into GUS with the 
> plugin
> LoadBlastSimFast.pm after already loading some data in with the plugin
> LoadBlastSimilarityPK.pm (which was slow compared with the Fasst 
> version).
> Well, the first thing that LoadBlastSimFast.pm does is start 
> complaining
> about PrimaryKey uniqueness constraints being violated.  It used some
> fancy Oracle SQL that I didn't understand:
> select dots.similarity_SQ.NEXTVAL from DUAL
> Which by debugging I found out started at one, and increased each time 
> we
> tested the plugin, regardless of commit not being turned on.  
> Naturally this crashed, because
> we already had about 3,000 entries in the dots.similarity table and
> we already had a sequence_id with value 1.  There
> was likewise a problem with the dots.similarityspan table.
>
> Since I only having a workingman's knowledge of OracleSQL, I switched 
> it
> so that it starts with
> select count(*) from dots.similarity
> And increases this by one for each entry.  Likewise for
> dots.similarityspan.  This seemed to fix the problem, and the plugin 
> works
> great on each file tested, claims to have entered all the correct data,
> and exits gracefully.  Yet when I run the same command line with 
> --commit
> appended to the end, we start getting these Primary Key constraints
> violated errors again.
>
> Perhaps I do not understand what happens with commit.  I thought that
> these kind of errors would be caught with a regular run without commit
> turned on.
> Any help?
>
>
>
>
> Michael Luchtan
> http://www.cs.uga.edu/~luchtan
>
>
>
>
>
> -------------------------------------------------------
> This SF.Net email is sponsored by: IBM Linux Tutorials
> Free Linux tutorial presented by Daniel Robbins, President and CEO of
> GenToo technologies. Learn everything from fundamentals to system
> administration.http://ads.osdn.com/?ad_id=1470&alloc_id=3638&op=click
> _______________________________________________
> Gusdev-gusdev mailing list
> Gus...@li...
> https://lists.sourceforge.net/lists/listinfo/gusdev-gusdev