[Dovecot] Quota/Dict Postgres Trigger
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?
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.
Thanks.
-Warren
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.
-----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-----
participants (3)
-
Timo Sirainen
-
tomas＠tuxteam.de
-
Warren Volz