Psql hangs mid way through script

Question

How do you diagnose why an instance of psql is hanging mid-way through executing a SQL script?

I have a sanitation script with a bunch of UPDATE statements like:

UPDATE table1 SET column1 = CONCAT('stuff', id);
UPDATE table2 SET column2 = CONCAT('stuff', id);
...

The script is part of an automated routine to prepare a development database. However, this is blocking me because the script hangs indefinitely.

There's about a hundred statements like this in the file, yet, about half way through, the script hangs. When I re-run the process from a parent bash script, it hangs at a different point, so it's not a specific statement that's causing the issue. Moreover, when I run the psql command manually, it runs perfectly without hanging.

The call I'm running is:

psql --user=myuser --no-password --host=localhost -d mydatabase -a -f sanitize.sql

Since it non-deterministically fails when run from inside Bash, but succeeds when I run it directly in a shell, that's likely a part of the problem, but I'm not sure what to investigate, since no error's being thrown. Where do I begin investigating this?

Answer 1

The problem ended up being some obscure bug in Bash. I was calling psql from a Bash script, which was being called from another Bash script, which was being run from a Cron job, which was redirecting all output to a log file. When I changed it so that Cron directly ran the psql call, and redirected the output, the "hanging" stopped.