Observation import: monitor and handle default partition overflow

[ClemRz]

Summary

The t_measurement table uses PostgreSQL range partitioning with a t_measurement_default partition. If any row lands in the default partition (from a code path bypassing PartitionManager), PostgreSQL will reject CREATE TABLE ... PARTITION OF for that range — causing all future imports targeting that quarter to fail with HTTP 500 until the default partition is manually repartitioned.

Current Behaviour

PartitionManager.ensurePartitions() creates quarterly partitions on-the-fly during import. The DDL also creates t_measurement_default as a catch-all. If rows reach the default partition (e.g., a direct SQL insert during debugging, or a future code path that bypasses the manager), the affected quarter becomes permanently broken for the import pipeline.

Proposed Fix

Two mitigations:

1. Monitoring / alerting

Add a cron job or periodic check that queries:

SELECT COUNT(*) FROM ONLY t_measurement_default;

If > 0, alert — rows in the default partition indicate a problem that needs manual repartitioning.

2. Pre-check in PartitionManager (defensive)

Before CREATE TABLE IF NOT EXISTS, check if any rows in t_measurement_default fall within the target range. If so, move them to a temp table, create the partition, then re-insert:

-- Check for conflicting rows
SELECT COUNT(*) FROM ONLY t_measurement_default WHERE timestamp >= $start AND timestamp < $end;
-- If > 0: move, create partition, re-insert

This is more complex but makes the system self-healing.

3. Test topology alignment

The test customSQL.js creates t_measurement as an unpartitioned table with DOUBLE PRECISION columns, while production uses numeric and range partitioning. Consider aligning the test schema to exercise the production layout, or at minimum document the difference.

Complexity

Low for monitoring, medium for self-healing.

Priority

Medium — silent until it breaks, then blocks all imports for the affected quarter.