create extension postgis raises out of memory if pg_store_plans in installed

[danielwestermann]

Hi,

on RedHat 7.7:

[root@rhel77 ~]# cat /etc/redhat-release 
Red Hat Enterprise Linux Server release 7.7 (Maipo)

... with this packages installed:

[root@rhel77 ~]# rpm -qa | egrep "postgres|postgis|store_plan"
postgresql12-server-12.3-5PGDG.rhel7.x86_64
postgresql12-contrib-12.3-5PGDG.rhel7.x86_64
postgresql12-12.3-5PGDG.rhel7.x86_64
postgis30_12-3.0.1-5.rhel7.x86_64
pg_store_plans12-1.4-1.el7.x86_64
postgresql12-libs-12.3-5PGDG.rhel7.x86_64
postgresql12-llvmjit-12.3-5PGDG.rhel7.x86_64

Once pg_store_plans is in shared_preload_libraries this does not work anymore:

-bash-4.2$ psql -p 5500
psql (12.3)
Type "help" for help.

postgres=# select version();
                                                 version                                                 
---------------------------------------------------------------------------------------------------------
 PostgreSQL 12.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
(1 row)

postgres=# \dx
                                     List of installed extensions
        Name        | Version |   Schema   |                        Description                        
--------------------+---------+------------+-----------------------------------------------------------
 pg_buffercache     | 1.3     | public     | examine the shared buffer cache
 pg_stat_statements | 1.7     | public     | track execution statistics of all SQL statements executed
 pg_store_plans     | 1.4     | public     | track plan statistics of all SQL statements executed
 pgstattuple        | 1.5     | public     | show tuple-level statistics
 plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language
(5 rows)

postgres=# show shared_preload_libraries ;
 shared_preload_libraries 
--------------------------
 pg_store_plans
(1 row)

postgres=# create extension postgis;
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.

OOM kicks in and kills the process. Can someone please have a look at this?

Thanks in advance
Daniel

[kasaharatt]

Hi.
Thanks for your report. I confirmed and could reproduce this problem.

When issue CREATE EXTENSION, pg_store_plans parses the query string for entire script per execute each query in the script. So, consume huge memory and killed by OOM-Killer.
This problem became apparent because the PostGIS script contains a lot of queries

Since the execution plans of the queries executed by CREATE/ALTER EXTENSION is not very important, we may want to ignore getting the execution plan of these operations.

I'll PR the patch to solve this problem.
Best regards,

[danielwestermann]

Hi Kasahara, thanks for working on that. Best regards Daniel

…

________________________________ From: Kasahara Tatsuhito <[email protected]> Sent: Thursday, August 6, 2020 09:05 To: ossc-db/pg_store_plans <[email protected]> Cc: Daniel Westermann (DWE) <[email protected]>; Author <[email protected]> Subject: Re: [ossc-db/pg_store_plans] create extension postgis raises out of memory if pg_store_plans in installed (#7) Hi. Thanks for your report. I confirmed and could reproduce this problem. When issue CREATE EXTENSION, pg_store_plans parses the query string for entire script per execute each query in the script. So, consume huge memory and killed by OOM-Killer. This problem became apparent because the PostGIS script contains a lot of queries Since the execution plans of the queries executed by CREATE/ALTER EXTENSION is not very important, we may want to ignore getting the execution plan of these operations. I'll PR the patch to solve this problem. Best regards, — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub<#7 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/ADJZHGKMQIRJDTXTQ2RTGMTR7JI5NANCNFSM4PTFJUFA>.