Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Subject: BUG #19380: Transition table in AFTER INSERT trigger misses rows from MERGE when used with INSERT in a CTE
To: pgsql-bugs@lists.postgresql.org
From: PG Bug reporting form <noreply@postgresql.org>
Cc: dwwoelfel@gmail.com
Reply-To: dwwoelfel@gmail.com, pgsql-bugs@lists.postgresql.org
Date: Mon, 19 Jan 2026 19:26:32 +0000
Message-ID: <19380-4e293be2b4007248@postgresql.org>
Auto-Submitted: auto-generated
Archived-At: <https://www.postgresql.org/message-id/19380-4e293be2b4007248%40postgresql.org>
Precedence: bulk

The following bug has been logged on the website:

Bug reference:      19380
Logged by:          Daniel Woelfel
Email address:      dwwoelfel@gmail.com
PostgreSQL version: 17.7
Operating system:   macOS (aarch64)
Description:       =20

In a CTE that inserts rows with both MERGE and INSERT, the transition table
will not contain the rows from the MERGE.

I have included a small reproduction script, which inserts 2 rows with a
merge and one row with an insert. On my machine, the trigger outputs: `Row
count: 1, Rows: [{"id":3,"val":"c"}]`, but I would expect it to output: `Row
count: 3, Rows: [{"id": 1, "val": "a"}, {"id": 2, "val": "b"},
{"id":3,"val":"c"}]`

```
CREATE TEMP TABLE merge_bug_test (id INT PRIMARY KEY, val TEXT);

-- Create trigger functions that list the IDs they see
CREATE OR REPLACE FUNCTION report_insert_rows()
RETURNS TRIGGER AS $$
BEGIN
    RAISE WARNING '[AFTER INSERT TRIGGER] Row count: %, Rows: %', (SELECT
COUNT(*) FROM newrows), (SELECT
COALESCE(json_agg(row_to_json(newrows))::text, 'EMPTY') FROM newrows);
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- Create statement-level triggers for INSERT
CREATE TRIGGER insert_trigger
    AFTER INSERT ON merge_bug_test
    REFERENCING NEW TABLE AS newrows
    FOR EACH STATEMENT
    EXECUTE FUNCTION report_insert_rows();

-- MERGE inserts rows, but INSERT CTE inserts nothing
WITH
  input_triples (id, val) AS (
    VALUES (1, 'a'), (2, 'b')
  ),
  insert_cte AS (
    INSERT INTO merge_bug_test (id, val) values (3, 'c')
    RETURNING id
  ),
  -- Insert two row with merge
  merge_cte AS (
    MERGE INTO merge_bug_test t
    USING input_triples s
    ON t.id =3D s.id
    WHEN NOT MATCHED THEN
        INSERT (id, val) VALUES (s.id, s.val)
    RETURNING t.id
  )
  -- Insert one row with a regular insert

SELECT id FROM merge_cte
UNION ALL
SELECT id FROM insert_cte;

DROP TABLE merge_bug_test;
DROP FUNCTION report_insert_rows;
```