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.lobreau@xxxxxxxxxx> Envoyé : mardi 4 février 2025 16:23 À : Tom Lane <tgl@xxxxxxxxxxxxx>; msalais@xxxxxxx Cc : 'Rajesh Kumar' <rajeshkumar.dba09@xxxxxxxxx>; pgsql-admin@xxxxxxxxxxxxxxxxxxxx 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
