[Dovecot] Quota/Dict Postgres Trigger
tomas at tuxteam.de
tomas at tuxteam.de
Wed Apr 29 12:29:41 EEST 2009
-----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-----
More information about the dovecot
mailing list