Consider following before_update validation trigger on contracts table:
FOR i IN
SELECT uuid FROM tb_some_orders
WHERE is_deleted = 0 AND uuid_contract = NEW.uuid
LOOP
RAISE '#is_deleted#:deleting is prohibited due to dependent objects';
END LOOP;
tb_some_orders will contain about 1 mil of rows
What should happen, will db fetch all records or read each record or one by one?
Is there any need for LIMIT 1 as I do in app code?
This code will read the rows one by one using a cursor. So in your case, reading will stop after the first row has been fetched, because you throw an exception.
It might still be a good idea to add LIMIT 1, so that the optimizer knows that it should plan the query so that the first row can be returned as fast as possible.
In the present case, the most idiomatic way to write that would be
IF EXISTS (SELECT 1 FROM tb_some_orders
WHERE is_deleted = 0
AND uuid_contract = NEW.uuid)
THEN
RAISE '#is_deleted#:deleting is prohibited due to dependent objects';
END IF;
The EXISTS will also stop executing as soon as it finds the first result row, and the syntax makes the purpose obvious.
RAISE. I have improved the answer.
existscondition will be way more efficient. In fact, a properly declared foreign key constraint would be a much better alternative to get rid of the trigger entirely.