[Dovecot] Quota/Dict Postgres Trigger

[Timo Sirainen]

On Apr 29, 2009, at 3:25 AM, Warren Volz wrote:

> I posted the trigger for v1.1 versions of Dovecot on the Wiki (http://wiki.dovecot.org/Quota/Dict 
> ) and while I understand the comment posted about two process  
> inserting at the same time, I'm not sure I understand how this is  
> fixed in v1.2 other than via the revised trigger in the Wiki. Does  
> someone have a known working trigger that will handle a double  
> insert correctly?

I don't think it's possible until PostgreSQL supports INSERT .. ON  
DUPLICATE KEY UPDATE .. like MySQL. Kind of annoying, I like  
PostgreSQL but this feature is really missing from it.

> Also, out of curiosity why wasn't the code for dict written to do an  
> update first and then an insert if this failed? That would eliminate  
> the need for this trigger.

There would still be a race condition. It's still possible that two  
processes do the steps at the exact same time and still finally find  
out that one of the INSERTs fail because they did everything at the  
same time. You could do similar EXCEPTION WHEN unique_violation  
THEN .. check with v1.1 too, although that still breaks in the very  
rare (or practically non-existent) condition if you happen to delete  
the row while another process is inserting. Or something like that.

Anyway the v1.0/v1.1 code was mainly written for MySQL and then I  
realized PostgreSQL requires some more kludgeing to get it working  
properly in all situations and added that code to v1.2.