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

**URL:** <https://community.cratedb.com/t/prometheus-failed-to-write-data-into-cratedb-after-encountering-error-a-document-with-the-same-primary-key-exists-already/1724>\
**Category:** Integrations\
**Created:** [February 26, 2024, 2:21am UTC](https://community.cratedb.com/t/prometheus-failed-to-write-data-into-cratedb-after-encountering-error-a-document-with-the-same-primary-key-exists-already/1724 "2024-02-26T02:21:02Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Jun\_Zhou](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/jun_zhou/32/1079_2.png) [@Jun\_Zhou](https://community.cratedb.com/u/Jun_Zhou)\
**Post date:** [February 26, 2024, 2:21am UTC](https://community.cratedb.com/t/prometheus-failed-to-write-data-into-cratedb-after-encountering-error-a-document-with-the-same-primary-key-exists-already/1724/1 "2024-02-26T02:21:02Z")

</div>

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?

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

```

cratedb-prometheus-adapter status

```auto
# 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)"

```

---

<div class="post-metadata">

**Author:** ![proddata](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/proddata/32/1379_2.png) [@proddata](https://community.cratedb.com/u/proddata)\
**Post date:** [February 26, 2024, 8:19am UTC](https://community.cratedb.com/t/prometheus-failed-to-write-data-into-cratedb-after-encountering-error-a-document-with-the-same-primary-key-exists-already/1724/2 "2024-02-26T08:19:54Z")

</div>

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

> <https://github.com/crate/cratedb-prometheus-adapter/blob/dc5c40f9b87ace1d543bd6cca0d6500884590c49/crate.go#L16>

to

```sql
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";

```

---

<div class="post-metadata">

**Author:** ![Jun\_Zhou](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/jun_zhou/32/1079_2.png) [@Jun\_Zhou](https://community.cratedb.com/u/Jun_Zhou)\
**Post date:** [February 26, 2024, 8:45am UTC](https://community.cratedb.com/t/prometheus-failed-to-write-data-into-cratedb-after-encountering-error-a-document-with-the-same-primary-key-exists-already/1724/3 "2024-02-26T08:45:52Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![proddata](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/proddata/32/1379_2.png) [@proddata](https://community.cratedb.com/u/proddata)\
**Post date:** [February 26, 2024, 8:51am UTC](https://community.cratedb.com/t/prometheus-failed-to-write-data-into-cratedb-after-encountering-error-a-document-with-the-same-primary-key-exists-already/1724/4 "2024-02-26T08:51:58Z")

</div>

I added an issue in the repo:

> <https://github.com/crate/cratedb-prometheus-adapter/issues/135>
>
> \_Originated from community question: https://community.cratedb.com/t/prometheus-…failed-to-write-data-into-cratedb-after-encountering-error-a-document-with-the-same-primary-key-exists-already/1724\_
> 
> \---
> 
> We probably could add the option to use upserts or make it the even default approach.
> 
> Since the table definition is using primary keys we should change the
> \[insert statement\](https://github.com/crate/cratedb-prometheus-adapter/blob/dc5c40f9b87ace1d543bd6cca0d6500884590c49/crate.go#L16)
> 
> https://github.com/crate/cratedb-prometheus-adapter/blob/dc5c40f9b87ace1d543bd6cca0d6500884590c49/crate.go#L16
> 
> to either ignore conflicts:
> 
> \`\`\`sql
> INSERT INTO metrics ("labels", "labels\_hash", "timestamp", "value", "valueRaw") VALUES ($1, $2, $3, $4, $5)
> ON CONFLICT ("timestamp", "labels\_hash", "day\_\_generated")
> DO NOTHING;
> \`\`\`
> 
> or handle upserts:
> 
> \`\`\`sql
> 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";
> \`\`\`
> 
> Potentially this could be configurable (to do either or).

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

---

<div class="post-metadata">

**Author:** ![surister](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/surister/32/1087_2.png) [@surister](https://community.cratedb.com/u/surister)\
**Post date:** [February 27, 2024, 10:53am UTC](https://community.cratedb.com/t/prometheus-failed-to-write-data-into-cratedb-after-encountering-error-a-document-with-the-same-primary-key-exists-already/1724/5 "2024-02-27T10:53:35Z")

</div>

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