Postgres: Ignore DELETE triggers for cascade deletes

Question

I am trying to implement a relation of persons to email addresses where a person must have at least one email address at all times. The tables look like this:

CREATE TABLE persons (
    id serial PRIMARY KEY,
    name text NOT NULL
);

CREATE TABLE email_addresses (
    id serial PRIMARY KEY,
    person_id integer REFERENCES persons (id) ON DELETE CASCADE ON UPDATE CASCADE,
    email_address text NOT NULL
);

In order to implement the constraint that a person must have at least one email address, I thought I'd use triggers. One of the triggers necessary to satisfy the constraint is a BEFORE DELETE trigger on the email_addresses table that raises an error if the DELETE would remove the last email address for a person:

CREATE FUNCTION email_addresses_delete_trigger() RETURNS trigger AS $$
    DECLARE
        num_email_addresses integer;
    BEGIN
        num_email_addresses := (SELECT count(*) FROM email_addresses WHERE person_id = OLD.person_id);
        IF num_email_addresses < 2 THEN
            RAISE EXCEPTION 'A person must have at least one email address';
        END IF;

        RETURN OLD;
    END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER email_addresses_delete_trigger BEFORE DELETE ON email_addresses
    FOR EACH ROW EXECUTE FUNCTION email_addresses_delete_trigger();

This trigger does what it is meant to do, however it prevents deletes of a person from the persons table. For example:

mydb=# DELETE FROM persons WHERE id = 1;
ERROR:  A person must have at least one email address
CONTEXT:  PL/pgSQL function email_addresses_delete_trigger() line 7 at RAISE
SQL statement "DELETE FROM ONLY "public"."email_addresses" WHERE $1 OPERATOR(pg_catalog.=) "person_id""

If I am deleting a person, then I want all their email addresses deleted too, and so I don't care if the constraint represented by the trigger is maintained during the process of the cascade delete. Is there a way to "ignore" this trigger when a person is deleted? Or is there some other way that I would need to delete a person?

@wildplasser I'm not sure what you're getting at. If it's the `num_email_addresses < 2` bit, there need to be at least two email addresses for a person before one is deleted in order to maintain the requirement that a person always has at least one email. — kcstricks, Jun 08 '20 at 01:10
Your text says: `at least one` , which for me means `(count > 0)` — wildplasser, Jun 08 '20 at 11:06

score 3 · Answer 1 · answered Jun 08 '20 at 06:16

3

My recommendation is to change the data model so that you have a not nullable foreign key constraint from persons to email_addresses that links to one of the addresses of the person. Then your requirement is automatically fulfilled, and you don't need a trigger.

That will make some things like deleting an e-mail address more complicated, but you don't have to rely on a trigger for integrity, which is always subject to race conditions.

answered Jun 08 '20 at 06:16

Laurenz Albe

209,280
17
206
263

Thanks for the advice, Laurenz, and for sharing your very insightful article! Let's say that I used table locking or a higher isolation level to avoid the possibility of a race condition, as you mention in the article. In this case, would there be a way to ignore the trigger for a cascade delete, or otherwise to delete a `person`? Even if it is still preferable to change the model as you recommend, I am curious if it is possible to stick with my original model while maintaining the constraint and allowing DELETEs from the `persons` table. – kcstricks Jun 09 '20 at 05:59
You can call the `pg_trigger_depth` function to find out the call level in your trigger function. – Laurenz Albe Jun 09 '20 at 06:09
Thanks Laurenz! That sounds messy and error prone. Not to mention using a trigger in this way seems like it would make for an expensive check constraint. I'll stick with your recommendation :) – kcstricks Jun 18 '20 at 16:44

Postgres: Ignore DELETE triggers for cascade deletes

1 Answers1