Re: Can Postgres Not Do This Safely ?!?

On Fri, Oct 29, 2010 at 4:59 PM, Jeff Davis <pgsql(at)j-davis(dot)com> 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;