Why do I get an empty result in Postgres UPSERT with UNION

Question

Trying the following UPSERT in PG (nodejs):

WITH upsert_result AS(
    INSERT INTO table1 (col1, col2)
    VALUES($1,$2)
    ON CONFLICT DO NOTHING
    RETURNING *
)
SELECT * FROM upsert_result
UNION
SELECT * FROM table1 WHERE col1=$1

Why do I sometimes get empty results?

It is my understanding that the UNION here was supposed to prevent this by fetching the conflicting row.

Here is the table definition:

CREATE EXTENSION IF NOT EXISTS "ltree";

CREATE TABLE IF NOT EXISTS table1(
    col1 TEXT NOT NULL UNIQUE REFERENCES table2 (col1) ON DELETE CASCADE,
    col2 ltree NOT NULL
);
CREATE INDEX IF NOT EXISTS tree_col2_idx ON table1 USING gist (col2);

I expected the query to return either the newly inserted row (if a row with same col1 doesn't already exist) or the existing row (if a row with the same col1 already exists). Indeed, this is what happens most of the time, but on some rare occasions, I get an empty result from the insert above.

Answer 1

There is a hidden race condition.

The check for conflicts includes (has to include!) rows from (yet) uncommitted concurrent transactions. If one has written to a row which your transaction now tries to UPSERT, your transaction has to wait for the other one to finish.

If the other transaction ends normally, your INSERT will detect a conflict and DO NOTHING, hence also not return the row. The SELECT sees the same snapshot from the start of the query and also cannot return the yet invisible row.

Read a detailed assessment here, especially chapter "Concurrency issue 1":

• How to use RETURNING with ON CONFLICT in PostgreSQL?

... and how to overcome it.

Aside: UNION ALL, not UNION. But that's also in the linked answer.