Postgres ON CONFLICT ON CONSTRAINT triggering errors in the error log

Question

I have a sql statement with ON CONFLICT ON CONSTRAINT in it to allow upserting. This seems to be working well except that my error logs have a bunch of errors related to this query.

I am confused by the error logging because we expect to run into this constraint and then just do the update - is there something broken here or is this just chatty logging?

RDS Postgres, version 12.7.

2021-12-18 16:00:32 UTC:172.31.1.154(33952):{username}@{database}:[28367]:ERROR: duplicate key value violates unique constraint "segments_sequence_number_event_id"
2021-12-18 16:00:32 UTC:172.31.1.154(33952):{username}@{database}:[28367]:DETAIL: Key (sequence_number, event_id)=(2, d5c70xxxx63478) already exists.
2021-12-18 16:00:32 UTC:172.31.1.154(33952):{username}@{database}:[28367]:STATEMENT: INSERT INTO segments (id,created_at,updated_at,event_id,sequence_number,start_time,end_time,bitrate,width,height) VALUES ('8e6d5xxxxbdae3','2021-12-18T16:00:32.596Z','2021-12-18T16:00:32.596Z','d5c70xxxx63478',2,1639843218000,1639843221000,2097152,1920,1080) ON CONFLICT ON CONSTRAINT sequence_number_event_id_unique DO UPDATE SET updated_at='2021-12-18T16:00:32.596Z',start_time=1639843218000,end_time=1639843221000,bitrate=2097152,width=1920,height=1080 RETURNING id,sequence_number,url,start_time,end_time,duration,bitrate,width,height,size,event_id,created_at,updated_at

Please post the offending SQL statement along with the DDL statments of the relevant tables/indexes etc. — SebDieBln
– SebDieBln, Commented Dec 21, 2021 at 17:16
@SebDieBln: The offending statement is hidden in the error message. — Erwin Brandstetter
– Erwin Brandstetter, Commented Dec 21, 2021 at 17:21

Erwin Brandstetter · Accepted Answer · 2021-12-21 17:34:23Z

3

Your ON CONFLICT clause uses ON CONSTRAINT sequence_number_event_id_unique

But the unique violation is raised by different constraint: unique constraint "segments_sequence_number_event_id"

To catch any and all conflicts, you can use ON CONFLICT DO NOTHING.
ON CONFLICT ... DO UPDATE can only catch a single "conflict target" (a single constraint, index, or index expressions).

How to use RETURNING with ON CONFLICT in PostgreSQL?

Aside: You don't have to pass values twice. You can just reuse values from excluded rows with:

...
SET (updated_at,start_time,end_time,bitrate,width,height)
  = (EXCLUDED.updated_at,EXCLUDED.start_time,EXCLUDED.end_time,EXCLUDED.bitrate,EXCLUDED.width,EXCLUDED.height)
...

See:

How to UPSERT multiple rows with individual values in one statement?

Corner-case difference: Values from the EXCLUDED row include changes from possible triggers ON INSERT (which is typically desirable).

edited Dec 21, 2021 at 17:34

answered Dec 21, 2021 at 17:15

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Erwin Brandstetter Over a year ago

@pherris: The names seem to indicate duplication with confused names. Maybe you can drop a duplicate constraint?

pherris Over a year ago

I agree - these look like the same constraint accidentally applied twice with slightly different names.

Erwin Brandstetter Over a year ago

Consider the added aside.

Collectives™ on Stack Overflow

Postgres ON CONFLICT ON CONSTRAINT triggering errors in the error log

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related