cpu-intensive immutable function and parallel scan

[Niels Jespersen]

Hello all

I have a cpu-intensive plpython3u function that computes a result on the value 
from a single column value from a simple select.This looks largely like this.

select function_name (t1.val1, 'constant1') from t1 where t1.p = '202012_1' and 
t1.val1 is not null;

The function is marked immutable and parallel safe.

The time spent in the function is quite substantial. The same execution plan 
for a query that invokes the funtion and a query that does not results in a 
15-times slowdown. Explain analyze show that both queries use 5 parallel 
workers.

I think I could achieve some speedup by parallelizing further, distributing the 
cpu-work among additional workers. But, how do I achive that best?

In Oracle I would either use a parallel-hint or an alter session force parallel 
query parallel 8.

Regards Niels

Re: cpu-intensive immutable function and parallel scan

[David Rowley]

On Tue, 22 Jun 2021 at 19:06, Niels Jespersen  wrote:
> I think I could achieve some speedup by parallelizing further, distributing 
> the cpu-work among additional workers. But, how do I achive that best?

You'll want to ensure max_parallel_workers_per_gather is set high
enough and you have max_parallel_workers set to something high enough.
You can then do:

alter table t1 set (parallel_workers = );

David

Postgres PANIC when it could not open file in pg_logical/snapshots directory

[Mike Yeap]

Hi all,

I have a Postgres version 11.11 configured with both physical replication
slots (for repmgr) as well as some logical replication slots (for AWS
Database Migration Service (DMS)). This morning, the server went panic with
the following messages found in the log file:

2021-06-22 04:56:35.314 +08 [PID=19457 application="[unknown]"
user_name=dms database=** host(port)=**(48360)] PANIC:  could not open file
"pg_logical/snapshots/969-FD606138.snap": Operation not permitted

2021-06-22 04:56:35.317 +08 [PID=1752 application="" user_name= database=
host(port)=] LOG:  server process (PID 19457) was terminated by signal 6:
Aborted

2021-06-22 04:56:35.317 +08 [PID=1752 application="" user_name= database=
host(port)=] LOG:  terminating any other active server processes


The PG server then terminates all existing PG processes.

The process with 19457 is from one of the DMS replication tasks, I have no
clue why it suddenly couldn't open a snapshot file. I checked the server
load and file systems and didn't find anything unusual at that time.

Appreciate if you can give me some guidance on troubleshooting this issue

Thanks

Regards,
Mike Yeap

second CTE kills perf

[Nicolas Seinlet]

Hello,

I'm trying to understand this behaviour and the limits of CTE, when they reach 
the limits, when they cannot receive parameters from a caller, ... I'm running 
a query on PostgreSQL 10 with a cte. the query runs in ~ 10 seconds. If I add a 
second CTE with the same query as the previous one and select * from second_cte 
as query, it now runs in ~ 10 minutes.

oversimplified example:
10 seconds version:
| WITH cte1 AS (SELECT x,y,z FROM table) SELECT row_number() over(),x,y,z FROM 
cte1 WHERE x=32;

10 minutes version:
| WITH cte1 AS (SELECT x,y,z FROM table), cte2 AS (SELECT row_number() 
over(),x,y,z FROM cte1) SELECT * FROM cte2 WHERE x=32;

The real examples, with query plans:
https://explain.dalibo.com/plan/98A
https://explain.dalibo.com/plan/o6X4

Thanks for your time,

Nicolas Seinlet.

publickey - nicolas@seinlet.com - 0xCAEB7FAF.asc
Description: application/pgp-keys


signature.asc
Description: OpenPGP digital signature

Re: Postgres PANIC when it could not open file in pg_logical/snapshots directory

[Vijaykumar Jain]

On Tue, 22 Jun 2021 at 13:32, Mike Yeap  wrote:

> Hi all,
>
> I have a Postgres version 11.11 configured with both physical replication
> slots (for repmgr) as well as some logical replication slots (for AWS
> Database Migration Service (DMS)). This morning, the server went panic with
> the following messages found in the log file:
>
> 2021-06-22 04:56:35.314 +08 [PID=19457 application="[unknown]"
> user_name=dms database=** host(port)=**(48360)] PANIC:  could not open file
> "pg_logical/snapshots/969-FD606138.snap": Operation not permitted
>
> 2021-06-22 04:56:35.317 +08 [PID=1752 application="" user_name= database=
> host(port)=] LOG:  server process (PID 19457) was terminated by signal 6:
> Aborted
>
> 2021-06-22 04:56:35.317 +08 [PID=1752 application="" user_name= database=
> host(port)=] LOG:  terminating any other active server processes
>

