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