EURODEO / e-soh

Monorepo for the 3 connected services of e-soh
5 stars 0 forks source link

[DATASTORE] Complete regular cleanup action #167

Open jo-asplin-met-no opened 3 weeks ago

jo-asplin-met-no commented 3 weeks ago

At fixed intervals (default if not configured = once a day) the datastore does a partial cleanup of the database by removing observations (from table observation) that are outside the valid rolling time range (default if not configured = [current time - 24H, current time]).

To complete the cleanup action, we also need to remove time series (from table time_series) and geo points (from table geo_points) that are no longer referred to (through a foreign key) by any observation i the observation table.

jo-asplin-met-no commented 2 weeks ago

Proposed implementation:

DELETE FROM time_series WHERE id in (SELECT id FROM time_series ts WHERE NOT EXISTS (SELECT FROM observation obs WHERE ts_id = ts.id));
DELETE FROM geo_point   WHERE id in (SELECT id FROM geo_point   gp WHERE NOT EXISTS (SELECT FROM observation obs WHERE geo_point_id = gp.id));