Re: [Dovecot] Quota PostgreSQL INSERT trigger

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?