1
1
Maybe of interest to small, semi-private instances.
Based on original work by @[email protected]
/*
If you use Lemmy Community Seeder or other jumpstart method to automatically subscribe to communities you may find a large number of low-traffic communities build up over time. The following can be used to purge the content within these communities en masse. Will not purge local instance communities regardless of activity level. Tested with 18.1-18.3. No warranties.
This is useful for purging the content of dead communities if a purge fails or simply to get rid of unwanted content quickly.
IMPORTANT:
You should always attempt to remove and/or purge communities via Lemmy's built-in mechanisms, first. This will give opportunity to signal remote instances to not deliver future content from these communities. As of the time of writing the effectiveness of these built-in actions have questionable reliability and effectiveness so this can be an alternative to at least get rid of the existing content.
Remote instances will not receive the appropriate signal to stop pushing content to your instance.
If they believe there are still subscribed users on your instance the community will eventually reappear with NEW content. This script will only be useful for getting rid of existing content. At this time there is no known method to reliably "correct" this at a later date.
No effort will be made to remove images from PICT-RS. Content will be orphaned and remain as flotsam
until maintenance is performed on the PICT-RS registry.
*/
DO $$
DECLARE
-- Configurable variables
subscriberThreshold INT := 0; -- Communities with less than subscriberThreshold subscribers will be purged.
postCount INT := 0; -- Communities with less than postCount posts will be purged.
commentCount INT := 0; -- Communities with less than commentCount comments will be purged.
dryRun BOOLEAN := true; -- dryRun := true to simulate purge. dryRun := false to actually purge. Destructive. No warranties.
purgeCommunityID BOOLEAN := false; -- purgeCommunityID := false don't purge community metadata (prevents new id being created if community still exists or reappears)
--
arow record;
communityID INT;
communityName VARCHAR;
BEGIN
for arow in
SELECT community_aggregates.community_id, community.actor_id FROM
public.community_aggregates INNER JOIN
public.community On public.community_aggregates.community_id = community.id
WHERE instance_id <> 1 AND ((community_aggregates.subscribers < subscriberThreshold) or (community_aggregates.posts < postCount) or (community_aggregates.comments < commentCount))
loop
communityID := arow.community_id;
communityName := arow.actor_id;
RAISE NOTICE 'Purging community content: %', communityName;
-- Easiest way to disable triggers for a transaction.
SET session_replication_role = replica;
-- Delete Comment Likes
DELETE FROM comment_like WHERE post_id IN (SELECT id FROM post WHERE community_id = communityID);
-- Delete Comment Aggregates
DELETE FROM comment_aggregates WHERE comment_id IN (
SELECT id FROM comment WHERE post_id IN (
(SELECT id FROM post WHERE community_id = communityID)
)
);
-- Delete Comment Replies
DELETE FROM comment_reply WHERE comment_id IN (
SELECT id FROM comment WHERE post_id IN (
(SELECT id FROM post WHERE community_id = communityID)
)
);
-- Delete Comment Reports
DELETE FROM comment_report WHERE comment_id IN (
SELECT id FROM comment WHERE post_id IN (
(SELECT id FROM post WHERE community_id = communityID)
)
);
-- Delete Comment Saved
DELETE FROM comment_saved WHERE comment_id IN (
SELECT id FROM comment WHERE post_id IN (
(SELECT id FROM post WHERE community_id = communityID)
)
);
DELETE FROM community_aggregates WHERE community_id = communityID;
DELETE FROM community_block WHERE community_id = communityID;
DELETE FROM community_follower WHERE community_id = communityID;
DELETE FROM community_language WHERE community_id = communityID;
DELETE FROM community_moderator WHERE community_id = communityID;
DELETE FROM community_person_ban WHERE community_id = communityID;
DELETE FROM mod_add_community WHERE community_id = communityID;
DELETE FROM mod_ban_from_community WHERE community_id = communityID;
DELETE FROM mod_feature_post WHERE post_id IN (
SELECT id FROM post WHERE community_id = communityID
);
DELETE FROM mod_hide_community WHERE community_id = communityID;
DELETE FROM mod_lock_post WHERE post_id IN (
(SELECT id FROM post WHERE community_id = communityID)
);
DELETE FROM mod_remove_comment WHERE comment_id IN (
SELECT id FROM comment WHERE post_id IN (
(SELECT id FROM post WHERE community_id = communityID)
)
);
DELETE FROM mod_remove_community WHERE community_id = communityID;
DELETE FROM mod_remove_post WHERE post_id IN (
(SELECT id FROM post WHERE community_id = communityID)
);
DELETE FROM mod_transfer_community WHERE community_id = communityID;
DELETE FROM person_mention WHERE comment_id IN (
SELECT id FROM comment WHERE post_id IN (
(SELECT id FROM post WHERE community_id = communityID)
)
);
DELETE FROM post_aggregates WHERE post_id IN (
SELECT id FROM post WHERE community_id = communityID
);
DELETE FROM post_like WHERE post_id IN (
SELECT id FROM post WHERE community_id = communityID
);
DELETE FROM post_read WHERE post_id IN (
SELECT id FROM post WHERE community_id = communityID
);
DELETE FROM post_report WHERE post_id IN (
SELECT id FROM post WHERE community_id = communityID
);
DELETE FROM post_saved WHERE post_id IN (
SELECT id FROM post WHERE community_id = communityID
);
-- Delete comments for posts IN removed community
DELETE FROM comment WHERE post_id IN (
SELECT id FROM post WHERE community_id = communityID
);
-- These last two must be last as they are used to find related entries IN all of the other tables.
-- Delete the posts for the community
DELETE FROM post WHERE community_id = communityID;
-- Delete the community
IF purgeCommunityID = true THEN
RAISE NOTICE 'Purging community metadata: %', communityName;
DELETE FROM community WHERE id = communityID;
ELSE
RAISE NOTICE 'Retaining community metadata: %', communityName;
END IF;
***
If there are no errors or issues, commit
IF dryRun = true THEN
ROLLBACK;
END IF;
IF dryRun = false THEN
COMMIT;
END IF;
END LOOP;
IF dryRun = true THEN
RAISE NOTICE 'Dry-run only. No data actually purged.';
RAISE NOTICE 'Set dryRun BOOLEAN := true to really purge';
ROLLBACK;
END IF;
IF dryRun = false THEN
COMMIT;
END IF;
END $$;
``` []() []()