Re: Any advice on debugging hanging postgresql-8.1.21 (many postmaster's) - Mailing list pgsql-general

I've upgraded my CentOS 5.5 VM to:

postgresql-libs-8.4.4-1PGDG.rhel5
postgresql-devel-8.4.4-1PGDG.rhel5
postgresql-8.4.4-1PGDG.rhel5
postgresql-contrib-8.4.4-1PGDG.rhel5
postgresql-docs-8.4.4-1PGDG.rhel5
postgresql-server-8.4.4-1PGDG.rhel5
postgresql-plperl-8.4.4-1PGDG.rhel5

and then have loaded my backup and
then run my cleanup script (source below)
and it would stuck:

pref=> \i bin/clean-phpbb-forum.sql
START TRANSACTION
DELETE 6
CREATE TABLE
CREATE TABLE
INSERT 0 26699
INSERT 0 129903
[... and here nothing happens for minutes....]

And in the
/var/lib/pgsql/data/pg_log/postgresql-Mon.log
see (probably because of the many inserts?)

LOG:  autovacuum launcher started
LOG:  database system is ready to accept connections
LOG:  statement: start transaction;
LOG:  statement: delete from phpbb_users
        where user_type=1 and user_inactive_reason=1 and
user_lastvisit=0 and age(to_timestamp(user_regdate))>interval '3
days';
LOG:  statement: create temp table old_topics (topic_id integer) on
commit delete rows;
LOG:  statement: create temp table old_posts (post_id integer) on
commit delete rows;
LOG:  statement: insert into old_topics select topic_id from phpbb_topics
        where forum_id=5 and topic_poster=1 and
age(to_timestamp(topic_time))>interval '7 days';
LOG:  statement: insert into old_posts select p.post_id from
phpbb_posts p, old_topics t
        where p.forum_id=5 and p.poster_id=1 and p.topic_id=t.topic_id;
LOG:  statement: delete from phpbb_posts where post_id in (select
post_id from old_posts);
LOG:  checkpoints are occurring too frequently (29 seconds apart)
HINT:  Consider increasing the configuration parameter "checkpoint_segments".
LOG:  checkpoints are occurring too frequently (29 seconds apart)

Then after some minutes waiting,
I've pressed ctrl-Z on the pgsql and then "fg"
and this has waken PostgreSQL up for some reason:

[1]+  Stopped                 psql
afarber@centos:~> fg
psql
DELETE 129903
DELETE 26699
UPDATE 1
UPDATE 1
UPDATE 1
UPDATE 1
COMMIT

Could it be that my script is somehow making
PostgreSQL hang? (or wait for some input?):

cat ~/bin/clean-phpbb-forum.sql
start transaction;

delete from phpbb_users
where user_type=1 and user_inactive_reason=1 and user_lastvisit=0 and
age(to_timestamp(user_regdate))>interval '3 days';

create temp table old_topics (topic_id integer) on commit delete rows;
create temp table old_posts (post_id integer) on commit delete rows;

insert into old_topics select topic_id from phpbb_topics
where forum_id=5 and topic_poster=1 and
age(to_timestamp(topic_time))>interval '7 days';

-- select count(topic_id) as "old topics:" from old_topics;

insert into old_posts select p.post_id from phpbb_posts p, old_topics t
where p.forum_id=5 and p.poster_id=1 and p.topic_id=t.topic_id;

-- select count(post_id) as "old posts:" from old_posts;

delete from phpbb_posts where post_id in (select post_id from old_posts);
delete from phpbb_topics where topic_id in (select topic_id from old_topics);

update phpbb_config set
config_value = (select count(topic_id) from phpbb_topics)
where config_name = 'num_topics';

update phpbb_config set
config_value = (select count(post_id) from phpbb_posts)
where config_name = 'num_posts';

update phpbb_users set
user_posts = (select count(post_id) from phpbb_posts where poster_id=1)
where user_id = 1;

update phpbb_forums set
forum_posts = (select count(post_id) from phpbb_posts),
forum_topics = (select count(topic_id) from phpbb_topics),
forum_topics_real = (select count(topic_id) from phpbb_topics)
where forum_id = 5;

commit

Thank you for any insights
Alex

PS: I haven't modified postgresql.conf
       or other conf files besides logging:

# diff postgresql.conf{.OLD,}
353c353
< #log_statement = 'none'                       # none, ddl, mod, all
---
> log_statement = 'all'                 # none, ddl, mod, all

# grep checkpoint postgresql.conf
#checkpoint_segments = 3                # in logfile segments, min 1, 16MB each
#checkpoint_timeout = 5min              # range 30s-1h
#checkpoint_completion_target = 0.5     # checkpoint target duration, 0.0 - 1.0
#checkpoint_warning = 30s               # 0 disables
#log_checkpoints = off