1
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.

2
  • #2 seems the best option: if there is correct indexing then the performance will be almost the same. Oct 25, 2023 at 10:11
  • With the UNIQUE constraint in place, the perfect index is provided implicitly. Oct 25, 2023 at 10:23

1 Answer 1

2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.