BUG #18209: New connection is waiting for ProcArrayLock lock when execute a stored procedure concurrently

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