Message-ID: <aeb7adb1-1a2a-4f50-a24f-40fd4da60299@dalibo.com>
Date: Tue, 4 Feb 2025 16:22:45 +0100
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Commit with wait event on advisory lock!
To: Tom Lane <tgl@sss.pgh.pa.us>, msalais@msym.fr
Cc: 'Rajesh Kumar' <rajeshkumar.dba09@gmail.com>,
 pgsql-admin@lists.postgresql.org
References: <00c201db6c13$7a1fbb80$6e5f3280$@msym.fr>
 <CAJk5AtY-TWUYU7TR3KfbOyzvfDytTFTJfVyB1cKWpwKxNSV3Ww@mail.gmail.com>
 <00e801db6c63$acd0f8a0$0672e9e0$@msym.fr> <3505513.1737506928@sss.pgh.pa.us>
 <010f01db6cf1$c07f7be0$417e73a0$@msym.fr> <3938129.1737566668@sss.pgh.pa.us>
 <011401db6d19$1d98ba40$58ca2ec0$@msym.fr> <4167142.1737584262@sss.pgh.pa.us>
Content-Language: fr
From: =?UTF-8?Q?Benoit_Lobr=C3=A9au?= <benoit.lobreau@dalibo.com>
Autocrypt: addr=benoit.lobreau@dalibo.com; keydata=
 xsBNBF3FR7IBCADDvtR0p09SI9OvnbWw+VEvO5IXefBWFWefxrPd0H3Sf3gsBLiKbRdkSjvz
 2y+ksRgBUkhK1Kiqe/QuddgvuT9OUFBCrOtZxkBgJYRbxBNTOnIehhEoK9sdqjwvwNyxJUEX
 er1UgErP+MJTvwpW1J8Uq9oV+NbqgNQ7ca5G2D3x7DI2Tbbw0iu+lmCIdXQpTk7hacErGscC
 p/eaJQFd6EPAZPS7BQEgjuDDjGCy4RzTSiobtqn0ocaEj+w7365FVKKZD2LjEm1mfBK/4nml
 H/vlssXkKKpUGetaib6AVpZK3a0xBTMi04ft/V8iGGJuVhE45WjjKSckmeLafTZCGxN9ABEB
 AAHNK0Jlbm9pdCBMb2Jyw6lhdSA8YmVub2l0LmxvYnJlYXVAZGFsaWJvLmNvbT7CwJQEEwEI
 AD4CGwMFCwkIBwIGFQoJCAsCBBYCAwECHgECF4AWIQRkbHJAht3VPY+ptaDDJY+vq0O76wUC
 Za56QgUJC6uZkAAKCRDDJY+vq0O760ECCACDPj6p9VXaX4GF9p+3zhoDwxsUWh0xODwsK//h
 SBGsRmPaElApbOR5rs2WDFaPE95XG3Vdo2rno7i4mt0fASls7vq4P0BPJiPM4fKEr+kfXAxP
 2clqmkBMn2o+EEeUGDmnn3RAVzDDoyML6N/unpyOTHWfscxvvj6AKreUtxzQm0LVQRL+hIaG
 SAlcwpVWQBPhA6Roi1/7Uz9qm8nWjFOuV6vO5yVe0sfRrrO9AFkjBGFT/B06sSs33Os6Hjs5
 2ckLn5Gxlty8XgTpbaG0bJiV+GDTcXh5UV8XVKaMomOLJxWw8US6PSSqQxodWuJqsvlxXV93
 ae6cMIosHsriaUo5zsBNBF3FR7IBCADEWwsQar1qtLKdoAoqKThz+kQXu3UADQ0c8JCgsp+8
 a6hFDg+uIw0j852lkJOPHll414Qw+kLC1bUo7H6+mPHzko/aR3TFz6vvOrm0afwFP7sm9Vbe
 UbpeohnrXBBwb1PjNhDphaXeMVovJqSgRifhc/sAR6fXi+2OCrH+8CUYa0aHhwoecStgw/tm
 EDPZ8lCsg5IdyO5Er4ZMOl/EbaRuTOpl7U/GzJBQSYf7t4KHotIpEeuJVv4X/+nTW3ALG+dG
 pRA5GplNnlQNfsEymnT4W7RbfafDp7Q7nK6w7d55ZU8UyiS6N/1wh/DL1hntC65lQbHlmwNx
 RG132ecNOcqFABEBAAHCwHwEGAEIACYCGwwWIQRkbHJAht3VPY+ptaDDJY+vq0O76wUCZa56
 dQUJC6uZwwAKCRDDJY+vq0O76y9jB/0fVyz7D56D2A6MUGASNkh6IiuHCF/UgEfU0m5fC6pd
 38+fmyUMkGR3QwrtZP3SCLaBKT4LHxhVu7tEUWI+6WK7/s0Jsx5L8aIfDKY4zjqQ9InP+LkA
 azkKW4JpIb9Ny0FzRjUyf9gReU5nMy9sUNS4DhoHG6c/bQYqkj9uA5AokiDDCRg2dobfpaNP
 C6qQQIXD+wi0aZ4jR5r4rXMxmwFutandoaxDpPBj4EXlyRHhzyKfgb2c6TXdCVQ5YJUFUi9k
 gl7CXplN8vBpMuzN5y6NPd3Xekcp16D5XCQNBQ2drIsnFR4IbDPI1Mqd6OJYH0vum6g8FmoN
 s7ygc4oi12nU
In-Reply-To: <4167142.1737584262@sss.pgh.pa.us>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/aeb7adb1-1a2a-4f50-a24f-40fd4da60299%40dalibo.com>
Precedence: bulk

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