proposal 9.4. Explain on signal

On 16 May 2013 11:09, Pavel Stehule <pavel.stehule@gmail.com> wrote:

Hello

I proposed a some months log plans of cancelled queries
/messages/by-id/CAFj8pRA-DuzkmDtu52CiUgb0P7TVri_B8LtjMJfWcnr1LPts6w@mail.gmail.com
. After discussion the proposal was changed to get plan of any running
query.

I have a proof concept patch now and I am thinking so it can work well

So I propose following concept:

1. function pg_explain_backend(PID int, loglevel int default 'log',
explain_top_level boolean default true);

Execution of this function ensure sending sigusr1 signal to PID process.

2. Sigusr1 handler will be enhanced for PROCSIG_EXPLAIN_MESSAGES
message and it will write explain result to log.

It share lot of code with auto_explain module. So I am thinking so we
should move auto_explain functionality to core. Then EXPLAIN ON SIGNAL
can be used for monitoring of query evaluating.

What a neat idea. So the original plan of EXPLAINing cancelled
queries... does this cater for that? Can cancelled queries
automatically invoke the EXPLAIN functionality as part of this
feature?

--
Thom

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

2013/5/16 Thom Brown <thom@linux.com>:

On 16 May 2013 11:09, Pavel Stehule <pavel.stehule@gmail.com> wrote:

Hello

I proposed a some months log plans of cancelled queries
/messages/by-id/CAFj8pRA-DuzkmDtu52CiUgb0P7TVri_B8LtjMJfWcnr1LPts6w@mail.gmail.com
. After discussion the proposal was changed to get plan of any running
query.

I have a proof concept patch now and I am thinking so it can work well

So I propose following concept:

1. function pg_explain_backend(PID int, loglevel int default 'log',
explain_top_level boolean default true);

Execution of this function ensure sending sigusr1 signal to PID process.

2. Sigusr1 handler will be enhanced for PROCSIG_EXPLAIN_MESSAGES
message and it will write explain result to log.

It share lot of code with auto_explain module. So I am thinking so we
should move auto_explain functionality to core. Then EXPLAIN ON SIGNAL
can be used for monitoring of query evaluating.

What a neat idea. So the original plan of EXPLAINing cancelled
queries... does this cater for that? Can cancelled queries
automatically invoke the EXPLAIN functionality as part of this
feature?

I would to get EXPLAIN of long queries without waiting on end.

So it is possible for manual cancelation (not for timeout)

SELECT pg_explain_backend(xx);
SELECT pg_cancel_backend(xx);

Regards

Pavel

--
Thom

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Em 16-05-2013 07:52, Pavel Stehule escreveu:

2013/5/16 Thom Brown <thom@linux.com>:

On 16 May 2013 11:09, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

Hello

I proposed a some months log plans of cancelled queries
/messages/by-id/CAFj8pRA-DuzkmDtu52CiUgb0P7TVri_B8LtjMJfWcnr1LPts6w@mail.gmail.com

What a neat idea. So the original plan of EXPLAINing cancelled

queries... does this cater for that? Can cancelled queries
automatically invoke the EXPLAIN functionality as part of this
feature?

I would to get EXPLAIN of long queries without waiting on end.

So it is possible for manual cancelation (not for timeout)

SELECT pg_explain_backend(xx); SELECT pg_cancel_backend(xx);

BTOH, we could provide a pg_cancel_backend(pid, boolean)
so when that boolean is true it will do that job. Particularly
I'm not a fan of this kind of boolean flag and appreciate
the two function above, so +1.

BTW, if somebody wants an explain-and-cancel behavior
he could creates a function and call both function
pg_(explain|cancel)_backend(..) consecutively.

[]s
- --
Dickson S. Guedes
mail/xmpp: guedes@guedesoft.net - skype: guediz
http://guedesoft.net - http://www.postgresql.org.br
http://github.net/guedes - twitter: @guediz
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.12 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iQEcBAEBAgAGBQJRl3thAAoJEBa5zL7BI5C74voIAJ0PhUlnRz/MEyS3ckeQPNEp
6ZT1f4zddkP+oC626+uv9Gb34lokg6Y+5JrMYFcKm3Pq+3mIiKaq2yY08GW3pkBk
7zbvTSKCQdNO7PprhR9EUjyJ5IZrwkG8nNZJm+98ohkv5dZiHqLl0ovGJGg2yeLd
kkRTmQOOmPalBado1i8SARaEq6apelpmPETl7fkutXAMhq4MSfsB0x0ZofT9/RDA
H18/kssql7BVtm7Rw9uJJe37vnpJJgrsjf8qHzJFZcyhxDjMDHAyzViacfKtd8Mv
WPbZcVTQ5jHHmyReIPECAPseQ/m9eV8gM66X2elO4MDyCZ0hB9xaqZCixnx1844=
=AL0R
-----END PGP SIGNATURE-----

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers