On Thu, Mar 19, 2020 at 9:12 PM Adrian Klaver <adrian.kla...@aklaver.com> wrote:
> On 3/19/20 7:38 PM, Michael Lewis wrote: > > > > > > On Thu, Mar 19, 2020, 5:48 PM David G. Johnston > > <david.g.johns...@gmail.com <mailto:david.g.johns...@gmail.com>> 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.kla...@aklaver.com >