pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feed From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: val.janeiko@gmail.com
Subject: BUG #16430: Sequence with cache > 1 makes it increment by number specified as cache
Date: Tue, 12 May 2020 14:47:04 +0000
Message-ID: <16430-f03b90a57994b00f@postgresql.org> (raw )
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.
view thread (6+ messages) latest in thread
Message-ID: <16430-f03b90a57994b00f@postgresql.org>
Permalink: ../16430-f03b90a57994b00f@postgresql.org/
Also on: postgresql.org/message-id/16430-f03b90a57994b00f@postgresql.org
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, val.janeiko@gmail.com
Subject: Re: BUG #16430: Sequence with cache > 1 makes it increment by number specified as cache
In-Reply-To: <16430-f03b90a57994b00f@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox