Re: Removing Last field from CSV string

Regular expressions, in my opinion, can be a very powerful text search and replace engine if you know how to use it.Feel free to enhance what I provided below; it seems to work for the example you provided. postgres=# select regexp_replace(substring('Class V,Class VI,Class VII,Competitive Exam,C

Re: Configuring more than one hot standby server

At Tue, 19 May 2020 22:39:20 +0530, Santhosh Kumar wrote in > Hi Community, > I read about "hot stand by" server and as far as the document goes, it > does not explicitly explain about, whether is it possible to configure more > than on database as a Hot stand by server. If we can configure mor

Re: A limit clause can cause a poor index choice

What does pg_stats say about column customer_id? Specifically, how many ndistinct, and what is the sum of the most common values? If you have 1000 distinct customer_id values, and the (default 100) most common values only cover 2% of the total rows, then the optimizer will assume that any given cus

Re: A limit clause can cause a poor index choice

Hi Nick, I believe a second ordering, by id desc, will get your query to use the right index, and shouldn't be functionally different from what you would expect. ``` select * from test_orders where customer_id=any(ARRAY[9993,9997,9912,9954,9100,9101,9102,9234,9500,9512]) order by o_date desc, i

Configuring more than one hot standby server

Hi Community, I read about "hot stand by" server and as far as the document goes, it does not explicitly explain about, whether is it possible to configure more than on database as a Hot stand by server. If we can configure more than one hot stand by server, which database will take the role of ma

Clarification relation logical replication

Hi, Can I have a cluster of multiple instances with logical replication enabled for all tables with every single instance as both publisher and subscriber, to mimic multi master replication? The documentation says, care must be taken on subscribed publication objects to not overlap? Ref : https:/

A limit clause can cause a poor index choice

The attached script builds a 10G test table which demonstrates a problem that we have in production with postgresql 12.3-1.pgdg18.04+1 on ubuntu linux. Indexes: test_orders_o_date_idx btree(o_date) test_orders_customer_id_o_date_idx btree(customer_id, o_date) We query for the most recent orders f

Re: Unique index on hash of jsonb value - correct solution?

Hi, first, thanks a lot for your input! Am 19.05.20 16:16 schrieb(en) Stephen Frost: That's only going to work up to a certain size for that text and blob value too, of course.. These three columns (stripped-down code, there are more in the table) are a “lazy” import from various python scr

Re: idle_in_transaction_session_timeout not getting log in PG v12.2

On 5/19/20 8:07 AM, Ishan Joshi wrote: Hi Adrian, 1) What OS and version and did it change between 11 and 12? --> it is cent os 7 an both v11.5 and v12.2 are on same server. 2) How are you starting the server and did that change?  --> through systemctl start postgresql-12 3) When did you c

Re: Huge tables, trying to delete OID's taking 6+hours per table

On 5/19/20 11:51 AM, Tory M Blue wrote: On Tue, May 19, 2020 at 6:40 AM Tom Lane > wrote: Tory M Blue mailto:tmb...@gmail.com>> writes: > The command i'm using is > ALTER TABLE tablename SET WITHOUT OIDS; > Would a drop column oid be better? Unf

Re: Huge tables, trying to delete OID's taking 6+hours per table

On Tue, May 19, 2020 at 6:40 AM Tom Lane wrote: > Tory M Blue writes: > > The command i'm using is > > ALTER TABLE tablename SET WITHOUT OIDS; > > Would a drop column oid be better? > > Unfortunately, you're kind of stuck. OIDs are not like regular columns > (at least before v12) --- they are i

Re: idle_in_transaction_session_timeout not getting log in PG v12.2

On 5/19/20 8:07 AM, Ishan Joshi wrote: Hi Adrian, 1) What OS and version and did it change between 11 and 12? --> it is cent os 7 an both v11.5 and v12.2 are on same server. 2) How are you starting the server and did that change?  --> through systemctl start postgresql-12 3) When did you c

RE: idle_in_transaction_session_timeout not getting log in PG v12.2

Hi Adrian, 1) What OS and version and did it change between 11 and 12? --> it is cent os 7 an both v11.5 and v12.2 are on same server. 2) How are you starting the server and did that change? --> through systemctl start postgresql-12 3) When did you copy the 11 *.conf files over and did

Re: idle_in_transaction_session_timeout not getting log in PG v12.2

