Thread: 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
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
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