Multiprocess/Parallel postgres call with Bash

Question

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.

You are waiting for the same pids multiple times, as you don't clear the array between batches. If pids ever get reused by the system, you may be waiting for unrelated processes to end, which could explain the hang (but I can't reproduce it). — jjanes, Aug 02 '22 at 14:46

score 2 · Answer 1 · answered Aug 02 '22 at 11:28

2

take a look to pgBench with a custom query.

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

answered Aug 02 '22 at 11:28

Frank N Stein

2,219
3
22
26

Bruno · Answer 2 · 2022-08-02T15:08:44.050

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).

Multiprocess/Parallel postgres call with Bash

2 Answers2