Re: Subscription stuck at initialize state

Hi Vijaykumar, I checked the pg_subscription_rel and all the tables in that subscription are in the state - i (initialize). I also tried creating a new publication on the source DB with just one table and tried to subscribe it, it doesn't work either. However, when I try to subscribe it on some

Re: Subscription stuck at initialize state

On Thu, Feb 3, 2022, 10:32 AM Abhishek Bhola wrote: > So far I figured out that the problem is on the subscriber side. > The same publication, when subscribed to on another DB, works fine. > Also noticed that the remote_lsn value on the target DB is still 0/0. > > targetdb=# select * from

Re: Undetected Deadlock

Hi again Some good news. After some more debugging & reflection, I realized that the likely cause is one of our own libraries that gets loaded as part of some custom functions we are using. Some of these functions trigger fetching of remote resources, for which a timeout is set using `alarm`.

Re: Can Postgres beat Oracle for regexp_count?

Shaozhong SHI writes: > The following has been attempted but no luck. > select regexp_matches('My High Street', '([A-Z][a-z]+[\s]*)+', 'g') > It is intended to match 'My High Street, but it turned out only 'Street' > was matched. You've got the parentheses in the wrong place, ie inside not

Re: Can Postgres beat Oracle for regexp_count?

On Wed, Feb 2, 2022 at 10:26 PM Shaozhong SHI wrote: > > select regexp_matches('My High Street', '([A-Z][a-z]+[\s]*)+', 'g') > It is intended to match 'My High Street, but it turned out only 'Street' > was matched. > > I'm too tired to find the documentation for why you saw your result but

Re: Can Postgres beat Oracle for regexp_count?

Hi, Tom, Lane, On Wed, 2 Feb 2022 at 22:26, Tom Lane wrote: > "David G. Johnston" writes: > > Given we don't have a regexp_count function this isn't surprising... > > FYI, it's there in HEAD. > > In the meantime, you could possibly do something like > > =# select count(*) from

Re: Subscription stuck at initialize state

So far I figured out that the problem is on the subscriber side. The same publication, when subscribed to on another DB, works fine. Also noticed that the remote_lsn value on the target DB is still 0/0. targetdb=# select * from pg_replication_origin_status ; local_id | external_id |

Re: Subscription stuck at initialize state

Update: Tried dropping and recreating publication on the source DB and subscription still won't move ahead. Not sure what I am missing. On Wed, Feb 2, 2022 at 1:20 PM Abhishek Bhola < abhishek.bh...@japannext.co.jp> wrote: > The only statement I see on the target DB log is > "logical replication

Re: increasing effective_cache_size slows down join queries by a factor of 4000x

