public inbox for [email protected]  

help / color / mirror / Atom feed

From: PG Bug reporting form <[email protected]>
To: [email protected]
Cc: [email protected]
Subject: BUG #19380: Transition table in AFTER INSERT trigger misses rows from MERGE when used with INSERT in a CTE
Date: Mon, 19 Jan 2026 19:26:32 +0000
Message-ID: <[email protected]> (raw)

The following bug has been logged on the website:

Bug reference:      19380
Logged by:          Daniel Woelfel
Email address:      [email protected]
PostgreSQL version: 17.7
Operating system:   macOS (aarch64)
Description:        

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 = 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;
```

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: BUG #19380: Transition table in AFTER INSERT trigger misses rows from MERGE when used with INSERT in a CTE
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox