From 01295e3add4411b8ffe02734e8370839be6f0a31 Mon Sep 17 00:00:00 2001 From: Viktor Holmberg Date: Tue, 25 Nov 2025 14:06:20 +0100 Subject: [PATCH v17 2/3] DO SELECT - Jians last doc + comment + trigger changes (no injection point testing) --- doc/src/sgml/ref/create_policy.sgml | 2 +- src/backend/executor/nodeModifyTable.c | 4 +--- src/test/regress/expected/rowsecurity.out | 2 +- src/test/regress/expected/triggers.out | 13 +++++++++++++ src/test/regress/sql/triggers.sql | 2 ++ 5 files changed, 18 insertions(+), 5 deletions(-) diff --git a/doc/src/sgml/ref/create_policy.sgml b/doc/src/sgml/ref/create_policy.sgml index e798eacfb42..c1510e212c0 100644 --- a/doc/src/sgml/ref/create_policy.sgml +++ b/doc/src/sgml/ref/create_policy.sgml @@ -584,7 +584,7 @@ CREATE POLICY name ON ON CONFLICT DO SELECT FOR UPDATE/SHARE Check existing row — - Existing row + Check existing row — — diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c index 9d3cd430084..cbecc1edb01 100644 --- a/src/backend/executor/nodeModifyTable.c +++ b/src/backend/executor/nodeModifyTable.c @@ -3099,9 +3099,7 @@ ExecOnConflictSelect(ModifyTableContext *context, mtstate->ps.state); } - /* Parse analysis should already have disallowed this, as RETURNING - * is required for DO SELECT. - */ + /* RETURNING is required for DO SELECT */ Assert(resultRelInfo->ri_projectReturning); *rslot = ExecProcessReturning(context, resultRelInfo, CMD_INSERT, diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out index d6a2be1f96e..e45031f7391 100644 --- a/src/test/regress/expected/rowsecurity.out +++ b/src/test/regress/expected/rowsecurity.out @@ -218,7 +218,7 @@ NOTICE: SELECT USING on rls_test_tgt.(3,"tgt d","TGT D") (1 row) ROLLBACK; --- ON CONFLICT DO SELECT should be similar to DO UPDATE, except there +-- ON CONFLICT DO SELECT should be similar to DO UPDATE, except there -- is not need to check the UPDATE policy in that case. BEGIN; INSERT INTO rls_test_tgt VALUES (4, 'tgt a') ON CONFLICT (a) DO SELECT RETURNING *; diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.out index 1eb8fba0953..98e56ecaef8 100644 --- a/src/test/regress/expected/triggers.out +++ b/src/test/regress/expected/triggers.out @@ -1745,6 +1745,19 @@ insert into upsert values(8, 'yellow') on conflict (key) do update set color = ' WARNING: before insert (new): (8,yellow) WARNING: before insert (new, modified): (9,"yellow trig modified") WARNING: after insert (new): (9,"yellow trig modified") +insert into upsert values(3, 'orange') on conflict (key) do select for update returning old.*, new.*, upsert.*; +WARNING: before insert (new): (3,orange) + key | color | key | color | key | color +-----+---------------------------+-----+---------------------------+-----+--------------------------- + 3 | updated red trig modified | 3 | updated red trig modified | 3 | updated red trig modified +(1 row) + +insert into upsert values(3, 'orange') on conflict (key) do select for update where upsert.key = 4 returning old.*, new.*, upsert.*; +WARNING: before insert (new): (3,orange) + key | color | key | color | key | color +-----+-------+-----+-------+-----+------- +(0 rows) + select * from upsert; key | color -----+----------------------------- diff --git a/src/test/regress/sql/triggers.sql b/src/test/regress/sql/triggers.sql index 5f7f75d7ba5..ee451ec7ed3 100644 --- a/src/test/regress/sql/triggers.sql +++ b/src/test/regress/sql/triggers.sql @@ -1197,6 +1197,8 @@ insert into upsert values(5, 'purple') on conflict (key) do update set color = ' insert into upsert values(6, 'white') on conflict (key) do update set color = 'updated ' || upsert.color; insert into upsert values(7, 'pink') on conflict (key) do update set color = 'updated ' || upsert.color; insert into upsert values(8, 'yellow') on conflict (key) do update set color = 'updated ' || upsert.color; +insert into upsert values(3, 'orange') on conflict (key) do select for update returning old.*, new.*, upsert.*; +insert into upsert values(3, 'orange') on conflict (key) do select for update where upsert.key = 4 returning old.*, new.*, upsert.*; select * from upsert; -- 2.48.1