Thread: Serial not so unique?
We have a table here with a serial value in it.
We have sets of test data that we run through a processor that changes
a fairly large set of tables in deterministic ways.
Sometimes (about 20%, it seems) with several of the data sets, we
get an error trying to insert rows into the table with the serial in it.
On investigation, it seems that the serial number has got to 101, then
set itself back to 4, causing nextval to return 5, and there are already
entries from 1-101.
Now, we use the serial as the primary key, and we never explicitly set it.
Has anyone seen anything like this? I can work around it by generating
a serial number within the application, but that's not ideal.
Is this another RTFM question?
Stephen
> Sometimes (about 20%, it seems) with several of the data sets, we
> get an error trying to insert rows into the table with the serial in it.
> On investigation, it seems that the serial number has got to 101, then
> set itself back to 4, causing nextval to return 5, and there are already
> entries from 1-101.
>
> Now, we use the serial as the primary key, and we never explicitly set it.
>
> Has anyone seen anything like this? I can work around it by generating
> a serial number within the application, but that's not ideal.
Odd problem. What do you get if you run:
select * from name_of_this_troublesome_sequence;
particularly for increment_by, max_value, min_value, and is_cycled?
-- Joe
Stephen Robert Norris <srn@commsecure.com.au> writes:
> On investigation, it seems that the serial number has got to 101, then
> set itself back to 4, causing nextval to return 5, and there are already
> entries from 1-101.
Never heard of such misbehavior before. What PG version are you
running? Any chance of providing a reproducible example?
> Has anyone seen anything like this? I can work around it by generating
> a serial number within the application, but that's not ideal.
Frankly, I suspect that the problem *is* in your application. Sequences
are completely reliable in everyone else's experience... they've got
documented shortcomings like leaving "holes" in their output, but they
don't generate the same nextval() multiple times.
regards, tom lane
On Sat, Aug 18, 2001 at 06:17:17AM -0700, Joe Conway wrote:
> > Sometimes (about 20%, it seems) with several of the data sets, we
> > get an error trying to insert rows into the table with the serial in it.
> > On investigation, it seems that the serial number has got to 101, then
> > set itself back to 4, causing nextval to return 5, and there are already
> > entries from 1-101.
> >
> > Now, we use the serial as the primary key, and we never explicitly set it.
> >
> > Has anyone seen anything like this? I can work around it by generating
> > a serial number within the application, but that's not ideal.
>
> Odd problem. What do you get if you run:
> select * from name_of_this_troublesome_sequence;
> particularly for increment_by, max_value, min_value, and is_cycled?
>
> -- Joe
1, 2^31 -1, 1, f
Stephen
On Sat, Aug 18, 2001 at 10:40:33AM -0400, Tom Lane wrote:
> Stephen Robert Norris <srn@commsecure.com.au> writes:
> > On investigation, it seems that the serial number has got to 101, then
> > set itself back to 4, causing nextval to return 5, and there are already
> > entries from 1-101.
>
> Never heard of such misbehavior before. What PG version are you
> running? Any chance of providing a reproducible example?
>
> > Has anyone seen anything like this? I can work around it by generating
> > a serial number within the application, but that's not ideal.
>
> Frankly, I suspect that the problem *is* in your application. Sequences
> are completely reliable in everyone else's experience... they've got
> documented shortcomings like leaving "holes" in their output, but they
> don't generate the same nextval() multiple times.
>
> regards, tom lane
It wouldn't surprise me if it was the application's fault, but what should
I look for? Is setval() the only way to effect the sequence?
Stephen
Stephen Robert Norris <srn@commsecure.com.au> writes:
> Is setval() the only way to effect the sequence?
Yes, AFAIR. (If you can find another way, it's probably a bug...)
regards, tom lane
On Sat, Aug 18, 2001 at 06:32:12PM -0400, Tom Lane wrote:
> Stephen Robert Norris <srn@commsecure.com.au> writes:
> > Is setval() the only way to effect the sequence?
>
> Yes, AFAIR. (If you can find another way, it's probably a bug...)
>
> regards, tom lane
Hmm, the string setvar doesn't occur anywhere in the code.
Stephen
> > > Sometimes (about 20%, it seems) with several of the data sets, we > > > get an error trying to insert rows into the table with the serial in it. > > > On investigation, it seems that the serial number has got to 101, then > > > set itself back to 4, causing nextval to return 5, and there are already > > > entries from 1-101. > > > > > > Now, we use the serial as the primary key, and we never explicitly set it. > > > > > > Has anyone seen anything like this? I can work around it by generating > > > a serial number within the application, but that's not ideal. > > > > Odd problem. What do you get if you run: > > select * from name_of_this_troublesome_sequence; > > particularly for increment_by, max_value, min_value, and is_cycled? > > > > -- Joe > > 1, 2^31 -1, 1, f > > Stephen Nothing stands out there. You might try to drop and recreate the sequence if you haven't already. Or, a longshot, but . . . you might check the table definition to be sure it's using the sequence that you think it is. -- Joe
On Sat, Aug 18, 2001 at 03:49:10PM -0700, Joe Conway wrote:
> > > > Sometimes (about 20%, it seems) with several of the data sets, we
> > > > get an error trying to insert rows into the table with the serial in
> it.
> > > > On investigation, it seems that the serial number has got to 101, then
> > > > set itself back to 4, causing nextval to return 5, and there are
> already
> > > > entries from 1-101.
> > > >
> > > > Now, we use the serial as the primary key, and we never explicitly set
> it.
> > > >
> > > > Has anyone seen anything like this? I can work around it by generating
> > > > a serial number within the application, but that's not ideal.
> > >
> > > Odd problem. What do you get if you run:
> > > select * from name_of_this_troublesome_sequence;
> > > particularly for increment_by, max_value, min_value, and is_cycled?
> > >
> > > -- Joe
> >
> > 1, 2^31 -1, 1, f
> >
> > Stephen
>
> Nothing stands out there. You might try to drop and recreate the sequence if
> you haven't already. Or, a longshot, but . . . you might check the table
> definition to be sure it's using the sequence that you think it is.
>
> -- Joe
Recreating the sequence solves the problem, of course. So does setval(102).
My problem is that it got into this state originally. The test case that
demonstrates it sometimes takes about 1.5 hours to run, and I have only got
about 24 hours left, so I may have to stop investigating and make the
application generate the id instead.
Stephen
Hi Stephen,
That's weird behaviour. If you'd manually created the sequence like
this :
CREATE SEQUENCE foo_seq MINVALUE 4 MAXVALUE 101 CYCLE
Then referenced it as the default like this :
CREATE TABLE bar (idnum integer UNIQUE DEFAULT nextval('foo_seq') NOT
NULL, otherstuff varchar(20));
That would explain the wrapping around behaviour, but not when the field
is a SERIAL type.
:(
+ Justin Clift
Stephen Robert Norris wrote:
>
> On Sat, Aug 18, 2001 at 03:49:10PM -0700, Joe Conway wrote:
> > > > > Sometimes (about 20%, it seems) with several of the data sets, we
> > > > > get an error trying to insert rows into the table with the serial in
> > it.
> > > > > On investigation, it seems that the serial number has got to 101, then
> > > > > set itself back to 4, causing nextval to return 5, and there are
> > already
> > > > > entries from 1-101.
> > > > >
> > > > > Now, we use the serial as the primary key, and we never explicitly set
> > it.
> > > > >
> > > > > Has anyone seen anything like this? I can work around it by generating
> > > > > a serial number within the application, but that's not ideal.
> > > >
> > > > Odd problem. What do you get if you run:
> > > > select * from name_of_this_troublesome_sequence;
> > > > particularly for increment_by, max_value, min_value, and is_cycled?
> > > >
> > > > -- Joe
> > >
> > > 1, 2^31 -1, 1, f
> > >
> > > Stephen
> >
> > Nothing stands out there. You might try to drop and recreate the sequence if
> > you haven't already. Or, a longshot, but . . . you might check the table
> > definition to be sure it's using the sequence that you think it is.
> >
> > -- Joe
>
> Recreating the sequence solves the problem, of course. So does setval(102).
> My problem is that it got into this state originally. The test case that
> demonstrates it sometimes takes about 1.5 hours to run, and I have only got
> about 24 hours left, so I may have to stop investigating and make the
> application generate the id instead.
>
> Stephen
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://www.postgresql.org/search.mpl
--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi
On Sun, Aug 19, 2001 at 12:42:36PM +1000, Justin Clift wrote:
> Hi Stephen,
>
> That's weird behaviour. If you'd manually created the sequence like
> this :
>
> CREATE SEQUENCE foo_seq MINVALUE 4 MAXVALUE 101 CYCLE
>
> Then referenced it as the default like this :
>
> CREATE TABLE bar (idnum integer UNIQUE DEFAULT nextval('foo_seq') NOT
> NULL, otherstuff varchar(20));
>
> That would explain the wrapping around behaviour, but not when the field
> is a SERIAL type.
>
> :(
>
> + Justin Clift
Indeed. What's worse is that the first time around it went from 1 -> 101.
Stephen
On Sun, Aug 19, 2001 at 01:23:13PM +1000, Justin Clift wrote:
> Hmmm...
>
> Well, that would be a
>
> CREATE SEQUENCE foo_seq START 1 MINVALUE 4 MAXVALUE 101 CYCLE
>
> Still, that's not helpful. :(
>
> Is there any chance that the application created the sequence, or that
> someone created it manually?
Nope, the table was created fresh from its schema.
> Then again, you showed us the values the sequence was using, and they
> definitely weren't like the ones the sequence up there would create.
>
> Out of curiosity, which version of PostgreSQL are you using?
> 7.1.2/7.1.3?
7.1.2 on RH 7.1.
>
> Regards and best wishes,
>
> Justin Clift
Stephen
On Sun, Aug 19, 2001 at 10:02:02AM +0800, Lincoln Yeoh wrote:
> At 09:18 AM 8/19/01 +1000, Stephen Robert Norris wrote:
> >Recreating the sequence solves the problem, of course. So does setval(102).
> >My problem is that it got into this state originally. The test case that
> >demonstrates it sometimes takes about 1.5 hours to run, and I have only got
>
> Maybe somewhere, something is using nextval of the wrong sequence?
>
> Did you do a search for setval (not setvar) in your code?
>
> Or grep for the sequence name.
>
> I suspect it's the app, but maybe you've just found a bug in PG.
The field in question is defined as a serial; until I started looking
at this I didn't even _know_ what the sequence was called.
There are no other sequences created (no explicit ones and no
other serial values).
Stephen
Stephen Robert Norris <srn@commsecure.com.au> writes:
> ... The test case that
> demonstrates it sometimes takes about 1.5 hours to run,
If you have a reproducible test case, let's see it. Bulk isn't as
important as being able to get the behavior under a microscope...
regards, tom lane
I encountered the same problem. The machine it happenned on _may_ have had a power outage before I noticed the problem. (I know it had one a while back when on the workbench, but I can't remember if that was before or after I'd setup the tables) BTW, this was a debian box, running the 7.1release-4 package. On Sat, Aug 18, 2001 at 03:55:28PM +1000, Stephen Robert Norris wrote: > We have a table here with a serial value in it. > > We have sets of test data that we run through a processor that changes > a fairly large set of tables in deterministic ways. > > Sometimes (about 20%, it seems) with several of the data sets, we > get an error trying to insert rows into the table with the serial in it. > On investigation, it seems that the serial number has got to 101, then > set itself back to 4, causing nextval to return 5, and there are already > entries from 1-101. > > Now, we use the serial as the primary key, and we never explicitly set it. > > Has anyone seen anything like this? I can work around it by generating > a serial number within the application, but that's not ideal. > > Is this another RTFM question? > > Stephen > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > -- Michael Samuel Tech Guy michael@hyperlink.net.au =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Hyperlink, a division of The Swish Group Ltd ACN 085 545 973 Level 6, 257 Collins St, Melbourne, VIC 3004 Phone 1300 368 638 Fax +61 3 9211 5406 http://www.hyperlink.net.au =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=