Okay, I noticed that my home server did not have the problem, and comparing the work and home servers let me finally nail it down.
It's a bug Ubuntu introduced in version 9.10, which is what my work server is running. Specifically, the version of /usr/bin/mysql in Ubuntu 9.10 uses the EditLine wrapper instead of the readline wrapper:
wmartin@work:~$ mysql --version && md5sum /usr/bin/mysql && lsb_release -a
mysql Ver 14.14 Distrib 5.1.37, for debian-linux-gnu (i486) using EditLine wrapper
e23604381e64ac805fdbb8b334ce4d17 /usr/bin/mysql
No LSB modules are available.
Distributor ID: Ubuntu
Description: Ubuntu 9.10
Release: 9.10
Codename: karmic
Compare to the more recent release on my home server:
wmartin@home:~$ mysql --version && md5sum /usr/bin/mysql && lsb_release -a
mysql Ver 14.14 Distrib 5.1.41, for debian-linux-gnu (i486) using readline 6.1
4274c43798239142576e77c022d35bd1 /usr/bin/mysql
No LSB modules are available.
Distributor ID: Ubuntu
Description: Ubuntu 10.04.2 LTS
Release: 10.04
Codename: lucid
The 10.04 (and presumably later) versions work just fine -- mysql honors the home/end/delete keys using Putty's default settings.
So the solution is either to upgrade the server to 10.04 or later, or to use the command line utility rlwrap to wrap the mysql client in a readline environment, as described in the blog post restoring readline capabilities of Ubuntu's 9.10 (Karmic Koala) MySQL Client.
Sadly, the rlwrap approach has the nasty side effect of making your password visible when you type it in instead of suppressing its output. So I think I'll take the upgrade route.
voretaq7's answer covers the key points, including the correct way to terminate backends but I'd like to add a little more explanation.
kill -9 (ie SIGKILL) should never, ever, ever be your first-choice default. It should be your last resort when the process doesn't respond to its normal shutdown requests and a SIGTERM (kill -15) has had no effect. That's true of Pg and pretty much everything else.
kill -9 gives the killed process no chance to do any cleanup at all.
When it comes to PostgreSQL, Pg sees a backed that's terminated by kill -9 as a backed crash. It knows the backend might have corrupted shared memory - because you could've interrupted it half way through writing a page into shm or modifying one, for example - so it terminates and restarts all the other backends when it notices that a backend has suddenly vanished and exited with a non-zero error code.
You'll see this reported in the logs.
If it appears to do no harm, that because Pg is restarting everything after the crash and your application is recovering from the lost connections cleanly. That doesn't make it a good idea. If nothing else backend crashes are less well tested than the normal-functioning parts of Pg and are much more complicated/varied, so the chances of a bug lurking in backend crash handling and recovery are higher.
BTW, if you kill -9 the postmaster then remove postmaster.pid and start it again without making sure every postgres backend is gone, very bad things can happen. This could easily happen if you accidentally killed the postmaster instead of a backend, saw the database had gone down, tried to restart it, removed the "stale" .pid file when the restart failed, and tried to restart it again. That's one of the reasons you should avoid waving kill -9 around Pg, and shouldn't delete postmaster.pid.
A demonstration:
To see exactly what happens when you kill -9 a backend, try these simple steps. Open two terminals, open psql in each, and in each run SELECT pg_backend_pid();. In another terminal kill -9 one of the PIDs. Now run SELECT pg_backend_pid(); in both psql sessions again. Notice how they both lost their connections?
Session 1, which we killed:
$ psql regress
psql (9.1.4)
Type "help" for help.
regress=# select pg_backend_pid();
pg_backend_pid
----------------
6357
(1 row)
[kill -9 of session one happens at this point]
regress=# select pg_backend_pid();
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
regress=# select pg_backend_pid();
pg_backend_pid
----------------
6463
(1 row)
Session 2, which was collateral damage:
$ psql regress
psql (9.1.4)
Type "help" for help.
regress=# select pg_backend_pid();
pg_backend_pid
----------------
6283
(1 row)
[kill -9 of session one happens at this point]
regress=# select pg_backend_pid();
WARNING: terminating connection because of crash of another server process
DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
HINT: In a moment you should be able to reconnect to the database and repeat your command.
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
regress=# select pg_backend_pid();
pg_backend_pid
----------------
6464
(1 row)
See how both sessions were broken? That's why you don't kill -9 a backend.
Best Answer
If you press Ctrl-C in 5.1 during query execution, it will halt the execution with the message "Query execution was interrupted"
mysql> INSERT INTO c SELECT rand()*1000, sha1(rand()) FROM c; Query aborted BY Ctrl+C ERROR 1317 (70100): Query execution was interruptedA further Ctrl-C press kills the client.
Older versions die horribly as noted in question, so upgrade if possible :)
Edit:
Looks like it was added in 5.0.25 (and 5.1.10).