On Wed, May 12, 2010 at 8:45 AM, Kenneth Marshall <ktm@xxxxxxxx> 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. -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
