1

I have the following schema:

CREATE FUNCTION myfunc() Returns trigger as $myfunc$
    DECLARE
        someVal integer;
    BEGIN
        SELECT Count(*) into someVal 
        FROM MyTable
        WHERE someAttribute = OLD.someAttribute;
        IF someVal = 100 then

            -- Recursion, don't do this
            -- DELETE FROM MyTable
            -- WHERE someAttribute = OLD.someAttribute;

            -- delete from MyTable2
            DELETE FROM MyTable2
            WHERE someAttribute = OLD.someAttribute;
        END IF;
        RETURN OLD;
    END;
$myfunc$ LANGUAGE plpgsql;

I have oversimplified what I did in my code, so let me clarify it.

I am registering this function with the following code:

CREATE TRIGGER myfunc BEFORE DELETE ON MyTable
FOR EACH ROW EXECUTE PROCEDURE myfunc();

So, when someone deletes a value in my relationship and if some condition occurs, I want to delete other values as well. Think about this: I have a hundred 10's in my database. If the user deletes one of them, it will be 99 and I want to delete all 99 of them because less than 100 attributes are no use to me (don't say why I have said I have oversimplified this).

So, If I delete from the same table inside the IF condition, I am encountering infinite loop, and the program stops when the stack overflows. What should I do? I need to delete those values from MyTable and MyTable2.

  • 2
    Please don’t oversimplify your problem. I can’t understand what you’re trying to do, or why. – Colin 't Hart May 17 at 21:49
  • Okay, clarification: When someone deletes a value in "MyTable", and the count of the rows corresponding to the someAttribute belows 100, I want to delete all remaning 99 rows too, because they are no use to me. I also want to delete those attributes from other table, MyTable2, but currently, that is not the focus of my problem – qds52625 May 18 at 9:03
  • Do you need the deletion to happen within the same transaction? If not, it may be simpler and better-performing to prune the table periodically with a scheduled procedure rather than a trigger. – Jon of All Trades May 23 at 18:22
  • I need that at the deletion time. @JonofAllTrades – qds52625 May 25 at 20:39
1

Add the following at the beginning of your function:

IF pg_trigger_depth() > 1 THEN
   RETURN OLD;
END IF;

Then recursion will be cut short after the first iteration.

To avoid the error that you get when the same row is deleted twice in the same statement, explicitly exclude it:

DELETE ...
WHERE ...
  AND OLD.ctid <> ctid
  • Thanks. The recursion problem is solved but now, it gives me this error: ERROR: tuple to be updated was already modified by an operation triggered by the current command HINT: Consider using an AFTER trigger instead of a BEFORE trigger to propagate changes to other rows. – qds52625 May 18 at 8:54
  • See my updated answer for a solution. – Laurenz Albe May 20 at 6:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.