Re: Inconsistency between pg_stat_activity and log_duration

>> 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.

Of course.

> 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?

Well... maybe it's a matter of doc.

Pgpool-II issues such SELECTs intenally to retrieve system catalog
info.

The query is piggy backed on the same connection to PostgreSQL opend
by user (pgpool-II cannot issue "sync" because it closes the
transaction, which in turn closes user's unnamed portal).

If user's query is SELECT, it can be sent to standbys because of load
balance. After such internal queries are sent to master, which will
remain "active" for long time because "sync" is not issued.

I got many inquries from pgpool-II users "Hey, when I looked at
pg_stat_activity, I noticed querys is running for so long time. why?".

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp