[Solved] SELECT transaction seems to be cached on postgres?

EverSQL Database Performance Knowledge Base

SELECT transaction seems to be cached on postgres?

Database type:

The server I am running distributes 'jobs' out to users, however, I'm having an issue where users get distributed the same job. I believe this is either a concurrency issue or a caching issue.

Here's the query I currently use (note the {...} is for python formatting):

UPDATE "job" 
SET pending=true, completor='{client.uuid}' 
WHERE "number" IN 
     SELECT "number" FROM "job" 
     WHERE pending=false AND closed=false AND gpu=true 
     ORDER BY "number" ASC LIMIT 1

Afterwards, I fetch the job where completor='{client.uuid}' and it fetches the job that was updated. I thought that using this system would make the entire process run in a single transaction, avoiding any concurrency issues.

However, after testing I found that clients can be given the same job even after 5 seconds apart from each other. Could this be a caching issue, or do I need to implement a locking mechanism?

Thanks in advance.

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
CREATE INDEX job_idx_number ON "job" ("number");
CREATE INDEX job_idx_pending_closed_gpu_number ON "job" ("pending","closed","gpu","number");
The optimized query:
        "job"."number" IN (
                "job".pending = false 
                AND "job".closed = false 
                AND "job".gpu = true 
            ORDER BY
                "job"."number" ASC LIMIT 1

Related Articles

* original question posted on StackOverflow here.