7.4Beta1 hang? - Mailing list pgsql-hackers

From Robert Creager
Subject 7.4Beta1 hang?
Date
Msg-id 20030809174838.6d09caf5.Robert_Creager@LogicalChaos.org
Whole thread Raw
Responses Re: 7.4Beta1 hang?
Re: 7.4Beta1 hang?
List pgsql-hackers
I appear to have a hang on my system (40 minutes so far, it's now
17:40).  The code is from CVS updated 8/6 if I remember correctly.

The machine is idle, with a vacuum waiting and an INSERT doing nothing. 
The vacuum is being generated via pg_autovacuum.

The output from the perl script leading up to the hang is:

2755/kir_27551925.fits.apm...  1775 stars imported 3421 per second
2755/kvr_27551925.fits.apm...  1219 stars imported 4639 per second
.....Kept  925 (75.9%) pairs of stars at 249.9 per second (  3.7)
2755/kir_27551989.fits.apm...  1727 stars imported 3564 per second
2755/kvr_27551989.fits.apm...   918 stars imported 3518 per second
.....Kept  694 (75.6%) pairs of stars at 635.3 per second (  1.1)
2755/kir_27552015.fits.apm...  1817 stars imported 4549 per second
2755/kvr_27552015.fits.apm...   958 stars imported 4197 per second
....

As you this shows, the insert that is hung is part of a series of
operaqtions which usually completes in under 4 seconds.

The insert is:

INSERT INTO obs_i
SELECT *
FROM temp_obs_i
WHERE obs_id IN (SELECT i_obs_id FROM obs_keep)

The PostgreSQL processes:

postgres 32242     1  0 Aug07 ?        00:00:01
/usr/local/pgsql/bin/postmaster -D /var/lib/pgsql/data postgres 32246
32242  0 Aug07 ?        00:02:38 postgres: stats buffer process         

postgres 32247 32246  0 Aug07 ?        00:02:30
postgres: stats collector process

postgres  6426 32242 32 16:36 ?        00:09:21
postgres: robert tassiv 192.168.0.250 INSERT    

postgres  6427 32242  1 16:36 ?        00:00:21
postgres: robert tassiv 192.168.0.250 idle

postgres  6615 32242  3 16:48 ?        00:00:34
postgres: robert tassiv localhost VACUUM waiting     

postgres  6824 32242  0 17:01 ?        00:00:00
postgres: checkpoint subprocess                       

Anything to look at before I kick it?  It's not built with debug, but I
can still get a backtrace.

Tables:

obs_keep is a temp table generated via perl script

SELECT i.obs_id AS i_obs_id,      v.obs_id AS v_obs_id
INTO TEMP obs_keep
FROM                (SELECT obs_id, file_id, loc   FROM temp_obs_v NATURAL JOIN files   WHERE  group_id = $group_id
AND pair_id  = $pair_id) AS v,  (SELECT obs_id, file_id, loc   FROM temp_obs_i NATURAL JOIN files   WHERE  group_id =
$group_id     AND pair_id  = $pair_id) AS i
 
WHERE i.loc @ scircle( v.loc, $MATCH_RADIUS )


tassiv=# \d temp_obs_i                    Table "public.temp_obs_i"Column  |  Type   |                   Modifiers
             
 
---------+---------+------------------------------------------------x       | real    | not nully       | real    | not
nullimag   | real    | not nullsmag    | real    | not nullloc     | spoint  | not nullobs_id  | integer | not null
defaultnextval('"obs_id_seq"'::text)file_id | integer | not nulluse     | boolean | default falsesolve   | boolean |
defaultfalsestar_id | integer | mag     | real    | 
 
Indexes:   "temp_obs_i_file_id_index" btree (file_id)   "temp_obs_i_index" gist (loc)   "temp_obs_i_loc_index" gist
(loc)  "temp_obs_i_obs_id_index" btree (obs_id)
 
Foreign-key constraints:   "temp_obs_i_files_constraint" FOREIGN KEY (file_id) REFERENCES
files(file_id) ON DELETE CASCADE Inherits: obs_root


tassiv=# \d obs_i                       Table "public.obs_i"Column  |  Type   |                   Modifiers
      
 
---------+---------+------------------------------------------------x       | real    | not nully       | real    | not
nullimag   | real    | not nullsmag    | real    | not nullloc     | spoint  | not nullobs_id  | integer | not null
defaultnextval('"obs_id_seq"'::text)file_id | integer | not nulluse     | boolean | default falsesolve   | boolean |
defaultfalsestar_id | integer | mag     | real    | 
 
Indexes:   "obs_i_file_id_index" btree (file_id)   "obs_i_loc_index" gist (loc)   "obs_i_obs_id_index" btree (obs_id)
"obs_i_star_id_index"btree (star_id)   "obs_i_use_index" btree (use)
 
Foreign-key constraints:   "obs_i_files_constraint" FOREIGN KEY (file_id) REFERENCES
files(file_id) ON DELETE CASCADE    "obs_i_star_id_constraint" FOREIGN
KEY (star_id) REFERENCES catalog(star_id) ON DELETE SET NULL Triggers:   obs_i_trig BEFORE INSERT OR DELETE OR UPDATE
ONobs_i FOR EACH ROW
 
EXECUTE PROCEDURE observations_trigger()
Inherits: obs_root

-- 17:05:52 up 8 days,  9:51,  4 users,  load average: 0.03, 0.51, 0.92

pgsql-hackers by date:

Previous
From: Larry Rosenman
Date:
Subject: Re: 7.4beta1 build problem on unixware
Next
From: Larry Rosenman
Date:
Subject: Another day, another SCO Compiler Error...