Re: RFC: Logging plan of the running query

> > 2/
> > It should be noted that the plan will not print to the log until
> > the plan begins executing the next plan node? depending on the
> > operation, that could take some time ( i.e.long seq scan of a table,
> > etc.)
> > Does this behavior need to be called out in docs?
>
> Seems reasonable, but long seq scan of a table would not cause the case
> since ExecProcNode() is called at least the number of the rows in a
> table, as far as I remember.
> Of course there can be the case where long time can elapse before
> executing ExecProcNode(), so I agree with adding the doc about this.

Correct. Seq Scan is a bad example since ExecProcNode is called for
every tuple as you mention. MultiExecProcNode, functions doing
time consuming computations, pg_sleep, etc. can all delay the
signal being sent.

I also realized that the extended query protocol may have its own caveats.
A query running under the extended protocol will alternate between
"active" and "idle in transaction"
as it transitions through parse, bind, and execute. If a user calls
pg_log_query_plan while
the state is "idle in transaction," it will result in a "backend with
PID ... is not running a query" log message.
This is more likely if the query repeatedly reissues an "execute"
message (i.e., JDBC fetchSize).
Of course, if the user executes pg_log_query_plan again, it will
(eventually) log the plan,
but it may be very confusing to see the "is not running a query"
message in the logs.
the chances of this behavior is low, but not 0, so it's probably worth
calling out
in documentation.

> 3/
> + * By default, only superusers are allowed to signal to log the plan because
> + * allowing any users to issue this request at an unbounded rate would

> Only superuser allowed to do this is very restrictive. Many shops do
> not, for good
> reasons, want DBAs or monitoring tools to connect as superuser. Why not allow
> this functionality with "pg_monitor" ?

I just realized that my comment above is unwarranted. A superuser can
just simply GRANT EXECUTE ON FUNCTION to pg_monitor, or whatever
monitoring role if they choose. You can ignore this.

--
Sami Imseih
Amazon Web Services (AWS)