diff options
-rw-r--r-- | lib/Multi/Maintenance.pm | 2 | ||||
-rw-r--r-- | util/sql/tableattrs.sql | 1 | ||||
-rw-r--r-- | util/updates/update_2.25.sql | 4 |
3 files changed, 7 insertions, 0 deletions
diff --git a/lib/Multi/Maintenance.pm b/lib/Multi/Maintenance.pm index 23fb6db5..23e17fff 100644 --- a/lib/Multi/Maintenance.pm +++ b/lib/Multi/Maintenance.pm @@ -93,6 +93,8 @@ my %dailies = ( cleansessions => q|DELETE FROM sessions WHERE lastused < NOW()-'1 month'::interval|, cleannotifications => q|DELETE FROM notifications WHERE read < NOW()-'1 month'::interval|, + cleannotifications2=> q|DELETE FROM notifications WHERE id IN ( + SELECT id FROM (SELECT id, row_number() OVER (PARTITION BY uid ORDER BY id DESC) > 500 from notifications) AS x(id,del) WHERE x.del)|, rmunconfirmusers => q|DELETE FROM users WHERE registered < NOW()-'1 week'::interval AND NOT email_confirmed|, cleanthrottle => q|DELETE FROM login_throttle WHERE timeout < NOW()|, ); diff --git a/util/sql/tableattrs.sql b/util/sql/tableattrs.sql index 1e812f01..4028e98e 100644 --- a/util/sql/tableattrs.sql +++ b/util/sql/tableattrs.sql @@ -77,6 +77,7 @@ ALTER TABLE wlists ADD CONSTRAINT wlists_vid_fkey FOR CREATE INDEX affiliate_links_rid ON affiliate_links (rid) WHERE NOT hidden; +CREATE INDEX notifications_uid ON notifications (uid); CREATE INDEX releases_producers_rid ON releases_producers (rid); CREATE INDEX releases_vn_vid ON releases_vn (vid); CREATE INDEX tags_vn_date ON tags_vn (date); diff --git a/util/updates/update_2.25.sql b/util/updates/update_2.25.sql index 6302daaf..55fdb537 100644 --- a/util/updates/update_2.25.sql +++ b/util/updates/update_2.25.sql @@ -74,3 +74,7 @@ CREATE TYPE producer_type AS ENUM ('co', 'in', 'ng'); ALTER TABLE producers_rev ALTER COLUMN type DROP DEFAULT; ALTER TABLE producers_rev ALTER COLUMN type TYPE producer_type USING type::producer_type; ALTER TABLE producers_rev ALTER COLUMN type SET DEFAULT 'co'; + + +-- Extra index +CREATE INDEX notifications_uid ON notifications (uid); |