Are you sure there is nothing else, do you see anything in
/var/log/kern.log or dmesg logs.
 i just did a small simulation of logical replication from A -> B, i
deleted one of the snapshots live, i also changed permissions to make it RO
my server did not crash at all. (pg14beta though) although i can try other
things to check at pg layer, but if something else externally has happened,
it would be difficult to reproduce.
pardon me for speculating, but
Is it network storage? did the underlying storage layer have a blip of some
kind?
are the mounts fine? are they readonly or were temporarily readonly ?
no bad hardware ?
If none of the above, did the server restart solve the issue? or is it
broken still, unable to start?


> The PG server then terminates all existing PG processes.
>
> The process with 19457 is from one of the DMS replication tasks, I have no
> clue why it suddenly couldn't open a snapshot file. I checked the server
> load and file systems and didn't find anything unusual at that time.
>
> Appreciate if you can give me some guidance on troubleshooting this issue
>
> Thanks
>
> Regards,
> Mike Yeap
>

is it crashing and dumping cores?
can you strace the postmaster on its startup to check what it going on ?

I can share my demo setup, but it would be too noisy in the thread, but can
do it later if you want.
the above assumptions are based on repmgnr and AWS do not interfere in your
primary server internals, just failover and publication.


-- 
Thanks,
Vijay
Mumbai, India

Re: replace inside regexp_replace

[Oliver Kohll]

On Mon, 21 Jun 2021 at 15:09, Francisco Olarte 
wrote:

> Oliver:
>
> On Mon, Jun 21, 2021 at 3:27 PM Oliver Kohll 
> wrote:
> ...
> > My attempt to do that is the regex
> > select regexp_replace(
> > 'here is [[my text]] to replace and [[some more]]',
> > E'\\[\\[(.*?)\\]\\]',
> > replace(E'\\1', ' ', '_'),
> > 'g'
> > );
> > which results in
> > 'here is my text to replace and some more'
>
> > It half works, i.e. it removes the brackets but doesn't seem to process
> the inner replace. It's as if the select were just
> > select regexp_replace(
> > 'here is [[my text]] to replace and [[some more]]',
> > E'\\[\\[(.*?)\\]\\]',
> > E'\\1',
> > 'g'
> > );
>
> > I've a feeling I'm missing something fundamental, any idea what?
>
> You are assuming replace will magically work in a way it does not. The
> inner replace is evaluated first:
>
> > select replace(E'\\1', ' ', '_');
>  replace
> -
>  \1
>
> and it's result is passed as 3rd argument to the outer replace, so
> both select are equivalent.
>
> What you want to do can be done in some languages passing a closure,
> or a function, to their replace function, or with special forms ( like
> the e modifier in perl s/// ), but I'm not sure it can be done.
>
> On languages with basic regex support, like I think SQL is, you
> normally have to either split the string in match/no match or do a
> multiple match ( match something like (.*?)\[\[(.*?)\]\]  with two
> captures ) and loop in the result aplying your second replacement (
> which is what perl does behind the scenes, and other languages do )
>
> In perl you can do it with something like:
>
> $ perl -pe 's{\[\[(.*?)\]\]}{ $1=~s/ /_/gr}eg'
> here is [[my text]] to replace and [[some more]]',
> here is my_text to replace and some_more',
>
> But note the magic e there.
>
> In python you can use the function form:
>
> re.sub(pattern, repl, string, count=0, flags=0)
>
> Return the string obtained by replacing ..repl can be a string or
> a function; if it is a string,
> If repl is a function, it is called for every non-overlapping
> occurrence of pattern. The function takes a single match object
> argument, and returns the replacement string.
>
> An so on on other languages, but in sql
>
> regexp_replace ( string text, pattern text, replacement text [, flags
> text ] ) → text
>
> The replacement is a plain text ( and AFAIK you cannot use functions
> as values in sql ).
>
> You could probably define your function doing that if you have any PL
> installed in your DB.
>
> Francisco Olarte.
>

Right, thanks, I have a better understanding now. The calling app is
written in Java so I will write a routine there to do it instead.

Cheers
Oliver

Highly Available Postgres on Kubernetes

[Venkat Pathi]

Hello Users,

Our application is in Kubernetes and currently, Standalone Postgres Pod is
deployed without HA.
I am tasked to implement a solution for Postgres HA on kubernetes.
I was wondering, what is the most popular and active solution out there
which provides HA for Postgres on Kubernetes with ease of use and
maintenance(updates, upgrades)

I came across a few possible solutions

   1. Patroni
   2. CrunchyData-Postgres Operator
   3. KubeDB
   4. Stolon
   5. Bitnami Postgres HA helm chart


Not sure which one to go with, any inputs are much appreciated

Thanks,
Venkat

Re: second CTE kills perf

[Vijaykumar Jain]

On Tue, 22 Jun 2021 at 13:50, Nicolas Seinlet  wrote:

> Hello,
>
> oversimplified example:
> 10 seconds version:
> | WITH cte1 AS (SELECT x,y,z FROM table) SELECT row_number() over(),x,y,z
> FROM cte1 WHERE x=32;
>
> 10 minutes version:
> | WITH cte1 AS (SELECT x,y,z FROM table), cte2 AS (SELECT row_number()
> over(),x,y,z FROM cte1) SELECT * FROM cte2 WHERE x=32;
>
>
A simplified setup of the above issue.
I was able to reproduce this
postgres=# -- create table t(id int primary key, name text);
postgres=# -- insert into t select x, x::text from generate_series(1,
100) x;


