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 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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Betsy Barker <betsy(dot)barker(at)supportservicesinc(dot)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