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

[Peter J. Holzer]

On 2020-03-21 14:51:35 -0600, Rob Sargent wrote:
> > On Mar 21, 2020, at 1:13 PM, Peter J. Holzer  wrote:
> > 
> > On 2020-03-21 12:55:33 -0600, Rob Sargent wrote:
> >> To me the description of the ID smacks of database-in-the-name folly. I
> >> recognize that OP is likely unable to take another path. I’ll not push 
> >> this any
> >> further. 
> > 
> > Identifiers often have internal structure. In Austria for example, the
> > social security number contains the birth date. Invoice numbers, project
> > numbers or court case numbers often contain the year.
> > 
> > That's because they are used by *people*, and people like their
> > identifiers to make some kind of sense. The computer doesn't care.
> 
> Since OP said this was digital not paper, I see this as a presentation
> problem bleeding into database design (assuming your response was an
> invitation to push further).

Well, that's the old natural primary key vs. surrogate primary key
debate.

(Trigger warning: You probably already heard that a gazillion times)

A primary key is an attribute (or a combination of attributes) which
unambiguosly identifies each entity. 

If the entity in question already has such an atttribute (e.g. an SSN,
invoice number, username, ...) and you use that as the primary key, is
is called a natural primary key[1].

If you generate a new attribute not related to existing attributes it is
called a surrogate (primary) key.

If the entity already has an attribute suitable as a primary key, why
would you want to use another one?

* The key candidate may be unwieldy: It might be a combination of
  several attributes, it might be a very long string, etc.
* You may suspect that the key candidate is not in fact a key. There
  have been cases of duplicate SSNs in several countries, and of course
  not every person has an SSN.

Some people say you should always use surrogate keys, never natural
keys. Mostly because of the second reason, I think: When you design a
system you have limited knowledge, so you can never be absolutely sure
that what appears to be a key candidate actually is one.

OTOH, surrogate keys have downsides, too: The attributes which would
have been a natural key are still there and must be stored, input,
output, maybe generated, used in business rules, etc. So that's a
(usually modest) increase in application complexity. More importantly,
everywhere you would have had a (to a domain expert) meaningful key you
now have a meaningless key - which makes it harder to understand the
data and makes extra joins necessary. So to inspect the contents of a
table instead of a simple "select * from tablename" you need a join over
three or four tables.

To get back to the OP's problem: As I understand it he needs to generate
that compound identifier anyway, because it is used outside of the
application. He could use a surrogate key in addition to that, but I
don't see any advantage to that. Most importantly it doesn't solve the
problem he wants to discuss in this thread[2].

hp


[1] But note that these attributes are almost always already artificial:
Humans aren't born with an SSN - that is assigned by an authority to
uniquely identify their clients; courts have sat for centuries
without the need to number their cases; etc.

[2] I admit I haven't been very helpful in this regard, either, going
off on tangents at every opportunity.

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

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

[Peter J. Holzer]

On 2020-03-21 13:45:54 -0700, pabloa98 wrote:
> On Sat, Mar 21, 2020 at 12:08 PM Peter J. Holzer  wrote:
> And I think that "care about gaps -> sequence doesn't work" is a
> knee-jerk reaction. It's similar to "can't parse HTML with regexps".
> True in the general case, and therefore people tend to blurt it out
> every time the topic comes up. But not necessarily true in specific
> cases. As I wrote above, there is no perfect solution - so you have to
> think about the actual requirements and the consequences of various
> solutions - and maybe using a sequence is the best (or least bad)
> solution.
> 
> OK. In that case, I will proceed with the millions of sequences. We will see.

Nonononononononono! (channelling Jean-Claude Laprie[1])

I was trying to say that in each case one should think about the pros
and cons of different approaches and be wary of dogmatic rules.

I wasn't recommending the use of sequences in your case. I would
actually be surprised if they work well for you.

But having said that - by all means try it. Maybe it does work, and
you'll give a talk about "The 50 million sequences database" at a
conference next year :-). Or maybe it doesn't, and then you'll have
learned something about where the actual limits are.

hp

[1] I don't expect you to know him :-)


-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

Re: Duplicate key violation on upsert

