How to distinguish when FOUND is false from when row version is obsolete in a Postgres stored function

Working on implementing optimistic concurrency in a .NET application backed by Postgres, I have arrived at an impasse.

The following stored function returns FOUND, enabling the invoking endpoint to return 404, if an ID is passed for which the WHERE clause identifies no match:

CREATE OR REPLACE FUNCTION institution_delete_by_id(IN in_id UUID) RETURNS BOOLEAN
    LANGUAGE plpgsql
AS
$$
BEGIN
    DELETE
    FROM institution
    WHERE id = in_id;
    RETURN FOUND;
END
$$;

Introducing optimistic concurrency, I would do something like the following (I suppose I will use xmin later, when I have read up on that):

CREATE OR REPLACE FUNCTION institution_delete_by_id(IN in_id UUID, IN in_version BIGINT) RETURNS BOOLEAN
    LANGUAGE plpgsql
AS
$$
BEGIN
    DELETE
    FROM institution
    WHERE id = in_id AND version = in_version; -- Here's the addition
    RETURN FOUND;
END
$$;

Doing it like this, however, the application can not infer if FOUND was false because the ID did not match or if FOUND was false because of a concurrent UPDATE of the table. As such, the endpoint has no way of telling whether 404 or 409 (CONFLICT) should be returned.

So, how does one ideally use FOUND and implement optimistic concurrency while allowing the application to tell the difference?

I guess, one could raise a special exception for when version != in_version. But how?

This would have the application control its flow based on a specific exception type. I do not like that, but is it OK here?

Is there not a standard for solving this problem? I am using Npgsql as database interface.

I have tried looking for an analogous example but have not found anything applicable.

Solution

You want to return one of three states:

the row does not exist
the row exists, but the version has changed
the row could be deleted

I suggest that you use an integer or enum to encode these three states.

About the function implementation:

DECLARE
   current_version bigint;
   row_id tid;
BEGIN
   -- find and lock the row if it exists
   SELECT version, ctid
   INTO current_version, row_id
   FROM institution
   WHERE id = in_id
   FOR UPDATE;

   IF NOT FOUND THEN
      RETURN 0;
   END IF;

   -- give up if the row was modified
   IF current_version <> in_version THEN
      RETURN 1;
   END IF;

   DELETE FROM institution
   WHERE ctid = row_id;

   RETURN 2;
END;