diff options
Diffstat (limited to 'src')
| -rw-r--r-- | src/ingestor.cpp | 4 | ||||
| -rw-r--r-- | src/schema.sql | 2 | ||||
| -rw-r--r-- | src/sql/accessLogPurgeOld.sql | 23 |
3 files changed, 13 insertions, 16 deletions
diff --git a/src/ingestor.cpp b/src/ingestor.cpp index cc5270f..c41454f 100644 --- a/src/ingestor.cpp +++ b/src/ingestor.cpp @@ -439,8 +439,8 @@ namespace WebStat { auto dbconn = dbpool->get(); const auto stopAt = Job::LastRunTime::clock::now() + settings.purgeDeleteMaxTime; const auto purge = dbconn->modify(SQL::ACCESS_LOG_PURGE_OLD, SQL::ACCESS_LOG_PURGE_OLD_OPTS); - purge->bindParam(0, settings.purgeDeleteMax); - purge->bindParam(1, std::format("{} days", settings.purgeDaysToKeep)); + purge->bindParam(0, std::format("{} days", settings.purgeDaysToKeep)); + purge->bindParam(1, settings.purgeDeleteMax); unsigned int purgedTotal {}; while (stopAt > Job::LastRunTime::clock::now()) { const auto purged = purge->execute(); diff --git a/src/schema.sql b/src/schema.sql index b211505..9d39bcd 100644 --- a/src/schema.sql +++ b/src/schema.sql @@ -107,6 +107,8 @@ CREATE TABLE access_log( CONSTRAINT fk_access_log_content_type FOREIGN KEY (content_type) REFERENCES entities(id) ON UPDATE CASCADE ); +CREATE INDEX idx_access_log_request_time ON access_log USING BRIN(request_time) WITH (autosummarize = TRUE); + CREATE OR REPLACE VIEW access_log_view AS SELECT l.id, diff --git a/src/sql/accessLogPurgeOld.sql b/src/sql/accessLogPurgeOld.sql index 18ec775..00e55b5 100644 --- a/src/sql/accessLogPurgeOld.sql +++ b/src/sql/accessLogPurgeOld.sql @@ -1,18 +1,13 @@ -WITH scope AS ( +WITH delete_batch AS ( SELECT - id + ctid FROM access_log + WHERE + request_time < CURRENT_DATE - ?::interval ORDER BY - id - LIMIT ? -), -scoperange AS ( - SELECT - min(id) minid, - max(id) maxid - FROM - scope) -DELETE FROM access_log USING scoperange -WHERE request_time < CURRENT_DATE - ?::interval - AND access_log.id BETWEEN scoperange.minid AND scoperange.maxid + request_time + FOR UPDATE +LIMIT ?) +DELETE FROM access_log AS al USING delete_batch AS del +WHERE al.ctid = del.ctid |