Postmaster hangs - Mailing list pgsql-bugs

I've been dealing with a postgres (8.3.8.1.fc10) problem for a while
that has become quite frustrating in recent weeks.  I have a set of
tables, one of which (geonames) is very large.  This contains the
geonames geographical information database -- about 7 million records:

CREATE TABLE geonames (
  geonameid BIGINT PRIMARY KEY,
  name VARCHAR(200),
  asciiname VARCHAR(200),
  alternatenames VARCHAR(4000),
  latitude DOUBLE PRECISION,
  longitude DOUBLE PRECISION,
  geoblock0 SMALLINT,
  geoblock1 SMALLINT,
  geoblock2 INTEGER,
  geoblock3 INTEGER,
  geoblock0_latitude SMALLINT,
  geoblock0_longitude SMALLINT,
  geoblock1_latitude SMALLINT,
  geoblock1_longitude SMALLINT,
  geoblock2_latitude SMALLINT,
  geoblock2_longitude SMALLINT,
  geoblock3_latitude SMALLINT,
  geoblock3_longitude SMALLINT,
  feature_class CHAR(1),
  feature_code VARCHAR(10),
  country_code CHAR(2),
  cc2 VARCHAR(60),
  admin1_code VARCHAR(20),
  admin2_code VARCHAR(80),
  admin3_code VARCHAR(20),
  admin4_code VARCHAR(20),
  name_admin1_code VARCHAR(221),
  name_admin1_name VARCHAR(601),
  population BIGINT,
  elevation INTEGER,
  gtopo30 INTEGER,
  timezone VARCHAR(255),
  modification_date TIMESTAMP,

  FOREIGN KEY (feature_class) REFERENCES feature_classes(class),
  FOREIGN KEY (feature_class, feature_code) REFERENCES
feature_codes(class, code),
  FOREIGN KEY (country_code) REFERENCES countries(iso),
  FOREIGN KEY (country_code, admin1_code) REFERENCES
admin1_codes(country, admin1_code),
  FOREIGN KEY (country_code, admin1_code, admin2_code) REFERENCES
admin2_codes(country, admin1_code, admin2_code),
  FOREIGN KEY (country_code, admin1_code, admin2_code, admin3_code)
REFERENCES admin3_codes(country, admin1_code, admin2_code, admin3_code),
  FOREIGN KEY (country_code, admin1_code, admin2_code, admin3_code,
admin4_code) REFERENCES admin4_codes(country, admin1_code, admin2_code,
admin3_code, admin4_code),
  FOREIGN KEY (timezone) REFERENCES timezones(name)
);

I'm in the middle of trying to set the "name_admin1"code" and
"name_admin1_name" fields.  Ideally, I'd like to use the following
command:

update geonames set name_admin1_code = upper(name || ',' ||
admin1_code), name_admin1_name = upper(name || ',' || (select
description from admin1_codes where admin1_codes.admin1_code =
geonames.admin1_code AND admin1_codes.country = geonames.country_code));

However, this command is *guaranteed* to lock up postmaster (more on
that in just a second).  As a consequence, I'm forced to break up the
command into smaller units, such as:

update geonames set name_admin1_code = upper(name || ',' ||
admin1_code), name_admin1_name = upper(name || ',' || (select
description from admin1_codes where admin1_codes.admin1_code =
geonames.admin1_code AND admin1_codes.country = geonames.country_code))
WHERE admin1_code = 'WV';

It'll get through about three or four of them (out of hundreds) before
it locks up.  Now, before lockup, postmaster is very active.  It shows
up on top.  The computer's hard drives clack nonstop.  Etc.  But once it
locks up (without warning), all of that stop.  Postmaster does nothing.
The computer goes silent.  I can't ctrl-break the psql process.  If I
try to start a new psql process, it won't get past the password prompt
-- psql will hang.  All Apache processes involving postgres queries
hang.  The postgres server cannot be restarted by any normal means (the
only solution I've found that works is a reboot).  And so forth.

This isn't the first time I've had this problem.  When setting my
geoblock fields, I had the exact same problem -- and this was before I
had any apache cgi scripts that would access the database.  So whatever
is jamming postgres seems to be entirely internal to the single update
process.

I've just been living with this problem until now, but tonight I got
sick enough of it that I felt I had to report it.  Is this a known bug?
Are there workarounds?

    - Karen