how to cancel a query in progress

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

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

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