PostgreSQL sequences nextval() strange behaviour

Question

I was testing postgresql sequences (using http://sqlfiddle.com with PostgreSQL 9.3 I dont have a PostgreSQL setup locally at the moment), but I was seeing strange behaviour.

Create a sequence

CREATE SEQUENCE counter_seq;
ALTER SEQUENCE counter_seq RESTART 9223372036854775805;

Then select the next value fromt the sequence:

SELECT nextval('counter_seq');

The result was 9223372036854776000 which is outside the range of BIGINT!?!?

The sequence itself seemed to update properly (it would fail after a couple of selects with ERROR: nextval: reached maximum value of sequence "counter_seq" (9223372036854775807)) but the result of nextval was incorrect when it did run.

I then tried setting the sequence to much lower:

ALTER SEQUENCE counter_seq RESTART 5223372036854775805;
SELECT nextval('counter_seq');

But the result was:

5223372036854776000

I couldn't get reliable behaviour until I dropped the sequence value to 5000000000000000.

Is this a postgresql bug or sqlfiddle?

Possibly, but I did test on sqlfiddle with different sequence name and it gave the same exact result. So I suppose it is safe to assume something is broken on their side. — Łukasz Kamiński
– Łukasz Kamiński, Commented Jun 2, 2017 at 12:45
counter_seq seems too common name - If I'm not mistaken - users share same schema there, so I think people are just rolling your sequence ahead — Vao Tsun
– Vao Tsun, Commented Jun 2, 2017 at 12:50
I have used counter_seq_s052305migdsgds and it gives wrong result as well: sqlfiddle.com/#!15/025825/1/0 — Łukasz Kamiński
– Łukasz Kamiński, Commented Jun 2, 2017 at 12:54
yes - and I just recalled that it was rexter to share schema, not sqlfiddle — Vao Tsun
– Vao Tsun, Commented Jun 2, 2017 at 12:55

Łukasz Kamiński · Accepted Answer · 2017-06-02 12:41:44Z

1

Looks like either SQLFiddle error or pgsql 9.3 issue. I'm not getting the same result on my local 9.6 nor does it give such error on http://rextester.com/COJS32891

answered Jun 2, 2017 at 12:41

Łukasz Kamiński

6,0401 gold badge26 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Vao Tsun Over a year ago

not reproducing on PostgreSQL 9.3.14 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit

Neilos Over a year ago

@ŁukaszKamiński looks like I'll be using rextester from now on

Neilos Over a year ago

Yes it looks like an sqlfiddle bug with displaying large numbers, I tried a different example: inserting 9223372036854775805 into a BIGINT field, when you read it back it is incorrect.

Collectives™ on Stack Overflow

PostgreSQL sequences nextval() strange behaviour

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related