Thread: UPDATE stalls when run in "batch mode"
I was running an SQL file in psql (via \i) and I noticed that the execution had been stuck at a particular place for a few hours, which was far longer than expected.
So I killed the processing of the file (with Ctrl-C), vacuumed everything I could think of and tried again.
The same thing happened: the processing stalled at the same place as before.
Once more I killed the processing of the file, but then, this time, instead of re-running the whole thing from the beginning like I did before, I just attempted to execute the statement at which the processing was hanging. It was in fact an update statement of the form:
UPDATE T SET K = NULL FROM B WHERE T.K = B.X;
For good measure, I then executed the next few statements individually, and they all finished in a few seconds. Then I ran the remainder of the original file, and everything went without a hitch, at the expected speed.
I can't begin to guess why the update statement above caused the processing to stall, but ran quickly when I executed it by itself. How can I understand this situation better? I.e. is there any diagnostic procedure I can perform that may lead to the explanation for this inconsistent behavior?
(FWIW, none of the statements in question were executed within a transaction block.)
TIA!
Kynn
"Kynn Jones" <kynnjo@gmail.com> writes:
> I can't begin to guess why the update statement above caused the processing
> to stall, but ran quickly when I executed it by itself. How can I
> understand this situation better?
Perhaps it was blocked on a lock rather than actually doing anything.
Did you look into pg_locks, or use top or vmstat or similar tool to see
if any work was getting done?
Another likely theory is that the script case was using a bad plan
for lack of up-to-date statistics. Putting an EXPLAIN into the script
and comparing it with the manual case would tell the tale.
What's causing the difference is not clear, though if you have
autovacuum enabled that might explain a problem of the second kind
(autovac might've updated the statistics before you tried the command
manually).
regards, tom lane