[Adrian Klaver]

On 3/21/20 2:45 PM, Matt Magoffin wrote:


On 22/03/2020, at 8:11 AM, Adrian Klaver > wrote:



I was thinking more about this:
"INSERT INTO solardatum.da_datum(ts, node_id, source_id, posted, 
jdata_i, jdata_a, jdata_s, jdata_t)

VALUES (…) ..."
from your OP. Namely whether it was:
VALUES (), (), (), ...
and if so there were values in the (),(),() that duplicated each other.
As to the second part of your response, ON CONFLICT does one of 
either INSERT or UPDATE. If:
1) There is no conflict for ON CONFLICT (node_id, ts, source_id) then 
the INSERT proceeds.

2) If there is a conflict then an UPDATE occurs using the SET values.
Now just me working through this:
da_datum_pkey   = (node_id, ts, source_id)
da_datum_x_acc_idx  = (node_id, source_id, ts DESC, jdata_a)
If 1) from above applies then da_datum_x_acc_idx will not be tripped 
as the only way that could happen is if the node_id, ts, source_id 
was the same as an existing row and that can't be true because the PK 
over the same values passed.


Well the below is complete rot. If you are UPDATEing then you are not 
creating a duplicate row, just overwriting a value with itself.


If 2) from above happened then you are trying to UPDATE a row with 
matching PK values(node_id, ts, source_id). Now it is entirely 
possible that since you are not testing for constraint violation on 
(node_id, source_id, ts DESC, jdata_a) that you be doing SET jdata_a 
= EXCLUDED.jdata_a, using a value that would trip da_datum_x_acc_idx


Sorry for the vagueness in my OP, I was trying to make it easier to 
read. The VALUES are for individual single column values, so a single 
possible row to insert/update.


So what you’ve outlined is basically what I thought should be happening. 
Namely, there can be only one row that will be inserted/updated. I am 
wondering if I should re-create the da_datum_x_acc_idx index without 
UNIQUE? I had it as UNIQUE to optimise the type of queries that make use 
of that index… but I did a little bit of testing using a non-UNIQUE 
index and those queries appear to execute around the same time as with 
the UNIQUE index. I just wasn’t sure if that would just be masking some 
other problem in my setup.


Well if the combination of values do not need to be UNIQUE then imposing 
UNIQUE on them, in my mind, is an unnecessary constraint.  Though it 
would be good to know why the constraint is being tripped.


Is there a chance the BEFORE trigger functions are doing something that 
could be leading to the error?


In the error log is there a line with the actual values that failed?



— m@



--
Adrian Klaver
adrian.kla...@aklaver.com

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

[Adrian Klaver]

On 3/21/20 11:08 AM, pabloa98 wrote:


 > As to below that is going to require more thought.
 >
Still no word on the actual requirement. As someone who believes
consecutive numbers on digital invoices is simply a mistaken
interpretation of the paper based system, I suspect a similar error
here. But again we haven’t really heard, far as I know. Something
really fishy about .
 >

It is fishy. This is the thing. The code, element, and group is part of 
a bigger code called item identifier (or ii).


An item identifier is a result of:  code || group || element || 
qualifier (a constant like 55) || check digit coming from some check 
algorithm.


For example:

for a triplet (group, element, code) like (1, 3, 63) the item identifier 
(or ii) is: 630010003558  (the last 8 is the check digit).
This number is converted to a bigint and stored (and used as PK or FK on 
other tables, etc, etc).


In an item identifier the room is assigned like:


Revised, to make it match above identifier:




> 8 digits for code (not padded with 0s)

3 digits for group
4 digits for element
2 digits for qualifier
1 digit for the check digit.
-
18 digits for item identifier.




So the question may actually be:

How do we improve our locking code, so we don't have to spawn millions 
of sequences?


What is the locking method you are using?


The lock part is because we solved a similar problem with a counter by 
row locking the counter and increasing it in another part of the 
database. The result is that all the queries using that table are queued 
by pair (group, element) that is not that bad because we are not 
inserting thousands of rows by second. Still is killing cluster 
performance (but performance is still OK from the business point of 
view). The problem using locks is that they are too sensitive to 
developer errors and bugs. Sometimes connected clients aborts and the 
connection is returned to the pool with the lock active until the 
connection is closed or someone unlocks the row. I would prefer to have 
something more resilient to developers/programming errors, if possible.








