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:

(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.

  • Are you sure you omly want to insert 1 row and not all with stauts NULL or ready??
    – nbk
    Apr 26, 2021 at 16:13
  • @nbk I am sure. This is used to sync a long running processing step, and effectively claims one subject that I will start crunching data on. I run this query again any time the processing workers are ready for another subject.
    – ChrisBob
    Apr 26, 2021 at 16:41

1 Answer 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...

  • Thank you. I would never have guessed that the difference was formatting the query with VALUES(). I also appreciate the warning about not having an ORDER BY clause, but I am aware and ok with that in this case.
    – ChrisBob
    Apr 26, 2021 at 16:43
  • 1
    @ChrisBob the warning about not having an ORDER BY clause If this SELECT may return more than 1 row then you must add ORDER BY clause by an expression which provides rows uniqueness. If not then the query is not deterministic which is bad practice.
    – Akina
    Apr 26, 2021 at 16:48
  • 1
    The key part of this answer is hidden on the FIRST line of the documentation for the PG INSERT statement at postgresql.org/docs/current/sql-insert.html. "INSERT inserts new rows into a table. One can insert one or more rows specified by value expressions, or zero or more rows resulting from a query."
    – ChrisBob
    Apr 26, 2021 at 16:57
  • we undestand sql. but an unordered list, is non deterministic to select the first row, so eiterh it is irrelevant which gets select or you have only one to begin with, that is vital information
    – nbk
    Apr 26, 2021 at 17:05

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.