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.