[SOLVED] Using mulitple sql query through psql connecting from a remote client

Using mulitple sql query through psql connecting from a remote client

I am connecting to postgressql DB through AWS Datapipeline using a shellscript activity. I have to delete the data of 60 tables and copy the data into the tables from files. When a copy job fails I want to rollback the table to the previous state so that the table wont be empty. I am trying to do it with multiple psql statements as below but the rollback is not working as expected.When there is an error in one of the files and copy statement fails, the table is deleted and it is empty. But, I want to roll it back the previous state.

'psql -h $2 -d $3 -U $4 -p $7 -c "BEGIN;"
psql -h $2 -d $3 -U $4 -p $7 -c "Delete  from  $var1;"

psql -h $2 -d $3 -U $4 -p $7 -c "\COPY $var1 FROM '$f' csv header;"
VAR3=$?
    echo "$VAR3"
if [ $VAR3 -gt 0 ];
then 
psql -h $2 -d $3 -U $4 -p $7 -c "ROLLBACK;"
else
psql -h $2 -d $3 -U $4 -p $7 -c "COMMIT;"
fi
psql -h $2 -d $3 -U $4 -p $7 -c "END;"'

Here is the standard output log when copy command returned an error.

BEGIN DELETE 560 1 ROLLBACK COMMIT

I am starting the transaction with Begin, then Delete statement and copy. If copy fails then I wrote the logic to rollback else commit and end. What is wrong here ?

Solution

Each psql invocation creates its own database connection. But a database transaction is always part of a single database connection, so you cannot start a transaction in one psql call and end it in another. one.

I would use a "here document":

psql <<EOF
BEGIN;
...
COMMIT;
EOF

For conditional processing you can use the \if functionality of recent psql versions.

Make sure there are no tab characters in the here document, they may lead to surprising behavior.