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