I'm stuck here with a query that kills postgresql backend.
I've installed postgresql 7.3.1 on two machines..
one with gcc 3.2, glibc 2.3.1 source based (a)
and the other with gcc 2.95.3, glibc 2.2.3 slackware 8 (b)
both are PIII with kernel 2.4.20.
(a) has ext3 and (b) has xfs for the database storage...
tested both with altered and original postgresql.conf...
I've the same program installed on both machines using the same database...
both fail when i'm executing a specific query...
Those are the errors (I've removed a lot from the queries to isolate the
problem):
This fails:
netinfo=> select * from view_all v where NOT EXISTS ( SELECT * FROM history h
WHERE ( h.ip = v.ip OR ( h.ip IS NULL AND v.ip IS NULL ) ) AND v.sw_ip IS
NULL AND ( h.mac = v.mac OR ( h.mac IS NULL AND v.mac IS NULL ) ) );
ERROR: replace_vars_with_subplan_refs: variable not in subplan target list
but this works:
netinfo=> select * from view_all v where NOT EXISTS ( SELECT * FROM history h
WHERE ( h.ip = v.ip OR ( h.ip IS NULL AND v.ip IS NULL ) ) AND ( h.mac =
v.mac OR ( h.mac IS NULL AND v.mac IS NULL ) ) );
this fails too:
netinfo=> select * from view_all v where NOT EXISTS ( SELECT * FROM history h
WHERE ( h.ip = v.ip OR ( h.ip IS NULL AND v.ip IS NULL ) ) AND ( h.mac =
v.mac OR ( h.mac IS NULL AND v.mac IS NULL ) ) AND ( ( h.sw_ip IS NULL AND
v.sw_ip IS NULL ) ) );
ERROR: replace_vars_with_subplan_refs: variable not in subplan target list
and this causes a segmentation fault:
netinfo=> select * from view_all v where NOT EXISTS ( SELECT * FROM history h
WHERE ( h.ip = v.ip OR ( h.ip IS NULL AND v.ip IS NULL ) ) AND ( h.mac =
v.mac OR ( h.mac IS NULL AND v.mac IS NULL ) ) AND ( h.sw_ip = v.sw_ip OR (
h.sw_ip IS NULL AND v.sw_ip IS NULL ) ) );
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
To reproduce it:
createdb test
createlang plpgsql test
psql -f create.sql test
and then run this:
select * from view_all v where NOT EXISTS ( SELECT * FROM history h WHERE (
h.ip = v.ip OR ( h.ip IS NULL AND v.ip IS NULL ) ) AND ( h.mac = v.mac OR (
h.mac IS NULL AND v.mac IS NULL ) ) AND ( h.sw_ip = v.sw_ip OR ( h.sw_ip IS
NULL AND v.sw_ip IS NULL ) ) );
<<V13>>
--
Key fingerprint: 7448 3D26 EA5D 275C 749A B597 3576 0073 6AD2 9335
CREATE TABLE netdevs(
ip VARCHAR(256)
PRIMARY KEY,
dev_type INT4
NOT NULL,
name VARCHAR(256)
NOT NULL,
community VARCHAR(256)
NOT NULL,
first_found INT8
DEFAULT '0'
NOT NULL,
last_found INT8
DEFAULT '0'
NOT NULL
) WITHOUT OIDS;
CREATE TABLE interfaces(
netdev_ip VARCHAR(256)
NOT NULL
REFERENCES netdevs(ip)
ON DELETE CASCADE
ON UPDATE CASCADE,
ifindex INT4
NOT NULL,
ifinternal INT4,
description VARCHAR(256),
alias VARCHAR(256),
has_netdev INT4,
PRIMARY KEY(netdev_ip, ifindex)
) WITHOUT OIDS;
CREATE TABLE macs(
mac VARCHAR(256)
NOT NULL,
netdev_ip VARCHAR(256)
NOT NULL,
ifindex INT4
NOT NULL,
PRIMARY KEY (mac,netdev_ip),
FOREIGN KEY (netdev_ip, ifindex)
REFERENCES interfaces(netdev_ip, ifindex)
ON DELETE CASCADE
ON UPDATE CASCADE
) WITHOUT OIDS;
CREATE TABLE ips(
ip VARCHAR(256)
NOT NULL,
mac VARCHAR(256)
NOT NULL,
netdev_ip VARCHAR(256)
NOT NULL,
ifindex INT4
NOT NULL,
PRIMARY KEY(ip,mac,netdev_ip),
FOREIGN KEY(netdev_ip,ifindex)
REFERENCES interfaces(netdev_ip, ifindex)
ON DELETE CASCADE
ON UPDATE CASCADE
) WITHOUT OIDS;
CREATE TABLE vendors(
prefix VARCHAR(50)
PRIMARY KEY
NOT NULL,
name VARCHAR(256)
) WITHOUT OIDS;
CREATE SEQUENCE seq_history;
CREATE TABLE history(
id INT4
PRIMARY KEY
DEFAULT NEXTVAL('seq_history'),
ts INT8
NOT NULL
DEFAULT EXTRACT(EPOCH FROM CURRENT_TIMESTAMP),
last_ts INT8
NOT NULL
DEFAULT EXTRACT(EPOCH FROM CURRENT_TIMESTAMP),
ip VARCHAR(256),
-- NOT NULL,
mac VARCHAR(256),
-- NOT NULL,
router_ip VARCHAR(256),
router_name VARCHAR(256),
router_ifindex INT4,
router_ifdescr VARCHAR(256),
router_ifalias VARCHAR(256),
sw_ip VARCHAR(256),
sw_name VARCHAR(256),
sw_ifindex INT4,
sw_ifdescr VARCHAR(256),
sw_ifalias VARCHAR(256)
-- PRIMARY KEY(ts,ip,mac)
) WITHOUT OIDS;
CREATE TABLE history_state(
hist_id INT4
PRIMARY KEY
REFERENCES history(id)
ON DELETE CASCADE
ON UPDATE CASCADE,
last_ignore INT8,
description TEXT
) WITHOUT OIDS;
CREATE SEQUENCE seq_history_ignore;
CREATE TABLE history_ignore(
id INT4
PRIMARY KEY
DEFAULT NEXTVAL('seq_history_ignore'),
ip VARCHAR(256),
mac VARCHAR(256),
router_name VARCHAR(256),
router_ifindex VARCHAR(256),
sw_name VARCHAR(256),
sw_ifindex VARCHAR(256)
) WITHOUT OIDS;
--
-- v2.4.0
-- persistent interfaces
--
CREATE TABLE p_interfaces(
netdev_ip VARCHAR(256)
NOT NULL
REFERENCES netdevs(ip)
ON DELETE CASCADE
ON UPDATE CASCADE,
ifindex INT4
NOT NULL,
ifinternal INT4,
description VARCHAR(256),
alias VARCHAR(256),
-- when this interface was last known to had a netdev
last_had_netdev INT8
DEFAULT '0'
NOT NULL,
-- when this interface was last found.
ts INT8
DEFAULT '0'
NOT NULL,
PRIMARY KEY(netdev_ip, ifindex)
) WITHOUT OIDS;
CREATE TABLE reg_persons (
id VARCHAR(256)
PRIMARY KEY,
fname VARCHAR(256)
NOT NULL,
sname VARCHAR(256)
NOT NULL,
description TEXT
) WITHOUT OIDS;
CREATE TABLE reg_ips (
ip VARCHAR(256)
PRIMARY KEY,
reged_macs INTEGER
DEFAULT '0',
reged_ifs INTEGER
DEFAULT '0',
personid VARCHAR(256)
REFERENCES reg_persons(id)
ON UPDATE CASCADE
ON DELETE SET NULL,
shortdesc VARCHAR(256),
description TEXT
) WITHOUT OIDS;
CREATE TABLE reg_macs (
mac VARCHAR(256)
PRIMARY KEY,
reged_ips INTEGER
DEFAULT '0',
reged_ifs INTEGER
DEFAULT '0',
personid VARCHAR(256)
REFERENCES reg_persons(id)
ON UPDATE CASCADE
ON DELETE SET NULL,
shortdesc VARCHAR(256),
description TEXT
) WITHOUT OIDS;
CREATE TABLE reg_ifaces(
netdev_ip VARCHAR(256),
ifindex INT4,
reged_ips INTEGER
DEFAULT '0',
reged_macs INTEGER
DEFAULT '0',
personid VARCHAR(256)
REFERENCES reg_persons(id)
ON UPDATE CASCADE
ON DELETE SET NULL,
shortdesc VARCHAR(256),
description TEXT,
do_locking INTEGER
DEFAULT '0',
PRIMARY KEY(netdev_ip,ifindex),
FOREIGN KEY(netdev_ip,ifindex)
REFERENCES p_interfaces(netdev_ip, ifindex)
ON DELETE CASCADE
ON UPDATE CASCADE
) WITHOUT OIDS;
CREATE TABLE reg_ip2mac (
-- ip VARCHAR(256)
ip INET
NOT NULL,
mac VARCHAR(256)
NOT NULL,
PRIMARY KEY (ip,mac)
) WITHOUT OIDS;
CREATE TABLE reg_ip2iface (
-- ip VARCHAR(256)
ip INET
NOT NULL,
netdev_ip VARCHAR(256)
NOT NULL,
ifindex INT4
NOT NULL,
PRIMARY KEY (ip,netdev_ip,ifindex),
FOREIGN KEY(netdev_ip,ifindex)
REFERENCES p_interfaces(netdev_ip, ifindex)
ON DELETE CASCADE
ON UPDATE CASCADE
) WITHOUT OIDS;
CREATE TABLE reg_mac2iface (
mac VARCHAR(256)
NOT NULL,
netdev_ip VARCHAR(256)
NOT NULL,
ifindex INT4
NOT NULL,
PRIMARY KEY (mac,netdev_ip,ifindex),
FOREIGN KEY(netdev_ip,ifindex)
REFERENCES p_interfaces(netdev_ip, ifindex)
ON DELETE CASCADE
ON UPDATE CASCADE
) WITHOUT OIDS;
CREATE TABLE blacklist (
mac VARCHAR(256)
NOT NULL
PRIMARY KEY,
-- REFERENCES reg_macs(mac)
-- ON DELETE CASCADE
-- ON UPDATE CASCADE,
ts INT8
NOT NULL
DEFAULT EXTRACT(EPOCH FROM CURRENT_TIMESTAMP),
reason TEXT
-- ,
-- PRIMARY KEY (mac,ts)
) WITHOUT OIDS;
CREATE SEQUENCE seq_ml_log;
CREATE TABLE ml_log(
id INT4
PRIMARY KEY
DEFAULT NEXTVAL('seq_ml_log'),
ts INT8
NOT NULL
DEFAULT EXTRACT(EPOCH FROM CURRENT_TIMESTAMP),
severity INT4
NOT NULL
DEFAULT 10,
msg TEXT
NOT NULL
) WITHOUT OIDS;
CREATE TABLE ml_devs(
netdev_ip VARCHAR(256)
PRIMARY KEY
REFERENCES netdevs(ip)
ON DELETE CASCADE
ON UPDATE CASCADE,
uname VARCHAR(256),
t_pass VARCHAR(256),
en_pass VARCHAR(256),
do_locking INT4
DEFAULT 0,
last_update INT8,
last_try INT8
) WITHOUT OIDS;
CREATE VIEW view_macs AS
SELECT macs.mac AS mac,
macs.netdev_ip AS sw_ip,
macs.ifindex AS sw_ifindex,
netdevs.name AS sw_name,
interfaces.description AS sw_ifdescr,
interfaces.alias AS sw_ifalias
FROM macs, interfaces, netdevs
WHERE macs.netdev_ip = netdevs.ip AND
macs.netdev_ip = interfaces.netdev_ip AND
macs.ifindex = interfaces.ifindex;
CREATE VIEW view_ips AS
SELECT ips.ip AS ip,
ips.mac AS mac,
netdevs.name AS router_name,
netdevs.ip AS router_ip,
interfaces.ifindex AS router_ifindex,
interfaces.description AS router_ifdescr,
interfaces.alias AS router_ifalias
FROM ips, netdevs, interfaces
WHERE ips.netdev_ip = netdevs.ip AND
interfaces.netdev_ip = netdevs.ip AND
ips.ifindex = interfaces.ifindex;
CREATE VIEW view_all_pre AS
SELECT i.ip AS ip,
mac AS mac,
i.router_ip AS router_ip,
i.router_name AS router_name,
i.router_ifindex AS router_ifindex,
i.router_ifdescr AS router_ifdescr,
i.router_ifalias AS router_ifalias,
m.sw_ip AS sw_ip,
m.sw_name AS sw_name,
m.sw_ifindex AS sw_ifindex,
m.sw_ifdescr AS sw_ifdescr,
m.sw_ifalias AS sw_ifalias
FROM view_ips i FULL JOIN view_macs m
USING(mac);
CREATE VIEW view_all AS
SELECT ip, mac, router_ip, router_name, router_ifindex, router_ifdescr,
router_ifalias, sw_ip, sw_name, sw_ifindex, sw_ifdescr, sw_ifalias,
vendors.name AS mac_name
FROM view_all_pre LEFT OUTER JOIN vendors
ON vendors.prefix = substr(mac,1,8);
CREATE VIEW view_history AS
SELECT id, ts, last_ts, ip, mac, router_ip, router_name, router_ifindex,
router_ifdescr, router_ifalias, sw_ip, sw_name, sw_ifindex, sw_ifdescr,
sw_ifalias, vendors.name AS mac_name
FROM history LEFT OUTER JOIN vendors
ON vendors.prefix = substr(mac,1,8);
CREATE VIEW view_history_not_ignored AS
SELECT * FROM view_history h
WHERE NOT EXISTS
( SELECT * FROM history_ignore i
WHERE ( i.router_name IS NULL OR i.router_name='' OR
i.router_name=h.router_name ) AND
( i.router_ifindex IS NULL OR
i.router_ifindex='' OR i.router_ifindex=h.router_ifindex ) AND
( i.sw_name IS NULL OR i.sw_name='' OR
i.sw_name=h.sw_name ) AND
( i.sw_ifindex IS NULL OR i.sw_ifindex='' OR
i.sw_ifindex=h.sw_ifindex ) AND
( i.ip IS NULL OR i.ip='' OR
i.ip=substring(h.ip from 1 for octet_length(i.ip))) AND
( i.mac IS NULL OR i.mac='' OR
i.mac=substring(h.mac from 1 for octet_length(i.mac))) );
-- Same as view_history_not_ignore but don't include
-- entries with state ignored
CREATE VIEW view_history_not_ignore_state AS
SELECT * FROM view_history_not_ignored h LEFT JOIN history_state hs
ON h.id=hs.hist_id
WHERE NOT EXISTS
( SELECT * FROM history_state hs
WHERE h.id = hs.hist_id AND
h.last_ts <= hs.last_ignore );
CREATE VIEW view_stated AS
SELECT * FROM view_history_not_ignored h JOIN history_state hs
ON h.id=hs.hist_id;
--CREATE VIEW view_hist AS
-- SELECT ip, mac, sw_ip, sw_ifindex, ts, last_ts FROM history;
-- position change
CREATE VIEW view_hist_ip_mac_cnt AS
SELECT ip, mac, count(*) FROM
( SELECT ip, mac, last_ts FROM view_history_not_ignored
GROUP BY ip, mac, last_ts ) lala
GROUP BY ip,mac;
-- mac change
CREATE VIEW view_hist_ip_cnt AS
SELECT ip, count(*) FROM
( SELECT DISTINCT ip, mac
FROM view_history_not_ignored ) koko
GROUP BY ip;
-- ip change
CREATE VIEW view_hist_mac_cnt AS
SELECT mac, count(*) FROM
( SELECT DISTINCT ip, mac
FROM view_history_not_ignored ) koko
GROUP BY mac;
-- position change (without ignored)
CREATE VIEW view_hist_ip_mac_cnt_state AS
SELECT ip, mac, count(*) FROM
( SELECT ip, mac, last_ts FROM view_history_not_ignore_state
GROUP BY ip, mac, last_ts ) lala
GROUP BY ip,mac;
-- mac change (without ignored)
CREATE VIEW view_hist_ip_cnt_state AS
SELECT ip, count(*) FROM
( SELECT DISTINCT ip, mac
FROM view_history_not_ignore_state ) koko
GROUP BY ip;
-- ip change (without ignored)
CREATE VIEW view_hist_mac_cnt_state AS
SELECT mac, count(*) FROM
( SELECT DISTINCT ip, mac
FROM view_history_not_ignore_state ) koko
GROUP BY mac;
-- view_all with ip and mac registries
--CREATE VIEW view_all_reg AS
-- SELECT ip, a.mac, router_ip, router_name, router_ifindex, router_ifdescr,
-- router_ifalias, sw_ip, sw_name, sw_ifindex, sw_ifdescr, sw_ifalias,
-- mac_name, reg_ip_ip, ip_personid, reged_macs, ip_shortdesc,
-- reg_macs.mac AS reg_mac_mac, reg_macs.personid AS mac_personid,
-- reged_ips, reg_macs.shortdesc AS mac_shortdesc FROM
-- ( SELECT view_all.ip, mac, router_ip, router_name,
router_ifindex, router_ifdescr,
-- router_ifalias, sw_ip, sw_name, sw_ifindex,
sw_ifdescr, sw_ifalias,
-- mac_name, personid AS ip_personid, reged_macs,
shortdesc AS ip_shortdesc,
-- reg_ips.ip AS reg_ip_ip
-- FROM view_all LEFT JOIN reg_ips
-- ON view_all.ip=reg_ips.ip ) AS a LEFT
JOIN reg_macs
-- ON a.mac=reg_macs.mac;
--CREATE VIEW view_history_reg AS
-- SELECT id, ts, last_ts, ip, a.mac, router_ip, router_name, router_ifindex,
-- router_ifdescr, router_ifalias, sw_ip, sw_name, sw_ifindex, sw_ifdescr,
-- sw_ifalias, mac_name, reg_ip_ip, ip_personid, reged_macs, ip_shortdesc,
-- reg_macs.mac AS reg_mac_mac, reg_macs.personid AS mac_personid,
-- reged_ips, reg_macs.shortdesc AS mac_shortdesc FROM
-- ( SELECT id, ts, last_ts, view_history.ip, mac, router_ip,
router_name, router_ifindex,
-- router_ifdescr, router_ifalias, sw_ip, sw_name,
sw_ifindex, sw_ifdescr,
-- sw_ifalias, mac_name, personid AS ip_personid,
reged_macs,
-- shortdesc AS ip_shortdesc, reg_ips.ip AS reg_ip_ip
-- FROM view_history LEFT JOIN reg_ips
-- ON view_history.ip=reg_ips.ip ) AS a
LEFT JOIN reg_macs
-- ON a.mac=reg_macs.mac;
CREATE VIEW view_history_reg AS
SELECT v.ip, v.mac,
id, ts, last_ts, router_ip, router_name, router_ifindex,
router_ifdescr, router_ifalias,
sw_ip, sw_name, sw_ifindex, sw_ifdescr, sw_ifalias, mac_name,
i.reged_macs AS ip_reged_macs, i.reged_ifs AS ip_reged_ifs,
m.reged_ips AS mac_reged_ips, m.reged_ifs AS mac_reged_ifs,
f.reged_ips AS if_reged_ips, f.reged_macs AS if_reged_macs,
( SELECT 1 WHERE EXISTS ( SELECT * FROM reg_ip2mac ii WHERE
ii.ip>>=INET(v.ip) AND ii.mac=v.mac ) ) AS i2m_assoc,
( SELECT 1 WHERE EXISTS ( SELECT * FROM reg_ip2iface ii WHERE
ii.ip>>=INET(v.ip) AND ii.ifindex=v.sw_ifindex AND ii.netdev_ip=v.sw_ip ) ) AS
i2f_assoc,
( SELECT 1 WHERE EXISTS ( SELECT * FROM reg_mac2iface ii WHERE
ii.mac=v.mac AND ii.ifindex=v.sw_ifindex AND ii.netdev_ip=v.sw_ip ) ) AS m2f_assoc,
( SELECT 1 WHERE i.ip IS NOT NULL ) AS ip_is_reg,
( SELECT 1 WHERE m.mac IS NOT NULL ) AS mac_is_reg,
( SELECT 1 WHERE f.netdev_ip IS NOT NULL ) AS if_is_reg,
i.shortdesc AS ip_shortdesc, m.shortdesc AS mac_shortdesc, f.shortdesc
AS if_shortdesc
FROM view_history v
LEFT JOIN reg_ips i ON INET(v.ip)<<=INET(i.ip)
LEFT JOIN reg_macs m ON v.mac=m.mac
LEFT JOIN reg_ifaces f ON ( v.sw_ip=f.netdev_ip AND
v.sw_ifindex=f.ifindex )
LEFT JOIN reg_ip2mac i2m ON ( INET(v.ip)<<=i2m.ip AND
v.mac=i2m.mac )
LEFT JOIN reg_ip2iface i2f ON ( INET(v.ip)<<=i2f.ip AND
i2f.ifindex=v.sw_ifindex AND i2f.netdev_ip=v.sw_ip )
LEFT JOIN reg_mac2iface m2f ON ( v.mac=m2f.mac AND
m2f.ifindex=v.sw_ifindex AND m2f.netdev_ip=v.sw_ip )
;
CREATE VIEW view_all_reg AS
SELECT v.ip, v.mac,
router_ip, router_name, router_ifindex, router_ifdescr, router_ifalias,
sw_ip, sw_name, sw_ifindex, sw_ifdescr, sw_ifalias, mac_name,
i.reged_macs AS ip_reged_macs, i.reged_ifs AS ip_reged_ifs,
m.reged_ips AS mac_reged_ips, m.reged_ifs AS mac_reged_ifs,
f.reged_ips AS if_reged_ips, f.reged_macs AS if_reged_macs,
( SELECT 1 WHERE EXISTS ( SELECT * FROM reg_ip2mac ii WHERE
ii.ip>>=INET(v.ip) AND ii.mac=v.mac ) ) AS i2m_assoc,
( SELECT 1 WHERE EXISTS ( SELECT * FROM reg_ip2iface ii WHERE
ii.ip>>=INET(v.ip) AND ii.ifindex=v.sw_ifindex AND ii.netdev_ip=v.sw_ip ) ) AS
i2f_assoc,
( SELECT 1 WHERE EXISTS ( SELECT * FROM reg_mac2iface ii WHERE
ii.mac=v.mac AND ii.ifindex=v.sw_ifindex AND ii.netdev_ip=v.sw_ip ) ) AS m2f_assoc,
( SELECT 1 WHERE i.ip IS NOT NULL ) AS ip_is_reg,
( SELECT 1 WHERE m.mac IS NOT NULL ) AS mac_is_reg,
( SELECT 1 WHERE f.netdev_ip IS NOT NULL ) AS if_is_reg,
i.shortdesc AS ip_shortdesc, m.shortdesc AS mac_shortdesc, f.shortdesc
AS if_shortdesc,
f.do_locking AS if_do_locking
FROM view_all v
LEFT JOIN reg_ips i ON INET(v.ip)<<=INET(i.ip)
LEFT JOIN reg_macs m ON v.mac=m.mac
LEFT JOIN reg_ifaces f ON ( v.sw_ip=f.netdev_ip AND
v.sw_ifindex=f.ifindex )
LEFT JOIN reg_ip2mac i2m ON ( i2m.ip>>=INET(v.ip) AND
v.mac=i2m.mac )
LEFT JOIN reg_ip2iface i2f ON ( i2f.ip>>=INET(v.ip) AND
i2f.ifindex=v.sw_ifindex AND i2f.netdev_ip=v.sw_ip )
LEFT JOIN reg_mac2iface m2f ON ( v.mac=m2f.mac AND
m2f.ifindex=v.sw_ifindex AND m2f.netdev_ip=v.sw_ip )
;
CREATE VIEW view_err AS
SELECT * FROM view_all_reg WHERE
sw_ip IS NOT NULL AND
sw_ip <> '' AND
mac IS NOT NULL AND
if_do_locking = 1 AND (
( mac IS NOT NULL AND
m2f_assoc IS NULL AND
( mac_reged_ifs = 1 OR
if_reged_macs = 1 ) ) OR
( ip IS NOT NULL AND
i2f_assoc IS NULL AND
( ip_reged_ifs = 1 OR
if_reged_ips = 1 ) ) OR
( ip IS NOT NULL AND
mac IS NOT NULL AND
i2m_assoc IS NULL AND
( ip_reged_macs = 1 OR
mac_reged_ips = 1 ) ) )
;
CREATE VIEW view_locks AS
SELECT p.netdev_ip AS sw_ip, p.ifindex AS ifindex,
p.description AS ifdescr, m2f.mac AS mac
FROM p_interfaces p, reg_ifaces i
LEFT JOIN reg_mac2iface m2f ON (
m2f.netdev_ip = i.netdev_ip AND m2f.ifindex = i.ifindex )
WHERE p.netdev_ip = i.netdev_ip AND
p.ifindex = i.ifindex AND
i.do_locking = '1' AND
NOT EXISTS (
SELECT * FROM blacklist bl
WHERE bl.mac=m2f.mac )
;
-- Views for locating good associations
CREATE VIEW view_t_i2m AS
SELECT DISTINCT ip, mac
FROM history h
WHERE
(SELECT count(*)
FROM ( SELECT DISTINCT ip
FROM history h2
WHERE mac=h.mac ) AS a
) = 1 AND
(SELECT count(*)
FROM ( SELECT DISTINCT mac
FROM history h2
WHERE ip=h.ip ) AS a
) = 1;
CREATE VIEW view_t_i2m_not AS
SELECT DISTINCT ip,mac
FROM history h
EXCEPT SELECT ip,mac
FROM view_t_i2m;
CREATE VIEW view_t_i2f AS
SELECT DISTINCT ip, sw_ip, sw_ifindex
FROM history h
WHERE
(SELECT count(*)
FROM ( SELECT DISTINCT sw_ip, sw_ifindex
FROM history h2
WHERE ip=h.ip ) AS a
) = 1 AND
sw_ifindex IS NOT NULL;
CREATE VIEW view_t_i2f_not AS
SELECT DISTINCT ip, sw_ip, sw_ifindex
FROM history h
WHERE sw_ifindex IS NOT NULL
EXCEPT SELECT ip, sw_ip, sw_ifindex
FROM view_t_i2f;
CREATE VIEW view_t_m2f AS
SELECT DISTINCT mac, sw_ip, sw_ifindex
FROM history h
WHERE
(SELECT count(*)
FROM ( SELECT DISTINCT sw_ip, sw_ifindex
FROM history h2
WHERE mac=h.mac ) AS a
) = 1 AND
sw_ifindex IS NOT NULL;
CREATE VIEW view_t_m2f_not AS
SELECT DISTINCT mac, sw_ip, sw_ifindex
FROM history h
WHERE sw_ifindex IS NOT NULL
EXCEPT SELECT mac, sw_ip, sw_ifindex
FROM view_t_m2f;
CREATE VIEW view_lockable_interfaces AS
SELECT * FROM netdevs n, p_interfaces p
WHERE n.dev_type=2 AND
n.ip=p.netdev_ip AND
( p.description LIKE 'Fast%' OR
p.description LIKE 'Giga%' );
CREATE VIEW view_lock_interfaces AS
SELECT ip, name, tot_ifs, lockable_ifs, netdev_ifs,
locked_ifs, uname, t_pass, en_pass,
do_locking, last_update, last_try
FROM
( SELECT ip, name, count(*) AS tot_ifs
FROM netdevs n, p_interfaces p
WHERE n.dev_type=2 AND
n.ip=p.netdev_ip
GROUP BY ip,name ) AS a
LEFT JOIN
( SELECT ip, count(*) AS lockable_ifs
FROM view_lockable_interfaces v
GROUP BY ip ) AS b USING(ip)
LEFT JOIN
( SELECT ip, count(*) AS netdev_ifs
FROM view_lockable_interfaces v
WHERE v.last_had_netdev>0
GROUP BY ip ) AS c USING(ip)
LEFT JOIN
( SELECT ip, count(*) AS locked_ifs
FROM view_lockable_interfaces v,
reg_ifaces rif
WHERE v.netdev_ip=rif.netdev_ip AND
v.ifindex=rif.ifindex AND
rif.do_locking=1
GROUP BY ip) AS d USING(ip)
LEFT JOIN ml_devs m ON a.ip=m.netdev_ip
;
CREATE VIEW view_lock_interface AS
SELECT p.netdev_ip, p.ifindex, p.description, p.alias,
( SELECT 1 WHERE p.last_had_netdev IS NOT NULL AND p.last_had_netdev >
0 ) AS has_netdev,
r.do_locking,
( SELECT 1 WHERE r.ifindex IS NOT NULL ) AS reged_if,
( SELECT COUNT(DISTINCT mac) FROM macs m WHERE
m.netdev_ip=p.netdev_ip AND m.ifindex=p.ifindex ) AS last_devs,
( SELECT COUNT(DISTINCT mac) FROM history h WHERE
h.sw_ip=p.netdev_ip AND h.sw_ifindex=p.ifindex ) AS hist_devs,
( SELECT COUNT(*) FROM reg_ip2iface r WHERE
r.netdev_ip=p.netdev_ip AND r.ifindex=p.ifindex) AS ip_assocs,
( SELECT COUNT(*) FROM reg_mac2iface r WHERE
r.netdev_ip=p.netdev_ip AND r.ifindex=p.ifindex) AS mac_assocs
FROM p_interfaces p
LEFT JOIN reg_ifaces r
ON p.ifindex=r.ifindex AND p.netdev_ip=r.netdev_ip;
-- Devices that are candidates for locking
-- is_old == 1 if the device was last set more than 32 hours ago
CREATE VIEW view_ml_tolock AS
SELECT *,
( SELECT 1 WHERE
( ((EXTRACT(EPOCH FROM CURRENT_TIMESTAMP)) - last_try) >
115200) OR
last_try IS NULL ) AS is_old
FROM ml_devs
WHERE ( t_pass IS NOT NULL AND
en_pass IS NOT NULL AND
t_pass <> '' AND
en_pass <> '' ) AND
do_locking=1;
--
-- ip,mac
-- +--- not exist -> INSERT
-- |
-- +--- exist
-- +--- old sw_ip is NULL -> UPDATE (delete & insert)
-- |
-- +--- old sw_ip is NOT NULL
-- +-- new sw_ip is NULL -> do nothing
-- |
-- +-- new sw_ip is NOT NULL
-- +-- old sw_ip == new sw_ip AND
-- | old sw_ifindex == new sw_ifindex -> UPDATE last_ts
-- |
-- +-- old sw_ip != new sw_ip OR
-- old sw_ifindex != new sw_ifindex -> INSERT
--
--
--v2.4.0: Don't insert entries reported for interfaces which were known
-- to have netdevs (from p_interfaces)
--
--
--
--
--
CREATE OR REPLACE FUNCTION set_history()
RETURNS INT4
AS '
-- Delete old entries with unknown switch ip that are
-- now known
DELETE FROM history
WHERE history.sw_ip IS NULL AND
EXISTS ( SELECT * FROM view_all v
WHERE history.ip = v.ip AND
history.mac = v.mac AND
v.sw_ip IS NOT NULL );
-- Delete old entries with unknown ip that are now known
DELETE FROM history
WHERE history.ip IS NULL AND
EXISTS ( SELECT * FROM view_all v
WHERE history.mac=v.mac AND
v.ip IS NOT NULL );
-- Insert New entries
INSERT INTO
history(ip,mac,router_ip,router_name,router_ifindex,router_ifdescr,router_ifalias,sw_ip,
sw_name,sw_ifindex,sw_ifdescr,sw_ifalias)
SELECT
ip,mac,router_ip,router_name,router_ifindex,router_ifdescr,router_ifalias,
sw_ip,sw_name,sw_ifindex,sw_ifdescr,sw_ifalias
FROM view_all v
WHERE
-- ip IS NOT NULL AND
-- mac IS NOT NULL AND
-- on interfaces where there was never a netdev
( NOT EXISTS (
SELECT last_had_netdev FROM p_interfaces p
WHERE p.netdev_ip=v.sw_ip AND
p.ifindex=v.sw_ifindex AND
p.last_had_netdev > 0 ) ) AND
-- and there is no same history entry
( NOT EXISTS ( SELECT * FROM history h
WHERE ( h.ip = v.ip OR ( h.ip
IS NULL AND v.ip IS NULL ) ) AND
( h.mac = v.mac OR (
h.mac IS NULL AND v.mac IS NULL ) ) AND
( h.sw_ip = v.sw_ip OR
( h.sw_ip IS NULL AND v.sw_ip IS NULL ) ) AND
( h.sw_ifindex =
v.sw_ifindex OR ( h.sw_ifindex IS NULL AND v.sw_ifindex IS NULL ) )
) ) AND
-- and we know more info than we did (if it not a change)
-- if sw_ip IS NULL and ip/mac already exists dont add it (ip/mac cannot be null
in that case)
( v.sw_ip IS NOT NULL OR
( NOT EXISTS ( SELECT * FROM history h
WHERE h.ip = v.ip AND
h.mac = v.mac )
) ) AND
-- if ip IS NULL and mac/sw_ip/sw_ifindex already exists dont add it (
mac/sw_ip/sw_ifindex cannot be null )
( v.ip IS NOT NULL OR
( NOT EXISTS ( SELECT * FROM history h
WHERE h.mac = v.mac AND
h.sw_ip = v.sw_ip AND
h.sw_ifindex = v.sw_ifindex )
) )
;
-- AND
-- and:
-- there is a history entry with different sw_ip/sw_ifindex
-- ( EXISTS ( SELECT * FROM history h
-- WHERE ( h.ip = v.ip OR ( h.ip IS NULL
AND v.ip IS NULL ) ) AND
-- ( h.mac = v.mac OR ( h.mac IS
NULL AND v.mac IS NULL ) ) AND
-- h.sw_ip IS NOT NULL AND
-- ( h.sw_ip <> v.sw_ip OR
-- h.sw_ifindex <> v.sw_ifindex )
AND
-- v.sw_ip IS NOT NULL ) );
-- and no same entry exists
-- Update existing entries
UPDATE history SET last_ts = EXTRACT(EPOCH FROM CURRENT_TIMESTAMP)
WHERE history.sw_ip IS NOT NULL AND
EXISTS ( SELECT * FROM view_all v
WHERE ( v.ip = history.ip OR ( v.ip IS
NULL AND history.ip IS NULL ) ) AND
( v.mac = history.mac OR (
v.mac IS NULL AND history.mac IS NULL ) ) AND
v.sw_ip = history.sw_ip AND
v.sw_ifindex =
history.sw_ifindex );
SELECT 1;
'
LANGUAGE SQL;
-- Remove data that are older than a week
CREATE OR REPLACE FUNCTION expire_history()
RETURNS INT4
AS '
DELETE FROM history
WHERE last_ts < ( EXTRACT(EPOCH FROM CURRENT_TIMESTAMP) -
604800 );
SELECT 1;
'
LANGUAGE SQL;
-- Remove reg_ip2mac entries that have no reged ip and no reged mac
CREATE OR REPLACE FUNCTION fix_ip2mac_ip_del()
RETURNS OPAQUE
AS '
BEGIN
DELETE FROM reg_ip2mac
WHERE reg_ip2mac.ip<<=INET(OLD.ip) AND
NOT EXISTS (
SELECT * FROM reg_macs m
WHERE reg_ip2mac.mac=m.mac ) AND
NOT EXISTS (
SELECT * FROM reg_ips i
WHERE reg_ip2mac.ip<<=INET(i.ip) );
RETURN OLD;
END
'
LANGUAGE 'plpgsql';
CREATE OR REPLACE FUNCTION fix_ip2mac_mac_del()
RETURNS OPAQUE
AS '
BEGIN
DELETE FROM reg_ip2mac
WHERE reg_ip2mac.mac=OLD.mac AND
NOT EXISTS (
SELECT * FROM reg_ips i
WHERE reg_ip2mac.ip<<=INET(i.ip) );
RETURN OLD;
END
'
LANGUAGE 'plpgsql';
-- Generic fix - not called by a trigger
CREATE OR REPLACE FUNCTION fix_ip2mac()
RETURNS INTEGER
AS '
BEGIN
-- TODO: Enable those
DELETE FROM reg_ip2mac
WHERE NOT EXISTS (
SELECT * FROM reg_ips i
WHERE reg_ip2mac.ip<<=INET(i.ip) ) AND
NOT EXISTS (
SELECT * FROM reg_macs m
WHERE reg_ip2mac.mac=m.mac );
RETURN 1;
END
'
LANGUAGE 'plpgsql';
-- When updating reg_ips or reg_macs also update reg_ip2mac
CREATE OR REPLACE FUNCTION fix_ip2mac_ip_upd()
RETURNS OPAQUE
AS '
BEGIN
UPDATE reg_ip2mac
SET ip=INET(NEW.ip)
WHERE ip=OLD.ip;
RETURN NEW;
END
'
LANGUAGE 'plpgsql';
CREATE OR REPLACE FUNCTION fix_ip2mac_mac_upd()
RETURNS OPAQUE
AS '
BEGIN
UPDATE reg_ip2mac
SET mac=NEW.mac
WHERE mac=OLD.mac;
RETURN NEW;
END
'
LANGUAGE 'plpgsql';
-- Auto run the above function
CREATE TRIGGER trig_fix_del AFTER DELETE
ON reg_ips FOR EACH ROW
EXECUTE PROCEDURE fix_ip2mac_ip_del();
CREATE TRIGGER trig_fix_del AFTER DELETE
ON reg_macs FOR EACH ROW
EXECUTE PROCEDURE fix_ip2mac_mac_del();
CREATE TRIGGER trig_fix_upd AFTER UPDATE
ON reg_ips FOR EACH ROW
EXECUTE PROCEDURE fix_ip2mac_ip_upd();
CREATE TRIGGER trig_fix_upd AFTER UPDATE
ON reg_macs FOR EACH ROW
EXECUTE PROCEDURE fix_ip2mac_mac_upd();
--
-- Set last_found when an interface is updated
--
CREATE OR REPLACE FUNCTION set_last_found()
RETURNS OPAQUE
AS '
BEGIN
-- Reset first found if device was not updated for 5 days
UPDATE netdevs
SET first_found=EXTRACT(EPOCH FROM CURRENT_TIMESTAMP)
WHERE ip=NEW.netdev_ip AND
( EXTRACT(EPOCH FROM CURRENT_TIMESTAMP) - last_found )
> 432000;
UPDATE netdevs
SET last_found=EXTRACT(EPOCH FROM CURRENT_TIMESTAMP)
WHERE ip=NEW.netdev_ip;
RETURN NEW;
END
'
LANGUAGE 'plpgsql';
CREATE TRIGGER trig_set_last_found
AFTER INSERT OR UPDATE
ON interfaces
FOR EACH ROW EXECUTE PROCEDURE set_last_found();
-- update p_interfaces as needed
CREATE OR REPLACE FUNCTION set_piface()
RETURNS INTEGER
AS '
BEGIN
-- delete changed interfaces and interfaces older than 5 days
DELETE FROM p_interfaces
WHERE EXISTS (
SELECT * FROM interfaces i
WHERE i.netdev_ip = p_interfaces.netdev_ip AND
i.ifindex = p_interfaces.ifindex AND
( (
-- If has_netdev changed from 0 to 1 , then this interface has changed
i.has_netdev=1 AND
p_interfaces.last_had_netdev<p_interfaces.ts
) OR
-- If alias chaqnged
-- i.alias<>p_interfaces.alias
-- OR
-- If description changed
i.description<>p_interfaces.description
) ) OR
( (EXTRACT(EPOCH FROM CURRENT_TIMESTAMP) -
p_interfaces.ts) > 432000) OR
-- If this is not a netdev interface any more (5 days)
( p_interfaces.last_had_netdev > 10 AND
( EXTRACT(EPOCH FROM CURRENT_TIMESTAMP) -
p_interfaces.last_had_netdev ) > 432000 );
-- If alias changed do not perform locking any more
UPDATE reg_ifaces
SET do_locking=0
WHERE EXISTS (
SELECT * FROM interfaces i, p_interfaces p
WHERE i.netdev_ip = p.netdev_ip AND
i.ifindex = p.ifindex AND
i.netdev_ip = reg_ifaces.netdev_ip AND
i.ifindex = reg_ifaces.ifindex AND (
( i.alias IS NULL AND p.alias IS NOT
NULL AND p.alias<>'' ) OR
( p.alias IS NULL AND i.alias IS NOT
NULL AND i.alias<>'' ) OR
( i.alias IS NOT NULL AND p.alias IS
NOT NULL AND i.alias <> p.alias ) ) );
-- insert non-existent interfaces into p_interfaces
INSERT INTO p_interfaces(netdev_ip, ifindex, ifinternal, description,
alias)
SELECT netdev_ip, ifindex, ifinternal, description, alias
FROM interfaces i
WHERE NOT EXISTS (
SELECT * FROM p_interfaces
WHERE netdev_ip=i.netdev_ip AND
ifindex=i.ifindex) AND
ifinternal<>-1;
-- update last_found
UPDATE p_interfaces
SET ts=EXTRACT(EPOCH FROM CURRENT_TIMESTAMP)
WHERE EXISTS (
SELECT * FROM interfaces i
WHERE i.netdev_ip=p_interfaces.netdev_ip AND
i.ifindex=p_interfaces.ifindex );
-- update last_had_netdev
-- this has to be done AFTER updating last_found to prevent last_had_netdev<last_found
UPDATE p_interfaces
SET last_had_netdev=EXTRACT(EPOCH FROM CURRENT_TIMESTAMP)
WHERE EXISTS (
SELECT * FROM interfaces i
WHERE i.netdev_ip=p_interfaces.netdev_ip AND
i.ifindex=p_interfaces.ifindex AND
i.has_netdev>0 );
-- update alias
UPDATE p_interfaces
SET alias=(
SELECT alias FROM interfaces i
WHERE i.netdev_ip=p_interfaces.netdev_ip AND
i.ifindex=p_interfaces.ifindex )
WHERE ( alias<>(
SELECT alias FROM interfaces i
WHERE i.netdev_ip=p_interfaces.netdev_ip AND
i.ifindex=p_interfaces.ifindex ) );
RETURN 1;
END
'
LANGUAGE 'plpgsql';
CREATE OR REPLACE FUNCTION update_blacklist()
RETURNS INTEGER
AS '
BEGIN
INSERT INTO blacklist(mac,reason)
SELECT mac, \'(auto) MAC-Interface mismatch\' FROM view_err v
WHERE NOT EXISTS ( SELECT * FROM blacklist b WHERE
v.mac=b.mac ) AND
( v.mac IS NOT NULL AND v.m2f_assoc IS NULL AND
( v.mac_reged_ifs = 1 OR
v.if_reged_macs = 1 ) );
-- AND
-- EXISTS ( SELECT * FROM reg_macs r WHERE
r.mac=v.mac) );
INSERT INTO blacklist(mac,reason)
SELECT mac, \'(auto) IP-Interface mismatch\' FROM view_err v
WHERE NOT EXISTS ( SELECT * FROM blacklist b WHERE
v.mac=b.mac ) AND
( v.ip IS NOT NULL AND v.i2f_assoc IS NULL AND
( v.ip_reged_ifs = 1 OR v.if_reged_ips
= 1 ) );
-- AND
-- EXISTS ( SELECT * FROM reg_macs r WHERE
r.mac=v.mac) );
INSERT INTO blacklist(mac,reason)
SELECT mac, \'(auto) IP-MAC mismatch\' FROM view_err v
WHERE NOT EXISTS ( SELECT * FROM blacklist b WHERE
v.mac=b.mac ) AND
( v.ip IS NOT NULL AND v.mac IS NOT NULL AND
v.i2m_assoc IS NULL AND
( v.ip_reged_macs = 1 OR
v.mac_reged_ips = 1 ) );
-- AND
-- EXISTS ( SELECT * FROM reg_macs r WHERE
r.mac=v.mac) );
RETURN 1;
END
'
LANGUAGE 'plpgsql';
CREATE OR REPLACE FUNCTION set_good_assoc()
RETURNS INTEGER
AS '
BEGIN
INSERT INTO reg_ip2mac(ip,mac)
SELECT INET(ip),mac FROM (
SELECT INET(ip) AS ip,mac FROM view_t_i2m
EXCEPT SELECT ip,mac FROM reg_ip2mac ) AS a;
INSERT INTO reg_ip2iface(ip, netdev_ip, ifindex)
SELECT INET(ip), sw_ip, sw_ifindex FROM (
SELECT INET(ip) AS ip, sw_ip, sw_ifindex FROM
view_t_i2f
EXCEPT SELECT ip, netdev_ip, ifindex FROM
reg_ip2iface ) AS a;
INSERT INTO reg_mac2iface(mac, netdev_ip, ifindex)
SELECT mac, sw_ip, sw_ifindex FROM (
SELECT mac, sw_ip, sw_ifindex FROM view_t_m2f
EXCEPT SELECT mac, netdev_ip, ifindex FROM
reg_mac2iface ) AS a;
RETURN 1;
END
'
LANGUAGE 'plpgsql';
--CREATE OR REPLACE FUNCTION trig_blacklist_add()
-- RETURNS OPAQUE
-- AS '
-- DECLARE
-- CNT INTEGER;
-- BEGIN
-- SELECT INTO CNT COUNT(*) FROM reg_mac2iface
-- WHERE mac=NEW.mac;
--
-- IF CNT = 0 THEN
-- RETURN NULL;
-- ELSE
-- RETURN NEW;
-- END IF;
-- END
-- '
-- LANGUAGE 'plpgsql';
--CREATE TRIGGER trig_blacklist_insert BEFORE INSERT
-- ON blacklist FOR EACH ROW
-- EXECUTE PROCEDURE trig_blacklist_add();
-- Expire log entries older than 7 days
CREATE OR REPLACE FUNCTION expire_ml_log()
RETURNS INTEGER
AS '
DELETE FROM ml_log
WHERE ts < ( EXTRACT(EPOCH FROM CURRENT_TIMESTAMP) - 604800 );
SELECT 1;
'
LANGUAGE SQL;
CREATE OR REPLACE FUNCTION set_ml_upd(VARCHAR(256))
RETURNS INTEGER
AS '
UPDATE ml_devs
SET last_update = (EXTRACT (EPOCH FROM CURRENT_TIMESTAMP))
WHERE netdev_ip = $1;
SELECT 1;
'
LANGUAGE SQL;
CREATE OR REPLACE FUNCTION set_ml_try(VARCHAR(256))
RETURNS INTEGER
AS '
UPDATE ml_devs
SET last_try = (EXTRACT (EPOCH FROM CURRENT_TIMESTAMP))
WHERE netdev_ip = $1;
SELECT 1;
'
LANGUAGE SQL;
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
