Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jYX3T-0000tV-53 for pgsql-bugs@arkaria.postgresql.org; Tue, 12 May 2020 15:42:51 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1jYX3S-0006jx-42 for pgsql-bugs@arkaria.postgresql.org; Tue, 12 May 2020 15:42:50 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jYX3R-0006jp-Ua for pgsql-bugs@lists.postgresql.org; Tue, 12 May 2020 15:42:49 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jYX3P-0000XF-Pa for pgsql-bugs@lists.postgresql.org; Tue, 12 May 2020 15:42:48 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.4/8.14.4) with ESMTP id 04CFgjTe022566; Tue, 12 May 2020 11:42:45 -0400 From: Tom Lane To: val.janeiko@gmail.com cc: pgsql-bugs@lists.postgresql.org Subject: Re: BUG #16430: Sequence with cache > 1 makes it increment by number specified as cache In-reply-to: <16430-f03b90a57994b00f@postgresql.org> References: <16430-f03b90a57994b00f@postgresql.org> Comments: In-reply-to PG Bug reporting form message dated "Tue, 12 May 2020 14:47:04 -0000" MIME-Version: 1.0 Content-Type: text/plain; charset="UTF-8" Content-ID: <22564.1589298165.1@sss.pgh.pa.us> Content-Transfer-Encoding: 8bit Date: Tue, 12 May 2020 11:42:45 -0400 Message-ID: <22565.1589298165@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk PG Bug reporting form writes: > When using Sequence inside a plpgsql function called using SELECT func() > using Npgsql library a sequence is incremented by the value specified in > CACHE. If you're reconnecting for each query, this is the expected result. Per the manual (see Notes under CREATE SEQUENCE): 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. If you don't like it, don't use cache settings greater than one. regards, tom lane