--pushdown
postgres=# explain (analyze,buffers) with cte as (select * from t) select
*, row_number() over () from cte where id = 10;
  QUERY PLAN
-
 WindowAgg  (cost=0.42..8.46 rows=1 width=18) (actual time=0.014..0.016
rows=1 loops=1)
   Buffers: shared hit=4
   ->  Index Scan using t_pkey on t  (cost=0.42..8.44 rows=1 width=10)
(actual time=0.010..0.011 rows=1 loops=1)
 Index Cond: (id = 10)
 Buffers: shared hit=4
 Planning Time: 0.074 ms
 Execution Time: 0.029 ms
(7 rows)

--no pushdown
postgres=# explain (analyze,buffers) with cte as (select * from t), cte2 as
(select *, row_number() over () from cte) select * from cte2 where id = 10;
   QUERY PLAN
-
 Subquery Scan on cte2  (cost=0.00..40405.00 rows=1 width=18) (actual
time=0.017..224.461 rows=1 loops=1)
   Filter: (cte2.id = 10)
   Rows Removed by Filter: 99
   Buffers: shared hit=609 read=4796
   ->  WindowAgg  (cost=0.00..27905.00 rows=100 width=18) (actual
time=0.012..185.554 rows=100 loops=1)
 Buffers: shared hit=609 read=4796
 ->  Seq Scan on t  (cost=0.00..15405.00 rows=100 width=10)
(actual time=0.007..45.168 rows=100 loops=1)
   Buffers: shared hit=609 read=4796
 Planning Time: 0.068 ms
 Execution Time: 224.479 ms
(10 rows)

-- without aggregate, pushdown works even with multiple ctes
(analyze,buffers) with cte as (select * from t), cte2 as (select * from cte
where id < 100) select * from cte2 where id = 10;
 QUERY PLAN
---
 Index Scan using t_pkey on t  (cost=0.42..8.45 rows=1 width=10) (actual
time=0.005..0.006 rows=1 loops=1)
   Index Cond: ((id < 100) AND (id = 10))
   Buffers: shared hit=4
 Planning:
   Buffers: shared hit=4
 Planning Time: 0.074 ms
 Execution Time: 0.015 ms
(7 rows)

--with window aggregate, even at the top cte, predicate is not applied
 explain (analyze,buffers) with cte as (select *, row_number() over () from
t), cte2 as (select * from cte where id < 100)  select * from cte2 where id
= 10;
   QUERY PLAN
-
 Subquery Scan on cte  (cost=0.00..42905.00 rows=1 width=18) (actual
time=0.013..226.454 rows=1 loops=1)
   Filter: ((cte.id < 100) AND (cte.id = 10))
   Rows Removed by Filter: 99
   Buffers: shared hit=673 read=4732
   ->  WindowAgg  (cost=0.00..27905.00 rows=100 width=18) (actual
time=0.009..187.550 rows=100 loops=1)
 Buffers: shared hit=673 read=4732
 ->  Seq Scan on t  (cost=0.00..15405.00 rows=100 width=10)
