On Thu, Mar 19, 2020 at 9:12 PM Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:
On 3/19/20 7:38 PM, Michael Lewis wrote:
>
>
> On Thu, Mar 19, 2020, 5:48 PM David G. Johnston
> <david.g.johnston@xxxxxxxxx <mailto:david.g.johnston@xxxxxxxxx>> wrote:
>
> However, one other consideration with sequences: do you care that
> PostgreSQL will cache/pin (i.e., no release) every single sequence
> you touch for the lifetime of the session? (I do not think DISCARD
> matters here but I'm just guessing)
>
>
>
> Would you expand on this point or is there someplace specific in the
> documentation on this?
>
See the section starting here:
https://www.postgresql.org/docs/12/sql-createsequence.html
Notes
"Unexpected results might be obtained if a cache setting greater than
one is used for a sequence object that will be used concurrently by
multiple sessions. Each session will allocate and cache successive
sequence values during one access to the sequence object and increase
the sequence object's last_value accordingly. Then, the next cache-1
uses of nextval within that session simply return the preallocated
values without touching the sequence object. So, any numbers allocated
but not used within a session will be lost when that session ends,
resulting in “holes” in the sequence.
...
"
We will use a CACHE 1. This is because when nextval('seq') is invoked, we are hitting 3 or 4 more tables so the sequence will not be a performance blocker (compared with all the operations in the transaction).
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
