Do I need higher transaction isolation to make constraints work reliably in PostgreSQL?

Question

I have a case where I need to check if a row exists, and if it exists to not creating it again. Pretty much summed like this below:

select id from table where constraintA=$1 and constraintB=$2

following right after that in my code:

if not exist
insert into table values ($1,$2, {other data})

To ensure that constraint are correct, I can make an unique index like unique(constraintA,constraintB).

But, in https://www.postgresql.org/docs/10/transaction-iso.html it says that Postgres uses locks on rows, and newly created data is isolated from other concurrent transactions. So they wont block each other, as I am not updating or deleting data.

Which brings me to my question, do I need an isolation level higher than read committed to ensure the correctness? if not, is my understanding correct?

PS: I am using Postgres 10.5

Answer 1

You are right to worry, but you can rely on database constraints to work as you expect, even in the face of concurrent transactions.

Constraints are implemented as special triggers in PostgreSQL, and the trigger functions involved “break” MVCC by taking a new snapshot that will also see uncommitted rows.

This is not in the regular documentation; for topics like this, the documentation is in the source. See src/backend/utils/adt/ri_triggers.c, particularly this part of RI_Initial_Check.