Re: Could postgres12 support millions of sequences? (like 10 million)

On Sun, Mar 22, 2020 at 5:36 PM Christopher Browne <cbbrowne(at)gmail(dot)com>
wrote:

>
> Then, on any of the tables where you need to assign sequence values, you'd
> need to run an "after" trigger to do the assignment. The function that
> finds the sequence value is kind of analagous:
> create or replace function get_next_counter (i_group integer, i_element
> integer) returns integer -- or bigint?
> as $$
> declare
> c_seqname name;
> c_query text;
> c_seqval integer;
> begin
> c_seqname := 'obj_counter_' || i_group || '_' || i_element;
> c_query := 'select nextval(' || quote_ident( c_seqname_ || ');';
>

or

c_query := format('select nextval(%I);', c_seqname);

> You're probably calling get_next_counter() millions of times, so perhaps
> that code gets expanded directly into place in the trigger function.
>

not tested but something like:

execute format('select nextval("obj_counter_%s_%s");', i_group, i_element)
into strict c_seqval;

or, more paranoidly:

execute format('select nextval(%I);', format('obj_counter_%s_%s', i_group,
i_element)) into strict c_seqval;

David J.