[Dovecot] expire plugin: columns not uniq
Hello list,
My dovecot setup have expire plugin enabled. It is working fine: new records added to database(sqlite), expire-tool successfully expunges old mail, but when expire-tool try to update database record for processed user, I get error in logs:
root@mail dovecot 0:0 # /usr/sbin/dovecot --exec-mail ext /usr/libexec/dovecot/expire-tool.sh --test Info: koshikov.n@domain.com/Spam: timestamp 1250622406 (Tue Aug 18 22:06:46 2009) -> 1250684287 (Wed Aug 19 15:18:07 2009)
in logs: Aug 19 13:29:11 dict: Error: sqlite: exec(INSERT INTO expires (expire_stamp,username,mailbox) VALUES ('1250684287','koshikov.n@domain.com','Spam')) failed: columns username, mailbox are not unique (19) Aug 19 13:29:11 dict: Error: sql dict: commit failed: 1҅�t+�]�Ћu�}��]Ð�t&
This is my expire dict config file:
connect = /var/mail/expire.db #v1.2 map { pattern = shared/expire/$user/$mailbox table = expires value_field = expire_stamp
fields {
username = $user
mailbox = $mailbox
}}
expire plugin related dovecot sections: dict { expire = sqlite:/etc/dovecot/plugins/expire.conf } plugin { expire = Trash 30 Spam 20 expire_dict = proxy::expire }
Version of sqlite is 3.6.16, dovecot-1.2.3.
How can I solve this ? and why expire plugin is trying to use INSERT, instead of UPDATE ?
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1
On Wed, 19 Aug 2009, Nikita Koshikov wrote:
How can I solve this ? and why expire plugin is trying to use INSERT, instead of UPDATE ?
Search the Wiki about Postgres and the requirement of a TRIGGER. I guess that sqlite requires something like this.
Unfortunately, there is no UPDATE OR INSERT in SQL and it is hard to simulate.
Bye,
Steffen Kaiser -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux)
iQEVAwUBSo5fbnWSIuGy1ktrAQKQdQgAs1fioq/x86JNb9L7SvGnU9wPOUICilsI RcY+PrsMzGxdRShlnDhEIJGRlbzr7jHDhDhMfej+TGP9XqBFPqBphVxT8JIJmGXz Z4qEvwLoyuSKxWikLxVLaslDPYPu2tjmvKt+HWaLgev7ecwFF8NoKBHhWV6fR3x9 o/xVVcj6hM38SkiGc6+t27OC5ch6Tq5Vqmau4QIB7fkN8bA8IoBU17rM0eW4k80m 7nKTVsPqFcDKlmes2d5vJVlCYg+NCy69RVIZEFQwOJahBjveSvJsXWnhks9e0nEk PmtMPMUFHOvfe+4KfK2GFxAPCznOSrxfgMuUewXnENCgLtXrox+zPA== =8vpx -----END PGP SIGNATURE-----
On Fri, 21 Aug 2009 10:48:42 +0200 (CEST) Steffen Kaiser <skdovecot@smail.inf.fh-brs.de> wrote:
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1
On Wed, 19 Aug 2009, Nikita Koshikov wrote:
How can I solve this ? and why expire plugin is trying to use INSERT, instead of UPDATE ?
Search the Wiki about Postgres and the requirement of a TRIGGER. I guess that sqlite requires something like this.
Thank for your answer. I'll dig in this direction.
Unfortunately, there is no UPDATE OR INSERT in SQL and it is hard to simulate.
You mean in dovecot expire plugin or where ?
Bye,
Steffen Kaiser -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux)
iQEVAwUBSo5fbnWSIuGy1ktrAQKQdQgAs1fioq/x86JNb9L7SvGnU9wPOUICilsI RcY+PrsMzGxdRShlnDhEIJGRlbzr7jHDhDhMfej+TGP9XqBFPqBphVxT8JIJmGXz Z4qEvwLoyuSKxWikLxVLaslDPYPu2tjmvKt+HWaLgev7ecwFF8NoKBHhWV6fR3x9 o/xVVcj6hM38SkiGc6+t27OC5ch6Tq5Vqmau4QIB7fkN8bA8IoBU17rM0eW4k80m 7nKTVsPqFcDKlmes2d5vJVlCYg+NCy69RVIZEFQwOJahBjveSvJsXWnhks9e0nEk PmtMPMUFHOvfe+4KfK2GFxAPCznOSrxfgMuUewXnENCgLtXrox+zPA== =8vpx -----END PGP SIGNATURE-----
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1
On Fri, 21 Aug 2009, Nikita Koshikov wrote:
You mean in dovecot expire plugin or where ?
No, in general. If one wants some data in a DB, but update if already present.
If you re-programm the TRIGGER in the client, you need to lock the whole table, if you want to overcome all race conditions and also don't want any errors logged (at least with postgres).
Bye,
Steffen Kaiser -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux)
iQEVAwUBSo62cHWSIuGy1ktrAQLimQf/QYdrEX3y94bK1jmMk4T+bIIMcjmzESe5 0o+W0AFSOifWop8JOHZy1YJUXa5gkGnhg09ar0zC9UQ8T4J97xz0ORfe3UHmisKv xh2zJ8lhw1OviQ4qrPcSot1/D/SQ/xdPKbBaaD1S7k+E0zK44P74PHGAvRsx+tUZ 3BR0U73LFtAiAhvW/y2rseo/vIuXBBSPthBK/nExiiODN9P9Vq3iOczfZwVUkVmo cNwArCwjkBGk6dGRnfGEoPKWsa/qFVHqQPjwen7j0T2ak58Vfm9Vc3pGmMSequGh MIjcOHcmpcF6AAhsZQIoYewgVtQQ+aXY9T4zQo6JbLCEyF7RCs/yRg== =cinD -----END PGP SIGNATURE-----
No, in general.
If one wants some data in a DB, but update if already present.
If you re-programm the TRIGGER in the client, you need to lock the whole table, if you want to overcome all race conditions and also don't want any errors logged (at least with postgres).
Thanks for explanation. I'm not so aware in databases, but if trigger will be programmed on client side - this will make code more portable(in case of database choicing) and what is the less evil - locking or portability... In any case, thanks for reply, it helps me much.
Search the Wiki about Postgres and the requirement of a TRIGGER. I guess that sqlite requires something like this.
Maybe someone family with sqlite will say how to make this trigger better and constrain check field existence before doing UPDATE
CREATE TRIGGER mergeexpire BEFORE INSERT ON expires FOR EACH ROW BEGIN UPDATE expires SET expire_stamp=NEW.expire_stamp WHERE username = NEW.username AND mailbox = NEW.mailbox; SELECT raise(ignore) WHERE (SELECT 1 FROM expires WHERE username = NEW.username AND mailbox = NEW.mailbox) IS NOT NULL; END;
This seem some kind crudely to me, but it's working.
On Fri, 2009-08-21 at 17:02 +0300, Nikita Koshikov wrote:
Search the Wiki about Postgres and the requirement of a TRIGGER. I guess that sqlite requires something like this.
Maybe someone family with sqlite will say how to make this trigger better and constrain check field existence before doing UPDATE
CREATE TRIGGER mergeexpire BEFORE INSERT ON expires FOR EACH ROW BEGIN UPDATE expires SET expire_stamp=NEW.expire_stamp WHERE username = NEW.username AND mailbox = NEW.mailbox; SELECT raise(ignore) WHERE (SELECT 1 FROM expires WHERE username = NEW.username AND mailbox = NEW.mailbox) IS NOT NULL; END;
This seem some kind crudely to me, but it's working.
That looks correct to me. I'll add it to the wiki page. A similar trigger could be used for dict quota too. So now that SQLite is working, I'll just start deprecating Berkeley DB support even more strongly. Thanks. :)
On Mon, 2009-08-24 at 13:27 -0400, Timo Sirainen wrote:
CREATE TRIGGER mergeexpire BEFORE INSERT ON expires FOR EACH ROW BEGIN UPDATE expires SET expire_stamp=NEW.expire_stamp WHERE username = NEW.username AND mailbox = NEW.mailbox; SELECT raise(ignore) WHERE (SELECT 1 FROM expires WHERE username = NEW.username AND mailbox = NEW.mailbox) IS NOT NULL; END;
This seem some kind crudely to me, but it's working.
That looks correct to me.
Oh, except there's a small race condition if the first row is being added at the same time by two processes. That's why the PostgreSQL trigger is so much more complex. But it should be somewhat rare I guess..
Unless SQLite has some locks that prevent that? Anyway I was thinking that two processes run the UPDATE part of the trigger and then both try to INSERT. One of them succeeds and the other one fails. But the row gets added anyway and the timestamp is the same anyway, so it probably doesn't matter all that much, just logs an error.
On Mon, 24 Aug 2009 13:33:15 -0400 Timo Sirainen <tss@iki.fi> wrote:
On Mon, 2009-08-24 at 13:27 -0400, Timo Sirainen wrote:
CREATE TRIGGER mergeexpire BEFORE INSERT ON expires FOR EACH ROW BEGIN UPDATE expires SET expire_stamp=NEW.expire_stamp WHERE username = NEW.username AND mailbox = NEW.mailbox; SELECT raise(ignore) WHERE (SELECT 1 FROM expires WHERE username = NEW.username AND mailbox = NEW.mailbox) IS NOT NULL; END;
This seem some kind crudely to me, but it's working.
That looks correct to me.
Sorry for such lately response, I have just returned from vacation.
Oh, except there's a small race condition if the first row is being added at the same time by two processes. That's why the PostgreSQL trigger is so much more complex. But it should be somewhat rare I guess..
Unless SQLite has some locks that prevent that? Anyway I was thinking that two processes run the UPDATE part of the trigger and then both try to INSERT. One of them succeeds and the other one fails. But the row gets added anyway and the timestamp is the same anyway, so it probably doesn't matter all that much, just logs an error.
As far as I know, sqlite permits to write database changes(INSERT,UPDATE or DELETE) to only 1 process at the same time and when process is updating data - sqlite table is locking. This might be OS queue processing, because sqlite have no master process for handling such racing. In my understanding, if two identical INSERTs will be passed to database(and the record already exists) - trigger's UPDATE will be run twice, but consistently. My admissions can be faulty and sqlite experts should shed some light on this situation. The above trigger is working about a week in my setup - I have checked error log for this period - there is nothing related to database issues.
participants (3)
-
Nikita Koshikov
-
Steffen Kaiser
-
Timo Sirainen