[sorry, I didn't intend to send my prev. reply off-list] Hello Gurjeet,
thanks for you interest! I've just summarized my problem at the http://www.phpbb.com/community/viewtopic.php?uid=221190&f=46&t=2105913&start=0 I log protocols of card games into forum_id=5 as Anonymous user_id=1 at my site http://preferans.de and there are currently: phpbb=> select count(*) from phpbb_posts where poster_id=1; count -------- 115431 (1 row) I use phpBB 3.0.7-PL1 with postgresql-server-8.1.21-1.el5_5.1 with CentOS Linux 5.5, both under 32-bit (development VM) and 64-bit (production server). Regards Alex On Fri, Oct 1, 2010 at 4:59 PM, Gurjeet Singh <singh.gurj...@gmail.com> wrote: > On Fri, Oct 1, 2010 at 10:52 AM, Alexander Farber > <alexander.far...@gmail.com> wrote: >> >> 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 >> > > Please stay on the list. > > With temporary tables you shouldn't be hitting that limit. BTW, what > Postgres version are you using? and any rough estimation of how many > elements that IN list has in the query that fails? > > You could have also done: > > DELETE FROM phpbb_posts WHERE post_id IN (select xyz from mytable where > ...) > > assuming that inner select would return the list: 334767, ..skipped.., > 382871, and avoided creating temp tables altogether. > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
