Re: Broken logical replication

Hi experts! I didn't notice html tags in previous message. Sorry for that. Hope that was the only reason of no answers :) Original message was: Giving 2 postgres servers: 1. Master -  PostgreSQL 10.16 2. Slave - PostgreSQL 13.5 Logical replication was configured and worked fine between them. At

Re: could not open relation with OID

On Wed, Jan 26, 2022 at 05:30:01PM -0800, Ben Chobot wrote: > We do a lot of queries per day, over a lot of hosts, all of which are on > 12.9. We've recently started doing a better job at analyzing our db logs and > have found that, a few times a day, every day, we see some of our queries > fail wi

Re: PostgreSQL Management and monitoring tool

On 1/26/22 22:24, Lucas wrote: Thanks… But I was just hoping that someone would share their solution in a more depth way… For example: Having Zabbix to monitor the database is nice, but it does not allow you to manage Replication, backups and more from the web, like awide.io

Re: PostgreSQL Management and monitoring tool

> On 27/01/2022, at 5:01 PM, Rob Sargent wrote: > > On 1/26/22 20:24, Lucas wrote: >> >>> On 27/01/2022, at 3:00 PM, Bruce Momjian >> > wrote: >>> >>> On Thu, Jan 27, 2022 at 02:28:17PM +1300, Lucas wrote: Hi guys. I’m migrating a few databases to PG 14

Re: PostgreSQL Management and monitoring tool

On 1/26/22 20:24, Lucas wrote: On 27/01/2022, at 3:00 PM, Bruce Momjian wrote: On Thu, Jan 27, 2022 at 02:28:17PM +1300, Lucas wrote: Hi guys. I’m migrating a few databases to PG 14 and was wondering that it would be very nice to have a tool to help me monitor and manage my databases. I f

Re: PostgreSQL Management and monitoring tool

> On 27/01/2022, at 3:00 PM, Bruce Momjian wrote: > > On Thu, Jan 27, 2022 at 02:28:17PM +1300, Lucas wrote: >> Hi guys. >> >> I’m migrating a few databases to PG 14 and was wondering that it would be >> very >> nice to have a tool to help me monitor and manage my databases. I found Awide >> a

Re: Undetected Deadlock

There may be a bug so perhaps still pursue reproducing the issue, but I would expect drop concurrently to resolve your issue with the two processes conflicting. Also, perhaps trying"insert, on conflict do update" could be more efficient than the copy but obviously there are too many unknowns and va

Re: Undetected Deadlock

> I must be missing something. You mentioned dropping a partition, so is there > an actual need for the delete? Could you detach concurrently and then drop > the table or delete rows if needed? The DELETE is part of a transaction performing data loading. Our application allows data to be overwri

Re: PostgreSQL Management and monitoring tool

On Thu, Jan 27, 2022 at 02:28:17PM +1300, Lucas wrote: > Hi guys. > > I’m migrating a few databases to PG 14 and was wondering that it would be very > nice to have a tool to help me monitor and manage my databases. I found Awide > and it looks really great, but it would cost me around $800 monthl

could not open relation with OID

We do a lot of queries per day, over a lot of hosts, all of which are on 12.9. We've recently started doing a better job at analyzing our db logs and have found that, a few times a day, every day, we see some of our queries fail with errors like: could not open relation with OID 201940279 In

PostgreSQL Management and monitoring tool

Hi guys. I’m migrating a few databases to PG 14 and was wondering that it would be very nice to have a tool to help me monitor and manage my databases. I found Awide and it looks really great, but it would cost me around $800 monthly. Do you guys know by any chance a simila

Re: Undetected Deadlock

I must be missing something. You mentioned dropping a partition, so is there an actual need for the delete? Could you detach concurrently and then drop the table or delete rows if needed? https://www.postgresql.org/docs/14/sql-altertable.html#SQL-ALTERTABLE-DETACH-PARTITION

Re: Undetected Deadlock

