Prometheus failed to write data into CrateDB after encountering error 'A document with the same primary key exists already'

Integrations
1

I have successfully integrate prometheus with CrateDB as link https://github.com/crate/cratedb-prometheus-adapter, while prometheus don’t write data into CrateDB after encountering error ‘ERROR: A document with the same primary key exists already’. Does Prometheus write data into crate not use upsert?

cr> select count(*) from metrics;
+------------+
|   count(*) |
+------------+
| 1216302437 |
+------------+
SELECT 1 row in set (0.003 sec)

cratedb-prometheus-adapter status

# systemctl status cratedb-prometheus-adapter
● cratedb-prometheus-adapter.service - Prometheus adapter to write to CrateDB
     Loaded: loaded (/etc/systemd/system/cratedb-prometheus-adapter.service; enabled; vendor preset: enabled)
     Active: active (running) since Mon 2024-02-26 09:57:07 CST; 10min ago
       Docs: https://github.com/crate/cratedb-prometheus-adapter/
   Main PID: 2142260 (cratedb-prometh)
      Tasks: 11 (limit: 38375)
     Memory: 10.5M
     CGroup: /system.slice/cratedb-prometheus-adapter.service
             └─2142260 /usr/bin/cratedb-prometheus-adapter

Feb 26 10:06:41 t-cratedb-s-001 cratedb-prometheus-adapter[2142260]: ts=2024-02-26T02:06:41.198Z caller=server.go:349 level=error msg="Failed to write data to CrateDB" err="error closing write batch: ERROR: A document with the same primary key exists already (SQLSTATE 23505)"
Feb 26 10:06:46 t-cratedb-s-001 cratedb-prometheus-adapter[2142260]: ts=2024-02-26T02:06:46.204Z caller=server.go:349 level=error msg="Failed to write data to CrateDB" err="error closing write batch: ERROR: A document with the same primary key exists already (SQLSTATE 23505)"
Feb 26 10:06:51 t-cratedb-s-001 cratedb-prometheus-adapter[2142260]: ts=2024-02-26T02:06:51.210Z caller=server.go:349 level=error msg="Failed to write data to CrateDB" err="error closing write batch: ERROR: A document with the same primary key exists already (SQLSTATE 23505)"
Feb 26 10:06:56 t-cratedb-s-001 cratedb-prometheus-adapter[2142260]: ts=2024-02-26T02:06:56.217Z caller=server.go:349 level=error msg="Failed to write data to CrateDB" err="error closing write batch: ERROR: A document with the same primary key exists already (SQLSTATE 23505)"
Feb 26 10:07:01 t-cratedb-s-001 cratedb-prometheus-adapter[2142260]: ts=2024-02-26T02:07:01.223Z caller=server.go:349 level=error msg="Failed to write data to CrateDB" err="error closing write batch: ERROR: A document with the same primary key exists already (SQLSTATE 23505)"
Feb 26 10:07:06 t-cratedb-s-001 cratedb-prometheus-adapter[2142260]: ts=2024-02-26T02:07:06.229Z caller=server.go:349 level=error msg="Failed to write data to CrateDB" err="error closing write batch: ERROR: A document with the same primary key exists already (SQLSTATE 23505)"
Feb 26 10:07:11 t-cratedb-s-001 cratedb-prometheus-adapter[2142260]: ts=2024-02-26T02:07:11.236Z caller=server.go:349 level=error msg="Failed to write data to CrateDB" err="error closing write batch: ERROR: A document with the same primary key exists already (SQLSTATE 23505)"
Feb 26 10:07:16 t-cratedb-s-001 cratedb-prometheus-adapter[2142260]: ts=2024-02-26T02:07:16.241Z caller=server.go:349 level=error msg="Failed to write data to CrateDB" err="error closing write batch: ERROR: A document with the same primary key exists already (SQLSTATE 23505)"
Feb 26 10:07:21 t-cratedb-s-001 cratedb-prometheus-adapter[2142260]: ts=2024-02-26T02:07:21.246Z caller=server.go:349 level=error msg="Failed to write data to CrateDB" err="error closing write batch: ERROR: A document with the same primary key exists already (SQLSTATE 23505)"
Feb 26 10:07:26 t-cratedb-s-001 cratedb-prometheus-adapter[2142260]: ts=2024-02-26T02:07:26.253Z caller=server.go:349 level=error msg="Failed to write data to CrateDB" err="error closing write batch: ERROR: A document with the same primary key exists already (SQLSTATE 23505)"
2

You are correct, the adapter currently doesn’t handle upserts.
This probably can be changed by editing:

to

INSERT INTO metrics ("labels", "labels_hash", "timestamp", "value", "valueRaw") VALUES ($1, $2, $3, $4, $5)
ON CONFLICT ("timestamp", "labels_hash", "day__generated")
DO UPDATE SET
   "value" = excluded."value",
   "valueRaw" = excluded."valueRaw";
2 Likes
3

Thanks @proddata , I will modify the code and try it out and get back to you with the results later.
Does the adapter has plan to support upsert?

4

I added an issue in the repo:

Maybe the ecosystem team can change that behaviour in the upcoming releases.

1 Like
5

Seems like a reasonable improvement, we would be more than happy to accept your contribution, otherwise we’ll probably get on this sometime soon.

1 Like