Predict the output
The same event arrives twice. What does the final SELECT print?
CREATE TABLE inbox (event_id text PRIMARY KEY);
CREATE TABLE points (user_id int PRIMARY KEY, total int);
INSERT INTO points VALUES (1, 0);
WITH new AS (
INSERT INTO inbox VALUES ('evt-9') ON CONFLICT DO NOTHING
RETURNING 1
)
UPDATE points SET total = total + 10
WHERE user_id = 1 AND EXISTS (SELECT 1 FROM new);
WITH new AS (
INSERT INTO inbox VALUES ('evt-9') ON CONFLICT DO NOTHING
RETURNING 1
)
UPDATE points SET total = total + 10
WHERE user_id = 1 AND EXISTS (SELECT 1 FROM new);
SELECT total FROM points;Hint 1 of 1
What does the CTE return the second time?
Choose an answer, then open Show answer below to check it.
Show answer
Answer: A 10
Correct. The second insert conflicts and returns no row, so the update matches nothing.
The inbox table records which event ids were already handled. The second INSERT hits the primary key and DO NOTHING returns no row, so EXISTS is false and the second +10 is skipped.
Why not the others?
- B
20. That's what happens without the inbox check. - C
0. The first delivery is new and applies. - D A unique violation. ON CONFLICT DO NOTHING absorbs it.
| total |
|---|
| 10 |
(1 row)