Lazily Evaluate Insert Arguments If Constraint Violated

Question

create table a (
 b text not null unique,
 c text not null
);
-- Sleeps for 1 second as expected.
insert into a (b, c) values ('d', pg_sleep(1)); 
-- Sleeps for 1 second even though record is not inserted.
insert into a (b, c) values ('d', pg_sleep(1)); 

Is there anyway to restructure the second statement such that the pg_sleep (or actual intensive function I am calling) is run only if no constraint is violated based on b? Note that c(the result of the function call) has no bearing on the constraint.

Couple workarounds:

1. insert with only b, and update c in the next statement if the insert occurred with no error. The downside is that I have to loosen / remove the not null constraint of c, since it will be temporarily null.

2. Under an advisory lock, check if an a with same b already exists, and if not do the insert. The downside is that I replicate the uniqueness check manually, and have negative performance implications.

• Charlieface: #2 seems the best option: if there is correct indexing then the performance will be almost the same.
• Erwin Brandstetter: With the UNIQUE constraint in place, the perfect index is provided implicitly.

Answer 1

If you put the check in the same query, the expensive function call is only executed if there is no conflicting row, yet:

INSERT INTO a (b, c)
SELECT 'd', pg_sleep(1)
WHERE NOT EXISTS (SELECT FROM a WHERE b = 'd');

Now, the insert can still fail under concurrent write load. Postgres does not allow to lock hypothetical rows. (So I don't see how an advisory lock would help.) But that only happens if a concurrent transaction happens to insert the same value for b concurrently and should be extremely rare. You only lose the performance optimization in that case, and nothing breaks.