0

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?

  • I'd look at pg_stat_activity to see if it's a query that is blocked by a lock and otherwise strace the psql process when hung. – Daniel Vérité Jun 1 '18 at 14:50
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.