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
