Bug #4367
closedBug #4252: Slow queries for list precalculation (heavy parallel sequential scan 8-20 sec per each 10 mins)
Enumeration tables not consistent for non array columns that can contain NULL
100%
Description
During the research of the new enumeration mechanism an edge case was omitted. The enum table update mechanism does not work correctly for columns that are not arrays and can contain NULL values, because in PostgreSQL (and SQL in general) NULL != NULL.
This does not seem to affect anything so it is just an internal consistency issue. The problem has arisen for columns cesnet_eventclass and cesnet_eventseverity.
The solution would be adding an additional UNIQUE index aimed especially at NULL values for the affected enum tables. An alternative solution would be disallowing NULL for data in enum tables, but this might not be ideal depending on the implementation (it might require further code alterations). Please discuss if you like the second solution better.
The proposed solution is as follows, running this on production systems:
DELETE FROM cesnet_eventclass WHERE data IS NULL; CREATE UNIQUE INDEX enum_cesnet_eventclass_data_null_key ON enum_cesnet_eventclass ((data IS NULL)) WHERE data IS NULL; DELETE FROM cesnet_eventseverity WHERE data IS NULL; CREATE UNIQUE INDEX enum_cesnet_eventseverity_data_null_key ON enum_cesnet_eventseverity ((data IS NULL)) WHERE data IS NULL;
and adding the UNIQUE indices to DB creation/migration scripts:
CREATE UNIQUE INDEX enum_cesnet_eventclass_data_null_key ON enum_cesnet_eventclass ((data IS NULL)) WHERE data IS NULL; CREATE UNIQUE INDEX enum_cesnet_eventseverity_data_null_key ON enum_cesnet_eventseverity ((data IS NULL)) WHERE data IS NULL;
The non-script part was already performed on dev and alt during development and testing.
Files
Related issues