RE: BUG #16430: Sequence with cache > 1 makes it increment by number specified as cache

I am not sure. I was porting MS SQL code to Postgre. (I assumed it works in MS SQL, but I haven’t checked, might be a bug)

But now that I know that this cache is not shared between connections it does not make sense to have cache more than max value (80k).

-Valentinas

From: David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
Sent: 12 May 2020 16:48
To: val(dot)janeiko(at)gmail(dot)com; pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: Re: BUG #16430: Sequence with cache > 1 makes it increment by number specified as cache

On Tuesday, May 12, 2020, PG Bug reporting form <noreply(at)postgresql(dot)org <mailto:noreply(at)postgresql(dot)org> > wrote:

The following bug has been logged on the website:

Bug reference: 16430
Logged by: Valentinas
Email address: val(dot)janeiko(at)gmail(dot)com <mailto:val(dot)janeiko(at)gmail(dot)com>
PostgreSQL version: 12.2
Operating system: Alpine (container)
Description:

When using Sequence inside a plpgsql function called using SELECT func()
using Npgsql library a sequence is incremented by the value specified in
CACHE.

That is a perfectly valid outcome per the meaning and documented usage of cache in the server.

Additionally if CACHE is set to > 80000 (e.g. 100000) the query always
returns 0.

Interesting...probably worth looking into even if a cache value that large doesn’t make much sense usually. Doing and posting some tests using just sql and psql would be a good start in isolating where the discrepancy lies.

David J.