On 5/19/20 7:40 AM, Ishan Joshi wrote: Yes Adrian...!!! Well then: 1) What OS and version and did it change between 11 and 12? 2) How are you starting the server and did that change? 3) When did you copy the 11 *.conf files over and did you restart the 12 server after? 4) What is being logg

RE: idle_in_transaction_session_timeout not getting log in PG v12.2

Yes Adrian...!!! Thanks & Regards, Ishan Joshi -Original Message- From: Adrian Klaver Sent: Tuesday, May 19, 2020 7:53 PM To: Ishan Joshi ; pgsql-gene...@postgresql.org Subject: Re: idle_in_transaction_session_timeout not getting log in PG v12.2 On 5/19/20 7:12 AM, Ishan Joshi wrote: >

Re: idle_in_transaction_session_timeout not getting log in PG v12.2

On 5/19/20 7:12 AM, Ishan Joshi wrote: Thank Adrian for response. Yes I have the same *.conf file as v11.5 in fact I have copied the same conf files in v12.2 Logging parameter are as follows I'm not seeing anything obviously wrong with below. Are you sure you are looking at the correct log

Re: Unique index on hash of jsonb value - correct solution?

Greetings, * Laurenz Albe (laurenz.a...@cybertec.at) wrote: > On Mon, 2020-05-18 at 18:43 +0200, Albrecht Dreß wrote: > > in a database I have a table with a text, a jsonb and a bytea column, which > > together shall be unique, like: > > > > > > Column | Type | Collation | Nullable |

RE: idle_in_transaction_session_timeout not getting log in PG v12.2

Thank Adrian for response. Yes I have the same *.conf file as v11.5 in fact I have copied the same conf files in v12.2 Logging parameter are as follows Parameter Values log_autovacuum_min_duration 1s log_checkpoints on log_connections on log_destination stderr log_directory log l

Re: idle_in_transaction_session_timeout not getting log in PG v12.2

On 5/19/20 2:08 AM, Ishan Joshi wrote: Hi Team, I have set idle_in_transaction_session_timeout on user level and it is set for 5 min. I can see after 5 min the session with “idle in transaction” state got killed and it log the details in postgresql log file with “FATAL: terminating connection

Re: Huge tables, trying to delete OID's taking 6+hours per table

Tory M Blue writes: > The command i'm using is > ALTER TABLE tablename SET WITHOUT OIDS; > Would a drop column oid be better? Unfortunately, you're kind of stuck. OIDs are not like regular columns (at least before v12) --- they are integrated into the tuple header in a hackish way, and so there'

idle_in_transaction_session_timeout not getting log in PG v12.2

Hi Team, I have set idle_in_transaction_session_timeout on user level and it is set for 5 min. I can see after 5 min the session with "idle in transaction" state got killed and it log the details in postgresql log file with "FATAL: terminating connection due to idle-in-transaction timeout" in v

Re: Hot and PITR backups in same server

Hi! Thank you. As Laurentz said, even if you set it to 5TB, no WAL files older than the files needed by the last checkpoint don't remain. If you don't need a hot-standby, you can use pg_receivewal to save WAL files on the "standby" server. If you need the hot-standby, WAL files should be archi

Re: Huge tables, trying to delete OID's taking 6+hours per table

On Tue, May 19, 2020 at 12:17 AM Tory M Blue wrote: > > The db is only 2TB, these tables are about 50% of the DB. > > > Just wondering what I can boost to give me some elevated temp performance > for what I would think would be a fairly quick operation. > > Give this query more work_mem? Mine are

Logical replication troubles

Hi, I have run into a (to me) weird issue with logical replication. We are running Zalandos postgres-operator in our Kubernetes clusters and have recently had a use-case where we wanted to start doing logical replication of select tables to a data warehouse, also running postgres. It worked as exp

Huge tables, trying to delete OID's taking 6+hours per table

The db is only 2TB, these tables are about 50% of the DB. Just wondering what I can boost to give me some elevated temp performance for what I would think would be a fairly quick operation. Give this query more work_mem? Mine are set pretty low, based on previous performance notes, this is a 9.5

Re: Hot and PITR backups in same server

At Mon, 18 May 2020 11:11:44 +0300, "Andrus" wrote in > Hi! > > >This is confused or at least confusing. > >- "max_wal_size" of 5TB is clearly insane. As Laurentz said, even if you set it to 5TB, no WAL files older than the files needed by the last checkpoint don't remain. If you don't need a h