WITH returned table behave unexpectedly when used with RETURNING in Postgresql?

I’m stumped. Why does this work fine…

WITH sst AS (SELECT * FROM subtopics_results WHERE student_id = 2)
SELECT * FROM subtopics_results AS str 
WHERE (subtopic_id,student_id) IN (SELECT subtopic_id,student_id FROM sst);

But the below doesn’t – it returns an empty table. I can query the returned table sst just fine (e.g. SELECT * FROM sst), but when I try to use

WITH sst AS (
    -- inside block is working fine
    INSERT INTO subtopics_results (paper_id, student_id, subtopic_id, marks_scored) 
        (
            WITH subtopics AS (
            SELECT qbr.question_id, q.subtopic_id, qbr.student_id, q.paper_id, qbr.marks_scored
            FROM question AS q
            JOIN question_breakdown_result AS qbr
            ON qbr.question_id = q.question_id
            WHERE paper_id = 1 AND qbr.student_id = 2
            )
            SELECT paper_id, student_id, subtopic_id, SUM(marks_scored)
            FROM subtopics GROUP BY subtopic_id, student_id, paper_id
            HAVING SUM(marks_scored) > 2
        ) 
    RETURNING subtopic_id, student_id
) 
-- this returns an empty table, even though identical to the above.
SELECT * FROM subtopics_results AS str 
WHERE (subtopic_id,student_id) IN (SELECT subtopic_id,student_id FROM sst);

I’m trying to select all the pairs in the table subtopics_results (subtopic_id and student_id) after an insertion, but keep getting nothing back, even though I do expect to have results.

I have tried inner joins, no avail.

Is this to do with the RETURNING statement?

Answer

That behavior is documented in “7.8.2. Data-Modifying Statements in WITH“:

(…)

The sub-statements in WITH are executed concurrently with each other and with the main query. Therefore, when using data-modifying statements in WITH, the order in which the specified updates actually happen is unpredictable. All the statements are executed with the same snapshot (see Chapter 13), so they cannot “see” one another’s effects on the target tables. (…)

(…)

Maybe using UNION ALL to merge the rows returned by RETURNING and the ones already in the table before the INSERT happens can do what you want here.

WITH
sst AS
(
INSERT INTO subtopics_results
            (...)
       RETURNING *
)
SELECT sst.*
       FROM sst
UNION ALL
SELECT str.*
       FROM subtopics_results AS str 
       WHERE (str.subtopic_id,
              str.student_id) IN (SELECT sst.subtopic_id,
                                         sst.student_id
                                         FROM sst);

Or, if you just want the inserted rows back (that’s not entirely clear to me) simply use:

INSERT INTO subtopics_results
            (...)
       RETURNING *;