--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Duplicate key violation on upsert

[Matt Magoffin]

> On 23/03/2020, at 9:44 AM, Adrian Klaver  wrote:
> Is there a chance the BEFORE trigger functions are doing something that could 
> be leading to the error?
> 
> In the error log is there a line with the actual values that failed?

The error log does not show the literal values, no. Here is a literal example 
from the logs:

2020-03-20 19:51:11 NZDT [15165]: [6-1] ERROR:  duplicate key value violates 
unique constraint "_hyper_1_1931_chunk_da_datum_x_acc_idx"
2020-03-20 19:51:11 NZDT [15165]: [7-1] CONTEXT:  SQL statement "INSERT INTO 
solardatum.da_datum(ts, node_id, source_id, posted, jdata_i, jdata_a, jdata_s, 
jdata_t)
VALUES (ts_crea, node, src, ts_post, jdata_json->'i', 
jdata_json->'a', jdata_json->'s', 
solarcommon.json_array_to_text_array(jdata_json->'t'))
ON CONFLICT (node_id, ts, source_id) DO UPDATE
SET jdata_i = EXCLUDED.jdata_i,
jdata_a = EXCLUDED.jdata_a,
jdata_s = EXCLUDED.jdata_s,
jdata_t = EXCLUDED.jdata_t,
posted = EXCLUDED.posted
RETURNING (xmax = 0)"
PL/pgSQL function solardatum.store_datum(timestamp with time 
zone,bigint,text,timestamp with time zone,text,boolean) line 10 at SQL statement
2020-03-20 19:51:11 NZDT [15165]: [8-1] STATEMENT:  select * from 
solardatum.store_datum($1, $2, $3, $4, $5) as result

As for the BEFORE triggers, the solardatum.trigger_agg_stale_datum one does an 
INSERT into a different table and a SELECT from this same table. The 
_timescaledb_internal.insert_blocker one is part of the TimescaleDB extension 
which looks like it wouldn’t have an impact to this issue, but the source of 
that is

https://github.com/timescale/timescaledb/blob/91fe723d3aaaf88b53ebf8adc3e16a68ec45/src/hypertable.c#L1359
 


— m@

Re: Mixed Locales and Upgrading

[Don Seiler]

On Tue, Mar 17, 2020 at 9:25 PM Michael Paquier  wrote:

>
> There is no way to know how much indexes would get broken without
> having a look at it.  Anything ASCII-based should be of no problem.
> If you have a doubt, reindexing evey index which includes text column
> data is the best course of action in my opinion if you have any
> doubts, because that's safe even if it has a higher cost.
>

Here's the fun part. A lot of the tables use UUIDv4 strings for primary
keys. However these are stored in text/varchar columns.

-- 
Don Seiler
www.seiler.us

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

[pabloa98]

> So the question may actually be:
>
> How do we improve our locking code, so we don't have to spawn millions
> of sequences?
>
> What is the locking method you are using?
>

I am not using locking with the million sequence solution. I do not want
something that locks because the problems described below

I prefer the solution generates a gap (skip a couple of numbers) and not
using locks.


>
> > The lock part is because we solved a similar problem with a counter by
> > row locking the counter and increasing it in another part of the
> > database. The result is that all the queries using that table are queued
> > by pair (group, element) that is not that bad because we are not
> > inserting thousands of rows by second. Still is killing cluster
> > performance (but performance is still OK from the business point of
> > view). The problem using locks is that they are too sensitive to
> > developer errors and bugs. Sometimes connected clients aborts and the
> > connection is returned to the pool with the lock active until the
> > connection is closed or someone unlocks the row. I would prefer to have
> > something more resilient to developers/programming errors, if possible.
> >
>
> Now I read this paragraph, I realize I was not clear enough.
I am saying we do not want to use locks because of all the problems
described.

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

[Adrian Klaver]

On 3/22/20 2:53 PM, pabloa98 wrote:


