98 lines
3.7 KiB
MySQL
98 lines
3.7 KiB
MySQL
|
-- -----------------------------------------------------------------------
|
||
|
|
-- Copyright (c) 2022 Cisco Systems, Inc. and others. All rights reserved.
|
||
|
|
--
|
||
|
|
-- Ugrade from 2.0.3 to 2.1.0 changes
|
||
|
|
-- -----------------------------------------------------------------------
|
||
|
|
ALTER TABLE base_attrs SET (autovacuum_vacuum_cost_limit = 1000);
|
||
|
|
ALTER TABLE base_attrs SET (autovacuum_vacuum_cost_delay = 5);
|
||
|
|
|
||
|
|
alter table global_ip_rib
|
||
|
|
add column advertising_peers int DEFAULT 0,
|
||
|
|
add column withdrawn_peers int DEFAULT 0;
|
||
|
|
|
||
|
|
CREATE OR REPLACE FUNCTION update_global_ip_rib(
|
||
|
|
int_time interval DEFAULT '15 minutes'
|
||
|
|
)
|
||
|
|
RETURNS void AS $$
|
||
|
|
DECLARE
|
||
|
|
execution_start timestamptz := clock_timestamp();
|
||
|
|
insert_count int;
|
||
|
|
BEGIN
|
||
|
|
raise INFO 'Start time : %', now();
|
||
|
|
raise INFO 'Interval Time : %', int_time;
|
||
|
|
raise INFO '-> Inserting rows in global_ip_rib ...';
|
||
|
|
|
||
|
|
lock table global_ip_rib IN SHARE ROW EXCLUSIVE MODE;
|
||
|
|
|
||
|
|
-- Load changed prefixes only - First time will load every prefix. Expect in that case it'll take a little while.
|
||
|
|
INSERT INTO global_ip_rib (prefix,prefix_len,recv_origin_as,
|
||
|
|
iswithdrawn,timestamp,first_added_timestamp,num_peers,advertising_peers,withdrawn_peers)
|
||
|
|
SELECT r.prefix,
|
||
|
|
max(r.prefix_len),
|
||
|
|
r.origin_as,
|
||
|
|
bool_and(r.iswithdrawn) as isWithdrawn,
|
||
|
|
max(r.timestamp),
|
||
|
|
min(r.first_added_timestamp),
|
||
|
|
count(distinct r.peer_hash_id) as total_peers,
|
||
|
|
count(distinct r.peer_hash_id) FILTER (WHERE r.iswithdrawn = False) as advertising_peers,
|
||
|
|
count(distinct r.peer_hash_id) FILTER (WHERE r.iswithdrawn = True) as withdrawn_peers
|
||
|
|
FROM ip_rib r
|
||
|
|
WHERE prefix in (
|
||
|
|
SELECT prefix
|
||
|
|
FROM ip_rib_log
|
||
|
|
WHERE
|
||
|
|
timestamp >= now() - int_time AND
|
||
|
|
origin_as != 23456
|
||
|
|
GROUP BY prefix
|
||
|
|
)
|
||
|
|
GROUP BY r.prefix, r.origin_as
|
||
|
|
ON CONFLICT (prefix,recv_origin_as)
|
||
|
|
DO UPDATE SET timestamp=excluded.timestamp,
|
||
|
|
first_added_timestamp=excluded.timestamp,
|
||
|
|
iswithdrawn=excluded.iswithdrawn,
|
||
|
|
num_peers=excluded.num_peers,
|
||
|
|
advertising_peers=excluded.advertising_peers,
|
||
|
|
withdrawn_peers=excluded.withdrawn_peers;
|
||
|
|
|
||
|
|
GET DIAGNOSTICS insert_count = row_count;
|
||
|
|
raise INFO 'Rows updated : %', insert_count;
|
||
|
|
raise INFO 'Execution time : %', clock_timestamp() - execution_start;
|
||
|
|
raise INFO 'Completion time: %', now();
|
||
|
|
|
||
|
|
-- Update IRR
|
||
|
|
raise INFO '-> Updating IRR info';
|
||
|
|
UPDATE global_ip_rib r SET
|
||
|
|
irr_origin_as=i.origin_as,
|
||
|
|
irr_source=i.source,
|
||
|
|
irr_descr=i.descr
|
||
|
|
FROM info_route i
|
||
|
|
WHERE r.timestamp >= now() - (int_time * 3) and i.prefix = r.prefix;
|
||
|
|
|
||
|
|
-- Update RPKI entries - Limit query to only update what has changed in interval time
|
||
|
|
-- NOTE: The global_ip_rib table should have current times when first run (new table).
|
||
|
|
-- This will result in this query taking a while. After first run, it shouldn't take
|
||
|
|
-- as long.
|
||
|
|
raise INFO '-> Updating RPKI info';
|
||
|
|
UPDATE global_ip_rib r SET rpki_origin_as=p.origin_as
|
||
|
|
FROM rpki_validator p
|
||
|
|
WHERE r.timestamp >= now() - (int_time * 3)
|
||
|
|
AND p.prefix >>= r.prefix
|
||
|
|
AND r.prefix_len >= p.prefix_len
|
||
|
|
AND r.prefix_len <= p.prefix_len_max;
|
||
|
|
|
||
|
|
END;
|
||
|
|
$$ LANGUAGE plpgsql;
|
||
|
|
|
||
|
|
drop index IF EXISTS global_ip_rib_iswithdrawn_timestamp_idx;
|
||
|
|
CREATE INDEX ON global_ip_rib (iswithdrawn,timestamp DESC);
|
||
|
|
|
||
|
|
drop index IF EXISTS ip_rib_timestamp_idx;
|
||
|
|
CREATE INDEX ON ip_rib (timestamp DESC);
|
||
|
|
|
||
|
|
drop index IF EXISTS global_ip_rib_iswithdrawn_idx;
|
||
|
|
drop index IF EXISTS global_ip_rib_timestamp_idx;
|
||
|
|
drop index IF EXISTS global_ip_rib_timestamp_prefix_idx;
|
||
|
|
|
||
|
|
CREATE INDEX ON global_ip_rib (timestamp DESC);
|
||
|
|
|