INSERT INTO with subquery that might return 0 results

All we need is an easy explanation of the problem, so here it is.

I have a status table that I am using to sync other long running processes, and my following query/system is functioning, but also filling up my error logs:

CREATE TABLE status(subject_id UUID PRIMARY KEY REFERENCES users(subject_id) 
ON DELETE CASCADE, status1 varchar, status2 varchar)


CREATE TABLE users(subject_id UUID PRIMARY KEY, start_date BIGINT)

problem query:

INSERT INTO status
(status1, subject_id) VALUES ('processing',
(SELECT u.subject_id FROM users AS u
    LEFT OUTER JOIN status ON (u.subject_id = status.subject_id)
        WHERE (status.status1 IS NULL or status.status1 = 'ready')
        and usrs.start_date IS NOT NULL LIMIT 1)) 
ON CONFLICT (subject_id) DO update SET status1 = 'processing'

The ON CONFLICT clause correctly handles the case where the subject_id already exists in status, but no the case where my subquery returns 0 results. In that case I don’t want anything to happen, but I would also prefer not to get errors in my log when that happens.

I need to handle cases where:

  1. Claim a subject_id that has no entry in status. This works well.
  2. Claim a subject_id that has an entry in status for another status column. The current ON CONFLICT clause handles this.
  3. Handle the case where there are no more users available. The current query tries to insert a NULL subject_id in this case.

This should be a single query because there are several worker processes that are using the status1, status2, … columns to synchronize their work and two could both start a long running process on the same subject if the select and insert are separate queries.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

INSERT INTO status (status1, subject_id) 
SELECT 'processing', u.subject_id 
    FROM users AS u
    LEFT OUTER JOIN status ON (u.subject_id = status.subject_id)
    WHERE (status.status1 IS NULL or status.status1 = 'ready')
      and usrs.start_date IS NOT NULL LIMIT 1
ON CONFLICT (subject_id) DO update SET status1 = 'processing'

PS. LIMIT without ORDER BY is a lottery…

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Tags:

Leave a Reply