Hello,
I'm using phpBB 3.0.7-PL1 with postgresql-server-8.1.21-1.el5_5.1 with
CentOS Linux 5.5, both under 32-bit (dev. VM) and 64-bit (prod. server)
One of the phpBB sub-forums grows quickly every day and I have problems
cleaning old messages there, because its phpBB's admin-script bails out with:
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)
I've read on max_stack_depth and it seems that changing it is a bad idea.
So I would like to write own SQL-function and run it as a cronjob.
There are 2 tables from which I should delete the too old records:
# \d phpbb_topics
Table "public.phpbb_topics"
Column | Type |
Modifiers
---------------------------+------------------------+--------------------------------------------------------
topic_id | integer | not null default
nextval('phpbb_topics_seq'::regclass)
forum_id | integer | not null default 0
topic_poster | integer | not null default 0
topic_time | integer | not null default 0
.................
# \d phpbb_posts
Table "public.phpbb_posts"
Column | Type | Modifiers
------------------+------------------------+-------------------------------------------------------
post_id | integer | not null default
nextval('phpbb_posts_seq'::regclass)
topic_id | integer | not null default 0
forum_id | integer | not null default 0
poster_id | integer | not null default 0
post_time | integer | not null default 0
.........
Also I should update the total post and topics numbers in the
phpbb_config and phpbb_users.
I can select the too old topics with:
select topic_id from phpbb_topics
where forum_id=5 and topic_poster=1 and
age(to_timestamp(topic_time))>interval '14 days'
this works ok and now I'd like to put the results into a temp. table
old_topic_ids (how?) and then I'm going to create another temp.table
for the old_post_ids and put the results of the following into it:
select post_id from phpbb_posts p, old_topics_id o
where p.forum_id=5 and p.poster_id=1 and p.topic_id=o.topic_id;
Then I'm going to update the stats (will the following work?):
update phpbb_config set config_value = (select count(*) from old_topic_ids)
where config_name = 'num_topics';
update phpbb_config set config_value = (select count(*) from old_post_ids)
where config_name = 'num_posts';
Then I'm going to delete the records:
delete from phpbb_topics where (... I don't know how to workarund the
in-statement here -
I'd like to delete all topic_id's which are contained in the temp.
table old_topic_ids).
delete from phpbb_posts where (... I don't know how to workarund the
in-statement here -
I'd like to delete all post_id's which are contained in the temp.
table old_post_ids).
And finally I update 1 more stat:
update phpbb_users set user_posts =
(select count(*) from phpbb_posts where user_id=1) where user_id = 1;
Could you please help me with my SQL statements above,
my SQL knowledge is very rusty. And I wonder how to create the temporary
tables for the old ids and if I have to clean up them later or
if they will be removed automatically when session closes
or my SQL function returns?
And finally, if I put everything into an SQL function:
create or replace function remove_old(num_days varchar) as $$
.........
$$ language sql;
then how can I use the num_days argument in my statement
.... where .... and age(to_timestamp(topic_time))>interval '14 days'
?
Thank you
Alex
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
