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. You need to use SERIALIZABLE isolation level for this to work. The default is READ COMMITTED. Or better yet, use Merlin's suggestion of PgQ. They've already worked this out in a safe, efficient way. It's the basis for Londiste, a replication system. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
