[Dovecot] expire plugin: columns not uniq

[Nikita Koshikov]

On Mon, 24 Aug 2009 13:33:15 -0400
Timo Sirainen <tss at 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.