I have a question about transactions. I am creating a shell script that will use psql
to execute 8 queries that will SELECT data from a database and INSERT the result of the SELECT into a temporary table.
My concern is concurrent access to the same tables while the script is running. While this is unlikely scenario in my case, I'd prefer to be safe about it.
Originally, I had the script executing each of the 8 queries separately, one after another. Then, I modified the psql
command to execute all of the queries, as one transaction. For example:
psql -h localhost -p 5432 -U postgres -1 -c "$SQL1; $SQL2; $SQL3;" mydb
However, it seems from the Postgres documentation that such a transaction will not guarantee a single snapshot of the database/tables while the query is executing. In other words, concurrent access could still affect the results.
Therefore, I don't really know where to proceed from here. What's the recommended option to ensure that concurrent access does not interfere with my 8 queries, every time the script is executed?