UPDATE stalls when run in "batch mode"

From: "Kynn Jones" <kynnjo(at)gmail(dot)com>
To: "pgsql-general General" <pgsql-general(at)postgresql(dot)org>
Subject: UPDATE stalls when run in "batch mode"
Date: 2008-03-16 20:05:33
Message-ID: c2350ba40803161305m41479987la5c4ddabea33c87b@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Robert Treat 2008-03-16 21:12:15 Re: Trigger to run @ connection time?
Previous Message Scott Marlowe 2008-03-16 19:20:58 Re: Redundant file server for postgres