Re: Copying a column into a temp. table - Mailing list pgsql-general

Thank you, I've created the following script which seems to work ok.

I just hope, that it won't be hit by the max_stack_depth-problem
which actually forced me to look for a custom solution -

   SQL ERROR [ postgres ]
   ERROR: stack depth limit exceeded HINT: Increase the configuration
   parameter "max_stack_depth". []

   SQL
   DELETE FROM phpbb_posts WHERE post_id IN (334767, ..skipped.., 382871)



# psql -a -f clean-phpbb-forum.sql

start transaction;
START TRANSACTION
create temp table old_topics (topic_id integer) on commit delete rows;
CREATE TABLE
create temp table old_posts (post_id integer) on commit delete rows;
CREATE TABLE

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 '14 days';
INSERT 0 14788
-- 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;
INSERT 0 73718
-- 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 73718
delete from phpbb_topics where topic_id in (select topic_id from old_topics);
DELETE 14788
update phpbb_config set
config_value = (select count(topic_id) from phpbb_topics)
where config_name = 'num_topics';
UPDATE 1
update phpbb_config set
config_value = (select count(post_id) from phpbb_posts)
where config_name = 'num_posts';
UPDATE 1
update phpbb_users set
user_posts = (select count(post_id) from phpbb_posts where poster_id=1)
where user_id = 1;
UPDATE 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;
UPDATE 1
commit
COMMIT