On 3/20/20 2:17 AM, Matt Magoffin wrote:
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"
What is the above index UNIQUE over?
What is da_datum_x_acc_idx index below indexed over?
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
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
