Thread: Slow alter sequence with PG10.1

Slow alter sequence with PG10.1

From

Michael Krüger

Date:

22 January 2018, 21:24:29

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@kruegers.email

Mobile: 0152 5891 8787

Re: Slow alter sequence with PG10.1

From

Rakesh Kumar

Date:

23 January 2018, 00:46:38

why are you not using CACHE clause which is precisely for the purpose :

cache
The optional clause CACHE cache specifies how many sequence numbers are to be preallocated and stored in memory for
fasteraccess. The minimum value is 1 (only one value can be generated at a time, i.e., no cache), and this is also the
default.

Re: Slow alter sequence with PG10.1

From

"David G. Johnston"

Date:

23 January 2018, 00:55:55

On Mon, Jan 22, 2018 at 8:24 AM, Michael Krüger <michael@kruegers.email> wrote:

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:

[...]

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?

Without testing/confirming I'd be inclined to agree that this is a regression for an unusual usage of sequences. Work was done to make typical use cases of sequences more feature-full and it is quite possible the added effort involved hurts your specific scenario. I'd expect a hacker to eventually pick this up, confirm the observation, and provide feedback. This seems like sufficient amount of detail to get the ball rolling.

David J.

Re: Slow alter sequence with PG10.1

From

"Ivan E. Panchenko"

Date:

23 January 2018, 01:47:46

I also confirm this problem:

Running Michael's script on 10.1 takes 314 seconds instead of 2.3 seconds on 9.6.5.

Moreover adding some timing shows that on 10.1 the iteration execution time grows linearly with each iteration. (!!)

If we remove ALTER SEQUENCE, the difference is only 2.5 times (5 seconds for 10.1 and 2 - for 9.6.5), and the linear growth effect is not observed.

Removing advisory locks saves ~ 200ms in both cases, and still 9.6.5. seems faster.

Ivan Panchenko

Postgres Professional
the Russian PostgreSQL Company
+79104339846

22.01.2018 21:55, David G. Johnston пишет:

On Mon, Jan 22, 2018 at 8:24 AM, Michael Krüger <michael@kruegers.email> wrote:
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:

[...]

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?

Without testing/confirming I'd be inclined to agree that this is a regression for an unusual usage of sequences. Work was done to make typical use cases of sequences more feature-full and it is quite possible the added effort involved hurts your specific scenario. I'd expect a hacker to eventually pick this up, confirm the observation, and provide feedback. This seems like sufficient amount of detail to get the ball rolling.

David J.

Re: Slow alter sequence with PG10.1

From

Laurenz Albe

Date:

23 January 2018, 01:52:21

Michael Krüger wrote:
> 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(
[...]
>     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);
[...]

> 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?

See https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=3d79013b970d4cc336c06eb77ed526b44308c03e

  Make ALTER SEQUENCE, including RESTART, fully transactional.

  Previously the changes to the "data" part of the sequence, i.e. the
  one containing the current value, were not transactional, whereas the
  definition, including minimum and maximum value were.  That leads to
  odd behaviour if a schema change is rolled back, with the potential
  that out-of-bound sequence values can be returned.

  To avoid the issue create a new relfilenode fork whenever ALTER
  SEQUENCE is executed, similar to how TRUNCATE ... RESTART IDENTITY
  already is already handled.

This fixed a bug introduced in v10 by this change:
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=1753b1b027035029c2a2a1649065762fafbf63f3

  Add pg_sequence system catalog

  Move sequence metadata (start, increment, etc.) into a proper system
  catalog instead of storing it in the sequence heap object.  This
  separates the metadata from the sequence data.  Sequence metadata is now
  operated on transactionally by DDL commands, whereas previously
  rollbacks of sequence-related DDL commands would be ignored.

Previous to that change, ALTER SEQUENCE modified the values in the
sequence data file in place --- different from an UPDATE to a regular
table, which would write a new table row.

Consequently, the changes ALTER SEQUENCE were not rolled back in
9.6 and before.

After the change, the kinds of ALTER SEQUENCE that changed the values
in pg_sequence were transactional, but ALTER SEQUENCE ... RESTART,
which changes "last_value", which is stored in the data file, was not.

See the example in
https://www.postgresql.org/message-id/20170522154227.nvafbsm62sjpbxvd@alap3.anarazel.de

