[Dovecot] quota dict play nice with existing tables
Hi,
I'm trying to set Dovecot 2.0 with postgresql dict. It's kind of working but not as I'd like to. Dovecot expects to get a dedicated table for quota management. When I've run *doveadm quota recalc -A* I've noticed in the logs that Dovecot tries to run DELETE statements. Fortunately it didn't succeed as I've only given it SELECT and UPDATE permissions on the fields that it needs.
I'd like to configure it, in UPDATE only mode as rows already exist and there is no need to issue any other statement.
Regards, Miha
It's time to get rid of your current e-mail client ... ... and start using si.Mail.
It's small & free. ( http://www.simail.si/ )
On Tue, 2010-09-14 at 18:07 +0200, Miha Vrhovnik wrote:
I'm trying to set Dovecot 2.0 with postgresql dict. It's kind of working but not as I'd like to. Dovecot expects to get a dedicated table for quota management. When I've run *doveadm quota recalc -A* I've noticed in the logs that Dovecot tries to run DELETE statements. Fortunately it didn't succeed as I've only given it SELECT and UPDATE permissions on the fields that it needs.
I'd like to configure it, in UPDATE only mode as rows already exist and there is no need to issue any other statement.
Maybe some day, but it's not something I'm planning on implementing anytime soon. Feel free to poke around the code to see if there's a way to do it without horrible changes.
On Tue, 2010-09-14 at 17:49 +0100, Timo Sirainen wrote:
On Tue, 2010-09-14 at 18:07 +0200, Miha Vrhovnik wrote:
I'm trying to set Dovecot 2.0 with postgresql dict. It's kind of working but not as I'd like to. Dovecot expects to get a dedicated table for quota management. When I've run *doveadm quota recalc -A* I've noticed in the logs that Dovecot tries to run DELETE statements. Fortunately it didn't succeed as I've only given it SELECT and UPDATE permissions on the fields that it needs.
I'd like to configure it, in UPDATE only mode as rows already exist and there is no need to issue any other statement.
Maybe some day, but it's not something I'm planning on implementing anytime soon. Feel free to poke around the code to see if there's a way to do it without horrible changes.
Maybe it could treat NULL value as "row not found" and DELETE could be replaced with UPDATE to NULL. Maybe.
"Timo Sirainen" tss@iki.fi wrote on 14.9.2010 18:51:29:
On Tue, 2010-09-14 at 17:49 +0100, Timo Sirainen wrote:
On Tue, 2010-09-14 at 18:07 +0200, Miha Vrhovnik wrote:
I'm trying to set Dovecot 2.0 with postgresql dict. It's kind of working but not as I'd like to. Dovecot expects to get a dedicated table for quota management. When I've run *doveadm quota recalc -A* I've noticed in the logs that Dovecot tries to run DELETE statements. Fortunately it didn't succeed as I've only given it SELECT and UPDATE permissions on the fields that it needs.
I'd like to configure it, in UPDATE only mode as rows already exist and there is no need to issue any other statement.
Maybe some day, but it's not something I'm planning on implementing anytime soon. Feel free to poke around the code to see if there's a way to do it without horrible changes.
Maybe it could treat NULL value as "row not found" and DELETE could be replaced with UPDATE to NULL. Maybe.
I don't know the internals but. Wouldn't it be possible to do: When removing/receiving new message and same in case of doveadm recalc. SELECT with key.. if not found INSERT else UPDATE
unless I'm missing something upper one could be optimized to UPDATE if changed rows < 1 then INSERT
which wouldn't require the SELECT and would also spare the trigger when using postresql.
Regards, Miha
-- It's time to get rid of your current e-mail client ... ... and start using si.Mail.
It's small & free. ( http://www.simail.si/ )
On Tue, 2010-09-14 at 19:08 +0200, Miha Vrhovnik wrote:
When removing/receiving new message and same in case of doveadm recalc. SELECT with key.. if not found INSERT else UPDATE
I don't think this kind of code exists anywhere? It's possible that doveadm does a SELECT, but it's not to find out if the row exists or not, it just wants to know what the current quota value is (although for quota recalc that's probably pointless and could be optimized away).
The actual row updating code works with INSERT INTO .. ON DUPLICATE KEY UPDATE .. with MySQL, and with INSERT+trigger with PostgreSQL.
unless I'm missing something upper one could be optimized to UPDATE if changed rows < 1 then INSERT
which wouldn't require the SELECT and would also spare the trigger when using postresql.
The trigger is there to avoid race conditions:
- two processes try to add the row at the same time
- one process tries to update the row when another had just deleted it
"Timo Sirainen" tss@iki.fi wrote on 14.9.2010 19:24:14:
On Tue, 2010-09-14 at 19:08 +0200, Miha Vrhovnik wrote:
When removing/receiving new message and same in case of doveadm recalc. SELECT with key.. if not found INSERT else UPDATE
I don't think this kind of code exists anywhere? I was being hypothetical.
The actual row updating code works with INSERT INTO .. ON DUPLICATE KEY UPDATE .. with MySQL, and with INSERT+trigger with PostgreSQL.
unless I'm missing something upper one could be optimized to UPDATE if changed rows < 1 then INSERT
which wouldn't require the SELECT and would also spare the trigger when using postresql.
The trigger is there to avoid race conditions:
- two processes try to add the row at the same time
- one process tries to update the row when another had just deleted it It seems that I missed the race conditions.
Have decided that it would be best to give the Dovecot dedicated table and then add DELETE trigger and modify insert/update one to also update the values in my table.
Regards, Miha
-- It's time to get rid of your current e-mail client ... ... and start using si.Mail.
It's small & free. ( http://www.simail.si/ )
participants (2)
-
Miha Vrhovnik
-
Timo Sirainen