Re: Slow alter sequence with PG10.1

Hello all,

I think a good alternative was found and seems to be working fine. I really
do appreciate all the help and feedback.
Many thanks.

Regards,
Michael

Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> schrieb am Di., 23. Jan. 2018 um
02:12 Uhr:

> On 01/22/2018 02:47 PM, Michael Krüger wrote:
> > Hello all,
> >
> > after changing the function to this:
> >
> > 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;
> > seq_idx int8;
> > BEGIN
> > SELECT oid::int4 INTO lock_id FROM pg_class WHERE relname =
> > split_part(use_seqname, '.', 2);
> > perform pg_advisory_lock(lock_id);
> >
> > seq_idx :=nextval(use_seqname);
> > perform setval(use_seqname, seq_idx + use_increment - 1, 't');
> >
> > perform pg_advisory_unlock(lock_id);
> > return seq_idx;
> > END;
> > $BODY$;
> >
> > I do get a total execution time of Time: 5922,428 ms (00:05,922) - much
> > better than before.
> >
> > Is there any drawback to use setval compared to the ALTER SEQUENCE which
> > I have used before? If not, then this could be the work around to go
> > with as it has a similar performance to the original function.
> >
> > I guess - the reason I'm a bit disappointed from the new behavior is
> > that we have used Postgresql for more than 10 years and it has never let
> > us down. We have been able to improve our product with every new release
>
> Well the nature of major version releases is that they can break
> backwards compatibility. This is one of the reasons there is 5 year
> community support on versions, time to develop a migration plan. I have
> been caught by changes, before e.g. the 8.3 change in casting rules, a
> later change that made plpythonu use Python rules for truthfulness
> instead of SQL, etc. You seem to have had a run of good luck. Going
> forward I would assume a major release will contain breaking changes and
> test thoroughly.
>
> > of Postgres. This is the first time for me that a new release of
> > Postgres caused some severe headaches among our customers.
> > If you all agree that this changed function should be equivalent to the
> > original one, then its at least an easy fix.
> >
> > Thank you all for your fast responses.
> >
> > Regards,
> > Michael
> >
>
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>