Multiprocess/Parallel postgres call with Bash-CodePudding

I'm trying to implement a testing simulation on a postgres database using pgbouncer for pooling and bash script for making requests. The script is intended to simulate multiple users making requests in order to enforce a workload on the database, which I'm doing with the following script:

#!/bin/bash

scriptName="/data/script/insert-call.sh"

insertionMultiplier=2000
numberOfProcess=5
pids=()

for ((i = 0 ; i <= $insertionMultiplier ; i  )); do
    for ((j = 0 ; j <= $numberOfProcess ; j  )); do
        psql -p 6432 test_db -U postgres -c "call insert_procedure()"  &
        pids =("$!")
    done
    
    for pid in "${pids[@]}"; do
        wait "$pid"
        return_code="$?"
        echo "PID= $pid; return_code=$return_code"
    done
    
done

The problem is that as the sub processes are created, some of them never finish and the execution keeps hanging in the second nested loop. How can I make these multiple requests succeed ? I already also tried using the bare "wait", but it didn't work neither.

CodePudding user response：

take a look to pgBench with a custom query.

www.stackoverflow.com/questions/17997070/postgresql-pgbench-tool-running-user-defined-sql-script

CodePudding user response：

You should not wait for a specific PID, as you don't know which one will finish first. You could pass the whole list of PIDs (${pids[@]}), but you could have to maintain it (remove the expired pids). Or, simpler, you could wait for any background process (if you don't have others somewhere else in the code) :

while  true; do
    wait -n -p pid
    return_code="$?"
    if ((pid == 0)); then
       echo "no more processes"
       break
    fi
    echo "PID= $pid; return_code=$return_code"
done

The wait command will wait for any background process, set pid variable to the PID of the terminated one (or 0 if no more processes available), and exit status will be the exit status of $pid process (or 127 if no more process is available).