The solution was to create a new filenode whenever ALTER SEQUENCE is run,
which is the cause for the performance regression.

Now maybe the fix is really not perfect, but what else could be done?
Introducing row versions for sequences is wrong, because all transactions
have to see the same value for a sequence, otherwise it could not
serve its purpose.

I think that having transactional ALTER SEQUENCE is worth the price
of a slowdown for ALTER SEQUENCE.

I'd say that your function abuses ALTER SEQUENCE, and it would be better to
rewrite it.

The best solution if you need a gap-less batch of sequence values is
in my opinion to use a sequence with defined START WITH 10000 INCREMENT BY 10000
and get the starting value for the next batch of 10000 with
SELECT nextval('seq') - 9999.
That will waste some values if you don't need all 10000 values, but it
is very efficient and does not require a lock at all.

Yours,
Laurenz Albe

Re: Slow alter sequence with PG10.1

From

Adrian Klaver

Date:

23 January 2018, 03:29:13

On 01/22/2018 07:24 AM, Michael Krüger wrote:
> 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$;

Not entirely sure I understand how the batching above works, still maybe 
something like this:

CREATE OR REPLACE FUNCTION public.multi_nextval(use_seqname text, 
use_increment integer)
  RETURNS bigint
  LANGUAGE plpgsql
AS $function$
DECLARE
     reply int8;
     lock_id int4;
     seq_idx int8 :=nextval(use_seqname);
BEGIN
     SELECT oid::int4 INTO lock_id FROM pg_class WHERE relname = 
split_part(use_seqname, '.', 2);
     perform pg_advisory_lock(lock_id);
     perform setval(use_seqname, seq_idx + use_increment, 't');
     reply := nextval(use_seqname);
     perform pg_advisory_unlock(lock_id);
     return reply;
END;
$function$

On an older laptop this does the 20000 loops in about 1.6 secs.

> 
> -- 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@kruegers.email
> Mobile: 0152 5891 8787


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: Slow alter sequence with PG10.1

From

Michael Krüger

Date:

23 January 2018, 04:11:07

Hi Adrian and all,

I do not fully understand the reasoning of making sequences transactional in the first place. As far as I know its also not done on Oracle or SQL Server, but maybe I'm even wrong on that. What I question is a change in behavior of existing functionality with such big impact, without config option to restore old behavior, or maybe to have another variant in place like:

create sequence [non] [transactional] to at least be able to decide which variant to use. Maintain the performance of the old behavior, or if transactions safety is needed, the new behavior with the performance impact but more safety if needed.

I will try if Adrians proposal does the trick for my application. Sounds promising, thanks.

Regards,

Michael

Adrian Klaver <adrian.klaver@aklaver.com> schrieb am Mo., 22. Jan. 2018 um 22:29 Uhr:

On 01/22/2018 07:24 AM, Michael Krüger wrote:
> 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$;

Not entirely sure I understand how the batching above works, still maybe
something like this:

CREATE OR REPLACE FUNCTION public.multi_nextval(use_seqname text,
use_increment integer)
RETURNS bigint
LANGUAGE plpgsql
AS $function$
DECLARE
reply int8;
lock_id int4;
seq_idx int8 :=nextval(use_seqname);
BEGIN
SELECT oid::int4 INTO lock_id FROM pg_class WHERE relname =
split_part(use_seqname, '.', 2);
perform pg_advisory_lock(lock_id);
perform setval(use_seqname, seq_idx + use_increment, 't');
reply := nextval(use_seqname);
perform pg_advisory_unlock(lock_id);
return reply;
END;
$function$

On an older laptop this does the 20000 loops in about 1.6 secs.

>
> -- 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@kruegers.email
> Mobile: 0152 5891 8787

--
Adrian Klaver
adrian.klaver@aklaver.com

Re: Slow alter sequence with PG10.1

From

Michael Krüger

Date:

23 January 2018, 04:47:15

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 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

Michael Krüger <michael@kruegers.email> schrieb am Mo., 22. Jan. 2018 um 23:11 Uhr:

Hi Adrian and all,

I do not fully understand the reasoning of making sequences transactional in the first place. As far as I know its also not done on Oracle or SQL Server, but maybe I'm even wrong on that. What I question is a change in behavior of existing functionality with such big impact, without config option to restore old behavior, or maybe to have another variant in place like:

