Thread: BUG #16179: is it reasonable to callback pgss_post_parse_analyze or pg_hint_plan_post_parse_analyze ???

The following bug has been logged on the website:

Bug reference:      16179
Logged by:          DamionZ Zhao
Email address:      zhq651@126.com
PostgreSQL version: 11.5
Operating system:   linux
Description:

When I only configure GUC as shared_preload_libraries = 'pg_hint_plan,
pg_stat_statment'
and have not run sql: 'create extension pg_hint_plan;  create extension
pg_stat_statment',
I expected that parse_analyze will not run into functions
:pgss_post_parse_analyze or pg_hint_plan_post_parse_analyze

In fact , it does not .  I print runtime call stack.
-----------------------------------------------------
statment 1: select 
#0  pgss_post_parse_analyze (pstate=0x2a45818, query=0x2a45928) at
pg_stat_statements.c:812
#1  0x00007fbe60506734 in pg_hint_plan_post_parse_analyze (pstate=0x2a45818,
query=0x2a45928) at pg_hint_plan.c:2767
#2  0x000000000058fbe5 in parse_analyze (parseTree=0x2a45798,
sourceText=0x2a44a90 "select * from abcd;", paramTypes=0x0, numParams=0,
queryEnv=0x0) at analyze.c:124
#3  0x00000000008926b6 in pg_analyze_and_rewrite (parsetree=0x2a45798,
query_string=0x2a44a90 "select * from abcd;", paramTypes=0x0, numParams=0,
queryEnv=0x0)
    at postgres.c:694
#4  0x00000000008938a0 in exec_simple_query (query_string=0x2a44a90 "select
* from abcd;") at postgres.c:1120
#5  0x0000000000897c04 in PostgresMain (argc=1, argv=0x2a73268,
dbname=0x2a73178 "postgres", username=0x2a73158 "postgres") at
postgres.c:4271
#6  0x00000000008021a0 in BackendRun (port=0x2a6b260) at postmaster.c:4405
#7  0x0000000000801934 in BackendStartup (port=0x2a6b260) at
postmaster.c:4077
#8  0x00000000007fe148 in ServerLoop () at postmaster.c:1749
#9  0x00000000007fd936 in PostmasterMain (argc=3, argv=0x2a3cc40) at
postmaster.c:1399
#10 0x0000000000731bab in main (argc=3, argv=0x2a3cc40) at main.c:232
Breakpoint 2, pgss_post_parse_analyze (pstate=0x2a45808, query=0x2a45918) at
pg_stat_statements.c:782
statment 2: create table 
(gdb) bt
#0  pgss_post_parse_analyze (pstate=0x2a45808, query=0x2a45918) at
pg_stat_statements.c:782
#1  0x00007fbe60506734 in pg_hint_plan_post_parse_analyze (pstate=0x2a45808,
query=0x2a45918) at pg_hint_plan.c:2767
#2  0x000000000058fbe5 in parse_analyze (parseTree=0x2a45788,
sourceText=0x2a44a90 "create table abcd\n(i int);", paramTypes=0x0,
numParams=0, queryEnv=0x0)
    at analyze.c:124
#3  0x00000000008926b6 in pg_analyze_and_rewrite (parsetree=0x2a45788,
query_string=0x2a44a90 "create table abcd\n(i int);", paramTypes=0x0,
numParams=0, queryEnv=0x0)
    at postgres.c:694
#4  0x00000000008938a0 in exec_simple_query (query_string=0x2a44a90 "create
table abcd\n(i int);") at postgres.c:1120
#5  0x0000000000897c04 in PostgresMain (argc=1, argv=0x2a73268,
dbname=0x2a73178 "postgres", username=0x2a73158 "postgres") at
postgres.c:4271
#6  0x00000000008021a0 in BackendRun (port=0x2a6b260) at postmaster.c:4405
#7  0x0000000000801934 in BackendStartup (port=0x2a6b260) at
postmaster.c:4077
#8  0x00000000007fe148 in ServerLoop () at postmaster.c:1749
#9  0x00000000007fd936 in PostmasterMain (argc=3, argv=0x2a3cc40) at
postmaster.c:1399
#10 0x0000000000731bab in main (argc=3, argv=0x2a3cc40) at main.c:232
(gdb)

It will degrade performance and may cause the wrong result if it contains
modify operation.

Hi,

On Wed, Dec 25, 2019 at 9:32 AM PG Bug reporting form
<noreply@postgresql.org> wrote:
>
> The following bug has been logged on the website:
>
> Bug reference:      16179
> Logged by:          DamionZ Zhao
> Email address:      zhq651@126.com
> PostgreSQL version: 11.5
> Operating system:   linux
> Description:
>
> When I only configure GUC as shared_preload_libraries = 'pg_hint_plan,
> pg_stat_statment'
> and have not run sql: 'create extension pg_hint_plan;  create extension
> pg_stat_statment',
> I expected that parse_analyze will not run into functions
> :pgss_post_parse_analyze or pg_hint_plan_post_parse_analyze
>
> In fact , it does not .  I print runtime call stack.
> -----------------------------------------------------
> statment 1: select
> #0  pgss_post_parse_analyze (pstate=0x2a45818, query=0x2a45928) at
> pg_stat_statements.c:812
> #1  0x00007fbe60506734 in pg_hint_plan_post_parse_analyze (pstate=0x2a45818,
> query=0x2a45928) at pg_hint_plan.c:2767
> #2  0x000000000058fbe5 in parse_analyze (parseTree=0x2a45798,
> sourceText=0x2a44a90 "select * from abcd;", paramTypes=0x0, numParams=0,
> queryEnv=0x0) at analyze.c:124
> [...]
>
> It will degrade performance and may cause the wrong result if it contains
> modify operation.

This is expected.  The hooks are called as soon as the libraries are
loaded, so configuring shared_preload_libraries (and restarting
postgres) will activate the extensions.  What you should do instead is
disable pg_stat_statements (or other extensions if they have such
possibility) if you want to be able to use it only when required,
without the need to restart postgres.  The CREATE EXTENSION part is
only necessary to access the counters stored in shared memory.

Regarding the performance overhead, it's true that the queryid
generation will still happen even if you disable pg_stat_statements.
However, note that this will be fixed with pg13, see
https://www.postgresql.org/message-id/BN8PR21MB1217B003C4F79DE230AA36B9B1580%40BN8PR21MB1217.namprd21.prod.outlook.com
for more details.