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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
