Re: [PATCH v3] pg_progress() SQL function to monitor progression of long running SQL queries/utilities

2017-07-26 16:24 GMT+02:00 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:

>
>
> 2017-07-26 15:27 GMT+02:00 Robert Haas <robertmhaas(at)gmail(dot)com>:
>
>> On Wed, Jun 21, 2017 at 10:01 AM, Remi Colinet <remi(dot)colinet(at)gmail(dot)com>
>> wrote:
>> > test=# SELECT pid, ppid, bid, concat(repeat(' ', 3 * indent),name),
>> value,
>> > unit FROM pg_progress(0,0);
>> > pid | ppid | bid | concat | value | unit
>> > -------+------+-----+------------------+------------------+---------
>> > 14106 | 0 | 4 | status | query running |
>> > 14106 | 0 | 4 | relationship | progression |
>> > 14106 | 0 | 4 | node name | Sort |
>> > 14106 | 0 | 4 | sort status | on tapes writing |
>> > 14106 | 0 | 4 | completion | 0 | percent
>> > 14106 | 0 | 4 | relationship | Outer |
>> > 14106 | 0 | 4 | node name | Seq Scan |
>> > 14106 | 0 | 4 | scan on | t_10m |
>> > 14106 | 0 | 4 | fetched | 25049 | block
>> > 14106 | 0 | 4 | total | 83334 | block
>> > 14106 | 0 | 4 | completion | 30 | percent
>> > (11 rows)
>> >
>> > test=#
>>
>> Somehow I imagined that the output would look more like what EXPLAIN
>> produces.
>>
>
> me too.
>
> Regards
>
> Pavel
>

Above output is better for utilities. No need to parse the fields. But I
can also provide a second SQL function name pg_progress_admin() with an
output similar to ANALYZE command.
Then comes an other question about the format of the output which can be
TEXT, XML, JSON or YAML as for the ANALYZE command.

An other solution is also to use a PL/SQL package to transform the
pg_progress() output into an output similar to ANALYZE command and let the
use decide which format (XML, JSON, ...) to use.

Thx & Rgds
Remi

>
>
>>
>> > If the one shared memory page is not enough for the whole progress
>> report,
>> > the progress report transfert between the 2 backends is done with a
>> series
>> > of request/response. Before setting the latch, the monitored backend
>> write
>> > the size of the data dumped in shared memory and set a status to
>> indicate
>> > that more data is to be sent through the shared memory page. The
>> monitoring
>> > backends get the result and sends an other signal, and then wait for the
>> > latch again. The monitored backend does not collect a new progress
>> report
>> > but continues to dump the already collected report. And the exchange
>> goes on
>> > until the full progress report has been dumped.
>>
>> This is basically what shm_mq does. We probably don't want to
>> reinvent that code, as it has taken a surprising amount of debugging
>> to get it fully working.
>>
>> --
>> Robert Haas
>> EnterpriseDB: http://www.enterprisedb.com
>> The Enterprise PostgreSQL Company
>>
>>
>> --
>> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-hackers
>>
>
>