On Fri, Oct 29, 2010 at 4:59 PM, Jeff Davis <pgsql@xxxxxxxxxxx> wrote: > On Fri, 2010-10-29 at 16:57 -0500, Andy Colson wrote: >> begin >> insert into logged select * from events where processed = false; >> update events set processed = true where processed = false; >> commit; > > There's a race condition there. The SELECT in the INSERT statement may > read 5 tuples, then a concurrent transaction inserts a 6th tuple, then > you do an update on all 6 tuples. > >> begin >> select * from events where processed = false; >> ... do you processing on each, which would include inserting it... >> update events set processed = true where processed = false; >> commit; > > Same problem here. > >> Just make sure you do it all in the same transaction, so the update sees >> the exact same set as the select. > > Regards, > Â Â Â ÂJeff Davis As stated earlier in the thread, there is a race condition within that transaction, but you could also use a temp table to get the ids that you are about to process. Maybe something like this (untested): begin; create temp table _idlist (id bigint) on commit drop as select id from eventlog where processed is false; insert into othertable select e.* from eventlog as e inner join _idlist as i on (i.id=e.id); update eventlog set processed=true from _idlist as i where eventlog.id = i.id; commit; -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
