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(at)163(dot)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