[Dovecot] Quota/Dict Postgres Trigger

[tomas at tuxteam.de]

-----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-----