Meter import bug - postgres unique constraints removed from meterreading table

[adrian-lara]

Expected Behavior

Meters and meter readings can be successfully imported.

Actual Behavior

An error is thrown within the dev1 database:
psycopg2.errors.InvalidColumnReference: there is no unique or exclusion constraint matching the ON CONFLICT specification

This was caused by a timescaledb version mismatch between prod (the dump source) and dev1 (the restore target). This mismatch caused the unique constraint to be lost (among other issues - see comment below).

It was decided that the most appropriate solution was to upgrade prod to the latest timescaledb version 1.6.0, install that same version on dev1, and explicitly specify this version within deployment scripts and documentation.

See comments for extra documentation from work-in-progress research.

Instance Information

Server Instance: dev1
Server SHA: 74a0116d

[adrian-lara]

Research Notes:
As the error states, there is a missing unique constraint on the dev1 database that was there previously. The loss of this unique constraint is related to a bug with timescaledb that was resolved in v 1.5.0.
(timescale/timescaledb#1409)

This needs to be confirmed, but it's likely that dev1 experienced this bug as it's current DB was a restore of the production DB that's likely on < timescale v1.5.0. The bug fix requires that the dump was created with 1.5+. Additionally, dumps and restores should really be done with the same version to avoid corrupting the DB.

Once confirmed, the first fix I'd like to attempt would be to upgrade the prod DB's timescaleDB, dump it, then restore it locally. If the unique constraint persists and meters can be imported without error a restore can be done for dev1.

Extra details:
It doesn't seem to be enough to add re-add a unique constraint on dev1 (and subsequently sync the Django migrations). The next error given when importing meters involves a ts_insert_blocker that shouldn't be in place at that time. This blocker prevents inserts on hypertables (the underlying tables that timescaledb uses to store records) and is enabled during a restore in which the SELECT timescaledb_pre_restore(); query is run. I suspect that the subsequent post_restore version of this query was not run, but this needs to be confirmed. For a specific import, if the ts_insert_blocker is removed on the target hypertable, another error comes up: unexpected failure to find arbiter index

[RDmitchell]

@nllong -- is there anything for me to test for this issue?

[nllong]

nope! closing. thanks!