Re: [GENERAL] Serial not so unique?
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 [EMAIL PROTECTED] so that your > message can get through to the mailing list cleanly > -- Michael Samuel Tech Guy [EMAIL PROTECTED] =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= 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 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Serial not so unique?
Stephen Robert Norris <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Serial not so unique?
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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] Serial not so unique?
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
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Serial not so unique?
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
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
Re: [GENERAL] Serial not so unique?
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
