postgres sequence not incrementing

Question

I created a PostgreSQL sequence on a PostgreSQL 10.7 dB called markush_seq I read from the seq

select nextval('markush_seq’)` )

using a java web service: When I run the web service on eclipse (using java 1.8.161) or call the sequence direct from SQL developer, it works fine and the sequence increments by 1 each time eg:

http://localhost:8086/wipdbws/read-markush-seq

21767823690

21767823691

21767823692

However when I run the webservice on AWS (which uses java 1.8.252) and read from the seq using:

https://aws-location/wipdbws/read-markush-seq

I get the sequence number returned as eg:

21767823692

21767823702

21767823693

21767823703

21767823694

21767823704

The sequence in AWS appears to be a combination of 2 incrementing sequences, 10 apart. It’s the same java code, the only thing that has changed is:

The location of the webservice

a. AWS – USWEST

b. Eclipse - London
The java version:

a. 1.8.161 in London

b. 1.8.252 in US WEST

The seq details are:

SELECT * FROM information_schema.sequences
where sequence_name='markush_seq';

select * from pg_sequences where sequencename='markush_seq';

Any suggestion appreciated.

Just for clarity, you meant you have two different PostgreSQL DBs, right? one locally and another in AWS. — fjsv
– fjsv, Commented Jun 3, 2020 at 9:34
Please post output of select * from pg_sequences where sequencename='markush_seq'; for each database. — pifor
– pifor, Commented Jun 3, 2020 at 9:39

pifor · Accepted Answer · 2020-06-03 09:35:05Z

2

Likely due to multiple sessions accessing the sequence and sequence cache settings.

Documentation says:

although multiple sessions are guaranteed to allocate distinct sequence values, the values might be generated out of sequence when all the sessions are considered. For example, with a cache setting of 10, session A might reserve values 1..10 and return nextval=1, then session B might reserve values 11..20 and return nextval=11 before session A has generated nextval=2. Thus, with a cache setting of one it is safe to assume that nextval values are generated sequentially; with a cache setting greater than one you should only assume that the nextval values are all distinct, not that they are generated purely sequentially. Also, last_value will reflect the latest value reserved by any session, whether or not it has yet been returned by nextval.

answered Jun 3, 2020 at 9:35

pifor

7,9722 gold badges12 silver badges18 bronze badges

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

6 Comments

DS. Over a year ago

I don't think so, it's in development , just me accessing it

pifor Over a year ago

Please post requested output in text mode because INFORMATION_SCHEMA has less information than PG system catalog.

pifor Over a year ago

Sequence hascache_size=10. Are your sure that your web service does not have several database connections ?

DS. Over a year ago

I'm not actually sure how many connetion I have. I'm using spring boot, there's nothing in application.properties so I presume it's the default connection pool size? But it's only me accessing the webService

DS. Over a year ago

That chache size of 10 looks like the culprit. I'll change it to 1 and report back

|

Collectives™ on Stack Overflow

postgres sequence not incrementing

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related