Re: On-demand running query plans using auto_explain and signals - Mailing list pgsql-hackers

On Wed, Sep 16, 2015 at 8:07 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

2015-09-16 16:31 GMT+02:00 Shulgin, Oleksandr <oleksandr.shulgin@zalando.de>:

I've added the timeout parameter to the pg_cmdstatus call, and more importantly to the sending side of the queue, so that one can limit the potential effect of handling the interrupt in case something goes really wrong.

I don't think so introduction new user visible timer is good idea. This timer should be invisible

My idea - send a signal and wait 1sec, then check if target process is living still. Stop if not. Wait next 5 sec, then check target process. Stop if this process doesn't live or raise warning "requested process doesn't communicate, waiting.." The final cancel should be done by statement_timeout.

what do you think about it?

That won't work really well with something like I use to do when testing this patch, namely:

postgres=# select pid, array(select pg_cmdstatus(pid, 1, 10)) from pg_stat_activity where pid<>pg_backend_pid() \watch 1

while also running pgbench with -C option (to create new connection for every transaction).  When a targeted backend exits before it can handle the signal, the receiving process keeps waiting forever.

The statement_timeout in this case will stop the whole select, not just individual function call.  Unless you wrap it to set statement_timeout and catch QUERY_CANCELED in plpgsql, but then you won't be able to ^C the whole select.  The ability to set a (short) timeout for the function itself proves to be a really useful feature to me.

We can still communicate some warnings to the client when no timeout is specified (and make 0 the default value actually).

What I'm now thinking about is probably we can extend this backend communication mechanism in order to query GUC values effective in a different backend or even setting the values.  The obvious candidate to check when you see some query misbehaving would be work_mem, for example.  Or we could provide a report of all settings that were overridden in the backend's session, to the effect of running something like this:

select * from pg_settings where context = 'user' and setting != reset_val;

this is a good idea. More times I interested what is current setting of query. We cannot to use simple query - because it is not possible to push PID to function simply, but you can to write function  pg_settings_pid() so usage can look like

select * from pg_settings_pid(xxxx, possible other params) where ...

I would rather have a more general way to run *readonly* queries in the other backend than invent a new function for every occurrence.

The obvious candidates to be set externally are the cmdstatus_analyze/instrument_*: when you decided you want to turn them on, you'd rather do that carefully for a single backend than globally per-cluster.  One can still modify the postgresql.conf and then send SIGHUP to just a single backend, but I think a more direct way to alter the settings would be better.

I am 100% for possibility to read the setting. But reconfiguration from other process is too hot coffee  - it can be available via extension, but not via usually used tools.

It can be reserved to superuser, and nobody is forcing one to use it anyway. :-)

In this light should we rename the API to something like "backend control" instead of "command status"?  The SHOW/SET syntax could be extended to support the remote setting retrieval/update.

prepare API, and this functionality can be part of referential implementation in contrib.

This patch should not to have too range be finished in this release cycle.

These are just the thoughts on what could be achieved using this cross-backend communication mechanism and ideas for generalization of the API.

--

Alex