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

Tom & List,
I apologize. I found the problem and it turns out that the original unique number generator for this table was being
incrementedby 3000 and those ids actually existed in the table. So, it wasn't the program and it wasn't the sequence,
justmy 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
programmingissue. 
>
> Also, the program fails at different places as it progresses, but it seems that it is always on the 3000th value of
thesequence. 
>
>
>             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
thesefailures, 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
thatvalue? 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