Periodically clear postgres ccnew indexes.

[dessalines]

In order to do lemmy's hot sorting, it needs to periodically rebuild indexes on the post_aggregates. Unfortunately this occasionally leaves behind extra unused "phantom" indexes that postgres titles idx_....ccnew1, idx....ccnew1. I'm not sure why or under what circumstance it creates these.

These pointless indexes can fill the database, on lemmy.ml they took up 11GB.

To solve this, do a migration to add a "drop_ccnew_indexes()" postgres function. Then periodically run this function in lemmy's recurring jobs.

The function:

CREATE OR REPLACE FUNCTION drop_ccnew_indexes() RETURNS INTEGER AS $$
DECLARE
i RECORD;
BEGIN
  FOR i IN
    (SELECT relname FROM pg_class WHERE relname like '%ccnew%')
    LOOP
      EXECUTE 'DROP INDEX ' || i.relname;
    END LOOP;
    RETURN 1;
  END;
$$ LANGUAGE plpgsql;