Trigger works for UPDATE, but for INSERT



  • In my database schema an organization can have multiple addresses but only one default address. I'm trying to create a trigger where if the is_default column is set to true on an insert or update, it sets the rest of the rows to false and the current one to true.

    The update part is working, however I'm getting a unique constraint error for insert.

    This is what I have:

    CREATE OR REPLACE FUNCTION public.ensure_only_one_default_address()
       RETURNS TRIGGER
      AS $$
      BEGIN
      -- nothing to do if updating the row currently enabled
      IF (TG_OP = 'UPDATE' AND OLD.is_default = true) THEN
          RETURN NEW;
      END IF;
    

    -- disable the currently enabled row
    EXECUTE format('UPDATE %I.%I SET is_default = false WHERE is_default = true AND organization_id = %L;', TG_TABLE_SCHEMA, TG_TABLE_NAME, OLD.organization_id);

    -- enable new row
    NEW.is_default := true;

     RETURN NEW;
    

    END;
    $$ LANGUAGE plpgsql;

    CREATE TRIGGER ensure_only_one_default_address
    BEFORE INSERT OR UPDATE OF is_default ON public.company_addresses
    FOR EACH ROW WHEN (NEW.is_default = true)
    EXECUTE PROCEDURE public.ensure_only_one_default_address();

    The constraint error is happening because I have a unique partial index on (organization_id, is_default) WHERE is_default = true, but I though that the trigger would fire first, then the unique constraint so I'm wondering if there is anything in the internals of Postgres that I'm missing.



  • Answer to question asked

    I though that the trigger would fire first, then the unique constraint ...

    You thought right. But the main error was OLD.organization_id instead of NEW.organization_id in the UPDATE - which is bound to do nothing in the INSERT case, where OLD is not defined.

    This used to raise and exception immediately in older Postgres versions. https://www.postgresql.org/docs/11/release-11.html

    In PL/pgSQL trigger functions, the OLD and NEW variables now read as NULL when not assigned (Tom Lane)

    Previously, references to these variables could be parsed but not executed.

    Goes to show the importance of declaring the Postgres version in use ...

    Use instead:

    CREATE OR REPLACE FUNCTION public.ensure_only_one_default_address()
      RETURNS TRIGGER
      LANGUAGE plpgsql AS
    $func$
    BEGIN
       -- nothing to do if updating the row currently enabled
       IF TG_OP = 'UPDATE' THEN
          IF OLD.is_default THEN                      -- !!!
             RETURN NEW;
           END IF;
       END IF;
    

    UPDATE public.company_addresses
    SET is_default = false
    WHERE is_default
    AND organization_id = NEW.organization_id; -- !!! NEW, not OLD

    RETURN NEW;
    END
    $func$;

    CREATE TRIGGER ensure_only_one_default_address
    BEFORE INSERT OR UPDATE OF is_default ON public.company_addresses
    FOR EACH ROW WHEN (NEW.is_default)
    EXECUTE PROCEDURE public.ensure_only_one_default_address();

    I did more:

    It's not safe to reference OLD in the INSERT case on the outer level. (Postgres is free to evaluate expressions in arbitrary sequence.) I hid that in a nested IF statement.

    Typically, it's cleaner to just write separate trigger functions and triggers for INSERT and UPDATE (unless this leads to massive code duplication).

    For dynamic SQL, pass the value OLD.organization_id as value. But why dynamic SQL in the first place? I made it static. Cleaner, faster.

    is_default = true is just a noisy way of saying is_default.

    Alternative with CTE and no trigger

    WITH pre_emptive AS (
       UPDATE company_addresses
       SET    is_default = false
       WHERE  is_default
       AND    organization_id = 2
       )
    INSERT INTO company_addresses(organization_id, is_default) VALUES (2, true);
    

    That said, I suggest a completely different approach:

    Alternative db design

    It's inefficient to update other rows to mark a new one as "default".

    Consider to add am email column for each organization that holds (and points to) the "default" instead:

    CREATE TABLE company_addresses (
      organization_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY
    , organization    text UNIQUE NOT NULL
    , email           text UNIQUE -- FK added below  -- can also be NOT NULL
    );
    

    CREATE TABLE email (
    email text PRIMARY KEY
    , organization_id int NOT NULL REFERENCES company_addresses ON DELETE CASCADE
    , UNIQUE (organization_id, email) -- seems redundant, but required for FK
    );

    ALTER TABLE company_addresses
    ADD CONSTRAINT company_addressesn_default_email_fkey
    FOREIGN KEY (organization_id, email) REFERENCES email (organization_id, email);

    Then you don't need a trigger at all. And there are various other advantages - like you have the default email in the main table without join.
    Detailed assessment:

    • https://dba.stackexchange.com/questions/95817/unique-across-tables/95912#95912



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2