2

In my application code, I have an ordinary UPDATE statement to update some row's properties. However, I've encountered a bug where this statement was provided with an incorrect ID and no row was affected.

This bug was silent because UPDATE statement that affects no rows still finishes successfully. I want to prevent such bugs in the future, and to do so, I want to instantly and loudly fail if this happens. One option that I have is to include a RETURNING statement and check it in the application code, but before going with it (and updating the whole application codebase where this pattern occurs — which is a massive change), I wanted to check the alternatives.

Is there a way to write an UPDATE statement in such a way that it produces an error if now rows are affected?

1

1 Answer 1

3

It would be possible to put the UPDATE into a CTE and then do something with the number of results:

WITH cte AS (
  UPDATE ...
  RETURNING ...
)
SELECT 1 / COUNT(*) FROM cte;

If the division by zero is too obscure for you, write a function that raises a proper error.

Personally, I would use a plain UPDATE, and then in my application, wrap all such SQL calls in a function that checks the number of rows. (PG returns that number from every UPDATE, but not every database driver supports this.)

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.