Re: Bulk processing & deletion

On 13 October 2011 20:08, Steve Crawford <scrawford(at)pinpointresearch(dot)com> wrote:
> On 10/13/2011 05:20 AM, Ivan Voras wrote:
>>
>> 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
>>
> Without knowing a bit more, it is difficult to say. A couple questions:
>
> 1. Are there conflicting processes - i.e. could multiple applications be in
> contention to process the same set of records?

No, only one bulk processor.

> 2. Is the processing "all or none" or could individual records fail? If so,
> how do you deal with reprocessing or returning those to the main table.

All or none; the nature of thing is that there can be no fatal failures.

> Depending on the nature of your app, it might be feasible to reorder the
> actions to move the records to be processed into a "processing" table and
> delete them from that table as the records are processed by the application.
>
> You could move the records into the processing table with:
>
> with foo as (delete from main_table where your_where_clause returning a,b,c)
> insert into processing_table (a,b,c) select a,b,c from foo;
>
> In this case I would not recommend temporary or unlogged tables for the
> processing table as that becomes the only source of the data once deleted
> from the master table.

Ok, thanks (to everyone)!