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?
pg_stat_activity
to see if it's a query that is blocked by a lock and otherwisestrace
the psql process when hung.