Comments (4)
affected are the entities channelepoch, epoch (if referred to by a channel epoch), and routing (if referring to such an epoch, however, this is always the case...)
evidence:
Select network.code as net, station.code as sta, channelepoch.id,
channelepoch.locationcode as loc, channelepoch.code as chan,
epoch.starttime as start,
epoch.endtime as end,
epoch.lastseen as ep_lastseen,
routing.lastseen as rout_lastseen,
service.name
from network inner join channelepoch on network.id = channelepoch.network_ref
inner join station on station.id = channelepoch.station_ref
inner join epoch on channelepoch.epoch_ref = epoch.id
left join routing on routing.epoch_ref = epoch.id
left join endpoint on routing.endpoint_ref = endpoint.id
left join service on endpoint.service_ref = service.id
where station.code = 'HAMIK'
and channelepoch.code = 'HGZ'
order by epoch.starttime, epoch.lastseen, channelepoch.id;
-- 5000
-- note: only channelepochs have routing,
-- but all - channelepoch, epoch and routing are multiplied
Select station.code as sta,
epoch.starttime as start,
epoch.endtime as end,
epoch.lastseen as ep_lastseen,
routing.lastseen as rout_lastseen,
service.name
from station inner join stationepoch on station.id = stationepoch.station_ref
inner join epoch on stationepoch.epoch_ref = epoch.id
left join routing on routing.epoch_ref = epoch.id
left join endpoint on routing.endpoint_ref = endpoint.id
left join service on endpoint.service_ref = service.id
where station.code = 'HAMIK'
order by epoch.starttime, epoch.lastseen, stationepoch.id;
-- 1 epoch, no routing
Select network.code as sta,
epoch.starttime as start,
epoch.endtime as end,
epoch.lastseen as ep_lastseen,
routing.lastseen as rout_lastseen,
service.name
from network inner join networkepoch on network.id = networkepoch.network_ref
inner join epoch on networkepoch.epoch_ref = epoch.id
left join routing on routing.epoch_ref = epoch.id
left join endpoint on routing.endpoint_ref = endpoint.id
left join service on endpoint.service_ref = service.id
where network.code = 'CH'
order by epoch.starttime, epoch.lastseen, networkepoch.id
-- 1 epoch, no routing.
from eidaws.
(note that this issue would widely be mediated by the cleanup mechanism for routes which disappeared from EIDA (old lastseen dates), however that process is not automated.
from eidaws.
While channelepochs and routes are duplicated rather than the lastseen date updated,
for network epochs and station epochs the lastseen date is not updated (which is probably fine as long as it is not used).
The issue does not prevent the federator from "functioning", however it bloats the database and makes queries slow in the long-run.
from eidaws.
Until the code is fixed, the database can be kept under control as follows:
-- do this only once:
CREATE INDEX IF NOT EXISTS epoch_epochtype_ref_idx
ON public.epoch USING btree
(epochtype_ref ASC NULLS LAST)
TABLESPACE pg_default;
CREATE UNIQUE INDEX IF NOT EXISTS epochtype_id_idx
ON public.epochtype USING btree
(id ASC NULLS LAST)
TABLESPACE pg_default;
-- do the following regularly:
delete from channelepoch where epoch_ref in
(select id from epoch where lastseen < (select ((now() - '3 days'::interval)::date)::timestamp));
delete from routing where epoch_ref in
(select id from epoch where lastseen < (select ((now() - '3 days'::interval)::date)::timestamp));
delete from epoch where lastseen < (select ((now() - '3 days'::interval)::date)::timestamp)
and id not in (select epoch_ref from stationepoch)
and id not in (select epoch_ref from networkepoch);
delete from epochtype where id not in (select epochtype_ref from epoch);
from eidaws.
Related Issues (17)
- Allow harvesting from files HOT 1
- `fdsnws-station` query filter parameters for `eidaws-stationlite`
- Epochs returned by `eidaws-stationlite` for `service=station&level=network|station`
- Make use `aiofiles.SpooledTemporaryFile`
- Allow non-standard URL base paths HOT 3
- Upgrade to `aioredis>=2.0.0`
- Enhance `fdsnws-station` crawling
- Improve route cleanup HOT 1
- Upgrade to `redis-py>=4.2.x`
- Upgrade to `aiohttp>=3.8.0`
- Optionally force HTTP while harvesting
- Circular spatial query filter parameters for `eidaws-stationlite`
- eida-stationlite-harvest seems not to update the lastseen date in table datacenter of routing DB HOT 1
- Wrong route selection in case of stations on different networks, but same names HOT 3
- wfcatalog endpoint requests using GET instead of POST
- RuntimeError in middleware HOT 1
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from eidaws.