So the question may actually be:

How do we improve our locking code, so we don't have to spawn millions
of sequences?

What is the locking method you are using?


I am not using locking with the million sequence solution. I do not want 
something that locks because the problems described below


I prefer the solution generates a gap (skip a couple of numbers) and not 
using locks.




 > The lock part is because we solved a similar problem with a
counter by
 > row locking the counter and increasing it in another part of the
 > database. The result is that all the queries using that table are
queued
 > by pair (group, element) that is not that bad because we are not
 > inserting thousands of rows by second. Still is killing cluster
 > performance (but performance is still OK from the business point of
 > view). The problem using locks is that they are too sensitive to
 > developer errors and bugs. Sometimes connected clients aborts and
the
 > connection is returned to the pool with the lock active until the
 > connection is closed or someone unlocks the row. I would prefer
to have
 > something more resilient to developers/programming errors, if
possible.
 >

Now I read this paragraph, I realize I was not clear enough.
I am saying we do not want to use locks because of all the problems 
described.


And what I was asking is what locking where you doing?

And it might be better to ask the list how to solve those problems, then 
to create a whole new set of problems by using millions of sequences.



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Duplicate key violation on upsert

[Adrian Klaver]

On 3/22/20 2:48 PM, Matt Magoffin wrote:


On 23/03/2020, at 9:44 AM, Adrian Klaver > wrote:
Is there a chance the BEFORE trigger functions are doing something 
that could be leading to the error?


In the error log is there a line with the actual values that failed?


The error log does not show the literal values, no. Here is a literal 
example from the logs:


2020-03-20 19:51:11 NZDT [15165]: [6-1] ERROR:  duplicate key value 
violates unique constraint "_hyper_1_1931_chunk_da_datum_x_acc_idx"
2020-03-20 19:51:11 NZDT [15165]: [7-1] CONTEXT:  SQL statement "INSERT 
INTO solardatum.da_datum(ts, node_id, source_id, posted, jdata_i, 
jdata_a, jdata_s, jdata_t)
                 VALUES (ts_crea, node, src, ts_post, jdata_json->'i', 
jdata_json->'a', jdata_json->'s', 
solarcommon.json_array_to_text_array(jdata_json->'t'))

                 ON CONFLICT (node_id, ts, source_id) DO UPDATE
                 SET jdata_i = EXCLUDED.jdata_i,
                         jdata_a = EXCLUDED.jdata_a,
                         jdata_s = EXCLUDED.jdata_s,
                         jdata_t = EXCLUDED.jdata_t,
                         posted = EXCLUDED.posted
                 RETURNING (xmax = 0)"
         PL/pgSQL function solardatum.store_datum(timestamp with time 
zone,bigint,text,timestamp with time zone,text,boolean) line 10 at SQL 
statement
2020-03-20 19:51:11 NZDT [15165]: [8-1] STATEMENT:  select * from 
solardatum.store_datum($1, $2, $3, $4, $5) as result


So the query is in the function solardatum.store_datum()?

If so what is it doing?

And could you capture the values and pass them to a RAISE NOTICE?



As for the BEFORE triggers, the solardatum.trigger_agg_stale_datum one 
does an INSERT into a different table and a SELECT from this same table. 
The _timescaledb_internal.insert_blocker one is part of the TimescaleDB 
extension which looks like it wouldn’t have an impact to this issue, but 
the source of that is


https://github.com/timescale/timescaledb/blob/91fe723d3aaaf88b53ebf8adc3e16a68ec45/src/hypertable.c#L1359

— m@



--
Adrian Klaver
adrian.kla...@aklaver.com

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

[pabloa98]

> > Now I read this paragraph, I realize I was not clear enough.
> > I am saying we do not want to use locks because of all the problems
> > described.
>
> And what I was asking is what locking where you doing?
>
> And it might be better to ask the list how to solve those problems, then
> to create a whole new set of problems by using millions of sequences.
>
> What we are doing in the **OTHER section** with row locks is basically:

BEGIN;
...
x = SELECT last_value + 1 FROM counter WHERE group = ... AND element = 
FOR UPDATE;
...

