[GENERAL] Re: Serial not so unique?
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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go 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] COPY and triggers
"Oliver Elphick" <[EMAIL PROTECTED]> writes: > I've been reading Great Bridge's document on Administration and Tuning > (PDF document under http://www.greatbridge.com/product/software.php) > and came across this statement on page 27: > "When using the COPY command to load data into a Great Bridge PostgreSQL > database, the triggers and constraints on tables are disabled." > This is contrary to what the man page on COPY says and to my experience. > Is this statement wrong? It's wrong. COPY does not fire rules, but it does fire triggers and constraints. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
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
Re: [GENERAL] COPY and triggers
Oliver Elphick writes: > "When using the COPY command to load data into a Great Bridge PostgreSQL > database, the triggers and constraints on tables are disabled." Perhaps that's a feature of Great Bridge PostgreSQL, but it's not a feature of Plain Old PostgreSQL. ;-) (It would be correct to say that rules are disabled.) -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
