RE: Commit with wait event on advisory lock!

Hi Benoit,

My reply comes very late but I think your explanation is a very good one. And it is some how what I suggested when I compared to Oracle. After all, content of dynamic tables in Oracle come from different arrays in memory...

Best regards

Michel SALAIS

-----Message d'origine-----
De : Benoit Lobréau <benoit(dot)lobreau(at)dalibo(dot)com>
Envoyé : mardi 4 février 2025 16:23
À : Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>; msalais(at)msym(dot)fr
Cc : 'Rajesh Kumar' <rajeshkumar(dot)dba09(at)gmail(dot)com>; pgsql-admin(at)lists(dot)postgresql(dot)org
Objet : Re: Commit with wait event on advisory lock!

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.

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