Thread: BUG #16430: Sequence with cache > 1 makes it increment by number specified as cache
BUG #16430: Sequence with cache > 1 makes it increment by number specified as cache
From
PG Bug reporting form
Date:
The following bug has been logged on the website:
Bug reference: 16430
Logged by: Valentinas
Email address: val.janeiko@gmail.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.
CREATE SEQUENCE resource_surrogate_id_uniquifier_sequence
AS integer
START WITH 0
INCREMENT BY 1
MINVALUE 0
MAXVALUE 79999
CYCLE
CACHE 5;
CREATE FUNCTION upsert_resource(
_base_resource_surrogate_id bigint,
_resource_type_id smallint,
_resource_id varchar(64),
_etag integer,
_allow_create boolean,
_is_deleted boolean,
_keep_history boolean,
_request_method varchar(10),
_raw_resource bytea,
_resource_write_claims resource_write_claim_table_type_1 ARRAY,
_compartment_assignments compartment_assignment_table_type_1 ARRAY,
_reference_search_params reference_search_param_table_type_1 ARRAY,
_token_search_params token_search_param_table_type_1 ARRAY,
_token_text_search_params token_text_table_type_1 ARRAY,
_string_search_params string_search_param_table_type_1 ARRAY,
_number_search_params number_search_param_table_type_1 ARRAY,
_quantity_search_params quantity_search_param_table_type_1 ARRAY,
_uri_search_params uri_search_param_table_type_1 ARRAY,
_date_time_search_parms date_time_search_param_table_type_1 ARRAY,
_reference_token_composite_search_params
reference_token_composite_search_param_table_type_1 ARRAY,
_token_token_composite_search_params
token_token_composite_search_param_table_type_1 ARRAY,
_token_date_time_composite_search_params
token_date_time_composite_search_param_table_type_1 ARRAY,
_token_quantity_composite_search_params
token_quantity_composite_search_param_table_type_1 ARRAY,
_token_string_composite_search_params
token_string_composite_search_param_table_type_1 ARRAY,
_token_number_number_composite_search_params
token_number_number_composite_search_param_table_type_1 ARRAY)
RETURNS integer
AS $$
<<body>>
DECLARE
_version integer; -- the version of the resource being written
BEGIN
_version = nextval('resource_surrogate_id_uniquifier_sequence');
RETURN _version;
END;
$$ LANGUAGE plpgsql;
The bellow query made with Npgsql 3 times returns 0, 5, 10 instead of 0, 1,
2:
SELECT upsert_resource(@baseResourceSurrogateId, @resourceTypeId,
@resourceId, @eTag, @allowCreate, @isDeleted, @keepHistory, @requestMethod,
@rawResource, @resourceWriteClaims, @compartmentAssignments,
@referenceSearchParams, @tokenSearchParams, @tokenTextSearchParams,
@stringSearchParams, @numberSearchParams, @quantitySearchParams,
@uriSearchParams, @dateTimeSearchParms,
@referenceTokenCompositeSearchParams, @tokenTokenCompositeSearchParams,
@tokenDateTimeCompositeSearchParams, @tokenQuantityCompositeSearchParams,
@tokenStringCompositeSearchParams,
@tokenNumberNumberCompositeSearchParams)
Additionally if CACHE is set to > 80000 (e.g. 100000) the query always
returns 0.
Re: BUG #16430: Sequence with cache > 1 makes it increment by number specified as cache
From
Tom Lane
Date:
PG Bug reporting form <noreply@postgresql.org> 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
Re: BUG #16430: Sequence with cache > 1 makes it increment by number specified as cache
From
Sergei Kornilov
Date:
Hello
In one DB connection:
=> CREATE SEQUENCE resource_surrogate_id_uniquifier_sequence
AS integer
START WITH 0
INCREMENT BY 1
MINVALUE 0
MAXVALUE 79999
CYCLE
CACHE 5;
CREATE SEQUENCE
=> select nextval('resource_surrogate_id_uniquifier_sequence');
nextval
---------
0
=> select nextval('resource_surrogate_id_uniquifier_sequence');
nextval
---------
1
=> select nextval('resource_surrogate_id_uniquifier_sequence');
nextval
---------
2
=> select nextval('resource_surrogate_id_uniquifier_sequence');
nextval
---------
3
In another connection:
=> select nextval('resource_surrogate_id_uniquifier_sequence');
nextval
---------
5
=> select nextval('resource_surrogate_id_uniquifier_sequence');
nextval
---------
6
All as expected. This is how the sequence cache option works - we preallocate values for further use in current
connection.
regards, Sergei
Re: BUG #16430: Sequence with cache > 1 makes it increment by numberspecified as cache
From
"David G. Johnston"
Date:
On Tuesday, May 12, 2020, PG Bug reporting form <noreply@postgresql.org> wrote:
The following bug has been logged on the website:
Bug reference: 16430
Logged by: Valentinas
Email address: val.janeiko@gmail.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.
Re: BUG #16430: Sequence with cache > 1 makes it increment by numberspecified as cache
From
Jeff Janes
Date:
On Tue, May 12, 2020 at 11:33 AM PG Bug reporting form <noreply@postgresql.org> wrote:
The following bug has been logged on the website:
MINVALUE 0
MAXVALUE 79999
CYCLE
...
Additionally if CACHE is set to > 80000 (e.g. 100000) the query always
returns 0.
Since you told it the maxval was 79999 and to CYCLE, were you expecting something else to happen? What do you think the desired behavior would be?
Cheers,
Jeff
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.g.johnston@gmail.com>
Sent: 12 May 2020 16:48
To: val.janeiko@gmail.com; pgsql-bugs@lists.postgresql.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@postgresql.org> wrote:
The following bug has been logged on the website:
Bug reference: 16430
Logged by: Valentinas
Email address: val.janeiko@gmail.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.