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
