1

I'm using node.js, node-postgres and Postgres to put together a script to process quite a lot of data from a table. I'm using the cluster module as well, so I'm not stuck with a single thread.

I don't want one of the child processes in the cluster duplicating the processing of another. How can I update the rows I just received from a select query without the possibility of another process or query having also selected the same rows?

I'm assuming my SQL query will look something like:

BEGIN;
SELECT * FROM mytable WHERE ... LIMIT 100;
UPDATE mytable SET status = 'processing' WHERE ...;
COMMIT;

Apologies for my poor knowledge of Postgres and SQL, I've used it once before in a simple PHP web app and never before with node.js.

Improve this question
2
  • You'd want to write a FOR UPDATE using something like this: stackoverflow.com/questions/18879584/… Commented Jan 2, 2015 at 12:08
  • @Anthony I'll take a look at FOR UPDATE, I'm not sure if it does exactly what I'm wanting to do though. Commented Jan 2, 2015 at 12:21

2 Answers 2

Reset to default
0

If you're using multithreaded application you cannot and should not be using "for Update" (in the main thread anyway) what you need to be using is advisory lock. Each thread can query a row or mnany rows, verifying that they're not locked, and then locking them so no other session uses them. It's as simple as this within each thread:

select * from mytab
where pg_try_advisory_lock(mytab.id)
limit 100

at the end be sure to release the locks using pg_advisory_unlock

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

this has the potential to lock all the rows in the table but only return a subset of them.
0 
BEGIN;
UPDATE mytable SET status = 'processing' WHERE status <> "processing" and id in 
( selecy ID FROM mytable where status <> "processing" limit 100) returning * ;
COMMIT;

There's a chance that's going to fail if some other query was working on the same rows so if you get an error, retry it until you get some data or no rows returned.

if you get zero rows either you're finished or there;s too many other simultaneous proceses like yours.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.