Unexpected query result - Mailing list pgsql-general

From Begin Daniel
Subject Unexpected query result
Date
Msg-id COL129-W296A93D384EEBC40B0436A94480@phx.gbl
Whole thread Raw
Responses Re: Unexpected query result
Re: Unexpected query result
List pgsql-general
In order to process a large amount of data I need to run a procedure using parallel batch processes.  
The query I run in each process is expected to ...

1- select a bunch of id (500) in a table (wait4processing) containing the list of all records to process
2- remove selected records from wait4processing table in order to eliminate duplicate processing
3- run the procedure (build_contributions_clusters) over the range of selected ids 

--The query I use:
With ids as( delete from wait4processing where id in( select id from wait4processing limit 500)  returning id) 
select build_contributions_clusters(min(id),max(id)) from ids;

The query runs properly if I send it sequentially (wait for the completion of the query before sening it again) but it does'nt work when sending multiple occurrences in parallel.  Seems from the results I got that the first query received by the server runs properly but the following ones try to process the same first 500 records even if deleted - the build_contributions_clusters procedure receive NULL values instead of the following 500 records.

Since I am almost certain it is the expected behavior, I would like to like to understand why, and I would also appreciate to see alternative queries to do the job.

Thanks :-)

pgsql-general by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: postgresql doesn't start
Next
From: Adrian Klaver
Date:
Subject: Re: Unexpected query result