Re: Copying a column into a temp. table

On Fri, Oct 1, 2010 at 10:52 AM, Alexander Farber <
alexander(dot)farber(at)gmail(dot)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.

Regards,
--
gurjeet.singh
@ EnterpriseDB - The Enterprise Postgres Company
http://www.EnterpriseDB.com

singh(dot)gurjeet(at){ gmail | yahoo }.com
Twitter/Skype: singh_gurjeet

Mail sent from my BlackLaptop device