Re: Commit with wait event on advisory lock!

On 2/4/25 4:22 PM, Benoit Lobréau wrote:
> On 1/22/25 11:17 PM, Tom Lane wrote:
>>> By the way I also have commits which are waiting on ClientRead...
>>
>> That, on the other hand, is surely impossible.  I think maybe you
>> are misreading the stats display.  Typically I'd expect that such a
>> case indicates that the session is idle (awaiting a new command)
>> and the COMMIT is the last thing it did before that.
>>
>>             regards, tom lane
>
> I can reproduce the issue using pgbench spamming "BEGIN; COMMIT;" and
> and running this query in psql:
>
> SELECT DISTINCT state, wait_event, query
> FROM pg_stat_activity
> WHERE backend_type ILIKE '%client%'
> AND query ILIKE 'COMMIT%'
> \watch 0.5
>
> After a short while I get the following :
>
> active | ClientRead | COMMIT;
>
> I looked into src/backend/utils/adt/pgstatfuncs.c and found that
> the state comes from the PgBackendStatus array, while the wait
> events are fetched from the proc array (using st_procpid taken
> from the backend status).
>
> I don't think there is a guarantee that this "snapshot" is
> consistent across both arrays. It might just be a case of spamming
> pg_stat_activity and occasionally ending up with an "inconsistent
> snapshot."
>
> Do you think this explanation holds weight?
>
> I haven't been able to reproduce the advisory lock issue yet.
>

Hi,

is this explanation for the active transaction with the ClientRead wait
event plausible?

Thanks! I appreciate any insights you can share.

--
Benoit Lobréau
Consultant
http://dalibo.com