Skip site navigation (1) Skip section navigation (2)

Postmaster hangs

From: Karen Pease <meme(at)daughtersoftiresias(dot)org>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Postmaster hangs
Date: 2009-10-26 02:52:36
Message-ID: 1256525556.25178.16.camel@localhost.localdomain (view raw, whole thread or download thread mbox)
Lists: pgsql-bugs
I've been dealing with a postgres ( 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 (
  name VARCHAR(200),           
  asciiname VARCHAR(200),
  alternatenames VARCHAR(4000),
  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

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 = 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 = 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

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


pgsql-bugs by date

Next:From: Tom LaneDate: 2009-10-26 03:13:34
Subject: Re: Postmaster hangs
Previous:From: Timothy MaddenDate: 2009-10-25 22:32:10
Subject: Re: BUG #5136: Please drop the string literal syntax for CREATE FUNCTION ...

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group