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.klaver@aklaver.com
