Re: [PATCH] New command to monitor progression of long running queries

On 18.04.2017 17:39, Remi Colinet wrote:
> Hello Maksim,
>
> The core implementation I suggested for the new PROGRESS command uses
> different functions from the one used by EXPLAIN for its core
> implementation.
> Some source code is shared with EXPLAIN command. But this shared code is
> only related to quals, properties, children, subPlans and few other nodes.
>
> All other code for PROGRESS is new code.
>
> I don't believe explain.c code can be fully shared with the one of the
> new PROGRESS command. These 2 commands have different purposes.
> The core code used for the new PROGRESS command is very different from
> the core code used for EXPLAIN.
>

Perhaps you will be forced to duplicate significant snippets of
functionality from explain.c into your progress.c.

>
> Regarding the queryDesc state of SQL query upon receiving a request to
> report its execution progress, it does not bring any issue. The request
> is noted when the signal is received by the monitored backend. Then, the
> backend continues its execution code path. When interrupts are checked
> in the executor code, the request will be dealt.
>

Yes, interrupts are checked in the CHECK_FOR_INTERRUPTS entries.

> When the request is being dealt, the monitored backend will stop its
> execution and report the progress of the SQL query. Whatever is the
> status of the SQL query, progress.c code checks the status and report
> either that the SQL query does not have a valid status, or otherwise the
> current execution state of the SQL query.
>
> SQL query status checking is about:
> - idle transaction
> - out of transaction status
> - null planned statement
> - utility command
> - self monitoring
>
> Other tests can be added if needed to exclude some SQL query state. Such
> checking is done in void HandleProgressRequest(void).
> I do not see why a SQL query progression would not be possible in this
> context. Even when the queryDescc is NULL, we can just report a <idle
> transaction> output. This is currently the case with the patch suggested.
>

It's interesting question - how much the active query is in a usable
state on the stage of execution. Tom Lane noticed that
CHECK_FOR_INTERRUPTS doesn't give us 100% guarantee about full
consistency [1].

> So far, I've found this new command very handy. It allows to evaluate
> the time needed to complete a SQL query.
>

Could you explain how you get the percent of execution for nodes of plan
tree and overall for query?

> A further improvement would be to report the memory, disk and time
> resources used by the monitored backend. An overuse of memory, disk and
> time resources can prevent the SQL query to complete.
>

This functionality is somehow implemented in explain.c. You can see my
patch to this file [2]. I only manipulate runtime statistics (data in
the structure 'Instrumentation') to achieve the printing state of
running query.

1. https://www.postgresql.org/message-id/24182.1472745492%40sss.pgh.pa.us
2.
https://github.com/postgrespro/pg_query_state/blob/master/runtime_explain.patch

--
Maksim Milyutin
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company