Re: problem with sequence PostgreSQL 7.3.4

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