Обсуждение: problem with sequence PostgreSQL 7.3.4
I am using PostgreSQL 7.3.4 and have noticed something odd while using a sequence. After using 3000 values, I seem to begetting a duplicate. I'm running a stored procedure that uses this sequence and it fails with a : WARNING: Error occurred while executing PL/pgSQL function calc_sum_facilities WARNING: line 678 at SQL statement ERROR: Cannot insert a duplicate key into unique index calculationdifferential_pkey I ran the program 4 times and checked the value of the sequence after each failure. There is a 3000 difference. Look at thelast_value in each case. ssi=> select * from calc_id_seq; sequence_name | last_value | increment_by | max_value | min_value | cache_value | log_cnt | is_cycled | is_called ---------------+------------+--------------+---------------------+-----------+-------------+---------+-----------+----------- calc_id_seq | 4527999 | 1 | 9223372036854775807 | 1 | 1000 | 32 | f | t sequence_name | last_value | increment_by | max_value | min_valu ---------------+------------+--------------+---------------------+--------- calc_id_seq | 4530999 | 1 | 9223372036854775807 | ssi=> select * from calc_id_seq; sequence_name | last_value | increment_by | max_value | min_valu ---------------+------------+--------------+---------------------+--------- calc_id_seq | 4533999 | 1 | 9223372036854775807 | (1 row) ssi=> select * from calc_id_seq; sequence_name | last_value | increment_by | max_value | min_value | cache_value | log_cnt | is_cycled | is_called ---------------+------------+--------------+---------------------+-----------+-------------+---------+-----------+----------- calc_id_seq | 4536999 | 1 | 9223372036854775807 | 1 | 1000 | 32 | f | t (1 row) Does anyone know what is going on , or how I can get the sequence to not act this way? Thank you, -- Betsy Barker IT Manager Support Services, Inc (720)489-1630 X 38
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
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
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
Not a waste of time. Just a reminder that we all make mistakes. You should
hear some of mine ;o)
On Tuesday 16 November 2004 12:24 pm, Betsy Barker saith:
> 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.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 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@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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
--
Quote: 42
"You know how Congress is. They'll vote for anything if the thing they
vote for will turn around and vote for them. Politics ain't nothing but
reciprocity."
--Will Rogers
Work: 1-336-372-6812
Cell: 1-336-363-4719
email: terry@esc1.com