Possible Identity Jump with Cache Greater than 1?-CodePudding

I have a table having an identity bigint cache 10

create table myschema.mytable
(
xid bigint NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 minvalue -9223372036854775808 start -9223372036854775808 MAXVALUE 9223372036854775807 CACHE 10 ),
x1 text, xtimestamp ...
)

create function myshema.myfun(...)
returns table(...)
language 'plpgsql'
cost 100
volatile security definer
as $$
declare vxid bigint;
begin
  insert into myschema.mytable(x1) values(...) 
  returning xid into vxid;
  return query select vxid;
end;
$$

Using pg-promise at Node API:

const pgp = require('pg-promise')();    // () = taking default initOptions
exports.db = pgp(
    {
        user: process.env.PGuser,
        host: process.env.PGhost,
        database: process.env.PGdatabase,
        password: process.env.PGpassword,
        port: process.env.PGport,
        max: 20,                     // default
        idleTimeoutMillis: 10000        // default
    }
);

I noticed new records are all with a gap of 10, select xid, xtimestamp from mytable order by 1 desc limit 8:

MS SQL has an identity Jump issue when it's restarted, but my Postgre, API and frontend are all running during the period.

It's not replicable if do the followings in pgAdmin, possibly it's always in the same session:

create table public.testidentitycache(xid bigint NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 minvalue -9223372036854775808 start -9223372036854775808 MAXVALUE 9223372036854775807 CACHE 10 ), xtxt text)

insert into public.testidentitycache(xtxt) values('this is 6')
insert into public.testidentitycache(xtxt) values('this is 5')
...
select * from public.testidentitycache

pg version 12

CodePudding user response：

In the Identity sequence parameters, you have instructed to use a CACHE 10, meaning that when calling the sequence, the next 10 values are fetched and put in memory. If the same connection calls the same sequence, a value from memory is returned... but if a different connection is used (like when using a pool), the next value is fetched from the sequence again (and again puts the next 10 in cache)

You can either live with it or use a cache of 1 (default)