Hello,
I am experiencing a duplicate key violation in Postgres 9.6 on occasion for one particular query, and I’m wondering where I’m going wrong. My table looks like this:
Table "solardatum.da_datum"
Column | Type | Collation | Nullable | Default
-----------+--------------------------+-----------+----------+---------
ts | timestamp with time zone | | not null |
node_id | bigint | | not null |
source_id | character varying(64) | | not null |
posted | timestamp with time zone | | not null |
jdata_i | jsonb | | |
jdata_a | jsonb | | |
jdata_s | jsonb | | |
jdata_t | text[] | | |
Indexes:
"da_datum_pkey" UNIQUE, btree (node_id, ts, source_id) CLUSTER, tablespace "solarindex"
"da_datum_reverse_pkey" UNIQUE, btree (node_id, ts DESC, source_id), tablespace "solarindex"
"da_datum_x_acc_idx" UNIQUE, btree (node_id, source_id, ts DESC, jdata_a) WHERE jdata_a IS NOT NULL, tablespace "solarindex"
Triggers:
aa_agg_stale_datum BEFORE INSERT OR DELETE OR UPDATE ON solardatum.da_datum FOR EACH ROW EXECUTE PROCEDURE solardatum.trigger_agg_stale_datum()
ts_insert_blocker BEFORE INSERT ON solardatum.da_datum FOR EACH ROW EXECUTE PROCEDURE _timescaledb_internal.insert_blocker()
The error/query looks like:
ERROR: duplicate key value violates unique constraint “_hyper_1_1931_chunk_da_datum_x_acc_idx"
Where: SQL statement "INSERT INTO solardatum.da_datum(ts, node_id, source_id, posted, jdata_i, jdata_a, jdata_s, jdata_t)
VALUES (…)
ON CONFLICT (node_id, ts, source_id) DO UPDATE
SET jdata_i = EXCLUDED.jdata_i,
jdata_a = EXCLUDED.jdata_a,
jdata_s = EXCLUDED.jdata_s,
jdata_t = EXCLUDED.jdata_t,
posted = EXCLUDED.posted
RETURNING (xmax = 0)"
I am using the TimescaleDB extension so there are child tables inheriting from this main table and that’s why the reported index name differs from the definition shown above. I’m not sure if the extension is the problem, so I thought I’d start here to see if I’ve configured something wrong or my expectations on how the upsert should work is wrong. My expectation was that basically the insert would never fail from a duplicate key violation.
The error always references the da_datum_x_acc_idx index, which is a partial index with jdata_a added as a covering column… that is, it’s only in the index so I can get some index-only results with that column. Is the partial index possibly an issue in this configuration?
Thanks for any insight,
Matt