Re: Inconsistency between pg_stat_activity and log_duration

On Fri, Feb 7, 2014 at 11:46 AM, Tatsuo Ishii <ishii(at)postgresql(dot)org> wrote:
>> I think that is the probable reason for the above mentioned behaviour.
>> As I understand here, the problem is that 'state' of backend is shown as
>> active along with 'query' which according to docs (If state is active this field
>> shows the currently executing query.) means that query is executing.
>>
>> This statement holds true for simple query but for prepared statement
>> (using message 'P', 'B', 'D', 'E', 'S') it might not be completely right as
>> we update the state only after sync message which can confuse some
>> users as you have stated. However I don't think it is good idea to change
>> state in between different messages or at least with the current set of
>> states.
>>
>>> I think this inconsistency is not very intutive to users...
>>
>> Do you think we can fix it in any easy way, or might be updating docs
>> can make users understand the current situation better?
>
> One idea is, calling pgstat_report_activity(STATE_IDLE) in
> exec_execute_message() of postgres.c. The function has already called
> pgstat_report_activity(STATE_RUNNING) which shows "active" state in
> pg_stat_actviity view. So why cann't we call
> pgstat_report_activity(STATE_IDLE) here.
>
> Somebody might claim that "idle" is a transaction state term.

Idle means "The backend is waiting for a new client command.", which
is certainly not true especially in case of 'E' message as still sync
message processing is left.

> In the
> case, I propose to add new state name, say "finished". So above
> proposal would calling pgstat_report_activity(STATE_FINISHED) instead.

Okay, so by state finish, it can mean "The backend has finished execution
of a query.". In that case I think this might need to be called at end
of exec_simple_query() as well, but then there will be very less difference
between idle and finish for simple query.

The argument here could be do we really need a new state for such a short
window between completion of 'E' message and processing of 'S' sync
message considering updation of state is not a very light call which can
be called between processing of 2 messages. It might make sense for cases
where sync message processing can take longer time.

Would it be not sufficient, If we just explain this in docs. Do users really
face any inconvenience or it's a matter of clear understanding for users?

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com