Different update_chg_stats parameter in docker-container and POSTGRES.md

[CrazyPigHead]

IN this file https://github.com/OpenBMP/obmp-psql/blob/4dded7f93f0f02a3e04b125aeb5e582c89c006fa/docs/POSTGRES.md

Update aggregation table stats
*/3 * * * * root psql -c "select update_chg_stats('26 minute')"

but , in the docker , the /usr/sbin/run file is

Update aggregation table stats
*/3 * * * * root . /usr/local/openbmp/pg_profile && psql -c "select update_chg_stats('8 minute')"

[TimEvens]

Thanks. The docs in this repo do need to be updated.

[TimEvens]

I've removed the POSTGRES.md file as we now use timescaledb docker image as the base. See https://github.com/OpenBMP/obmp-docker/tree/main/postgres for more details on the postgres container. The PSQL App container applies the schemas now, see https://github.com/OpenBMP/obmp-docker/tree/main/psql-app.

[CrazyPigHead]

there is aquestion for the function update_chg_stats.
in container the parameter is 8 minutes.
for example:
now is 10:24, the sql filter the data between 10:07 - 10:15, but the data between 10:00 - 10:07 will never be selected.

-- byprefix updates
  INSERT INTO stats_chg_byprefix (interval_time, peer_hash_id, prefix, prefix_len, withdraws,updates)
	SELECT
	       to_timestamp((extract(epoch from timestamp)::bigint / 900)::bigint * 900) at time zone 'utc' as IntervalTime,
	       peer_hash_id,prefix,prefix_len,
	       count(case WHEN ip_rib_log.iswithdrawn = true THEN 1 ELSE null END) as withdraws,
	       count(case WHEN ip_rib_log.iswithdrawn = false THEN 1 ELSE null END) as updates
	     FROM ip_rib_log
	     WHERE timestamp >= to_timestamp((extract(epoch from now())::bigint / 900)::bigint * 900) at time zone 'utc' - int_window
	           AND timestamp < to_timestamp((extract(epoch from now())::bigint / 900)::bigint * 900) at time zone 'utc'   -- current minute
	     GROUP BY IntervalTime,peer_hash_id,prefix,prefix_len
	ON CONFLICT (interval_time,peer_hash_id,prefix) DO UPDATE
		SET updates=excluded.updates, withdraws=excluded.withdraws;

[TimEvens]

@CrazyPigHead ,

The cron job runs every 3 minutes, but the prefix SQL timestamp >= to_timestamp((extract(epoch from now())::bigint / 900)::bigint * 900) at time zone 'utc' - int_window normalizes the time to 15 minute intervals (0, 15, 30, 45).

Regardless if current time, the interval is always fixed on the 15 minute period minutes interval. The subtraction of 8 minutes from the interval period, results in 9:52 - 10:00 then 10:07 - 10:15. So in this case as you mentioned, it skips 8 minutes on interval change.

Thanks for raising the issue to fix the interval.

[TimEvens]

The overlap of intervals is needed so that we count late arriving updates.

[TimEvens]

@CrazyPigHead , docker openbmp/psql-app:build-52 (also latest now) has this fix applied.