Re: ToDo: log plans of cancelled queries

2013/1/11 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
>> My propose is proposed for different dimensions and purpose - for
>> example - we have a limit 20 minutes for almost all queries, and after
>> this limit we killing queries. But we have to know little bit more
>> about these bad queries - and we hope, so execution plan can give this
>> additional info. We have same motivation like people who use
>> auto_explain for slow query - but we can't to wait to query complete.
>
> Oh, sorry, not enough caffeine yet --- somehow I was thinking about
> pg_stat_statements not auto_explain.
>
> However, auto_explain is even worse on the other problem. You flat out
> cannot do catalog lookups in a failed transaction, but there's no way to
> print a decompiled plan without such lookups. So it won't work. (It
> would also be appropriate to be suspicious of whether the executor's
> plan state tree is even fully set up at the time the error is thrown...)

yes, it is - I have a few ideas

1) using signal handler - we don't use a SIGTRAP - so we can use
SIGTRAP (for example) - and inside signal handler we can ensure dump
of plan. It has one advantage - we can take a plan - and maybe more
without query cancelling - somebody can have significantly higher
limit then we, but he would to know a plan.

2) creating some hook for some operations called before query is
really cancelled - this can be called before exception is raised, so
we can materialise plan without problem in this moment. This hook can
be used for auto_explain, because we don't would to print plans for
queries that was faster cancelled than auto_explain limit is it. Maybe
this hook can be called before raising more kinds of exceptions -
across temp file limits. We are not interested on queries that was
finished different errors - because these errors coming quickly
usually.

From my perspective some possible integration with auto_explain can be
nice, I would not to set limits on more places.

Regards

Pavel

>
> regards, tom lane