Thread: auto_explain makes CREATE EXTENSION unusable

auto_explain makes CREATE EXTENSION unusable

From
Sandro Santilli
Date:
Running 'CREATE EXTENSION postgis' on my machine after enabling
auto_explain results in the process writing gigabytes of logs
keeping the CPU busy for tens of minutes.

RhodiumToad on IRC explained to me:

< RhodiumToad> the extension script code doesn't split the script
               into statements itself, it passes the whole thing to the
               raw parser and gets back a list of statements

As a result, each INSERT statement found in the PostGIS extension script
results in the whole extension script (including copyright notices!)
being dumped in the logs ("Query Text:").

It is also impossible to know which actual statement the plan refers
to as always the same full script is dumped.

PostGIS has ~4k INSERT statements in its extension script.
While the CREATE EXTENSION statement would take under one second to
complete with auto_explain disabled, here's the state of my machine
at this very moment, with auto_explain enabled:

  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
 8905 strk      20   0 4637680 186348  62448 R  99.5  1.1  14:41.26 postgres: strk dbtopo [local] CREATE EXTENSION

 -rw------- 1 strk strk 10228477364 Nov 18 12:37 /tmp/pg93.log

It's been running for almost 15 minutes now, and filling up the logfile.

Could this be considered a bug in auto_explain ?

--strk;

  ()   Free GIS & Flash consultant/developer
  /\   http://strk.keybit.net/services.html