On 2/2/22 22:10, Artyom Shaposhnikov wrote: the row estimate became ~1000x smaller with the stat in place, so it looks like it grossly miscalculates the query plans without the stats for large tables representing M:M relations. Well, if the estimates are significantly off (and 3 orders of

Re: Can Postgres beat Oracle for regexp_count?

"David G. Johnston" writes: > Given we don't have a regexp_count function this isn't surprising... FYI, it's there in HEAD. In the meantime, you could possibly do something like =# select count(*) from regexp_matches('My High Street', '([A-Z][a-z]+[\s])', 'g'); count --- 2 (1 row)

Re: increasing effective_cache_size slows down join queries by a factor of 4000x

> Question: are there other ways to give PostgreSQL a hint What you your pg version? Maybe with pg_hint_plan extension ( https://github.com/ossc-db/pg_hint_plan ) "pg_hint_plan makes it possible to tweak PostgreSQL execution plans using so-called "hints" in SQL comments, like /*+ SeqScan(a) */."

Re: increasing effective_cache_size slows down join queries by a factor of 4000x

the row estimate became ~1000x smaller with the stat in place, so it looks like it grossly miscalculates the query plans without the stats for large tables representing M:M relations. On Wed, Feb 2, 2022 at 11:47 AM Michael Lewis wrote: > > What does the row estimate look like on the scan of

Re: Can Postgres beat Oracle for regexp_count?

On Wed, Feb 2, 2022 at 1:20 PM Shaozhong SHI wrote: > It has been found that regexp_count works brilliantly in Oracle. > What query exactly did you execute in Oracle that you wish to see if an equivalent can be formulated in PostgreSQL? > > However, it is not easy to replicate that in

Can Postgres beat Oracle for regexp_count?

It has been found that regexp_count works brilliantly in Oracle. However, it is not easy to replicate that in Postgres. The following codes have been experimented but without any luck. select regexp_matches('My High Street', '([A-Z][a-z]+[\s])', 'g') select regexp_matches('My High Street',

Re: increasing effective_cache_size slows down join queries by a factor of 4000x

What does the row estimate look like on the scan of data table with that statistic in place? Anytime the stats give a mis-estimate this far off, I wouldn't expect that plans would be optimal except by luck. Index Scan using data_pkey on data t (cost=0.57..21427806.53 *rows=58785023* width=131)

increasing effective_cache_size slows down join queries by a factor of 4000x

I recently moved a postgres DB to a more powerful server with 1TB of RAM instead of 64GB before. To my surprise after running the tuning on the new server, the performance of joins deteriorated by 4000x compared to the old server. I carefully examined all of the changes and found the culprit: if

Re: pg_basebackup with hostssl ?

Sent with ProtonMail Secure Email. ‐‐‐ Original Message ‐‐‐ On Wednesday, February 2nd, 2022 at 17:20, Adrian Klaver wrote: > > Before you do that I would establish that you are connecting to the > > correct Postgres instance. > > Good news, all up and running ! The new

Re: Regular Expression For Duplicate Words

It's an interesting question. But I also don't know how to do it in PostgreSQL. But I figured out alternative solutions. GNU Grep:grep -E '(hello)[[:blank:]]+\1' <<<'one hello hello world' ripgrep: rg '(hello)[[:blank:]]+\1' --pcre2 <<<'one hello hello world' On Wed, Feb 2, 2022 at

Re: pg_basebackup with hostssl ?

On 2/2/22 09:18, Laura Smith wrote: Seems like I was looking for the wrong words on the right page ! Thanks, will try that. Before you do that I would establish that you are connecting to the correct Postgres instance. -- Adrian Klaver adrian.kla...@aklaver.com

Re: pg_basebackup with hostssl ?

Sent with ProtonMail Secure Email. ‐‐‐ Original Message ‐‐‐ On Wednesday, February 2nd, 2022 at 16:50, Adrian Klaver wrote: > Not completely: > > https://www.postgresql.org/docs/current/app-pgbasebackup.html > > -d connstr > > --dbname=connstr > > Specifies parameters used to

Re: pg_basebackup with hostssl ?

On Wed, Feb 2, 2022 at 9:37 AM Laura Smith < n5d9xq3ti233xiyif...@protonmail.ch> wrote: > The point I am making is that pg_basebackup is connecting without > encryption and I want to know how to tell it to use encryption. The docs > are silent on the subject. > It is a client application that

Re: pg_basebackup with hostssl ?

On 2/2/22 08:37, Laura Smith wrote: Sent with ProtonMail Secure Email. ‐‐‐ Original Message ‐‐‐ On Wednesday, February 2nd, 2022 at 16:30, Adrian Klaver wrote: I am not following. pg_basebackup is a stand alone client that is not involved in replication other then establishing a

Re: pg_basebackup with hostssl ?

Forgot to add that I also have : "hostsslallall10.0.0.0/8md5"

Re: pg_basebackup with hostssl ?

Sent with ProtonMail Secure Email. ‐‐‐ Original Message ‐‐‐ On Wednesday, February 2nd, 2022 at 16:30, Adrian Klaver wrote: > I am not following. pg_basebackup is a stand alone client that is not > > involved in replication other then establishing a starting point from > > which to

Re: pg_basebackup with hostssl ?

On 2/2/22 07:48, Laura Smith wrote: I've had a quick glance through the man page for pg_basebackup but can't see any flags to set ssl. Long story short, I've got the following in pg_hba.conf of the master: hostsslreplicationall10.0.0.0/8md5 But the slave is complaining: FATAL:

pg_basebackup with hostssl ?

I've had a quick glance through the man page for pg_basebackup but can't see any flags to set ssl. Long story short, I've got the following in pg_hba.conf of the master: hostsslreplicationall10.0.0.0/8md5 But the slave is complaining: FATAL: no pg_hba.conf entry for replication

Re: Regular Expression For Duplicate Words

On Wed, Feb 2, 2022 at 1:00 AM Shaozhong SHI wrote: > This link is interesting. > > regex - Regular Expression For Duplicate Words - Stack Overflow > > > Is there any example in Postgres? > > Not that I'm

Regular Expression For Duplicate Words

This link is interesting. regex - Regular Expression For Duplicate Words - Stack Overflow Is there any example in Postgres? Regards, David