Re: pg_stat_statements : how to catch non successfully finishedstatements ?

Progress report on this subject:

1/ Some clarifications: 

What is expected here is to update pgss counters for ALL the queries that
have been executed,
taking into account queries finished in SUCCESS and thoses finised with
ERROR.

Main interest here is to catch queries that are cancelled or interrupted
by a timeout after a long running period, that could be hours in BI
reporting environments.

Errors during parsing are not catched, because there is no hook available
before parsing 
and because witout parse tree available, QueryId can not be calculated. 
This is not a problem yet because the objective is not to count errors.

There was a remark off-list saying that cummulating SUCCESS and ERROR
counters
for the same query, could be a problem for thoses monitoring AVG indicators 
(becomming smaller than the SUCCESS ones).

One proposal is to add a Boolean flag (success) in the key of pgss:

dbid, userid, queryid, success, calls, total_time, ...
1     1       123      t        100      100 000
1     1       123      f         10    1 000 000

  

2/ Modifying pgss_ExecutorRun (as suggested by Tom Lane) with:


    PG_CATCH();
    {
        /* Added part to get counters on errors */
            EState       *estate;
            if (queryDesc->totaltime && pgss_enabled())
            {
                    estate = queryDesc->estate;
                    InstrStopNode(queryDesc->totaltime, estate->es_processed);
                    InstrEndLoop(queryDesc->totaltime);

            pgss_store(queryDesc->sourceText,
                   queryDesc->plannedstmt->queryId,
                   queryDesc->plannedstmt->stmt_location,
                   queryDesc->plannedstmt->stmt_len,
                   queryDesc->totaltime->total * 1000.0,    /* convert to msec */
                   queryDesc->estate->es_processed,
                   &queryDesc->totaltime->bufusage,
                   NULL);
            }

        nested_level--;
        PG_RE_THROW();
    }
    PG_END_TRY();


permits to catch simple queries in pgss (to be enhanced for utility
statements, pl/pgsql, 
parallel queries, ...).
Would such a code have a chance to be validated ?

Feedback is welcome.
Regards
PAscal



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html