Thread: BUG #16554: Consistent sequence gaps occuring next day
The following bug has been logged on the website: Bug reference: 16554 Logged by: Amit Ahuja Email address: toamitahuja@yahoo.co.in PostgreSQL version: 10.7 Operating system: Linux GNU Description: I am currently working on the following postgres version hosted on RDS/Aurora - PostgreSQL 10.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.9.3, 64-bit I work as DB modeller and have created several sequences for the existing tables. The sequences have been created with generic 'create sequence seq_name' sql statement and default parameters remain the same (cache is 1 , start val is 1 etc). The application/db are hosted on amazon AWS. It has been observed that the sequences are starting with a gap of around 30 ( more or less) the next day. The sequences are generated sequentially (continuous with no gaps) on/within the same day. However once the application/server is stopped and restarted the next day , the sequence starts with a gap ( mostly around 30 or may be 10 , 20 etc). Even though the default cache value of these sequences is 1 , I am not sure why are these gaps occurring the next day and how can I resolve this . Kindly suggest . Regards, Amit
On Sat, Jul 25, 2020 at 3:32 AM PG Bug reporting form <noreply@postgresql.org> wrote:
The following bug has been logged on the website:
Bug reference: 16554
Logged by: Amit Ahuja
Email address: toamitahuja@yahoo.co.in
PostgreSQL version: 10.7
Operating system: Linux GNU
Description:
I am currently working on the following postgres version hosted on
RDS/Aurora -
PostgreSQL 10.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.9.3, 64-bit
I work as DB modeller and have created several sequences for the existing
tables.
The sequences have been created with generic 'create sequence seq_name' sql
statement and default parameters remain the same (cache is 1 , start val is
1 etc).
The application/db are hosted on amazon AWS.
It has been observed that the sequences are starting with a gap of around 30
( more or less) the next day. The sequences are generated sequentially
(continuous with no gaps) on/within the same day. However once the
application/server is stopped and restarted the next day , the sequence
starts with a gap ( mostly around 30 or may be 10 , 20 etc). Even though the
default cache value of these sequences is 1 , I am not sure why are these
gaps occurring the next day and how can I resolve this . Kindly suggest .
Gaps occurring in a sequence is something that can happen for various reasons so this isn't a bug.
If you do want to know more I suggest you send your question to the -general list with some additional detail about how your application/server stops and is restarted and generally how it uses these sequences. Clarifying whether its just client software that stops or the database server itself would be needed too.
David J.
PG Bug reporting form <noreply@postgresql.org> writes:
> It has been observed that the sequences are starting with a gap of around 30
> ( more or less) the next day. The sequences are generated sequentially
> (continuous with no gaps) on/within the same day. However once the
> application/server is stopped and restarted the next day , the sequence
> starts with a gap ( mostly around 30 or may be 10 , 20 etc).
This is expected behavior, as a result of optimization to reduce the
amount of WAL traffic generated by nextval calls. The optimization
is not user-adjustable (cf. SEQ_LOG_VALS in sequence.c).
It's not particularly worth doing something about it, because if your
application requires a gapless sequence, you cannot use Postgres
sequence objects to get that anyway. Transactions that roll back
after executing a nextval() will also cause gaps.
regards, tom lane