Postgresql – Running queries one after the other in psql/scripting environment

postgresqlscripting

I am a little bit concerned about the possibility of queries running concurrently. I want to run a set of queries at worst as soon as the previous is ready through psql (PostgreSQL) and a shell script.

My initial attempt was something like this example:

queries=("SELECT ...", "SELECT ...")
for query in ${#queries[@]}
    "$query" | psql -U postgres -d database 
done

I am pretty sure that with the way this is written that the bash script is waiting for each other command to be executed before moving to the next. But I am not sure on the interaction with psql – can there still exist the possibility that the database is executing queries concurrently? If so, how do I approach this so that my commands and queries are executed after each other?

Best Answer

As @a_horse_with_no_name said, if you want to restrict the order on db level, use sequential statements, either with psql -f queries.sql or :

psql <<EOF 
select..;
select..;
EOF

In your example you call psql for each query. You don't send them to background so each psql waits previous to end before calling next. Concurrency should be sane here as well. Things that you have different here: lack of control over transaction, you open/close many connections instead of one and you put sql code into bash variable

Related Question