Re: MOVE

On Fri, Jan 14, 2005 at 08:49:24PM +0100, PFC wrote:
> the auto-expiration cron
> job, I must also expire ALL his open chatroom connections.
> INSERT INTO archive (...) SELECT ... FROM active WHERE user_id = ...;
> DELETE FROM active WHERE user_id = ...;
>
> Now, if the user inserts a connection between the two queries above, the
> thing will fail (the
> connection will just be deleted). I know that there are many ways to do it
> right :

Why not just do it in a single transaction? I don't think you need to
use SERIALIZABLE at all, I think normal read-committed mode will do
what you want, no?

BEGIN;
INSERT INTO archive (...) SELECT ... FROM active WHERE user_id = ...;
DELETE FROM active WHERE user_id = ...;
COMMIT;

The DELETE can only delete the rows returned by the select, that's the
whole point of transactions...

Hope this helps,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.