Обсуждение: proposal 9.4. Explain on signal
Hello I proposed a some months log plans of cancelled queries http://www.postgresql.org/message-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. Regards Pavel comments?
On 16 May 2013 11:09, Pavel Stehule <pavel.stehule@gmail.com> wrote: > Hello > > I proposed a some months log plans of cancelled queries > http://www.postgresql.org/message-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
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 >> http://www.postgresql.org/message-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
-----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 >>> http://www.postgresql.org/message-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-----