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

That's a good point.

A command is more straightforward because it targets only one backend.
The user is supposed to know which backend pid is taking a long time to
complete based on pg_stat_activity().

This is somehow the same approach as EXPLAIN command.
But the use is limited to psql utility. And this adds one more command.

I see 2 possible choices:

1 - either convert the command into a table.
This is the way it is done on Oracle database with v$session_longops view.
Obviously, this requires probing the status of each backend. This
inconvenient can be mitigated by using a threeshold of a few seconds before
considering a backend progression. v$session_longops only reports long
running queries after at least 6 seconds of execution.
This is less efficient that targeting directly a given pid or backend id.
But this is far better for SQL.

2 - either convert the command into a function
The advantage of a function is that it can accept parameters. So parameters
could be the pid of the backend, the verbosity level, the format (text,
json, ....).
This would not reduce the options of the current command. And then a view
could be created on top of the function.

May be a mix of both a function with parameters and a view created on the
function is the solution.

Regards
Remi

2017-05-06 5:57 GMT+02:00 Jaime Casanova <jaime(dot)casanova(at)2ndquadrant(dot)com>:

> On 5 May 2017 at 22:38, Vinayak Pokale <vinpokale(at)gmail(dot)com> wrote:
> >
> > On Mon, Apr 17, 2017 at 9:09 PM, Remi Colinet <remi(dot)colinet(at)gmail(dot)com>
> > wrote:
> >>
> >> Hello,
> >>
> >> I've implemented a new command named PROGRESS to monitor progression of
> >> long running SQL queries in a backend process.
> >>
> > Thank you for the patch.
> >
>
> sorry if i'm bikeshedding to soon but... why a command instead of a
> function?
> something like pg_progress_backend() will be in sync with
> pg_cancel_backend()/pg_terminate_backend() and the result of such a
> function could be usable by a tool to examine a slow query status
>
> --
> Jaime Casanova www.2ndQuadrant.com
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>