Slow alter sequence with PG10.1

Dear community,

I'm using PG10.1 on CentOS Linux release 7.4.1708 (Core) after upgrading it
from PG9.6.6. My application heavily uses sequences and requires different
increments of sequence numbers, e.g. a range of 100, 1000 or 5000 numbers,
so it is not possible to set a fixed increment on a sequence that can be
used by my application.

With PG10.1 the performance has dropped seriously so that my application
becomes unusable. After investigating different aspects, I was able to
isolate the issue to be related to the sequences in Postgres 10.1.

Below shows a simple test script showing the problem:
-- 1) Create a sequence
CREATE SEQUENCE my_sequence_1 INCREMENT BY 1 MINVALUE 1 NO MAXVALUE START
WITH 1 CYCLE;

-- 2) Create a function that allows to request a number range
CREATE OR REPLACE FUNCTION multi_nextval(
use_seqname text,
use_increment integer)
RETURNS bigint
LANGUAGE 'plpgsql'
COST 100
VOLATILE
AS $BODY$
DECLARE
reply int8;
lock_id int4;
BEGIN
SELECT oid::int4 INTO lock_id FROM pg_class WHERE relname =
split_part(use_seqname, '.', 2);
perform pg_advisory_lock(lock_id);
execute 'ALTER SEQUENCE ' || use_seqname || ' INCREMENT BY ' ||
use_increment::text;
reply := nextval(use_seqname);
execute 'ALTER SEQUENCE ' || use_seqname || ' INCREMENT BY 1';
perform pg_advisory_unlock(lock_id);
return reply - use_increment + 1;
END;
$BODY$;

-- 3) Loop 20000 times and request 5000 values each time
DO $$
DECLARE
--
i_index integer;
i_value bigint;
BEGIN
FOR i_index IN select * from generate_series(1,20000,1)
LOOP
SELECT multi_nextval('my_sequence_1',5000) INTO i_value ;
if (i_index % 250 = 0) THEN
raise notice 'Loop: % - NextVal: %', i_index, i_value;
end if;
END LOOP;
END$$;

On my computer I tried this code on PG9.6.6 and it executed in roughly 3
seconds.
When running it on PG10.1 it takes over 7 minutes.

Further investigation showed that the problem is related to ALTER
SEQUENCE...

I can't believe that PG10.1 was changed that dramatically without providing
a workaround or a way to switch to the old PG9.6 performance, at least I
can't find anything in the documentation.

Is this a bug?

Thanks in advance,
Michael

--
Email: michael(at)kruegers(dot)email
Mobile: 0152 5891 8787