Hi Alvaro Thanks for the feedback! > What version were you using previously? We were previously on 11.4. Another difference is that we were using inheritance based partitioning before, whereas now we are using declarative partitioning. > Maybe the lock is already taken before the DELETE is run;

Re: Counting the number of repeated phrases in a column

On Tue, Jan 25, 2022 at 11:10 AM Shaozhong SHI wrote: > > There is a short of a function in the standard Postgres to do the following: > > It is easy to count the number of occurrence of words, but it is rather > difficult to count the number of occurrence of phrases. > > For instance: > > A cell

Re: Counting the number of repeated phrases in a column

Am Wed, Jan 26, 2022 at 08:35:06PM + schrieb Shaozhong SHI: > Whatever. Can we try to build a regex for 'The City of London London > Great London UK ' ? Would you be so kind as do be more specific about that "we" ? Best, Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B

Re: Counting the number of repeated phrases in a column

On 1/26/22 13:35, Shaozhong SHI wrote: On Tue, 25 Jan 2022 at 17:10, Shaozhong SHI wrote: There is a short of a function in the standard Postgres to do the following: It is easy to count the number of occurrence of words, but it is rather difficult to count the number of oc

Counting the number of repeated phrases in a column

On Wednesday, January 26, 2022, Shaozhong SHI wrote: > > > Whatever. Can we try to build a regex for 'The City of London London > Great London UK ' ? > Not even if you paid me. I’d probably die before I succeeded if you tortured me. David J.

Re: Counting the number of repeated phrases in a column

On Tue, 25 Jan 2022 at 17:10, Shaozhong SHI wrote: > There is a short of a function in the standard Postgres to do the > following: > > It is easy to count the number of occurrence of words, but it is rather > difficult to count the number of occurrence of phrases. > > For instance: > > A cell of

Could not serialize access due to concurrent update

In this post, I am not asking a question, I am sharing an experience. The application is running on Linux, PostgreSQL 13.5. using Websphere 9 application server. When using "SKIP LOCKED" option, I suddenly started seeing errors like "Could not serialize access due to concurrent update". After s

Re: SELECT with LIKE clause makes full table scan

El día miércoles, enero 26, 2022 a las 11:21:12p. m. +0800, Julien Rouhaud escribió: > Hi, > > On Wed, Jan 26, 2022 at 11:07 PM Matthias Apitz wrote: > > > > We changed two relevant Indexes to > > > > CREATE INDEX d01ort ON d01buch(d01ort bpchar_pattern_ops ); > > CREATE INDEX d01ort2 ON d01buc

Re: SELECT with LIKE clause makes full table scan

Hi, On Wed, Jan 26, 2022 at 11:07 PM Matthias Apitz wrote: > > We changed two relevant Indexes to > > CREATE INDEX d01ort ON d01buch(d01ort bpchar_pattern_ops ); > CREATE INDEX d01ort2 ON d01buch(d01ort2 bpchar_pattern_ops ); When you said changed, did you drop the previous ones? As Tom mention

Re: SELECT with LIKE clause makes full table scan

We changed two relevant Indexes to CREATE INDEX d01ort ON d01buch(d01ort bpchar_pattern_ops ); CREATE INDEX d01ort2 ON d01buch(d01ort2 bpchar_pattern_ops ); and now the same queries are fast. We're looking through our code for more such LIKE clauses on VCHAR columns. Thanks for all the hints

Re: SELECT with LIKE clause makes full table scan

Julien Rouhaud writes: > On Wed, Jan 26, 2022 at 02:34:21PM +0100, Dominique Devienne wrote: >> On Wed, Jan 26, 2022 at 1:13 PM Julien Rouhaud wrote: >>> That index can't be used with a LIKE that has a trailing wildcard. >> So what makes you say that? --DD > This part of the documentation you m

Re: SELECT with LIKE clause makes full table scan

