-----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: <http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE> >> 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-----