0

I have a table that records requests to an external API for data enrichment. I want to ensure that there is only one row for a given entity with status = 'Pending' (meaning a process is doing the work.) With a unique constraint, I have that covered.

Now I want to pull out the pending request WITH FOR UPDATE to ensure that only one process is using it at a time, in case two async jobs try to work on the same request.

I can INSERT […] ON CONFLICT DO NOTHING for the upsert. Annoyingly, this returns nothing if there is an existing row so I can't rely on RETURNING, but I can then SELECT […] WITH FOR UPDATE NOWAIT to ensure that only on process can hold it.

Can I do this in one statement?

Essentially I would like to do this:

INSERT […] ON CONFLICT DO NOTHING RETURNING * WITH FOR UPDATE NOWAIT
Improve this question
3
  • Why not use two statements, one that inserts (or does nothing) and a second one to lock the row? Commented May 5, 2024 at 4:23
  • I could, but I'd prefer to do it atomically! Commented May 8, 2024 at 19:55
  • It will be atomic, if you perform both statements in the same REPEATABLE READ transaction. Commented May 8, 2024 at 20:14
Related questions
629
UPSERT *not* INSERT or REPLACE
1251
Getting error: Peer authentication failed for user "postgres", when trying to get pgsql working with rails
383
How to use RETURNING with ON CONFLICT in PostgreSQL?
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.