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?
Best Answer
The problem ended up being some obscure bug in Bash. I was calling
psqlfrom 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 thepsqlcall, and redirected the output, the "hanging" stopped.