Re: problem with sequence PostgreSQL 7.3.4

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