I have a table where id is primary key and squence. In transaction I call sequence
select NEXTVAL(next_cons_id) as next_cons_id
then create a new record where field id = next_cons_id got from code bellow.
DDL sequence
CREATE SEQUENCE IF NOT EXISTS next_cons_id START WITH 1;
In most cases it work well. Having done a small study I found some patterns.
Id, ... gap ..., nextId
606401,605557,
604556,605401,
603555,604401,
602594,603401,
601594,602401
Ids with dates:
601594 2019-10-10 00:04:54
602401 2019-10-10 01:36:33
602594 2019-10-10 21:46:43
603401 2019-10-11 01:19:49
603555 2019-10-11 21:41:26
604401 2019-10-12 00:30:09
604556 2019-10-13 00:15:32
605401 2019-10-13 03:51:42
605557 2019-10-13 22:42:22
606401 2019-10-14 02:14:28
And after I did create console script that make insert by one record per second in test table:
237343 2019-10-14 00:31:03 2019-10-14 00:31:03
237344 2019-10-14 00:31:03 2019-10-14 00:31:03
....gap...
238000 2019-10-14 00:31:04 2019-10-14 00:31:04
It table use only test script.
But it time we can see some problem:
There were no network problems at this point
In monitoring of zabbix rollback of transactions not found.
Database has one replication server.
Server version: 10.3.13
Do you have any ideas to fix this?
UPD:
I remove the sequence and recreate this table with
id primary key autouncrement
but this case has been repeated.