PostgreSQL Bugs

Collected from the PG bugs email list.

Bug ID16430
PG Version12.2
OSAlpine (container)
Opened2020-05-12 14:47:04+00
Reported byValentinas
StatusNew

Body of first available message related to this bug follows.

The following bug has been logged on the website:

Bug reference:      16430
Logged by:          Valentinas
Email address:      (redacted)
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.

Messages

DateAuthorSubject
2020-05-12 14:47:04+00PG Bug reporting formBUG #16430: Sequence with cache > 1 makes it increment by number specified as cache
2020-05-12 15:42:45+00Tom LaneRe: BUG #16430: Sequence with cache > 1 makes it increment by number specified as cache
2020-05-12 15:44:24+00Sergei KornilovRe: BUG #16430: Sequence with cache > 1 makes it increment by number specified as cache
2020-05-12 15:47:35+00"David G(dot) Johnston"Re: BUG #16430: Sequence with cache > 1 makes it increment by number specified as cache
2020-05-12 22:15:48+00Jeff JanesRe: BUG #16430: Sequence with cache > 1 makes it increment by number specified as cache
2020-05-12 22:56:23+00RE: BUG #16430: Sequence with cache > 1 makes it increment by number specified as cache