(actual time=0.005..44.613 rows=100 loops=1)
   Buffers: shared hit=673 read=4732
 Planning Time: 0.055 ms
 Execution Time: 226.468 ms

--without cte predicate is applied before window aggregate ?
postgres=# explain (analyze,buffers) select *, row_number() over () from t
where id = 10;
   QUERY PLAN
-
 WindowAgg  (cost=0.42..8.46 rows=1 width=18) (actual time=0.018..0.020
rows=1 loops=1)
   Buffers: shared hit=4
   ->  Index Scan using t_pkey on t  (cost=0.42..8.44 rows=1 width=10)
(actual time=0.013..0.014 rows=1 loops=1)
 Index Cond: (id = 10)
 Buffers: shared hit=4
 Planning Time: 0.053 ms
 Execution Time: 0.037 ms
(7 rows)


Thank you for raising this, I hope i'll gain something here.

-- 
Thanks,
Vijay
Mumbai, India

Re: cpu-intensive immutable function and parallel scan

[Tom Lane]

David Rowley  writes:
> On Tue, 22 Jun 2021 at 19:06, Niels Jespersen  wrote:
>> I think I could achieve some speedup by parallelizing further, distributing 
>> the cpu-work among additional workers. But, how do I achive that best?

> You'll want to ensure max_parallel_workers_per_gather is set high
> enough and you have max_parallel_workers set to something high enough.
> You can then do:
> alter table t1 set (parallel_workers = );

Also, if you don't have a fairly high COST value set on the function,
try raising that to make the planner understand that it's expensive.
My recollection is that the amount of parallelism it goes for is partly
predicated on the estimated query cost.

regards, tom lane

Re: second CTE kills perf

[Tom Lane]

Nicolas Seinlet  writes:
> I'm trying to understand this behaviour and the limits of CTE, when they 
> reach the limits, when they cannot receive parameters from a caller, ... I'm 
> running a query on PostgreSQL 10 with a cte. the query runs in ~ 10 seconds. 
> If I add a second CTE with the same query as the previous one and select * 
> from second_cte as query, it now runs in ~ 10 minutes.

> oversimplified example:
> 10 seconds version:
> | WITH cte1 AS (SELECT x,y,z FROM table) SELECT row_number() over(),x,y,z 
> FROM cte1 WHERE x=32;

> 10 minutes version:
> | WITH cte1 AS (SELECT x,y,z FROM table), cte2 AS (SELECT row_number() 
> over(),x,y,z FROM cte1) SELECT * FROM cte2 WHERE x=32;

[ shrug... ]  You are asking for two different computations, and the
second one is far more expensive.

In the first case, the WHERE x=32 clause is applied before the window
function, so we can (indeed must) filter out all rows not having x=32
before doing the window function.

In the second case, WHERE x=32 is applied above/after the window
function.  We cannot push down the WHERE to before the window function.
(In this case, filtering beforehand would obviously change the results
of row_number, but in general we don't know enough about window function
behavior to risk such changes.)  So row_number has to be computed over
the entire contents of the "table", and that's not cheap.

It does surprise me a bit that row_number is quite *that* expensive,
but if you are expecting equivalent results from these two queries,
you're simply wrong.

regards, tom lane

www.postgresql-archive.org content deleted

[Vijaykumar Jain]

Ok, i am not sure if this is the right place to post this,

I am seeing a lot of messages from archives, shown as deleted.
PostgreSQL - performance - Estimating wal_keep_size | Threaded View
(postgresql-archive.org)


if i open any old archive thread, it says content deleted by the author.
*CONTENTS DELETED*
The author has deleted this message.

Is
this due to some activity/ intentional ?


-- 
Thanks,
Vijay
Mumbai, India

Re: www.postgresql-archive.org content deleted

[Magnus Hagander]

On Tue, Jun 22, 2021 at 7:59 PM Vijaykumar Jain
 wrote:
>
> Ok, i am not sure if this is the right place to post this,
>
> I am seeing a lot of messages from archives, shown as deleted.
> PostgreSQL - performance - Estimating wal_keep_size | Threaded View 
> (postgresql-archive.org)
>
> if i open any old archive thread, it says content deleted by the author.
> CONTENTS DELETED
> The author has deleted this message.
>
> Is this due to some activity/ intentional ?


