Thread: BUG #18209: New connection is waiting for ProcArrayLock lock when execute a stored procedure concurrently
BUG #18209: New connection is waiting for ProcArrayLock lock when execute a stored procedure concurrently
From
PG Bug reporting form
Date:
The following bug has been logged on the website: Bug reference: 18209 Logged by: 长军 李 Email address: lcj122@163.com PostgreSQL version: 12.11 Operating system: liunx Description: hi hackers, when I use pgbench to execute a stored procedure , I find that new connections cannot access the database. Going through the stack I found that new connections are waiting for a ProcArrayLock lock, I want to know why and what is the solution? Here is my test method ps: The number of cpu cores on my machine is 8 1. The first step is to initialize the environment create table tbl_test (id int, dim_type_code text, c_time timestamp); insert into tbl_test select generate_series(1,10000),'REGION',clock_timestamp(); CREATE OR REPLACE FUNCTION ora_decode( VARIADIC p_decode_list text[]) RETURNS text LANGUAGE 'plpgsql' COST 100 VOLATILE PARALLEL UNSAFE AS $BODY$ -- not set will reduce ProcArrayLock competition -- declare v_len integer; declare v_len integer = 3; begin return 'abc'; end $BODY$; 2. The second step is to write the script:query.sql SELECT ora_decode(VARIADIC ARRAY['REGION'::text, 'REGION'::text, v.dim_type_code]) from tbl_test v; 3. The third step is to use pgbench to run the previous script concurrently. pgbench -M prepared -r -P 1 -c 64 -j 64 -T 1200 postgres -f query.sql Step 4: Create a new connection psql Then, I found that the new connection could not be established and the process was stuck in the authentication phase pg1211 89778 10.8 0.0 279660 4864 ? Rs 11:29 0:03 \_ postgres: postgres postgres [local] SELECT pg1211 89780 10.3 0.0 279660 4864 ? Rs 11:29 0:03 \_ postgres: postgres postgres [local] SELECT pg1211 89781 10.5 0.0 279660 4864 ? Rs 11:29 0:03 \_ postgres: postgres postgres [local] SELECT pg1211 89782 7.7 0.0 279660 4864 ? Rs 11:29 0:02 \_ postgres: postgres postgres [local] SELECT pg1211 90067 0.0 0.0 276292 864 ? Ss 11:30 0:00 \_ postgres: postgres postgres [local] authentication The program stack is as follows pstack 90067 #0 0x00007f0d814c4b2b in ?? () from /lib64/libpthread.so.0 #1 0x00007f0d814c4bbf in ?? () from /lib64/libpthread.so.0 #2 0x00007f0d814c4c5b in sem_wait () from /lib64/libpthread.so.0 #3 0x00000000007ced1a in PGSemaphoreLock (sema=0x2aaaaac011b8) at pg_sema.c:316 #4 0x000000000086bda9 in LWLockAcquire (lock=0x2aaaaac04280, mode=LW_EXCLUSIVE) at lwlock.c:1241 #5 0x000000000085406a in ProcArrayAdd (proc=0x2aaab377c670) at procarray.c:286 #6 0x00000000008673c8 in InitProcessPhase2 () at proc.c:488 #7 0x00000000009f8795 in InitPostgres (in_dbname=0x248ffe8 "postgres", dboid=0, username=0x248ffc8 "postgres", useroid=0, out_dbname=0x0, override_allow_connections=false) at postinit.c:596 #8 0x000000000087f06c in PostgresMain (argc=1, argv=0x2490120, dbname=0x248ffe8 "postgres", username=0x248ffc8 "postgres") at postgres.c:3896 #9 0x00000000007e67aa in BackendRun (port=0x2487fc0) at postmaster.c:4510 #10 0x00000000007e5f91 in BackendStartup (port=0x2487fc0) at postmaster.c:4193 #11 0x00000000007e2694 in ServerLoop () at postmaster.c:1725 #12 0x00000000007e1f6d in PostmasterMain (argc=3, argv=0x2460d60) at postmaster.c:1398 #13 0x000000000070f17e in main (argc=3, argv=0x2460d60) at main.c:228