Where does the infinite loop come from?

Question

PostgreSQL

trigger function

CREATE OR REPLACE FUNCTION delete_abonent() RETURNS TRIGGER AS $$ BEGIN
IF OLD.bilet_number IN (SELECT bilet_number FROM all_lends) THEN
RAISE EXCEPTION 'У этого абонента есть выданные книги!'; RETURN NULL; END IF;
DELETE FROM all_users WHERE user_login = OLD.user_login;
EXECUTE format('DROP USER %I', OLD.user_login);
DELETE FROM all_abonents WHERE bilet_number = OLD.bilet_number;
END; $$ LANGUAGE plpgsql;

trigger

CREATE TRIGGER before_delete_abonent INSTEAD OF DELETE ON all_abonents
FOR EACH ROW EXECUTE PROCEDURE delete_abonent();

I'm trying to delete an entry by calling a trigger, but for some reason it says that the role does not exist, although it exists,
how can I understand this?

PS
with the drop user IF EXISTS condition, the function goes into an infinite loop,
I suppose it is deleted without a condition on the first pass, and then this error,
but where does the loop come from?
PPS
the problem is not in the removal of the role, but just in this cycle, i.e. even without the role removal command

, something does not work with this operator, I don’t understand what

DELETE FROM all_abonents WHERE bilet_number = OLD.bilet_number;

Answer 1

Let's you better tell why you think that there is no recursion here.
You doing

INSTEAD OF DELETE ON all_subscribers

and in this trigger DELETE FROM all_abonents WHERE - why shouldn't it go into infinite recursion?
Instead of imposing controversial restrictions on actions from triggers, postgresql will obediently execute recursive calls until the stack is exhausted, and it is the developer's task to provide conditions for exiting recursion.

Answer 2

Why the recursion is understandable - you call DELETE in an INSTEAD OF trigger on DELETE.
Another question is what do you want to do. If you upon successful verification

IF OLD.bilet_number IN (SELECT bilet_number FROM all_lends)

want to delete this subscriber (and only him), then you had to do a regular non-INSTEAD) trigger and instead of the DELETE statement at the end, do RETURN OLD.