I have a database where, periodically, I get a query that is producing
pg_noname files that are >1gig in size ... according to syslog, for that
process:
Sep 7 18:36:39 pgsql postgres[47078]: DEBUG: ExecRestrPos: node type 17 not supported
Sep 7 18:36:39 pgsql postgres[47078]: DEBUG: ExecRestrPos: node type 17 not supported
Sep 7 18:36:40 pgsql postgres[47078]: DEBUG: ExecRestrPos: node type 17 not supported
Sep 7 18:36:56 pgsql postgres[47078]: DEBUG: ExecMarkPos: node type 17 not supported
%
the query that appears to be causing this, in this particular case, is:
SELECT distinct s.gid, s.created , geo_distance(pd.location, '(-97.4382912597586,37.7021126098755)') FROM status
s,personal_data pd, relationship_wanted rw, personal_ethnicity pe, personal_religion pr, personal_bodytype pb,
personal_smoking ps WHERE s.active AND s.status != 0 AND (s.gid = pd.gid AND pd.gender = 0) AND
(s.gid= rw.gid AND rw.gender = 1) AND geo_distance(pd.location,
'(-97.4382912597586,37.7021126098755)')<= 500
ORDER BY geo_distance( pd.location, '(-97.4382912597586,37.7021126098755)'), s.created desc;
now, its a reasonable oft run query, and from a debugging log that I keep,
it normally takes <1sec to run:
[0.38 secs]: SELECT distinct s.gid, s.created , geo_distance(pd.location, '(-97.4382912597586,37.7021126098755)')
FROM status s, personal_data pd, relationship_wanted rw WHERE s.active AND s.status != 0
AND(s.gid = pd.gid AND pd.gender = 0) AND (s.gid = rw.gid AND rw.gender = 1 ) AND
geo_distance(pd.location, '(-97.4382912597586,37.7021126098755)' ) <= 500 ORDER BY geo_distance( pd.location,
'(-97.4382912597586,37.7021126098755)'), s.created desc;
So, I'm curious as to why it periodically just hangs ... how do you debug
something like this? :( Its been happening ~once per day, so should be
reasonably debugging (unless, of course, now that I mention something it
never comes back *sigh*) ...
Thoughts?
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
