Postgres sequence not generating sequential values

Question

I have this sequence defined in postgres 9.6.11 running in AWS RDS

DROP SEQUENCE IF EXISTS org.my_seq;
CREATE SEQUENCE IF NOT EXISTS org.my_seq
  INCREMENT 1
  MINVALUE 1
  NO MAXVALUE
  START 1
  CACHE 20;

This sequence is then invoked by Hibernate sequence generation strategy from within Spring boot application deployed within Docker container. The values generated are shown below. I am completely confused by why the values generated by this sequence are all over the place. I also noticed that every time I shut down my docker container, restart it, and test the application again, the values start to come from a whole different range. Any ideas as to why this may be happening?

-28 -27 -26 -25 -8 -7 1 2 52 53 72 92 93 94 112 113 132 133 152 172 192 193 212

Seems to be the same as your previous question Postgres 9.6.11 sequence in RDS generating negative values? Please edit it to add/remove information instead of reposting. — Bergi
– Bergi, Commented Feb 6, 2020 at 20:12
I have deleted that old question, as I was not getting any response on it. — sshekhar
– sshekhar, Commented Feb 6, 2020 at 21:01

Laurenz Albe · Accepted Answer · 2020-02-06 17:57:53Z

2

I cannot explain the negative values - perhaps someone has ignored the default values.

But the gaps are clear:

Sequences are not transactional in the way that a ROLLBACK does not reset the sequence. So any value that has been generated will be lostif the INSERT that wanted to use that value fails.

That is a feature, not a bug, because it allows fast operation.
If you use CACHE 20 performance is further improved, because now each database session gets 20 values whenever it requests a new number from the sequence. The session caches these values and doesn't have to access the sequence for the next couple of times it needs a value.

Of course these cached values die with the session, so if a database session requests a sequence value and then terminates, there will be a gap of 20 values.

This is all perfectly fine and as expected.

answered Feb 6, 2020 at 17:57

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

1 Comment

sshekhar Over a year ago

Thanks Laurenz, I read up a bit on postgres sessions in their architectural documentation. Now it makes sense why the block of ids changes when I restart my docker container. However, the negative values still do not make sense.

Collectives™ on Stack Overflow

Postgres sequence not generating sequential values

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related