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
comparedto 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.lobreau@dalibo.com>
Envoyé : mardi 4 février 2025 16:23
À : Tom Lane <tgl@sss.pgh.pa.us>; msalais@msym.fr
Cc : 'Rajesh Kumar' <rajeshkumar.dba09@gmail.com>; pgsql-admin@lists.postgresql.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
thewait 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
spammingpg_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
