Why is my materialized view not showing data for specific network interfaces?
I've created a materialized view to track state changes of network adapters. While many adapters show up correctly, specific et-
prefixed interfaces are missing, even though the NATS subscription events show state changes for them. Here's the SQL for the materialized view and the query that returns no rows for the et-5/1/0
interface, despite the NATS event indicating a state change:
CREATE MATERIALIZED VIEW port_state_changes AS WITH port_history AS (
SELECT
timestamp,
source,
regexp_replace(
regexp_replace(prefix, '^interfaces/interface\[name=', ''),
']',
''
) AS iface,
CAST(
COALESCE(
updates -> 0 -> 'values' ->> 'state/oper-status',
'N/A'
) ILIKE 'UP' AS BOOLEAN
) as up
FROM
gnmi_all
WHERE
(
"subscription-name" = 'netq-interface-state'
OR "subscription-name" = 'junos-interface-state'
)
AND updates -> 0 ->> 'Path' = 'state/oper-status'
),
port_history_diff AS (
SELECT
*,
LAG(up, 1) OVER (
PARTITION BY source,
iface
ORDER BY
timestamp ASC
) prev_state
FROM
port_history
)
SELECT
timestamp,
source,
iface,
up,
prev_state
FROM
port_history_diff
WHERE
(
up != prev_state
OR prev_state = null
);```
The NATS event for the `et-5/1/0` interface is as follows:
```json
{"source":"vaeq-cu2a-r201-wan-eth-pe-02","subscription-name":"junos-interface-state","timestamp":1703701723490358231,"time":"2023-12-27T18:28:43.490358231Z","prefix":"interfaces/interface[name=et-5/1/0]","updates":[{"Path":"state/oper-status","values":{"state/oper-status":"UP"}}]}
However, querying the materialized view for this interface yields no results:
dev=> select * from port_state_changes where iface='et-5/1/0';
timestamp | source | iface | up | prev_state
-----------+--------+-------+----+------------
(0 rows)
Josh Toft
Asked on Dec 27, 2023
The issue was due to incorrect SQL logic in the materialized view definition. Specifically, the condition prev_state = null
in the WHERE clause always evaluates to false because in SQL, NULL is not equal to anything, including itself. The correct way to check for NULL is using IS NULL
. The corrected WHERE clause should be up != prev_state OR prev_state IS NULL
. After making this change, the materialized view should correctly reflect the state changes for all network interfaces, including those with the et-
prefix.