0

I currently have a Python program that enters rows into a Postgres table that essentially works as a list of data I need to process. These processes create on-disk files and trigger other behavior so I only want it to run once for each row.

I then have another script that takes the rows from that table then begins to do the processing. So, for example, there might be 100 rows and each row might take 10-20 minutes to complete and each produces a few output files.

I currently am running into the problem where I can only run this script one at a time in fear that running two in parallel might end up with them processing the same data twice.

If I create a boolean field that I flip within the application when it's 'busy', I fear having a stale lock due to an abruptly killed process that doesn't end gracefully. If I use locks as built within Postgres, it seems they disappear upon the connection/session ending. But if I'm on an unstable connection, I'm not quite sure what the behavior would be or how I can get the behavior I want? Given these are 10-20 minute processes, I foresee connection being lost within that time frame and thus the lock being lost. Thanks for any advice on where to go. I'm using a Python library called psycopg2 to connect to the Postgres database.

Improve this question
6
  • This is commonly labelled a queue, as far as I can see there’s no in built way in PostgreSQL but it’s commonly achieved with for update skip locked for decent concurrency. See blabosoft.com/implementing-queue-in-postgresql for ideas
    – Andrew Sayer
    Commented May 21, 2022 at 18:29
  • @AndrewSayer Thanks! This gave me a clear way to go forward. I suppose I already have a queue table. So I can just add a processing_started_at field and only work on rows that don't have a processing_started_at field. In the end of the blog post, he mentions stale locks that don't exit gracefully and says "there are many good solutions for handling such situations, but I don’t cover them in this article". I was thinking of just checking the time of the processing_started_at and removing it if it's beyond a reasonable time. Is this the common approach?
    – Pensw
    Commented May 21, 2022 at 18:59
  • It doesn't matter if you, as the programmer, are on an unstable connection. Is the program you write also on an unstable connection?
    – jjanes
    Commented May 21, 2022 at 21:26
  • 1
    It isn't clear what behavior you do want. You apparently don't want to clean it up manually, and also don't want it cleaned up automatically. So, what then do you want?
    – jjanes
    Commented May 21, 2022 at 21:29
  • Yeah, the connection between what the program is running on and the database server is unstable. I establish a new connection and commit each statement immediately rather than attempting to retain one connection throughout the program. I think ideally I want a lock tied to the process, rather than the connection which may drop. That way if a process ends prematurely, it will drop the lock by itself, but I will be able to retain the lock within that process. This way I wouldn't deal with either stale locks or duplicate work. Was curious what the norms are in dealing with this in practice.
    – Pensw
    Commented May 21, 2022 at 22:15

1 Answer 1

Reset to default
0

Instead of a boolean attribute that shows if a row is being processed, add a timestamp with time zone attribute that you set to current_timestamp when you start processing a row. When looking for rows to process, add a WHERE condition like

WHERE locked_at IS NULL OR locked_at < current_timestamp - INTERVAL '30 minutes'

Then you have a lock that automatically expires after 10 minutes. If processing could take longer than 30 minutes, use a different interval.

Improve this answer

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.