This site is not affiliated with the PostgreSQL project in any way,
it's an independent third party.

The official PostgreSQL archives are on https://www.postgresql.org/list/


-- 
 Magnus Hagander
 Me: https://www.hagander.net/
 Work: https://www.redpill-linpro.com/

Re: www.postgresql-archive.org content deleted

[Tom Lane]

Vijaykumar Jain  writes:
> Ok, i am not sure if this is the right place to post this,
> I am seeing a lot of messages from archives, shown as deleted.
> PostgreSQL - performance - Estimating wal_keep_size | Threaded View
> (postgresql-archive.org)
> 
> if i open any old archive thread, it says content deleted by the author.
> *CONTENTS DELETED*
> The author has deleted this message.
> Is
> this due to some activity/ intentional ?

As it says right on the front page of that site:

This is a postgreSQL mailing list archive and forum provided by
Nabble.  It is not an official postgresql website.

You'd have to ask Nabble.  The PG community has nothing to do with
that site.  The community's mail archives are at

https://www.postgresql.org/list/

regards, tom lane

Re: www.postgresql-archive.org content deleted

[Vijaykumar Jain]

On Tue, 22 Jun 2021 at 23:31, Magnus Hagander  wrote:

This site is not affiliated with the PostgreSQL project in any way,
> it's an independent third party.
>
> The official PostgreSQL archives are on https://www.postgresql.org/list/
>
>
coola, many thanks.
that's good to hear.

Re: www.postgresql-archive.org content deleted

[Vijaykumar Jain]

i get it now, thanks.,
anyways, it does seem to be under serious attack.

Exclusion constraint with custom operator not working as expected

[Rhys A.D. Stewart]

Greetings All,

Firstly, apologies for cross posting.
I would like to create a table which will contain postGIS geometries,
specifically linestrings.  Each line string should be unique, unique in the
sense that no linestring should st_equals any other. (see
https://postgis.net/docs/manual-3.1/ST_Equals.html)

So, LINESTRING(10 10, 50 50) and LINESTRING(50 50, 10 10) are "st_equal".

 I did the following:

BEGIN;

DROP OPERATOR IF EXISTS |*| (geometry, geometry) CASCADE;

CREATE OPERATOR |*| (
FUNCTION = st_equals,
LEFTARG = geometry,
RIGHTARG = geometry,
COMMUTATOR = |*|
);

CREATE OPERATOR CLASS my_ops FOR TYPE geometry
USING gist FAMILY gist_geometry_ops_2d AS
OPERATOR 99 |*| (geometry, geometry);

-- This returns True
SELECT  'LINESTRING(10 10, 50 50)'::geometry |*| 'LINESTRING(50 50, 10
10)'::geometry;

DROP TABLE IF EXISTS test_1 ;
CREATE TABLE test_1 (
fid integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
g geometry,
EXCLUDE USING GIST (g WITH |*|)
);

INSERT INTO test_1 (g) VALUES ('LINESTRING(10 10, 50 50)') ON CONFLICT DO
NOTHING;
INSERT INTO test_1 (g) VALUES ('LINESTRING(50 50, 10 10)') ON CONFLICT DO
NOTHING; -- This should do nothing;

SELECT fid, st_astext(g) FROM test_1; -- both rows returned, exclusion
doesn't work as I think it should.

ROLLBACK;

But where I expected the second insert to 'DO NOTHING', it very much did
something. So clearly I am missing something somewhere or my understanding
of exclusion constraints is lacking...or both.  Any suggestions to get the
desired outcome? (Using a trigger doesn't count :-D )

But
Rhys
Peace & Love | Live Long & Prosper

SV: cpu-intensive immutable function and parallel scan

[Niels Jespersen]

>Fra: David Rowley  Sendt: 22. juni 2021 09:10
>Emne: Re: cpu-intensive immutable function and parallel scan
>On Tue, 22 Jun 2021 at 19:06, Niels Jespersen  wrote:

>> I think I could achieve some speedup by parallelizing further, distributing 
>> the cpu-work among additional workers. But, how do I achive that best?
>You'll want to ensure max_parallel_workers_per_gather is set high enough and 
>you have max_parallel_workers set to something high enough.
>You can then do:
>alter table t1 set (parallel_workers = );

Thank you. That helped. A lot. 
/Niels