Serial not so unique?

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 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

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

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
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=