Обсуждение: how to cancel a query in progress
I had a few queries that were fired from pgAdmin, but failed to stop running after I killed the GUI. I tried to stop the queries by killing the pid (of the process running the query, not the pid of the server) from the linux command line, and much to my surprise, the whole database went down and then recovered (using the data in pg_xlog I assume). So, now that I've learned this lesson, what is the proper way to kill a query, and can it be done from psql or are there special postgres tools to do this. Thanks, Whit
Whit Armstrong <armstrong.whit@gmail.com> writes: > I had a few queries that were fired from pgAdmin, but failed to stop > running after I killed the GUI. > I tried to stop the queries by killing the pid (of the process running > the query, not the pid of the server) from the linux command line, and > much to my surprise, the whole database went down and then recovered > (using the data in pg_xlog I assume). I suppose you used "kill -9"? Don't do that. > So, now that I've learned this lesson, what is the proper way to kill > a query, and can it be done from psql or are there special postgres > tools to do this. kill -INT is a safe query-cancel method. regards, tom lane
Thanks, Tom. Lesson learned. Are there any integrity checks I need to run on the db after this type of crash and recovery, or is vacuum --all good enough? -Whit On Wed, Jun 17, 2009 at 11:19 AM, Tom Lane<tgl@sss.pgh.pa.us> wrote: > Whit Armstrong <armstrong.whit@gmail.com> writes: >> I had a few queries that were fired from pgAdmin, but failed to stop >> running after I killed the GUI. > >> I tried to stop the queries by killing the pid (of the process running >> the query, not the pid of the server) from the linux command line, and >> much to my surprise, the whole database went down and then recovered >> (using the data in pg_xlog I assume). > > I suppose you used "kill -9"? Don't do that. > >> So, now that I've learned this lesson, what is the proper way to kill >> a query, and can it be done from psql or are there special postgres >> tools to do this. > > kill -INT is a safe query-cancel method. > > regards, tom lane >
Whit Armstrong <armstrong.whit@gmail.com> writes: > Are there any integrity checks I need to run on the db after this type > of crash and recovery, or is vacuum --all good enough? There isn't anything you need to do. Postgres crashes don't corrupt on-disk data, as a general rule, and a SIGKILL crash seems particularly safe from that. regards, tom lane
Thanks. That's a relief. getting a few "page xxx is uninitialized --- fixing" warnings in the vacuum output, but seems like this should be expected since I killed an insert in progress. can anyone confirm that these warnings are ok? WARNING: relation "balances_primary" page 1883404 is uninitialized --- fixing WARNING: relation "balances_primary" page 1883405 is uninitialized --- fixing WARNING: relation "balances_primary" page 1883406 is uninitialized --- fixing -Whit On Wed, Jun 17, 2009 at 12:09 PM, Tom Lane<tgl@sss.pgh.pa.us> wrote: > Whit Armstrong <armstrong.whit@gmail.com> writes: >> Are there any integrity checks I need to run on the db after this type >> of crash and recovery, or is vacuum --all good enough? > > There isn't anything you need to do. Postgres crashes don't corrupt > on-disk data, as a general rule, and a SIGKILL crash seems particularly > safe from that. > > regards, tom lane >
Whit Armstrong <armstrong.whit@gmail.com> writes: > getting a few "page xxx is uninitialized --- fixing" warnings in the > vacuum output, but seems like this should be expected since I killed > an insert in progress. Yeah, that isn't too surprising. When a table runs out of space the first thing we do is extend it with zeroed pages, mainly to make sure that the disk space is available. The next thing that will hit disk is WAL records about the insertions, and finally the updated pages themselves (overwriting the zeroes). A forced database crash before the WAL gets written leads to some all-zero pages left behind on disk. They're harmless, and VACUUM knows enough to clean them up and make them useful (but it emits a WARNING just to be chatty). regards, tom lane