is postgres UPSERT fully atomic/thread safe?

Question

We're trying to stream data to postgres 11, using the following query:

INSERT INTO identifier_to_item 
values (:id, :identifier_value, :identifier_type, :identifier_manufacturer, :delivery_timestamp_utc, :item)
ON CONFLICT (identifier_value, manufacturer, type) DO UPDATE 
SET item = :item, delivery_timestamp_utc = :delivery_timestamp_utc 
WHERE identifier_to_item.delivery_timestamp_utc < :delivery_timestamp_utc

Basically "insert record in the table, if it already exists -> optionally override some fields based on the data already stored in the database".

We would like to hook this query to message queue and run it in high concurrent environment within several instances. It is possible that the same row will be accessed from different connections using this query. For us it's critical that only items with highest delivery timestamp will eventually make it to the table

According to documentation:

ON CONFLICT DO UPDATE guarantees an atomic INSERT or UPDATE outcome; provided there is no independent error, one of those two outcomes is guaranteed, even under high concurrency.

but is also accessing the fields in UPDATE WHERE part atomic and thread safe? Is this statement using some kind of pessimistic row/table locking?

Every DML statement is atomic. Btw: you don't need to repeat the parameters in the `SET` part, you can use `SET item = excluded.item, delivery_timestamp_utc = excluded.delivery_timestamp_utc` — , May 28 '20 at 13:44

score 1 · Accepted Answer · answered May 28 '20 at 14:02

PostgreSQL is not using threads on the server side.

PostgreSQL does not implement pessimistic/optimistic row level locking : it is the left to the application to decide to implement pessimistic or optimistic locking.

PostgreSQL does not escalate row level locks to table lock.

score 1 · Answer 2 · answered Jan 28 '23 at 19:04

From the documenation:

ON CONFLICT DO UPDATE guarantees an atomic INSERT or UPDATE outcome; provided there is no independent error, one of those two outcomes is guaranteed, even under high concurrency.

It does not mention what happens on ON CONFLICT DO NOTHING.

As a test, I did an INSERT ... ON CONFLICT DO NOTHING with 10 threads thousands of times and did not see any errors.

is postgres UPSERT fully atomic/thread safe?

2 Answers2