This is an automated email from the ASF dual-hosted git repository.
chenjinbao1989 pushed a commit to branch cbdb-postgres-merge
in repository https://gitbox.apache.org/repos/asf/cloudberry.git
The following commit(s) were added to refs/heads/cbdb-postgres-merge by this
push:
new 4ac5445c650 fix_with
4ac5445c650 is described below
commit 4ac5445c650c93f0f9bae220e0d6cf6cda9a7b74
Author: liushengsong <[email protected]>
AuthorDate: Wed Jan 14 17:55:45 2026 +0800
fix_with
---
src/backend/parser/parse_cte.c | 63 +++++++++
src/test/regress/expected/with.out | 274 +++++++++++++++++--------------------
src/test/regress/sql/with.sql | 115 ++++++++--------
3 files changed, 244 insertions(+), 208 deletions(-)
diff --git a/src/backend/parser/parse_cte.c b/src/backend/parser/parse_cte.c
index 8559088df23..a052f260bca 100644
--- a/src/backend/parser/parse_cte.c
+++ b/src/backend/parser/parse_cte.c
@@ -281,6 +281,69 @@ analyzeCTE(ParseState *pstate, CommonTableExpr *cte)
/* Analysis not done already */
Assert(!IsA(cte->ctequery, Query));
+ /*
+ * Before analyzing the CTE's query, we'd better identify the data type
of
+ * the cycle mark column if any, since the query could refer to that.
+ * Other validity checks on the cycle clause will be done afterwards.
+ */
+ if (cycle_clause)
+ {
+ TypeCacheEntry *typentry;
+ Oid op;
+
+ cycle_clause->cycle_mark_value =
+ transformExpr(pstate, cycle_clause->cycle_mark_value,
+ EXPR_KIND_CYCLE_MARK);
+ cycle_clause->cycle_mark_default =
+ transformExpr(pstate, cycle_clause->cycle_mark_default,
+ EXPR_KIND_CYCLE_MARK);
+
+ cycle_clause->cycle_mark_type =
+ select_common_type(pstate,
+
list_make2(cycle_clause->cycle_mark_value,
+
cycle_clause->cycle_mark_default),
+ "CYCLE", NULL);
+ cycle_clause->cycle_mark_value =
+ coerce_to_common_type(pstate,
+
cycle_clause->cycle_mark_value,
+
cycle_clause->cycle_mark_type,
+
"CYCLE/SET/TO");
+ cycle_clause->cycle_mark_default =
+ coerce_to_common_type(pstate,
+
cycle_clause->cycle_mark_default,
+
cycle_clause->cycle_mark_type,
+
"CYCLE/SET/DEFAULT");
+
+ cycle_clause->cycle_mark_typmod =
+ select_common_typmod(pstate,
+
list_make2(cycle_clause->cycle_mark_value,
+
cycle_clause->cycle_mark_default),
+
cycle_clause->cycle_mark_type);
+
+ cycle_clause->cycle_mark_collation =
+ select_common_collation(pstate,
+
list_make2(cycle_clause->cycle_mark_value,
+
cycle_clause->cycle_mark_default),
+ true);
+
+ /* Might as well look up the relevant <> operator while we are
at it */
+ typentry = lookup_type_cache(cycle_clause->cycle_mark_type,
+
TYPECACHE_EQ_OPR);
+ if (!OidIsValid(typentry->eq_opr))
+ ereport(ERROR,
+ errcode(ERRCODE_UNDEFINED_FUNCTION),
+ errmsg("could not identify an equality
operator for type %s",
+
format_type_be(cycle_clause->cycle_mark_type)));
+ op = get_negator(typentry->eq_opr);
+ if (!OidIsValid(op))
+ ereport(ERROR,
+ errcode(ERRCODE_UNDEFINED_FUNCTION),
+ errmsg("could not identify an
inequality operator for type %s",
+
format_type_be(cycle_clause->cycle_mark_type)));
+
+ cycle_clause->cycle_mark_neop = op;
+ }
+
query = parse_sub_analyze(cte->ctequery, pstate, cte, NULL, true);
cte->ctequery = (Node *) query;
diff --git a/src/test/regress/expected/with.out
b/src/test/regress/expected/with.out
index 82e567168b1..64820370511 100644
--- a/src/test/regress/expected/with.out
+++ b/src/test/regress/expected/with.out
@@ -694,7 +694,6 @@ select * from search_graph order by seq;
Output: g_1.f, g_1.t, g_1.label
-> Seq Scan on public.graph0 g_1
Output: g_1.f, g_1.t, g_1.label
- Optimizer: Postgres query optimizer
(22 rows)
with recursive search_graph(f, t, label) as (
@@ -1128,29 +1127,26 @@ with recursive search_graph(f, t, label) as (
where g.f = sg.t
) cycle f, t set is_cycle using path
select * from search_graph;
-
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- CTE Scan on search_graph
- Output: search_graph.f, search_graph.t, search_graph.label,
search_graph.is_cycle, search_graph.path
- CTE search_graph
- -> Recursive Union
- -> Seq Scan on pg_temp.graph g
- Output: g.f, g.t, g.label, false, ARRAY[ROW(g.f, g.t)]
- -> Merge Join
- Output: g_1.f, g_1.t, g_1.label, CASE WHEN (ROW(g_1.f, g_1.t)
= ANY (sg.path)) THEN true ELSE false END, array_cat(sg.path, ARRAY[ROW(g_1.f,
g_1.t)])
- Merge Cond: (g_1.f = sg.t)
- -> Sort
- Output: g_1.f, g_1.t, g_1.label
- Sort Key: g_1.f
- -> Seq Scan on pg_temp.graph g_1
- Output: g_1.f, g_1.t, g_1.label
- -> Sort
- Output: sg.path, sg.t
- Sort Key: sg.t
- -> WorkTable Scan on search_graph sg
- Output: sg.path, sg.t
- Filter: (NOT sg.is_cycle)
-(20 rows)
+
QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Output: g.f, g.t, g.label, (false), (ARRAY[ROW(g.f, g.t)])
+ -> Recursive Union
+ -> Seq Scan on pg_temp.graph g
+ Output: g.f, g.t, g.label, false, ARRAY[ROW(g.f, g.t)]
+ -> Hash Join
+ Output: g_1.f, g_1.t, g_1.label, CASE WHEN (ROW(g_1.f, g_1.t) =
ANY (sg.path)) THEN true ELSE false END, array_cat(sg.path, ARRAY[ROW(g_1.f,
g_1.t)])
+ Hash Cond: (sg.t = g_1.f)
+ -> WorkTable Scan on search_graph sg
+ Output: sg.f, sg.t, sg.label, sg.is_cycle, sg.path
+ Filter: (NOT sg.is_cycle)
+ -> Hash
+ Output: g_1.f, g_1.t, g_1.label
+ -> Broadcast Motion 3:3 (slice2; segments: 3)
+ Output: g_1.f, g_1.t, g_1.label
+ -> Seq Scan on pg_temp.graph g_1
+ Output: g_1.f, g_1.t, g_1.label
+(19 rows)
with recursive search_graph(f, t, label) as (
select * from graph g
@@ -1234,18 +1230,15 @@ with recursive test as (
from test
) cycle x set is_cycle using path
select * from test;
-
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- CTE Scan on test
- Output: test.x, test.is_cycle, test.path
- CTE test
- -> Recursive Union
- -> Result
- Output: 0, false, '{(0)}'::record[]
- -> WorkTable Scan on test test_1
- Output: ((test_1.x + 1) % 10), CASE WHEN (ROW(((test_1.x + 1)
% 10)) = ANY (test_1.path)) THEN true ELSE false END, array_cat(test_1.path,
ARRAY[ROW(((test_1.x + 1) % 10))])
- Filter: (NOT test_1.is_cycle)
-(9 rows)
+
QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Recursive Union
+ -> Result
+ Output: 0, false, ARRAY[ROW(0)]
+ -> WorkTable Scan on test
+ Output: ((test.x + 1) % 10), CASE WHEN (ROW(((test.x + 1) % 10)) =
ANY (test.path)) THEN true ELSE false END, array_cat(test.path,
ARRAY[ROW(((test.x + 1) % 10))])
+ Filter: (NOT test.is_cycle)
+(8 rows)
with recursive test as (
select 0 as x
@@ -2016,7 +2009,6 @@ WITH RECURSIVE x(n) AS (SELECT n FROM x UNION ALL SELECT
1)
ERROR: recursive reference to query "x" must not appear within its
non-recursive term
LINE 1: WITH RECURSIVE x(n) AS (SELECT n FROM x UNION ALL SELECT 1)
^
-<<<<<<< HEAD
-- recursive term with a self-reference within a subquery is not allowed
WITH RECURSIVE cte(level, id) as (
SELECT 1, 2
@@ -2050,8 +2042,6 @@ WITH RECURSIVE x(n) AS (
ERROR: window functions in the target list of a recursive query is not
supported
LINE 4: SELECT level+1, row_number() over() FROM x, bar)
^
-CREATE TEMPORARY TABLE y (a INTEGER) DISTRIBUTED RANDOMLY;
-=======
-- allow this, because we historically have
WITH RECURSIVE x(n) AS (
WITH x1 AS (SELECT 1 AS n)
@@ -2100,8 +2090,7 @@ WITH RECURSIVE x(n) AS (
ERROR: recursive query "x" must not contain data-modifying statements
LINE 1: WITH RECURSIVE x(n) AS (
^
-CREATE TEMPORARY TABLE y (a INTEGER);
->>>>>>> REL_16_9
+CREATE TEMPORARY TABLE y (a INTEGER) DISTRIBUTED RANDOMLY;
INSERT INTO y SELECT generate_series(1, 10);
-- LEFT JOIN
WITH RECURSIVE x(n) AS (SELECT a FROM y WHERE a = 1
@@ -2810,14 +2799,6 @@ INSERT INTO bug6051 SELECT * FROM t1;
ERROR: writable CTE queries cannot be themselves writable
DETAIL: Apache Cloudberry currently only support CTEs with one writable
clause, called in a non-writable context.
HINT: Rewrite the query to only include one writable clause.
-SELECT * FROM bug6051;
- i
----
- 1
- 2
- 3
-(3 rows)
-
CREATE TEMP TABLE bug6051_2 (i int);
CREATE RULE bug6051_ins AS ON INSERT TO bug6051 DO INSTEAD
INSERT INTO bug6051_2
@@ -2835,36 +2816,22 @@ SELECT * FROM bug6051;
3
(3 rows)
-<<<<<<< HEAD
-SELECT * FROM bug6051_2;
- i
----
-(0 rows)
-
--- check INSERT...SELECT rule actions are disallowed on commands
-=======
-- check INSERT ... SELECT rule actions are disallowed on commands
->>>>>>> REL_16_9
-- that have modifyingCTEs
CREATE OR REPLACE RULE bug6051_ins AS ON INSERT TO bug6051 DO INSTEAD
INSERT INTO bug6051_2
SELECT NEW.i;
WITH t1 AS ( DELETE FROM bug6051 RETURNING * )
INSERT INTO bug6051 SELECT * FROM t1;
-<<<<<<< HEAD
ERROR: writable CTE queries cannot be themselves writable
DETAIL: Apache Cloudberry currently only support CTEs with one writable
clause, called in a non-writable context.
HINT: Rewrite the query to only include one writable clause.
-=======
-ERROR: INSERT ... SELECT rule actions are not supported for queries having
data-modifying statements in WITH
->>>>>>> REL_16_9
-- silly example to verify that hasModifyingCTE flag is propagated
CREATE TEMP TABLE bug6051_3 AS
SELECT a FROM generate_series(11,13) AS a;
CREATE RULE bug6051_3_ins AS ON INSERT TO bug6051_3 DO INSTEAD
SELECT i FROM bug6051_2;
-<<<<<<< HEAD
-BEGIN; SET LOCAL force_parallel_mode = on;
+BEGIN; SET LOCAL debug_parallel_query = on;
WITH t1 AS ( DELETE FROM bug6051_3 RETURNING * )
INSERT INTO bug6051_3 SELECT * FROM t1;
ERROR: writable CTE queries cannot be themselves writable
@@ -2878,29 +2845,6 @@ SELECT * FROM bug6051_3;
13
12
(3 rows)
-=======
-BEGIN; SET LOCAL debug_parallel_query = on;
-WITH t1 AS ( DELETE FROM bug6051_3 RETURNING * )
- INSERT INTO bug6051_3 SELECT * FROM t1;
- i
----
- 1
- 2
- 3
- 1
- 2
- 3
- 1
- 2
- 3
-(9 rows)
-
-COMMIT;
-SELECT * FROM bug6051_3;
- a
----
-(0 rows)
->>>>>>> REL_16_9
-- check case where CTE reference is removed due to optimization
EXPLAIN (VERBOSE, COSTS OFF)
@@ -3187,30 +3131,38 @@ WITH cte_basic AS MATERIALIZED (SELECT 1 a, 'cte_basic
val' b)
MERGE INTO m USING (select 0 k, 'merge source SubPlan' v offset 0) o ON m.k=o.k
WHEN MATCHED THEN UPDATE SET v = (SELECT b || ' merge update' FROM cte_basic
WHERE cte_basic.a = m.k LIMIT 1)
WHEN NOT MATCHED THEN INSERT VALUES(o.k, o.v);
- QUERY PLAN
--------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------
Merge on public.m
- CTE cte_basic
- -> Result
- Output: 1, 'cte_basic val'::text
- -> Hash Right Join
- Output: m.ctid, o.k, o.v, o.*
- Hash Cond: (m.k = o.k)
- -> Seq Scan on public.m
- Output: m.ctid, m.k
- -> Hash
- Output: o.k, o.v, o.*
- -> Subquery Scan on o
- Output: o.k, o.v, o.*
- -> Result
- Output: 0, 'merge source SubPlan'::text
- SubPlan 2
+ -> Explicit Redistribute Motion 3:3 (slice1; segments: 3)
+ Output: m.ctid, m.gp_segment_id, o.k, o.v, o.*
+ -> Split Merge
+ Output: m.ctid, m.gp_segment_id, o.k, o.v, o.*
+ -> Hash Right Join
+ Output: m.ctid, m.gp_segment_id, o.k, o.v, o.*
+ Hash Cond: (m.k = o.k)
+ -> Seq Scan on public.m
+ Output: m.ctid, m.gp_segment_id, m.k
+ -> Hash
+ Output: o.k, o.v, o.*
+ -> Redistribute Motion 1:3 (slice2; segments: 1)
+ Output: o.k, o.v, o.*
+ Hash Key: o.k
+ -> Subquery Scan on o
+ Output: o.k, o.v, o.*
+ -> Result
+ Output: 0, 'merge source
SubPlan'::text
+ SubPlan 1
-> Limit
- Output: ((cte_basic.b || ' merge update'::text))
- -> CTE Scan on cte_basic
- Output: (cte_basic.b || ' merge update'::text)
- Filter: (cte_basic.a = m.k)
-(21 rows)
+ Output: ((share0_ref1.b || ' merge update'::text))
+ -> Result
+ Output: (share0_ref1.b || ' merge update'::text)
+ Filter: (share0_ref1.a = m.k)
+ -> Shared Scan (share slice:id 0:0)
+ Output: share0_ref1.a, share0_ref1.b
+ -> Result
+ Output: 1, 'cte_basic val'::text
+(31 rows)
-- InitPlan
WITH cte_init AS MATERIALIZED (SELECT 1 a, 'cte_init val' b)
@@ -3230,30 +3182,38 @@ WITH cte_init AS MATERIALIZED (SELECT 1 a, 'cte_init
val' b)
MERGE INTO m USING (select 1 k, 'merge source InitPlan' v offset 0) o ON
m.k=o.k
WHEN MATCHED THEN UPDATE SET v = (SELECT b || ' merge update' FROM cte_init
WHERE a = 1 LIMIT 1)
WHEN NOT MATCHED THEN INSERT VALUES(o.k, o.v);
- QUERY PLAN
---------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------------
Merge on public.m
- CTE cte_init
- -> Result
- Output: 1, 'cte_init val'::text
- InitPlan 2 (returns $1)
+ InitPlan 1 (returns $0) (slice3)
-> Limit
Output: ((cte_init.b || ' merge update'::text))
- -> CTE Scan on cte_init
+ -> Subquery Scan on cte_init
Output: (cte_init.b || ' merge update'::text)
Filter: (cte_init.a = 1)
- -> Hash Right Join
- Output: m.ctid, o.k, o.v, o.*
- Hash Cond: (m.k = o.k)
- -> Seq Scan on public.m
- Output: m.ctid, m.k
- -> Hash
- Output: o.k, o.v, o.*
- -> Subquery Scan on o
- Output: o.k, o.v, o.*
- -> Result
- Output: 1, 'merge source InitPlan'::text
-(21 rows)
+ -> Shared Scan (share slice:id 3:0)
+ Output: share0_ref1.a, share0_ref1.b
+ -> Result
+ Output: 1, 'cte_init val'::text
+ -> Explicit Redistribute Motion 3:3 (slice1; segments: 3)
+ Output: m.ctid, m.gp_segment_id, o.k, o.v, o.*
+ -> Split Merge
+ Output: m.ctid, m.gp_segment_id, o.k, o.v, o.*
+ -> Hash Right Join
+ Output: m.ctid, m.gp_segment_id, o.k, o.v, o.*
+ Hash Cond: (m.k = o.k)
+ -> Seq Scan on public.m
+ Output: m.ctid, m.gp_segment_id, m.k
+ -> Hash
+ Output: o.k, o.v, o.*
+ -> Redistribute Motion 1:3 (slice2; segments: 1)
+ Output: o.k, o.v, o.*
+ Hash Key: o.k
+ -> Subquery Scan on o
+ Output: o.k, o.v, o.*
+ -> Result
+ Output: 1, 'merge source
InitPlan'::text
+(31 rows)
-- MERGE source comes from CTE:
WITH merge_source_cte AS MATERIALIZED (SELECT 15 a, 'merge_source_cte val' b)
@@ -3273,29 +3233,41 @@ WITH merge_source_cte AS MATERIALIZED (SELECT 15 a,
'merge_source_cte val' b)
MERGE INTO m USING (select * from merge_source_cte) o ON m.k=o.a
WHEN MATCHED THEN UPDATE SET v = (SELECT b || merge_source_cte.*::text || '
merge update' FROM merge_source_cte WHERE a = 15)
WHEN NOT MATCHED THEN INSERT VALUES(o.a, o.b || (SELECT
merge_source_cte.*::text || ' merge insert' FROM merge_source_cte));
- QUERY PLAN
------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------
Merge on public.m
- CTE merge_source_cte
- -> Result
- Output: 15, 'merge_source_cte val'::text
- InitPlan 2 (returns $1)
- -> CTE Scan on merge_source_cte merge_source_cte_1
- Output: ((merge_source_cte_1.b || (merge_source_cte_1.*)::text) ||
' merge update'::text)
- Filter: (merge_source_cte_1.a = 15)
- InitPlan 3 (returns $2)
- -> CTE Scan on merge_source_cte merge_source_cte_2
- Output: ((merge_source_cte_2.*)::text || ' merge insert'::text)
- -> Hash Right Join
- Output: m.ctid, merge_source_cte.a, merge_source_cte.b,
merge_source_cte.*
- Hash Cond: (m.k = merge_source_cte.a)
- -> Seq Scan on public.m
- Output: m.ctid, m.k
- -> Hash
- Output: merge_source_cte.a, merge_source_cte.b,
merge_source_cte.*
- -> CTE Scan on merge_source_cte
- Output: merge_source_cte.a, merge_source_cte.b,
merge_source_cte.*
-(20 rows)
+ InitPlan 1 (returns $0) (slice3)
+ -> Subquery Scan on merge_source_cte
+ Output: ((merge_source_cte.b || (merge_source_cte.*)::text) || '
merge update'::text)
+ Filter: (merge_source_cte.a = 15)
+ -> Shared Scan (share slice:id 3:0)
+ Output: share0_ref1.a, share0_ref1.b
+ -> Result
+ Output: 15, 'merge_source_cte val'::text
+ InitPlan 2 (returns $1) (slice4)
+ -> Subquery Scan on merge_source_cte_1
+ Output: ((merge_source_cte_1.*)::text || ' merge insert'::text)
+ -> Shared Scan (share slice:id 4:0)
+ Output: share0_ref2.a, share0_ref2.b
+ -> Explicit Redistribute Motion 3:3 (slice1; segments: 3)
+ Output: m.ctid, m.gp_segment_id, o.a, o.b, o.*
+ -> Split Merge
+ Output: m.ctid, m.gp_segment_id, o.a, o.b, o.*
+ -> Hash Right Join
+ Output: m.ctid, m.gp_segment_id, o.a, o.b, o.*
+ Hash Cond: (m.k = o.a)
+ -> Seq Scan on public.m
+ Output: m.ctid, m.gp_segment_id, m.k
+ -> Hash
+ Output: o.a, o.b, o.*
+ -> Redistribute Motion 1:3 (slice2)
+ Output: o.a, o.b, o.*
+ Hash Key: o.a
+ -> Subquery Scan on o
+ Output: o.a, o.b, o.*
+ -> Shared Scan (share slice:id 2:0)
+ Output: share0_ref3.a,
share0_ref3.b
+(34 rows)
DROP TABLE m;
-- check that run to completion happens in proper ordering
@@ -3554,7 +3526,7 @@ SELECT * FROM parent;
-- check EXPLAIN VERBOSE for a wCTE with RETURNING
EXPLAIN (VERBOSE, COSTS OFF)
WITH wcte AS ( INSERT INTO int8_tbl VALUES ( 42, 47 ) RETURNING q2 )
-DELETE FROM a USING wcte WHERE aa = q2;
+DELETE FROM a_star USING wcte WHERE aa = q2;
ERROR: writable CTE queries cannot be themselves writable
DETAIL: Apache Cloudberry currently only support CTEs with one writable
clause, called in a non-writable context.
HINT: Rewrite the query to only include one writable clause.
diff --git a/src/test/regress/sql/with.sql b/src/test/regress/sql/with.sql
index d3f939867a0..e58ade9f747 100644
--- a/src/test/regress/sql/with.sql
+++ b/src/test/regress/sql/with.sql
@@ -954,8 +954,6 @@ WITH RECURSIVE x(n) AS (
SELECT level+1, row_number() over() FROM x, bar)
SELECT * FROM x LIMIT 10;
-CREATE TEMPORARY TABLE y (a INTEGER) DISTRIBUTED RANDOMLY;
-
-- allow this, because we historically have
WITH RECURSIVE x(n) AS (
WITH x1 AS (SELECT 1 AS n)
@@ -991,6 +989,8 @@ WITH RECURSIVE x(n) AS (
DELETE FROM graph RETURNING f)
SELECT * FROM x;
+CREATE TEMPORARY TABLE y (a INTEGER) DISTRIBUTED RANDOMLY;
+
INSERT INTO y SELECT generate_series(1, 10);
-- LEFT JOIN
@@ -1332,8 +1332,6 @@ SELECT * FROM bug6051;
WITH t1 AS ( DELETE FROM bug6051 RETURNING * )
INSERT INTO bug6051 SELECT * FROM t1;
-SELECT * FROM bug6051;
-
CREATE TEMP TABLE bug6051_2 (i int);
CREATE RULE bug6051_ins AS ON INSERT TO bug6051 DO INSTEAD
@@ -1344,7 +1342,6 @@ WITH t1 AS ( DELETE FROM bug6051 RETURNING * )
INSERT INTO bug6051 SELECT * FROM t1;
SELECT * FROM bug6051;
-SELECT * FROM bug6051_2;
-- check INSERT ... SELECT rule actions are disallowed on commands
-- that have modifyingCTEs
@@ -1492,58 +1489,62 @@ UPDATE SET (k, v) = (SELECT k, v FROM upsert_cte WHERE
upsert_cte.k = withz.k)
RETURNING k, v;
DROP TABLE withz;
--- MERGE16_FIXME: MERGE with CTE has some errors, Disable it first
---
--- -- WITH referenced by MERGE statement
--- CREATE TABLE m AS SELECT i AS k, (i || ' v')::text v FROM
generate_series(1, 16, 3) i;
--- ALTER TABLE m ADD UNIQUE (k);
---
--- WITH RECURSIVE cte_basic AS (SELECT 1 a, 'cte_basic val' b)
--- MERGE INTO m USING (select 0 k, 'merge source SubPlan' v) o ON m.k=o.k
--- WHEN MATCHED THEN UPDATE SET v = (SELECT b || ' merge update' FROM
cte_basic WHERE cte_basic.a = m.k LIMIT 1)
--- WHEN NOT MATCHED THEN INSERT VALUES(o.k, o.v);
---
--- -- Basic:
--- WITH cte_basic AS MATERIALIZED (SELECT 1 a, 'cte_basic val' b)
--- MERGE INTO m USING (select 0 k, 'merge source SubPlan' v offset 0) o ON
m.k=o.k
--- WHEN MATCHED THEN UPDATE SET v = (SELECT b || ' merge update' FROM
cte_basic WHERE cte_basic.a = m.k LIMIT 1)
--- WHEN NOT MATCHED THEN INSERT VALUES(o.k, o.v);
--- -- Examine
--- SELECT * FROM m where k = 0;
---
--- -- See EXPLAIN output for same query:
--- EXPLAIN (VERBOSE, COSTS OFF)
--- WITH cte_basic AS MATERIALIZED (SELECT 1 a, 'cte_basic val' b)
--- MERGE INTO m USING (select 0 k, 'merge source SubPlan' v offset 0) o ON
m.k=o.k
--- WHEN MATCHED THEN UPDATE SET v = (SELECT b || ' merge update' FROM
cte_basic WHERE cte_basic.a = m.k LIMIT 1)
--- WHEN NOT MATCHED THEN INSERT VALUES(o.k, o.v);
---
--- -- Examine
--- SELECT * FROM m where k = 1;
---
--- -- See EXPLAIN output for same query:
--- EXPLAIN (VERBOSE, COSTS OFF)
--- WITH cte_init AS MATERIALIZED (SELECT 1 a, 'cte_init val' b)
--- MERGE INTO m USING (select 1 k, 'merge source InitPlan' v offset 0) o ON
m.k=o.k
--- WHEN MATCHED THEN UPDATE SET v = (SELECT b || ' merge update' FROM cte_init
WHERE a = 1 LIMIT 1)
--- WHEN NOT MATCHED THEN INSERT VALUES(o.k, o.v);
---
--- -- MERGE source comes from CTE:
--- WITH merge_source_cte AS MATERIALIZED (SELECT 15 a, 'merge_source_cte val'
b)
--- MERGE INTO m USING (select * from merge_source_cte) o ON m.k=o.a
--- WHEN MATCHED THEN UPDATE SET v = (SELECT b || merge_source_cte.*::text || '
merge update' FROM merge_source_cte WHERE a = 15)
--- WHEN NOT MATCHED THEN INSERT VALUES(o.a, o.b || (SELECT
merge_source_cte.*::text || ' merge insert' FROM merge_source_cte));
--- -- Examine
--- SELECT * FROM m where k = 15;
---
--- -- See EXPLAIN output for same query:
--- EXPLAIN (VERBOSE, COSTS OFF)
--- WITH merge_source_cte AS MATERIALIZED (SELECT 15 a, 'merge_source_cte val'
b)
--- MERGE INTO m USING (select * from merge_source_cte) o ON m.k=o.a
--- WHEN MATCHED THEN UPDATE SET v = (SELECT b || merge_source_cte.*::text || '
merge update' FROM merge_source_cte WHERE a = 15)
--- WHEN NOT MATCHED THEN INSERT VALUES(o.a, o.b || (SELECT
merge_source_cte.*::text || ' merge insert' FROM merge_source_cte));
---
--- DROP TABLE m;
+
+-- WITH referenced by MERGE statement
+CREATE TABLE m AS SELECT i AS k, (i || ' v')::text v FROM generate_series(1,
16, 3) i;
+ALTER TABLE m ADD UNIQUE (k);
+
+WITH RECURSIVE cte_basic AS (SELECT 1 a, 'cte_basic val' b)
+MERGE INTO m USING (select 0 k, 'merge source SubPlan' v) o ON m.k=o.k
+WHEN MATCHED THEN UPDATE SET v = (SELECT b || ' merge update' FROM cte_basic
WHERE cte_basic.a = m.k LIMIT 1)
+WHEN NOT MATCHED THEN INSERT VALUES(o.k, o.v);
+
+-- Basic:
+WITH cte_basic AS MATERIALIZED (SELECT 1 a, 'cte_basic val' b)
+MERGE INTO m USING (select 0 k, 'merge source SubPlan' v offset 0) o ON m.k=o.k
+WHEN MATCHED THEN UPDATE SET v = (SELECT b || ' merge update' FROM cte_basic
WHERE cte_basic.a = m.k LIMIT 1)
+WHEN NOT MATCHED THEN INSERT VALUES(o.k, o.v);
+-- Examine
+SELECT * FROM m where k = 0;
+
+-- See EXPLAIN output for same query:
+EXPLAIN (VERBOSE, COSTS OFF)
+WITH cte_basic AS MATERIALIZED (SELECT 1 a, 'cte_basic val' b)
+MERGE INTO m USING (select 0 k, 'merge source SubPlan' v offset 0) o ON m.k=o.k
+WHEN MATCHED THEN UPDATE SET v = (SELECT b || ' merge update' FROM cte_basic
WHERE cte_basic.a = m.k LIMIT 1)
+WHEN NOT MATCHED THEN INSERT VALUES(o.k, o.v);
+
+-- InitPlan
+WITH cte_init AS MATERIALIZED (SELECT 1 a, 'cte_init val' b)
+MERGE INTO m USING (select 1 k, 'merge source InitPlan' v offset 0) o ON
m.k=o.k
+WHEN MATCHED THEN UPDATE SET v = (SELECT b || ' merge update' FROM cte_init
WHERE a = 1 LIMIT 1)
+WHEN NOT MATCHED THEN INSERT VALUES(o.k, o.v);
+-- Examine
+SELECT * FROM m where k = 1;
+
+-- See EXPLAIN output for same query:
+EXPLAIN (VERBOSE, COSTS OFF)
+WITH cte_init AS MATERIALIZED (SELECT 1 a, 'cte_init val' b)
+MERGE INTO m USING (select 1 k, 'merge source InitPlan' v offset 0) o ON
m.k=o.k
+WHEN MATCHED THEN UPDATE SET v = (SELECT b || ' merge update' FROM cte_init
WHERE a = 1 LIMIT 1)
+WHEN NOT MATCHED THEN INSERT VALUES(o.k, o.v);
+
+-- MERGE source comes from CTE:
+WITH merge_source_cte AS MATERIALIZED (SELECT 15 a, 'merge_source_cte val' b)
+MERGE INTO m USING (select * from merge_source_cte) o ON m.k=o.a
+WHEN MATCHED THEN UPDATE SET v = (SELECT b || merge_source_cte.*::text || '
merge update' FROM merge_source_cte WHERE a = 15)
+WHEN NOT MATCHED THEN INSERT VALUES(o.a, o.b || (SELECT
merge_source_cte.*::text || ' merge insert' FROM merge_source_cte));
+-- Examine
+SELECT * FROM m where k = 15;
+
+-- See EXPLAIN output for same query:
+EXPLAIN (VERBOSE, COSTS OFF)
+WITH merge_source_cte AS MATERIALIZED (SELECT 15 a, 'merge_source_cte val' b)
+MERGE INTO m USING (select * from merge_source_cte) o ON m.k=o.a
+WHEN MATCHED THEN UPDATE SET v = (SELECT b || merge_source_cte.*::text || '
merge update' FROM merge_source_cte WHERE a = 15)
+WHEN NOT MATCHED THEN INSERT VALUES(o.a, o.b || (SELECT
merge_source_cte.*::text || ' merge insert' FROM merge_source_cte));
+
+DROP TABLE m;
-- check that run to completion happens in proper ordering
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
