dovecot - Dec 2011 - Quota PostgreSQL INSERT trigger

Wiki instructions for creating insert trigger for PostgreSQL are wrong
  http://wiki.dovecot.org/Quota/Dict

Calling INSERT on the table from within BEFORE INSERT trigger creates
cascading trigger.

http://www.postgresql.org/docs/current/interactive/trigger-definition.html


Instead, one should return NEW record from the trigger, and it will be
inserted after trigger execution.

---
CREATE OR REPLACE FUNCTION merge_quota2() RETURNS TRIGGER AS $$
BEGIN
  IF NEW.messages < 0 OR NEW.messages IS NULL THEN
    -- ugly kludge: we came here from this function, really do try to insert
    IF NEW.messages IS NULL THEN
      NEW.messages = 0;
    ELSE
      NEW.messages = -NEW.messages;
    END IF;
    return NEW;
  END IF;

  LOOP
    UPDATE quota2 SET bytes = bytes + NEW.bytes,
      messages = messages + NEW.messages
      WHERE username = NEW.username;
    IF found THEN
      RETURN NULL;
    END IF;

    BEGIN
      IF NEW.messages = 0 THEN
        RETURN NEW;
      ELSE
        NEW.messages = - NEW.messages;
        RETURN NEW;
      END IF;
    EXCEPTION WHEN unique_violation THEN
      -- someone just inserted the record, update it
    END;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS mergequota2 ON quota2;
CREATE TRIGGER mergequota2 BEFORE INSERT ON quota2
   FOR EACH ROW EXECUTE PROCEDURE merge_quota2();
---


Best regards,
Adis

On Thu, 2011-12-15 at 09:37 +0100, Adis Nezirovic wrote:
> Wiki instructions for creating insert trigger for PostgreSQL are wrong
>   http://wiki.dovecot.org/Quota/Dict
> 
> Calling INSERT on the table from within BEFORE INSERT trigger creates
> cascading trigger.
Yes..
> Instead, one should return NEW record from the trigger, and it will be
> inserted after trigger execution.
But does this handle race conditions when a record is inserted/deleted
while the trigger is being run? That's the reason this trigger is so
complex.
>     BEGIN
>       IF NEW.messages = 0 THEN
>         RETURN NEW;
>       ELSE
>         NEW.messages = - NEW.messages;
>         RETURN NEW;
>       END IF;
>     EXCEPTION WHEN unique_violation THEN
>       -- someone just inserted the record, update it
>     END;
I'd guess the unique_violation is never caught here, and instead the
whole INSERT fails?