[Dovecot] 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:
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.
I'd guess the unique_violation is never caught here, and instead the whole INSERT fails?
participants (2)
-
Adis Nezirovic
-
Timo Sirainen