create sequence [non] [transactional] to at least be able to decide which variant to use. Maintain the performance of the old behavior, or if transactions safety is needed, the new behavior with the performance impact but more safety if needed.

I will try if Adrians proposal does the trick for my application. Sounds promising, thanks.

Regards,
Michael

Adrian Klaver <adrian.klaver@aklaver.com> schrieb am Mo., 22. Jan. 2018 um 22:29 Uhr:
On 01/22/2018 07:24 AM, Michael Krüger wrote:
> 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$;

Not entirely sure I understand how the batching above works, still maybe
something like this:

CREATE OR REPLACE FUNCTION public.multi_nextval(use_seqname text,
use_increment integer)
RETURNS bigint
LANGUAGE plpgsql
AS $function$
DECLARE
reply int8;
lock_id int4;
seq_idx int8 :=nextval(use_seqname);
BEGIN
SELECT oid::int4 INTO lock_id FROM pg_class WHERE relname =
split_part(use_seqname, '.', 2);
perform pg_advisory_lock(lock_id);
perform setval(use_seqname, seq_idx + use_increment, 't');
reply := nextval(use_seqname);
perform pg_advisory_unlock(lock_id);
return reply;
END;
$function$

On an older laptop this does the 20000 loops in about 1.6 secs.

>
> -- 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@kruegers.email
> Mobile: 0152 5891 8787

--
Adrian Klaver
adrian.klaver@aklaver.com

Re: Slow alter sequence with PG10.1

From

Peter Eisentraut

Date:

23 January 2018, 04:51:04

On 1/22/18 17:11, Michael Krüger wrote:
> I do not fully understand the reasoning of making sequences
> transactional in the first place.

It was sequence DDL that was made transactional.  Sequence use is still
nontransactional.

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Slow alter sequence with PG10.1

From

"David G. Johnston"

Date:

23 January 2018, 05:40:54

On Mon, Jan 22, 2018 at 3:47 PM, Michael Krüger <michael@kruegers.email> wrote:

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.

Not that I can think of.

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 of Postgres. This is the first time for me that a new release of Postgres caused some severe headaches among our customers.

OK...but I am not surprised as to how this played out. The system doesn't expect ALTER SEQUENCE to be executed frequently and your example doesn't argue for it since setval is indeed provided and can handle the situation where you wish to skip to some other sequence number for subsequent calls.

Disruption of existing code is unavoidable since two of the goals of this project are innovation and stability. That your customers discovered this particular instance of disruption is more on you than the project - major upgrades, especially the first few patch releases, don't end up seeing a wide variety of unusual setups (like this one) until they hit production since, as it seems here, people with those unusual setups are not putting it through its paces during the beta release period.

David J.

Re: Slow alter sequence with PG10.1

From

Kenneth Marshall

Date:

23 January 2018, 05:54:36

On Mon, Jan 22, 2018 at 04:40:54PM -0700, David G. Johnston wrote:
> 
> > 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 of
> > Postgres. This is the first time for me that a new release of Postgres
> > caused some severe headaches among our customers.
> >
> 
> OK...but I am not surprised as to how this played out.  The system doesn't
> expect ALTER SEQUENCE to be executed frequently and your example doesn't
> argue for it since setval is indeed provided and can handle the situation
> where you wish to skip to some other sequence number for subsequent calls.
> 
> Disruption of existing code is unavoidable since two of the goals of this
> project are innovation and stability.  That your customers discovered this
> particular instance of disruption is more on you than the project - major
> upgrades, especially the first few patch releases, don't end up seeing a
> wide variety of unusual setups (like this one) until they hit production
> since, as it seems here, people with those unusual setups are not putting
> it through its paces during the beta release period.
> 
> David J.

Hi,

I am not trying to be snarky, but it really behooves users to test their
systems with a new release before moving to it and not let their unwitting
customers be their debug team. :(

Regards,
Ken

Re: Slow alter sequence with PG10.1

From

Adrian Klaver

Date:

23 January 2018, 07:12:39

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

Re: Slow alter sequence with PG10.1

From

Michael Krüger

Date:

23 January 2018, 12:35:30

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