-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1
On Wed, Apr 29, 2009 at 03:38:27AM -0400, Timo Sirainen wrote:
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.
FWIW, this seems to be the canonical way of dealing with that in PostgreSQL:
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. [...]
Right. The trick seems to be to wrap the thing in one plpgsql function (which wraps the try-to-update-then-insert into one transaction), so the client doesn't see anything of that.
The race condition is taken care of via the implicit (sub-) transaction in the BEGIN...EXCEPTION block, AFAIU.
Regards
- -- tomás -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux)
iD8DBQFJ+B4FBcgs9XrR2kYRAlCrAJ9lAa/ZIvav/I66MhMRQzRzuTdI3wCfeaNq KFa8JvnNFQIo6OxfTDCo+2c= =U4BP -----END PGP SIGNATURE-----