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

[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.gurjeet@gmail.com> wrote:
> On Fri, Oct 1, 2010 at 10:52 AM, Alexander Farber
> <alexander.farber@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.
>