1

I've implemented simple update/insert query like this:

-- NOTE: :time is replaced in real code, ids are placed statically for example purposes
-- set status_id=1 to existing rows, update others
UPDATE account_addresses
SET status_id = 1, updated_at = :time
WHERE account_id = 1
  AND address_id IN (1,2,3)
  AND status_id IN (2);

-- filter values according to what that update query returns, i.e. construct query like this to insert remaining new records:
INSERT INTO account_addresses (account_id, address_id, status_id, created_at, updated_at)
SELECT account_id, address_id, status_id, created_at::timestamptz, updated_at::timestamptz
FROM (VALUES (1,1,1,:time,:time),(1,2,1,:time,:time)) AS sub(account_id, address_id, status_id, created_at, updated_at)
WHERE NOT EXISTS (
  SELECT 1 FROM account_addresses AS aa2
  WHERE aa2.account_id = sub.account_id AND aa2.address_id = sub.address_id
)
RETURNING id;

-- throws:
-- PG::UniqueViolation: ERROR:  duplicate key value violates unique constraint "..."
-- DETAIL:  Key (account_id, address_id)=(1, 1) already exists.

The reason why I'm doing it this way is: the record MAY exist with status_id=2. If so, set status_id=1. Then insert new records. If it already exists, but was not affected by first UPDATE query, ignore it (i.e. rows with status_id=3).

This works nicely, but doing it concurrently, it crashes on duplicate key in race condition. But why is race condition occurring, if I'm trying to do that "insert-where-not-exists" atomically?

Dalibor Filus
  • 1,140
  • 9
  • 19

1 Answers1

2

Ah. I just searched a little more and insert where not exists is not atomic.

Quote from http://www.postgresql.org/message-id/26970.1296761016@sss.pgh.pa.us :

Mage writes:

The main question is that isn't "insert into ... select ... where not exists" atomic?

No, it isn't: it will fail in the presence of other transactions doing the same thing, because the EXISTS test will only see rows that committed before the command started. You might care to read the manual's chapter about concurrency: http://www.postgresql.org/docs/9.0/static/mvcc.html

Dalibor Filus
  • 1,140
  • 9
  • 19