Re: problem with sequence PostgreSQL 7.3.4 - Mailing list pgsql-novice

Not a waste of time. Just a reminder that we all make mistakes. You should
hear  some of mine ;o)

On Tuesday 16 November 2004 12:24 pm, Betsy Barker saith:
> Tom & List,
> I apologize. I found the problem and it turns out that the original unique
> number generator for this table was being incremented by 3000 and those ids
> actually existed in the table. So, it wasn't the program and it wasn't the
> sequence, just my stupidity.
>
> My sincere apologies for wasting your time.....
>
> Betsy Barker
>
>
> On Fri, 12 Nov 2004 16:37:26 -0700
>
> Betsy Barker <betsy.barker@supportservicesinc.com> wrote:
> > Hi Tom,
> > I actually select the nextval right before the insert, as you can see
> > below, so I'm not sure how it could be a programming issue.
> >
> > Also, the program fails at different places as it progresses, but it
> > seems that it is always on the 3000th value of the sequence.
> >
> >
> >             IF diffdolhospitals >= 5 THEN
> >                 checkdollar    := true;
> >                     RAISE NOTICE ''Will insert differential dollar calculation '';
> >                 SELECT nextval(''calc_id_seq'') INTO diffcalcid;
> >                 INSERT INTO calculationdifferential VALUES
> > (diffcalcid,calcid,_tempdifferentials.differentialid,
> > diffdolhospitals,diffdolrangebegin,
> > diffdolrangeend,diffdolmode,diffdolfiftieth,diffdolmean,
> > diffdoltwentyfifth,diffdolfiftieth,diffdolseventyfifth,checkdollar); END
> > IF;
> >
> >             -- INSERT PERCENTAGE  DIFFERENTIAL CALCULATION
> >             IF diffpcthospitals >= 5 THEN
> >                 checkdollar    := false;
> >                     RAISE NOTICE ''Will insert differential percentage calculation '';
> >                 SELECT nextval(''calc_id_seq'') INTO diffcalcid;
> >                 INSERT INTO calculationdifferential VALUES
> > (diffcalcid,calcid,_tempdifferentials.differentialid,
> > diffpcthospitals,diffpctrangebegin,
> > diffpctrangeend,diffpctmode,diffpctfiftieth,diffpctmean,
> > diffpcttwentyfifth,diffpctfiftieth,diffpctseventyfifth,checkdollar); END
> > IF;
> >
> >
> >
> > However, do you think it might have something to do with the fact that I
> > run the program over and over again, after these failures, and there is a
> > transaction issue or something like that?
> >
> > The reason I have the cache at 1000 is that I will easily use 1000 values
> > in one run. Do you recommend I decrease that value? Because I can.
> >
> > Thanks,
> > Betsy
> >
> >
> > On Fri, 12 Nov 2004 17:25:58 -0500
> >
> > Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > > Betsy Barker <betsy.barker@supportservicesinc.com> writes:
> > > > I am using PostgreSQL 7.3.4 and have noticed something odd while
> > > > using a sequence. After using 3000 values, I seem to be getting a
> > > > duplicate.
> > >
> > > I don't think I'm going to believe that without seeing a complete test
> > > case.  It seems much more likely that there's a bug in your program.
> > >
> > > The cache_value setting of 1000 seems awfully high; that would very
> > > likely cause odd behavior in terms of values being skipped or not being
> > > handed out in sequence.  But I've not heard of any problems that could
> > > cause generation of duplicates (at least not since 7.2.1, and even that
> > > bug only surfaced during a database crash).
> > >
> > >             regards, tom lane
> >
> > --
> > Betsy Barker
> > IT Manager
> > Support Services, Inc
> > (720)489-1630 X 38
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 7: don't forget to increase your free space map settings
>
> --
> Betsy Barker
> IT Manager
> Support Services, Inc
> (720)489-1630 X 38
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend

--
Quote: 42
"You know how Congress is. They'll vote for anything if the thing they
 vote for will turn around and vote for them. Politics ain't nothing but
 reciprocity."

 --Will Rogers

 Work: 1-336-372-6812
 Cell: 1-336-363-4719
email: terry@esc1.com