A PostgreSQL get-or-create query puts INSERT ... ON CONFLICT DO NOTHING RETURNING brand_id in a WITH clause, UNION ALL a SELECT of the existing row, yet sometimes returns nothing; why?
answer
- DO NOTHING returns nothing
- one statement, one snapshot
- the other run committed late
- no-op update has a price
basics
~20 sDO NOTHING returns no row for a conflict, and the fallback SELECT shares the statement's snapshot, so a brand committed by a concurrent transaction after that snapshot is invisible to it; the query returns zero rows although the brand exists.
solid answer
~50 sAll sub-statements of a PostgreSQL `WITH` and the main query run with **one snapshot**. When another sync run inserts the same brand and commits after that snapshot was taken, our `INSERT` waits on the unique index, sees the conflict and does nothing, so `RETURNING` is empty; the `SELECT` in the same statement still cannot see the committed row, so `UNION ALL` yields nothing. Fixes: run the fallback `SELECT` as a **separate statement** under read committed, which takes a fresh snapshot, in a short retry loop; or use `DO UPDATE SET name = EXCLUDED.name RETURNING brand_id`, which always returns the row, even one invisible to the snapshot, at the cost of a new row version, WAL and row-level `UPDATE` triggers on every call. Under repeatable read a separate statement does not help, because the snapshot is per transaction.
code
sql · 17 linesCREATE FUNCTION brand_id_for(p_name text) RETURNS bigint
LANGUAGE plpgsql AS $$
DECLARE
v_id bigint;
BEGIN
LOOP
INSERT INTO brand (name) VALUES (p_name)
ON CONFLICT (name) DO NOTHING
RETURNING brand_id INTO v_id;
EXIT WHEN v_id IS NOT NULL;
SELECT brand_id INTO v_id FROM brand WHERE name = p_name;
EXIT WHEN v_id IS NOT NULL;
END LOOP;
RETURN v_id;
END;
$$;go deeper
Remember that DO NOTHING returns no row on a conflict, so get-or-create code needs a second way to fetch the existing id.
Explain that every part of a data-modifying WITH runs on one snapshot, so a commit after that snapshot is invisible to the fallback SELECT.
Choose between a fresh-statement retry and a no-op DO UPDATE by their write, trigger and lock costs, and know repeatable read changes the answer.
Consider removing per-row get-or-create from the hot path entirely, resolving reference data in one set-based step per batch.
## The pattern While syncing a supplier's product feed, each product row references a `brand`. The sync resolves brand names to ids with a get-or-create query: ```sql WITH ins AS ( INSERT INTO brand (name) VALUES ('Acme Tools') ON CONFLICT (name) DO NOTHING RETURNING brand_id ) SELECT brand_id FROM ins UNION ALL SELECT brand_id FROM brand WHERE name = 'Acme Tools'; ``` It looks airtight: either the insert returns the new id, or the `SELECT` finds the existing one. Occasionally it returns **zero rows**, and the product is written with a NULL brand or the job fails. ## Two facts that combine 1. **`DO NOTHING` returns nothing for a conflict.** `RETURNING` reports only rows actually inserted or updated; a skipped row produces no output. 2. **A data-modifying `WITH` shares one snapshot.** PostgreSQL runs the `WITH` sub-statements and the main query concurrently with each other, all with the **same snapshot**, so none of them sees another's effects — or any commit that happened after that snapshot was taken. `RETURNING` output is the only channel between them. Now add a concurrent sync run: - Run A inserts brand 'Acme Tools' and has not committed yet. - Run B's statement starts and takes its snapshot. - Run B's `INSERT` finds A's entry in the unique index and **waits** for A. - A commits. B's insert now has a real conflict, so `DO NOTHING` skips it; `ins` is empty. - B's `SELECT` uses the snapshot from before A's commit, so it **cannot see** the brand either. The manual describes exactly this: under read committed, `ON CONFLICT DO NOTHING` may decline to insert because of another transaction whose effects are not visible to the `INSERT`'s snapshot. ## Fixes and their costs | Approach | Returns the id every time? | Cost | |---|---|---| | Same-statement `UNION ALL` fallback | no | — | | Separate `SELECT` after the `INSERT`, retried in a loop (read committed) | yes, eventually | an extra round trip on conflict | | `DO UPDATE SET name = EXCLUDED.name RETURNING brand_id` | yes | a new row version, WAL and row-level `UPDATE` triggers per call, plus a row lock | | Pre-resolve all brands in one set-based upsert before loading products | yes, per batch | restructures the job | The **no-op update** works because `DO UPDATE` acts on the conflicting row even when no version of it is visible to the snapshot, and an updated row is returned. Its price is real: each call writes a new tuple version that vacuum must later remove (table bloat), fires `BEFORE`/`AFTER UPDATE` row triggers, and takes a row lock that serializes concurrent callers on hot brands. A filtered `DO UPDATE ... WHERE false` does not help — filtered rows are not returned. The **retry loop** is cheaper: in read committed each statement takes a new snapshot, so a `SELECT` issued after the `INSERT` sees A's committed brand. Loop because the brand could in principle be deleted between the two statements. ## Isolation level matters Under **repeatable read** or **serializable** every statement in the transaction shares the transaction's snapshot, so the separate `SELECT` cannot see the brand either, and an `ON CONFLICT` that meets a row invisible to that snapshot raises a serialization failure instead; handling that belongs with PostgreSQL's isolation levels. ## Other writable-CTE rules worth knowing - Data-modifying statements are allowed only in a `WITH` attached to the **top-level** statement. - They run **exactly once and to completion**, whether or not the main query reads their output. - Modifying the same row twice in one statement is unsupported and the outcome unpredictable; keep sub-statements on disjoint rows. - A data-modifying sub-statement without `RETURNING` still runs, but the rest of the query cannot refer to it. - The command tag reported to the client counts only the main statement's rows, not those changed inside `WITH`. ```sql -- Read committed: separate statements, so the SELECT sees later commits INSERT INTO brand (name) VALUES ('Acme Tools') ON CONFLICT (name) DO NOTHING; SELECT brand_id FROM brand WHERE name = 'Acme Tools'; ```
- Why not just use ON CONFLICT (name) DO UPDATE SET name = EXCLUDED.name everywhere?It always returns the id, but every call writes a new row version of the brand, generates WAL, fires row-level `UPDATE` triggers and locks the row, so concurrent syncs queue on popular brands and vacuum has more to clean. For a lookup that almost always hits an existing row, a `DO NOTHING` plus separate `SELECT` is far cheaper.
- In the WITH version, would SELECT brand_id FROM ins UNION ALL SELECT ... FROM brand ever return two rows?Not for the race described. If the insert succeeds, the main query's `SELECT` from `brand` cannot see the new row, because every part of the statement shares the snapshot taken before the insert. So it returns the id once from `ins`, never twice.
A single statement works from one photograph of the database taken as it starts; everything inside it, including the fallback SELECT, looks at that photo. A brand committed after the shutter clicked is simply not in the picture until a new statement takes a new photo.
- The SELECT in the main query sees rows inserted by the WITH clause above it.
- ON CONFLICT DO NOTHING RETURNING returns the existing row when there is a conflict.
- Adding WHERE false to DO UPDATE returns the row without writing it.
- Retrying the SELECT inside a REPEATABLE READ transaction will eventually see the brand.
- A no-op DO UPDATE is free because the values do not change.