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.