if everything is fine, then:
UPDATE counter SET last_value = x WHERE group = ... AND element = 
FOR UPDATE;
COMMIT;
else:
ROLLBACK;

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

[Christopher Browne]

On Sun, 22 Mar 2020 at 17:54, pabloa98  wrote:

>
> So the question may actually be:
>>
>> How do we improve our locking code, so we don't have to spawn millions
>> of sequences?
>>
>> What is the locking method you are using?
>>
>
> I am not using locking with the million sequence solution. I do not want
> something that locks because the problems described below
>
> I prefer the solution generates a gap (skip a couple of numbers) and not
> using locks.
>

If you can cope with gaps, that's a good thing.  That means that some kind
of usage of sequences would be compatible with your application.


>
>> > The lock part is because we solved a similar problem with a counter by
>> > row locking the counter and increasing it in another part of the
>> > database. The result is that all the queries using that table are
>> queued
>> > by pair (group, element) that is not that bad because we are not
>> > inserting thousands of rows by second. Still is killing cluster
>> > performance (but performance is still OK from the business point of
>> > view). The problem using locks is that they are too sensitive to
>> > developer errors and bugs. Sometimes connected clients aborts and the
>> > connection is returned to the pool with the lock active until the
>> > connection is closed or someone unlocks the row. I would prefer to have
>> > something more resilient to developers/programming errors, if possible.
>> >
>>
>> Now I read this paragraph, I realize I was not clear enough.
> I am saying we do not want to use locks because of all the problems
> described.
>

Cool, that means you have been thinking through similar considerations to
what others have in mind, and it doesn't sound like there are dramatically
different understandings.

Let's circle back to the schema that you provided...

CREATE TABLE counter(
group INT NOT NULL,
element INT NOT NULL,
seq_number INT NOT NULL default 0,
CONSTRAINT PRIMARY KEY (group, element)
);

That's not a table using the sequential values; that's what you imagining
you could have as a way of referencing the sequences, right?

I would represent it slightly differently...

create table counter (
   group integer not null,
   element integer not null,
   sequence_name name,
   primary key (group, element)
);
Arguably, there's no need for sequence_name altogether, as it's never
directly referenced by anything.

And then have a function that might fire upon creation of new entries in
this table.

create or replace function generate_sequence (i_group integer, i_element
integer) returns name
as $$
declare
  c_seqname name;
  c_query text;
begin
   c_seqname := 'obj_counter_' || i_group || '_'  || i_element;
   c_query := 'create sequence if not exists ' || c_seqname || ';';
   execute c_query;
   update counter set sequence_name = c_seqname where group = i_group and
element = i_element;
   return c_seqname;
end
$$ language plpgsql;

You'd need a trigger function to put onto the table that runs this
function; that is left as an exercise for the reader.

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_ || ');';
   execute c_query into c_seqval;
   return c_seqval;
end
$$ language plpgsql;

Again, that just grabs a nextval(); you'd need to execute this inside a
trigger function called ON INSERT on any of the tables that need sequence
values assigned.
That encapsulates the usage of this horde of sequences.  You're probably
calling get_next_counter() millions of times, so perhaps that code gets
expanded directly into place in the trigger function.

-- 
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"

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

[David G. Johnston]

On Sun, Mar 22, 2020 at 5:36 PM Christopher Browne 
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.

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

[pabloa98]

On Sun, Mar 22, 2020 at 5:36 PM Christopher Browne 
wrote:

> On Sun, 22 Mar 2020 at 17:54, pabloa98  wrote:
>
>>
>> So the question may actually be:
>>>
>>> How do we improve our locking code, so we don't have to spawn millions
>>> of sequences?
>>>
>>> What is the locking method you are using?
>>>
>>
>> I am not using locking with the million sequence solution. I do not want
>> something that locks because the problems described below
>>
>> I prefer the solution generates a gap (skip a couple of numbers) and not
>> using locks.
>>
>
> If you can cope with gaps, that's a good thing.  That means that some kind
> of usage of sequences would be compatible with your application.
>

Indeed