> We face in a PostgreSQL 11.4 installation on a potent Linux host a > ... > Why is this (ignoring the Index) and what could be done? IMHO: 11.4 is very old. ( Release date: 2019-06-20 ) and missing a lot of patches. The latest patch release is 11.14 ( see https://www.postgresql.org/docs/11/rele

Re: SELECT with LIKE clause makes full table scan

Hi, On Wed, Jan 26, 2022 at 02:34:21PM +0100, Dominique Devienne wrote: > On Wed, Jan 26, 2022 at 1:13 PM Julien Rouhaud wrote: > > > > > Why is this (ignoring the Index) and what could be done? > > > [...] > > > create INDEX d01ort on d01buch(d01ort) ;/* D01ORT*/ > > > > That index can't b

Re: SELECT with LIKE clause makes full table scan

On Wed, Jan 26, 2022 at 1:13 PM Julien Rouhaud wrote: > > > > Why is this (ignoring the Index) and what could be done? > > [...] > > create INDEX d01ort on d01buch(d01ort) ;/* D01ORT*/ > > That index can't be used with a LIKE that has a trailing wildcard. Really? That seems to contradict th

Re: SELECT with LIKE clause makes full table scan

Hi, On Wed, Jan 26, 2022 at 12:39:25PM +0100, Matthias Apitz wrote: > > > > sisis=# explain (analyze, buffers) select * from d01buch where d01ort > > > like 'Z 9610%' ; > > > QUERY PLAN > > >

Re: SELECT with LIKE clause makes full table scan

El día miércoles, enero 26, 2022 a las 12:20:08 +0100, Josef Šimánek escribió: > st 26. 1. 2022 v 11:55 odesílatel Matthias Apitz napsal: > > > > > > Hello, > > > > We face in a PostgreSQL 11.4 installation on a potent Linux host a > > serious performance degree. > > > > A SELECT with a LIKE clau

Re: SELECT with LIKE clause makes full table scan

On Wed, Jan 26, 2022 at 11:55 AM Matthias Apitz wrote: > A SELECT with a LIKE clause on a table with ca. 5.200.000 rows on a > column with an Index ignores this and does a full table scan: > > sisis=# explain (analyze, buffers) select * from d01buch where d01ort like 'Z > 9610%' ; > -> Parallel S

Re: SELECT with LIKE clause makes full table scan

st 26. 1. 2022 v 11:55 odesílatel Matthias Apitz napsal: > > > Hello, > > We face in a PostgreSQL 11.4 installation on a potent Linux host a > serious performance degree. > > A SELECT with a LIKE clause on a table with ca. 5.200.000 rows on a > column with an Index ignores this and does a full tab

SELECT with LIKE clause makes full table scan

Hello, We face in a PostgreSQL 11.4 installation on a potent Linux host a serious performance degree. A SELECT with a LIKE clause on a table with ca. 5.200.000 rows on a column with an Index ignores this and does a full table scan: sisis=# explain (analyze, buffers) select * from d01buch where

Re: Counting the number of repeated phrases in a column

On 26.01.2022 11:11, Shaozhong SHI wrote: On Tue, 25 Jan 2022 at 21:33, Ivan Panchenko mailto:i.panche...@postgrespro.ru>> wrote: On 26.01.2022 00:21, benj@laposte.net wrote: > Le 25/01/2022 à 18:10, Shaozhong SHI a écrit : >> There is a sh

Re: Counting the number of repeated phrases in a column

>On Tue, 25 Jan 2022 at 21:33, Ivan Panchenko > >wrote: > > >> >> On 26.01.2022 00:21, benj(dot)dev(at)laposte(dot)net wrote: >> > Le 25/01/2022 à 18:10, Shaozhong SHI a écrit : >> >> There is a short of a function in the standard Postgres to do the >> >> following: >> >> >> >> It is easy to count

Re: Counting the number of repeated phrases in a column

On Tue, 25 Jan 2022 at 21:33, Ivan Panchenko wrote: > > On 26.01.2022 00:21, benj@laposte.net wrote: > > Le 25/01/2022 à 18:10, Shaozhong SHI a écrit : > >> There is a short of a function in the standard Postgres to do the > >> following: > >> > >> It is easy to count the number of occurrence