Hello, I have a table with a large number of records (millions), on which the following should be performed: 1. Retrieve a set of records by a SELECT query with a WHERE condition 2. Process these in the application 3. Delete them from the table Now, in the default read-committed transaction isolation, I can't just use the same WHERE condition with a DELETE in step 3 as it might delete more records than are processed in step 1 (i.e. phantom read). I've thought of several ways around it and would like some feedback on which would be the most efficient: #1: Create a giant DELETE WHERE ... IN (...) SQL command for step #3 with primary keys of records from step 1 - but will it hit a SQL string length limitation in the database? Is there such a limit (and what is it?) #2: Same as #1 but with batching the records to e.g. 1000 at a time, all in one transaction #2: Use a higher isolation level, probably Repeatable Read (PG 9.0) - but then the question is will this block other clients from inserting new data into the table? Also, is Repeatable Read enough? Any other ideas?
Attachment:
signature.asc
Description: OpenPGP digital signature
