On Wed, May 12, 2010 at 8:45 AM, Kenneth Marshall <ktm@rice.edu> wrote:
> On Wed, May 12, 2010 at 10:22:14AM -0400, David Schnur wrote:
>> I develop an app that uses a back-end Postgres database, currently 8.3.9.
>> The database is started when the app starts up, and stopped when it shuts
>> down. Shutdown uses pg_ctl with -m fast, and waits two minutes for the
>> process to complete. If it doesn't, it tries -m immediate, and waits two
>> more minutes before logging an error and giving up.
>>
>> One user, on OSX 10.5.8, has a script that stops the app each morning, to
>> upgrade to the newest build. In his case, both the fast and immediate
>> shutdowns time out, and Postgres continues running for at least 2-4 hours.
>> At that point he brings up the terminal to kill all the back-ends manually,
>> so we haven't seen it finish shutting down on its own yet. It is in fact
>> shutting down, because all queries fail with the 'database system is
>> shutting down' error.
>>
>> The query running during this time is a DELETE that runs as part of the
>> application's daily maintenance. The size of the DELETE varies, and in his
>> case happened to be unusually large one day, which is apparently what
>> triggered the problem. Since the DELETE never gets a chance to finish, the
>> problem recurs every morning.
>>
>> I'll obviously need to deal with that query, but I'm concerned that Postgres
>> is unable to interrupt it. Why might this be happening? Thanks,
>>
>> David
>
> In many cases, I/O requests are not interruptable until they complete
> and DELETE causes a lot of I/O. Check to see if the processes are in
> device-wait, D in top or ps. The solution is to fix the DELETE processing.
> One option would be to batch it in smaller numbers of rows which should
> allow the quit to squeeze in between one of the batches.
Also see if truncate can be used here or not.