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

On Fri, Mar 20, 2020 at 8:13 PM pabloa98 wrote: > > I hope I described the problem completely. > > 1) What is a group - does it exist prior to records being inserted? How many groups are you working with? How long do they live for? 2) #1 but for element 3) How many records per second per group

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

On Friday, March 20, 2020, pabloa98 wrote: > > If there is another solution different than millions of sequences that do >> not block, generate few gaps (and those gaps are small) and never generate >> collisions then I will happily use it. > > You are going to have to lose the not blocking

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

> Nothing I saw that said int could not become bigint. > > > My bad. The code cannot be a bigint. Or it could be a bigint between 1 to :) I thought it was not important. The code could be a number from 1 to (so an Int will be OK) assigned in order-ish. This is because of business

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

On Fri, Mar 20, 2020 at 3:59 PM Peter J. Holzer wrote: > On 2020-03-19 16:48:19 -0700, David G. Johnston wrote: > > First, it sounds like you care about there being no gaps in the records > you end > > up saving. If that is the case then sequences will not work for you. > > I think (but I would

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

On 3/20/20 2:13 PM, pabloa98 wrote: On Fri, Mar 20, 2020 at 10:26 AM Adrian Klaver mailto:adrian.kla...@aklaver.com>> wrote: On 3/20/20 9:59 AM, Adrian Klaver wrote: > On 3/19/20 10:31 PM, pabloa98 wrote: >> I see. >> >> Any suggestion? It should behave like a

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

On 3/20/20 4:29 PM, Peter J. Holzer wrote: On 2020-03-20 17:11:42 -0600, Rob Sargent wrote: On Mar 20, 2020, at 4:59 PM, Peter J. Holzer wrote: On 2020-03-19 16:48:19 -0700, David G. Johnston wrote: First, it sounds like you care about there being no gaps in the records you end up saving.

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

> On Mar 20, 2020, at 5:29 PM, Peter J. Holzer wrote: > >  >> >> >> OP has said small gaps are ok. > > Yes. This wasn't a response to the OP's requirements, but to David's > (rather knee-jerk, IMHO) "don't use sequences" response. Very often the > requirements which would preclude

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

On 2020-03-20 17:11:42 -0600, Rob Sargent wrote: > On Mar 20, 2020, at 4:59 PM, Peter J. Holzer wrote: > > On 2020-03-19 16:48:19 -0700, David G. Johnston wrote: > >> First, it sounds like you care about there being no gaps in the records > >> you end > >> up saving. If that is the case then

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

> On Mar 20, 2020, at 4:59 PM, Peter J. Holzer wrote: > > On 2020-03-19 16:48:19 -0700, David G. Johnston wrote: >> First, it sounds like you care about there being no gaps in the records you >> end >> up saving. If that is the case then sequences will not work for you. > > I think (but I

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

On 2020-03-19 16:48:19 -0700, David G. Johnston wrote: > First, it sounds like you care about there being no gaps in the records you > end > up saving.  If that is the case then sequences will not work for you. I think (but I would love to be proven wrong), that *nothing* will work reliably, if

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

On Fri, Mar 20, 2020 at 10:26 AM Adrian Klaver wrote: > On 3/20/20 9:59 AM, Adrian Klaver wrote: > > On 3/19/20 10:31 PM, pabloa98 wrote: > >> I see. > >> > >> Any suggestion? It should behave like a sequence in the sense that > >> concurrent transitions will get different numbers from this > >>

Re: Duplicate key violation on upsert

> On 21/03/2020, at 8:10 AM, Adrian Klaver wrote: > >> The _hyper_1_1931_chunk_da_datum_x_acc_idx index has the same definition as >> the da_datum_x_acc_idx above (it is defined on a child table). That is, they >> are both essentially: >> UNIQUE, btree (node_id, source_id, ts DESC, jdata_a)

Re: PG12 autovac issues

> > We haven't isolated *which* table it is blocked on (assuming it is), > but all autovac's cease running until we manually intervene. > > When we get into this state again, is there some other information > (other than what is in pg_stat_statement or pg_stat_activity) that > would be useful for

Re: Duplicate key violation on upsert

On 3/20/20 11:34 AM, Matt Magoffin wrote: On 21/03/2020, at 4:00 AM, Adrian Klaver > wrote: On 3/20/20 2:17 AM, Matt Magoffin wrote: Hello, Indexes:     "da_datum_pkey" UNIQUE, btree (node_id, ts, source_id) CLUSTER, tablespace "solarindex"     

Re: PG12 autovac issues

Hi, On 2020-03-20 12:42:31 -0500, Justin King wrote: > When we get into this state again, is there some other information > (other than what is in pg_stat_statement or pg_stat_activity) that > would be useful for folks here to help understand what is going on? If it's actually stuck on a single

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

On Fri, Mar 20, 2020 at 5:39 AM rob stone wrote: > Hello, > > On Thu, 2020-03-19 at 14:36 -0700, pabloa98 wrote: > > Hello, > > > > My schema requires a counter for each combination of 2 values. > > Something like: > > > > CREATE TABLE counter( > > group INT NOT NULL, > > element INT NOT NULL, >

Re: unexpected chunk number 2 (expected 0) for toast value ... in pg_toast_18536

On Mon, Mar 16, 2020 at 12:38:35PM -0700, Andres Freund wrote: > > We have ruled out (?) below-PG hardware problems by a > > successful run of: > > > > cp -rv —preserve=all /var/lib/postgresql/9.6 /tmp/ > > FWIW, I don't think that rules out hardware problems at all. In plenty > cases of

Re: unexpected chunk number 2 (expected 0) for toast value ... in pg_toast_18536

On Sun, Mar 15, 2020 at 08:11:18PM -0400, Tom Lane wrote: > Karsten Hilbert writes: > >>> According to > >>> http://www.databasesoup.com/2013/10/de-corrupting-toast-tables.html > >>> an UPDATE of the row is recommended -- should that work > >>> better than a DELETE ? > > > OK, got that. What I

Re: Duplicate key violation on upsert

> On 21/03/2020, at 4:00 AM, Adrian Klaver wrote: > > On 3/20/20 2:17 AM, Matt Magoffin wrote: >> Hello, >> Indexes: >> "da_datum_pkey" UNIQUE, btree (node_id, ts, source_id) CLUSTER, >> tablespace "solarindex" >> "da_datum_reverse_pkey" UNIQUE, btree (node_id, ts DESC, source_id),

Re: PG12 autovac issues

On Thu, Mar 19, 2020 at 6:56 PM Andres Freund wrote: > > Hi, > > On 2020-03-19 18:07:14 -0500, Justin King wrote: > > On Thu, Mar 19, 2020 at 5:35 PM Andres Freund wrote: > > > > > > Hi, > > > > > > On 2020-03-19 10:23:48 -0500, Justin King wrote: > > > > > From a single stats snapshot we can't

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

On 3/20/20 9:59 AM, Adrian Klaver wrote: On 3/19/20 10:31 PM, pabloa98 wrote: I see. Any suggestion? It should behave like a sequence in the sense that concurrent transitions will get different numbers from this alternative sequence like solution. In our case, we will need to do a call

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

On 3/19/20 10:31 PM, pabloa98 wrote: I see. Any suggestion? It should behave like a sequence in the sense that concurrent transitions will get different numbers from this alternative sequence like solution. In our case, we will need to do a call nextval('some_seq') (or similar) from

Re: Passwordcheck configuration

On 3/19/20 6:19 PM, Tom Lane wrote: Dave Hughes writes: I have a requirement to set some password complexity for our database such as length of password, upper case, lower case, special characters, expiration limit, reuse, etc. Usually, if you have to do something like that, we recommend

Re: Passwordcheck configuration

Thank you for the information! This issue originated from a Department of Defense STIG (Security Technical Implementation Guides). It's a security check that applications and databases have to go through. I'll just leave this one as a "finding" since there isn't a way to really configure it to

Re: How to get RAISE INFO in JDBC

Ravi Krishna schrieb am 20.03.2020 um 15:48: How do we get the notification mentioned in RAISE INFO of PL/PGSQL in JDBC. I can get it in psql, but not in JDBC. I am talking about redshift which has the same procedural language like postgres. I can only answer this for Postgres. As Redhisft

Re: Duplicate key violation on upsert

On 3/20/20 2:17 AM, Matt Magoffin wrote: Hello, I am experiencing a duplicate key violation in Postgres 9.6 on occasion for one particular query, and I’m wondering where I’m going wrong. My table looks like this:                       Table "solardatum.da_datum"   Column   |           

How to get RAISE INFO in JDBC

How do we get the notification mentioned in RAISE INFO of PL/PGSQL in JDBC. I can get it in psql, but not in JDBC. I am talking about redshift which has the same procedural language like postgres. thanks

Re: Partition by hash formula

On Fri, 2020-03-20 at 12:50 +0300, Vlad Bokov wrote: > recently I read > https://blog.dbi-services.com/hash-partitioning-in-postgresql-11/ > and reproduced steps from the, which gave exact same count outcome, > which made me think > hashing is a pure function. > > Examined the data I saw

Re: Wal receiver process listens to physical IP

On Fri, 2020-03-20 at 11:27 +, Mariya Rampurawala wrote: > I have been working on a project where I need to virtualize the postgresql > standby server. > The wal receiver process listens to the Physical IP and a random port. > How can I configure it to listen to a VIP that the wal sender

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

Hello, On Thu, 2020-03-19 at 14:36 -0700, pabloa98 wrote: > Hello, > > My schema requires a counter for each combination of 2 values. > Something like: > > CREATE TABLE counter( > group INT NOT NULL, > element INT NOT NULL, > seq_number INT NOT NULL default 0, > CONSTRAINT PRIMARY KEY (group,

Wal receiver process listens to physical IP

Hi, I have been working on a project where I need to virtualize the postgresql standby server. The wal receiver process listens to the Physical IP and a random port. How can I configure it to listen to a VIP that the wal sender process can connect to? Also, Is there a way I can configure the

promise postgres that I will only ROLLBACK

Hey, I have a unit testing framework, where every test is ran in BEGIN..ROLLBACK, to ensure that data created during testing is never persisted. Now, my question is: is there any way to tell postgres that "Look, I will never ask you for a COMMIT, therefore you don't have to lock anything for

Partition by hash formula

Hello, recently I read https://blog.dbi-services.com/hash-partitioning-in-postgresql-11/ and reproduced steps from the, which gave exact same count outcome, which made me think hashing is a pure function. Examined the data I saw following distribution: dept_1: 1,14,39...; dept_2: 3,11,21...;

Duplicate key violation on upsert

Hello, I am experiencing a duplicate key violation in Postgres 9.6 on occasion for one particular query, and I’m wondering where I’m going wrong. My table looks like this: Table "solardatum.da_datum" Column | Type | Collation | Nullable | Default