>
>
>>
>>> > The lock part is because we solved a similar problem with a counter by
>>> > row locking the counter and increasing it in another part of the
>>> > database. The result is that all the queries using that table are
>>> queued
>>> > by pair (group, element) that is not that bad because we are not
>>> > inserting thousands of rows by second. Still is killing cluster
>>> > performance (but performance is still OK from the business point of
>>> > view). The problem using locks is that they are too sensitive to
>>> > developer errors and bugs. Sometimes connected clients aborts and the
>>> > connection is returned to the pool with the lock active until the
>>> > connection is closed or someone unlocks the row. I would prefer to
>>> have
>>> > something more resilient to developers/programming errors, if possible.
>>> >
>>>
>>> Now I read this paragraph, I realize I was not clear enough.
>> I am saying we do not want to use locks because of all the problems
>> described.
>>
>
> Cool, that means you have been thinking through similar considerations to
> what others have in mind, and it doesn't sound like there are dramatically
> different understandings.
>
> Let's circle back to the schema that you provided...
>
> CREATE TABLE counter(
> group INT NOT NULL,
> element INT NOT NULL,
> seq_number INT NOT NULL default 0,
> CONSTRAINT PRIMARY KEY (group, element)
> );
>
> That's not a table using the sequential values; that's what you imagining
> you could have as a way of referencing the sequences, right?
>
> I would represent it slightly differently...
>
> create table counter (
>group integer not null,
>element integer not null,
>sequence_name name,
>primary key (group, element)
> );
> Arguably, there's no need for sequence_name altogether, as it's never
> directly referenced by anything.
>
> And then have a function that might fire upon creation of new entries in
> this table.
>
> create or replace function generate_sequence (i_group integer, i_element
> integer) returns name
> as $$
> declare
>   c_seqname name;
>   c_query text;
> begin
>c_seqname := 'obj_counter_' || i_group || '_'  || i_element;
>c_query := 'create sequence if not exists ' || c_seqname || ';';
>execute c_query;
>update counter set sequence_name = c_seqname where group = i_group and
> element = i_element;
>return c_seqname;
> end
> $$ language plpgsql;
>
> You'd need a trigger function to put onto the table that runs this
> function; that is left as an exercise for the reader.
>
> 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_ || ');';
>execute c_query into c_seqval;
>return c_seqval;
> end
> $$ language plpgsql;
>
> Again, that just grabs a nextval(); you'd need to execute this inside a
> trigger function called ON INSERT on any of the tables that need sequence
> values assigned.
> That encapsulates the usage of this horde of sequences.  You're probably
> calling get_next_counter() millions of times, so perhaps that code gets
> expanded directly into place in the trigger function.
>
>
I like this approach.
When I have a medium number of sequence I will report how it behaves. It
will take some time though.

Pablo

Re: Passwordcheck configuration

[Michael Paquier]

On Thu, Mar 19, 2020 at 07:19:06PM -0400, Tom Lane wrote:
> passwordcheck hasn't got any out-of-the-box configurability.  It's mainly
> meant as sample code that people could modify if they have a mind to.

Here is an example:
https://github.com/michaelpq/pg_plugins/tree/master/passwordcheck_extra

> (I seem to recall some recent discussion about deprecating/removing
> passwordcheck altogether, but I can't find it right now.)

This was mentioned last here if I recall correctly:
https://www.postgresql.org/message-id/ac785d69-41ec-4d0a-ac37-1f9ff55c9...@amazon.com
--
Michael


signature.asc
Description: PGP signature

Postgres cluster setup

[Sonam Sharma]

Hello,.

Do you have similar setup like Oracle RAC in postgres core . I found in edb
but didn't find anything in postgres core. We are looking for setting up
replication with no outage and other node will be up , if the primary is
down. Any help would be great 😊

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

[pabloa98]

On Sun, Mar 22, 2020 at 6:58 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Sun, Mar 22, 2020 at 5:36 PM Christopher Browne 
> 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.
>
>
I will add this to the previous solution.

Thank you all for all the ideas and suggestions.

I hope there will be int he future sequence data type and support and
optimizations of sequences in postgresql to deal with a lot of them. They
will be very useful no only for me :)  but problems like monitoring and
counting things by the zillions.

Pablo