You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@cloudberry.apache.org by dj...@apache.org on 2026/10/16 07:22:04 UTC
(cloudberry) 01/02: Let ORCA plan an in-place UPDATE of append-optimized tables
This is an automated email from the ASF dual-hosted git repository.
tuhaihe pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/cloudberry.git
commit bb9f21f12babf3260e9507b110040ace6ab51d6a
Author: liushengsong <ls...@gmail.com>
AuthorDate: Tue Sep 22 17:28:01 2026 +0800
Let ORCA plan an in-place UPDATE of append-optimized tables
Any UPDATE on an AO, AOCS or PAX table that does not modify a
distribution key column made GPORCA give up and silently fall back to
the Postgres planner:
INFO: GPORCA failed to produce a plan, falling back to Postgres-based
planner
DETAIL: DXL-to-PlStmt Translation: Attribute number 7 not found in
project list
With optimizer_trace_fallback off, which is the default, none of this
reaches the client; only EXPLAIN's "Optimizer:" line gives it away.
Since PG14 a non-split UPDATE builds the new tuple from the plan slot
plus the old tuple, and ExecModifyTable fetches that old tuple by tid.
Append-optimized tables cannot do that lookup -- it would mean scanning
the block directory and the visibility map per tuple, and the block
directory is not guaranteed to exist -- so appendonly_tuple_fetch_row_
version() errors out with "feature not supported on appendoptimized
relations".
The planner works around this by adding a wholerow junk column for such
tables (see the GPDB_14_MERGE_FIXME in add_row_identity_columns), which
makes oldtuple non-NULL and skips the fetch. ORCA has no such column,
so CTranslatorDXLToPlStmt::TranslateDXLDml() forced a split update
instead. But CExpressionPreprocessor::ConvertSplitUpdateToInPlaceUpdate()
had already downgraded the plan to an in-place update -- it only looks at
whether a distribution or partition key column is modified, never at the
access method -- so the DMLAction column the forced split needs was not
in the plan, and the translation failed.
The old tuple is only needed for the columns the subplan does not
assign. ORCA's non-split UPDATE assigns every live column, so nothing
reads it. Record that in ri_projectNewNeedsOldTuple while building the
update projection and skip the fetch when it is false, which lets an
append-optimized table run the plan, and drop the forced split.
This is what the FIXME asks for: the update plan now has the same shape
whether it comes from the planner or from ORCA, and it matches what the
storage engine already does, since appendonly_tuple_update() is itself a
delete followed by an append. Iceberg needs no special case either --
its UPDATE writes the position delete and the new row in one
ExecForeignUpdate call.
The skip is limited to append-optimized relations for now. Letting heap
take it as well would remove the wholerow junk column the planner ships
for those tables, but needs the trigger, EPQ and cross-partition update
paths audited first.
Forcing the split the other way round was tried first and rejected: it
made nine queries that update the same row twice in one statement fail
with "multiple updates to a row by the same query is not allowed", which
only a split update raises.
The answer files change where a recorded fallback disappears, where an
UPDATE plan gains its ORCA shape, and in ao_locks and
uao/parallel_update_{row,column}, which go back to locking all segments
because ORCA does not do direct dispatch for these updates.
See: Issue#603
(cherry picked from commit befb03fea31fd7d4f75a0ab4cdd624f83705777a)
---
.../modify_table_data_corrupt_optimizer.out | 75 +++++++-------
.../regress/expected/DML_over_joins_optimizer.out | 113 +++------------------
.../regress/expected/direct_dispatch_optimizer.out | 72 +++++++------
.../src/test/regress/expected/gporca_optimizer.out | 39 +++----
.../test/regress/expected/hash_index_optimizer.out | 8 --
.../regress/expected/partition_prune_optimizer.out | 14 +--
.../expected/qp_correlated_query_optimizer.out | 16 ---
.../expected/qp_orca_fallback_optimizer.out | 2 -
.../regress/expected/qp_subquery_optimizer.out | 2 -
.../expected/qp_targeted_dispatch_optimizer.out | 8 +-
.../expected/qp_union_intersect_optimizer.out | 3 +-
.../test/regress/expected/update_gp_optimizer.out | 64 +++++++-----
src/backend/executor/nodeModifyTable.c | 33 ++++++
.../gpopt/translate/CTranslatorDXLToPlStmt.cpp | 5 -
src/include/nodes/execnodes.h | 8 ++
.../output/uao/parallel_update_optimizer.source | 6 +-
src/test/regress/expected/ao_locks_optimizer.out | 6 +-
src/test/regress/expected/orca_split_update.out | 104 +++++++++++++++++++
.../expected/orca_split_update_optimizer.out | 108 ++++++++++++++++++++
src/test/regress/greenplum_schedule | 2 +-
src/test/regress/sql/orca_split_update.sql | 47 +++++++++
21 files changed, 467 insertions(+), 268 deletions(-)
diff --git a/contrib/pax_storage/src/test/isolation2/expected/modify_table_data_corrupt_optimizer.out b/contrib/pax_storage/src/test/isolation2/expected/modify_table_data_corrupt_optimizer.out
index 576d61483a8..eef3815de4e 100644
--- a/contrib/pax_storage/src/test/isolation2/expected/modify_table_data_corrupt_optimizer.out
+++ b/contrib/pax_storage/src/test/isolation2/expected/modify_table_data_corrupt_optimizer.out
@@ -94,28 +94,28 @@ ABORT
-- For planner, this will error out
explain (costs off) update tab1 set a = 999 from tab2, tab3 where tab1.a = tab2.a and tab1.b = tab3.b;
- QUERY PLAN
-------------------------------------------------------------------------------------
- Update on tab1
- -> Hash Join
- Hash Cond: (tab3.b = tab1.b)
- -> Seq Scan on tab3
- -> Hash
- -> Redistribute Motion 3:3 (slice1; segments: 3)
- Hash Key: tab1.b
- -> Hash Join
- Hash Cond: (tab2.a = tab1.a)
- -> Seq Scan on tab2
- -> Hash
- -> Redistribute Motion 3:3 (slice2; segments: 3)
- Hash Key: tab1.a
- -> Seq Scan on tab1
- Optimizer: Postgres query optimizer
+ QUERY PLAN
+---------------------------------------------------------------------------
+ Update on tab1
+ -> Result
+ -> Redistribute Motion 3:3 (slice1; segments: 3)
+ Hash Key: tab1.b
+ -> Hash Join
+ Hash Cond: (tab2.a = tab1.a)
+ -> Seq Scan on tab2
+ -> Hash
+ -> Broadcast Motion 3:3 (slice2; segments: 3)
+ -> Hash Join
+ Hash Cond: (tab3.b = tab1.b)
+ -> Seq Scan on tab3
+ -> Hash
+ -> Seq Scan on tab1
+ Optimizer: GPORCA
(15 rows)
begin;
BEGIN
update tab1 set a = 999 from tab2, tab3 where tab1.a = tab2.a and tab1.b = tab3.b;
-ERROR: distribution key of the tuple (0, 1) doesn't belong to current segment (actually from seg0) (nodeModifyTable.c:1790) (seg1 127.0.0.1:7003 pid=31750) (nodeModifyTable.c:1790)
+UPDATE 1
abort;
ABORT
@@ -149,28 +149,29 @@ ABORT
-- For orca, this will error out
explain (costs off) update tab1 set a = 999 from tab2, tab3 where tab1.a = tab2.a and tab1.b = tab3.a;
- QUERY PLAN
-------------------------------------------------------------------------------------------
- Update on tab1
- -> Explicit Redistribute Motion 3:3 (slice1; segments: 3)
- -> Hash Join
- Hash Cond: (tab3.a = tab1.b)
- -> Seq Scan on tab3
- -> Hash
- -> Broadcast Motion 3:3 (slice2; segments: 3)
- -> Hash Join
- Hash Cond: (tab2.a = tab1.a)
- -> Seq Scan on tab2
- -> Hash
- -> Redistribute Motion 3:3 (slice3; segments: 3)
- Hash Key: tab1.a
- -> Seq Scan on tab1
- Optimizer: Postgres query optimizer
-(15 rows)
+ QUERY PLAN
+---------------------------------------------------------------------------------------------
+ Update on tab1
+ -> Result
+ -> Redistribute Motion 3:3 (slice1; segments: 3)
+ Hash Key: tab1.b
+ -> Hash Join
+ Hash Cond: (tab3.a = tab1.b)
+ -> Seq Scan on tab3
+ -> Hash
+ -> Broadcast Motion 3:3 (slice2; segments: 3)
+ -> Hash Join
+ Hash Cond: (tab2.a = tab1.a)
+ -> Seq Scan on tab2
+ -> Hash
+ -> Broadcast Motion 3:3 (slice3; segments: 3)
+ -> Seq Scan on tab1
+ Optimizer: GPORCA
+(16 rows)
begin;
BEGIN
update tab1 set a = 999 from tab2, tab3 where tab1.a = tab2.a and tab1.b = tab3.a;
-UPDATE 2
+ERROR: distribution key of the tuple (0, 1) doesn't belong to current segment (actually from seg0) (nodeModifyTable.c:1790) (seg1 127.0.0.1:7003 pid=31750) (nodeModifyTable.c:1790)
abort;
ABORT
diff --git a/contrib/pax_storage/src/test/regress/expected/DML_over_joins_optimizer.out b/contrib/pax_storage/src/test/regress/expected/DML_over_joins_optimizer.out
index b098d7c2df5..3a848d52520 100644
--- a/contrib/pax_storage/src/test/regress/expected/DML_over_joins_optimizer.out
+++ b/contrib/pax_storage/src/test/regress/expected/DML_over_joins_optimizer.out
@@ -24,11 +24,7 @@ insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
analyze r;
analyze s;
update r set b = r.b + 1 from s where r.a = s.a;
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 21 not found in project list
update r set b = r.b + 1 from s where r.a in (select a from s);
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 35 not found in project list
delete from r using s where r.a = s.a;
delete from r;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
@@ -45,11 +41,7 @@ delete from s;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
update r set b = r.b + 4 from s where r.b = s.b;
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 21 not found in project list
update r set b = b + 1 where b in (select b from s);
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 25 not found in project list
delete from s using r where r.a = s.b;
delete from r;
delete from s;
@@ -64,29 +56,22 @@ delete from s;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
explain update s set b = b + 1 where exists (select 1 from r where s.a = r.b);
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 28 not found in project list
- QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
- Update on s (cost=47.71..49.13 rows=0 width=0)
- -> Explicit Redistribute Motion 3:3 (slice1; segments: 3) (cost=47.71..49.13 rows=33 width=52)
- -> Result (cost=47.71..48.46 rows=33 width=52)
- -> HashAggregate (cost=47.71..48.05 rows=33 width=52)
- Group Key: (RowIdExpr)
- -> Redistribute Motion 3:3 (slice2; segments: 3) (cost=3.92..47.63 rows=33 width=52)
- Hash Key: (RowIdExpr)
- -> Hash Join (cost=3.92..46.96 rows=33 width=52)
- Hash Cond: (r.b = s.a)
- -> Seq Scan on r (cost=0.00..34.33 rows=3333 width=10)
- -> Hash (cost=2.67..2.67 rows=100 width=50)
- -> Broadcast Motion 3:3 (slice3; segments: 3) (cost=0.00..2.67 rows=100 width=50)
- -> Seq Scan on s (cost=0.00..1.33 rows=33 width=50)
- Optimizer: Postgres query optimizer
-(14 rows)
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------
+ Update on s (cost=0.00..865.12 rows=34 width=1)
+ -> Hash Semi Join (cost=0.00..862.77 rows=100 width=18)
+ Hash Cond: (s.a = r.b)
+ -> Seq Scan on s (cost=0.00..431.00 rows=100 width=18)
+ Filter: (NOT (a IS NULL))
+ -> Hash (cost=431.13..431.13 rows=10000 width=4)
+ -> Result (cost=0.00..431.13 rows=10000 width=4)
+ -> Redistribute Motion 3:3 (slice1; segments: 3) (cost=0.00..431.11 rows=10000 width=4)
+ Hash Key: r.b
+ -> Seq Scan on r (cost=0.00..431.05 rows=3334 width=4)
+ Optimizer: GPORCA
+(11 rows)
update s set b = b + 1 where exists (select 1 from r where s.a = r.b);
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 28 not found in project list
explain delete from s where exists (select 1 from r where s.a = r.b);
QUERY PLAN
---------------------------------------------------------------------------------------------------------------
@@ -121,11 +106,7 @@ create table s (a int8, b int8) distributed by (a);
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
update r set b = r.b + 1 from s where r.a = s.a;
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 21 not found in project list
update r set b = r.b + 1 from s where r.a in (select a from s);
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 35 not found in project list
delete from r using s where r.a = s.a;
delete from r;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
@@ -142,11 +123,7 @@ delete from s;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
update r set b = r.b + 4 from s where r.b = s.b;
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 21 not found in project list
update r set b = b + 1 where b in (select b from s);
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 25 not found in project list
delete from s using r where r.a = s.b;
delete from r;
delete from s;
@@ -161,8 +138,6 @@ delete from s;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
update s set b = b + 1 where exists (select 1 from r where s.a = r.b);
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 28 not found in project list
delete from s where exists (select 1 from r where s.a = r.b);
-- ----------------------------------------------------------------------
-- Test: heap_motion3.sql
@@ -182,11 +157,7 @@ create table s (a float4, b float4) distributed by (a);
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
update r set b = r.b + 1 from s where r.a = s.a;
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 21 not found in project list
update r set b = r.b + 1 from s where r.a in (select a from s);
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 35 not found in project list
delete from r using s where r.a = s.a;
delete from r;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
@@ -203,11 +174,7 @@ delete from s;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
update r set b = r.b + 4 from s where r.b = s.b;
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 21 not found in project list
update r set b = b + 1 where b in (select b from s);
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 25 not found in project list
delete from s using r where r.a = s.b;
delete from r;
delete from s;
@@ -222,8 +189,6 @@ delete from s;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
update s set b = b + 1 where exists (select 1 from r where s.a = r.b);
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 28 not found in project list
delete from s where exists (select 1 from r where s.a = r.b);
-- ----------------------------------------------------------------------
-- Test: heap_motion4.sql
@@ -243,11 +208,7 @@ create table s (a float(24), b float(24)) distributed by (a);
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
update r set b = r.b + 1 from s where r.a = s.a;
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 21 not found in project list
update r set b = r.b + 1 from s where r.a in (select a from s);
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 35 not found in project list
delete from r using s where r.a = s.a;
delete from r;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
@@ -264,11 +225,7 @@ delete from s;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
update r set b = r.b + 4 from s where r.b = s.b;
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 21 not found in project list
update r set b = b + 1 where b in (select b from s);
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 25 not found in project list
delete from s using r where r.a = s.b;
delete from r;
delete from s;
@@ -283,8 +240,6 @@ delete from s;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
update s set b = b + 1 where exists (select 1 from r where s.a = r.b);
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 28 not found in project list
delete from s where exists (select 1 from r where s.a = r.b);
-- ----------------------------------------------------------------------
-- Test: heap_motion5.sql
@@ -304,11 +259,7 @@ create table s (a float(53), b float(53)) distributed by (a);
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
update r set b = r.b + 1 from s where r.a = s.a;
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 21 not found in project list
update r set b = r.b + 1 from s where r.a in (select a from s);
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 35 not found in project list
delete from r using s where r.a = s.a;
delete from r;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
@@ -325,11 +276,7 @@ delete from s;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
update r set b = r.b + 4 from s where r.b = s.b;
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 21 not found in project list
update r set b = b + 1 where b in (select b from s);
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 25 not found in project list
delete from s using r where r.a = s.b;
delete from r;
delete from s;
@@ -344,8 +291,6 @@ delete from s;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
update s set b = b + 1 where exists (select 1 from r where s.a = r.b);
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 28 not found in project list
delete from s where exists (select 1 from r where s.a = r.b);
------------------------------------------------------------
-- Update with Motion:
@@ -370,8 +315,6 @@ update s set a = r.a from r where r.b = s.b;
-- Statement contains correlated subquery
------------------------------------------------------------
update s set b = (select min(a) from r where b = s.b);
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 23 not found in project list
delete from s where b = (select min(a) from r where b = s.b);
------------------------------------------------------------
-- Update partition key (requires moving tuples from one partition to another)
@@ -415,8 +358,6 @@ update s set a = r.a from r where r.b = s.b;
-- Statement contains correlated subquery
------------------------------------------------------------
update s set b = (select min(a) from r where b = s.b);
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 23 not found in project list
delete from s where b = (select min(a) from r where b = s.b);
------------------------------------------------------------
-- Update partition key (requires moving tuples from one partition to another)
@@ -460,8 +401,6 @@ update s set a = r.a from r where r.b = s.b;
-- Statement contains correlated subquery
------------------------------------------------------------
update s set b = (select min(a) from r where b = s.b);
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 23 not found in project list
delete from s where b = (select min(a) from r where b = s.b);
------------------------------------------------------------
-- Update partition key (requires moving tuples from one partition to another)
@@ -505,8 +444,6 @@ update s set a = r.a from r where r.b = s.b;
-- Statement contains correlated subquery
------------------------------------------------------------
update s set b = (select min(a) from r where b = s.b);
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 23 not found in project list
delete from s where b = (select min(a) from r where b = s.b);
------------------------------------------------------------
-- Update partition key (requires moving tuples from one partition to another)
@@ -550,8 +487,6 @@ update s set a = r.a from r where r.b = s.b;
-- Statement contains correlated subquery
------------------------------------------------------------
update s set b = (select min(a) from r where b = s.b);
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 23 not found in project list
delete from s where b = (select min(a) from r where b = s.b);
------------------------------------------------------------
-- Update partition key (requires moving tuples from one partition to another)
@@ -929,8 +864,6 @@ SELECT a,b,c FROM update_test ORDER BY a,c;
(2 rows)
UPDATE update_test SET a = DEFAULT, b = DEFAULT;
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 14 not found in project list
SELECT a,b,c FROM update_test ORDER BY a,c;
a | b | c
----+---+-----
@@ -940,8 +873,6 @@ SELECT a,b,c FROM update_test ORDER BY a,c;
-- aliases for the UPDATE target table
UPDATE update_test AS t SET b = 10 WHERE t.a = 10;
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 13 not found in project list
SELECT a,b,c FROM update_test ORDER BY a,c;
a | b | c
----+----+-----
@@ -950,8 +881,6 @@ SELECT a,b,c FROM update_test ORDER BY a,c;
(2 rows)
UPDATE update_test t SET b = t.b + 10 WHERE t.a = 10;
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 13 not found in project list
SELECT a,b,c FROM update_test ORDER BY a,c;
a | b | c
----+----+-----
@@ -961,8 +890,6 @@ SELECT a,b,c FROM update_test ORDER BY a,c;
UPDATE update_test SET a=v.i FROM (VALUES(100, 20)) AS v(i, j)
WHERE update_test.b = v.j;
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 14 not found in project list
SELECT a,b,c FROM update_test ORDER BY a,c;
a | b | c
-----+----+-----
@@ -986,8 +913,6 @@ INSERT INTO t2 (id, data1, data2) VALUES (4, 3, 104);
-- (depending upon exactly where the data is stored, which will vary depending
-- upon the number of segments; in my case, I used only 2 segments).
UPDATE t1 SET data2 = t2.data2 FROM t2 WHERE t1.data1 = t2.data1;
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 22 not found in project list
SELECT * from t1;
id | data1 | data2
----+-------+-------
@@ -1183,8 +1108,6 @@ select sales.* from sales,s,r where sales.id = s.b and sales.month = r.b+1;
(10 rows)
update sales set month = month+1 from r,s where sales.id = s.b and sales.month = r.b+1;
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 34 not found in project list
select sales.* from sales,s,r where sales.id = s.b and sales.month = r.b+2;
id | year | month | day | region
----+------+-------+-----+-----------
@@ -1210,8 +1133,6 @@ select sales.* from sales where id in (select s.b from s, r where s.a = r.b) and
(4 rows)
update sales set region = 'new_region' where id in (select s.b from s, r where s.a = r.b) and day in (select a from r);
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 52 not found in project list
select sales.* from sales where id in (select s.b from s, r where s.a = r.b) and day in (select a from r);
id | year | month | day | region
----+------+-------+-----+------------
@@ -1264,8 +1185,6 @@ select r.* from r,s,sales where s.a = sales.day and sales.month = r.b;
(6 rows)
update r set b = r.b + 1 from s,sales where s.a = sales.day and sales.month = r.b;
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 34 not found in project list
select r.* from r,s,sales where s.a = sales.day and sales.month = r.b-1;
a | b
---+----
@@ -1606,8 +1525,6 @@ select * from s where a = 4 and a in (select b from m);
(2 rows)
update s set b = b + 1 where a = 4 and a in (select b from m);
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 25 not found in project list
select * from s where a = 4 and a in (select b from m);
a | b
---+----
@@ -1716,8 +1633,6 @@ select * from s where a = 4 and a in (select b from m);
PREPARE plan2 AS update s set b = b + 1 where a = 4 and a in (select b from m);
EXECUTE plan2;
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 25 not found in project list
select * from s where a = 4 and a in (select b from m);
a | b
---+----
diff --git a/contrib/pax_storage/src/test/regress/expected/direct_dispatch_optimizer.out b/contrib/pax_storage/src/test/regress/expected/direct_dispatch_optimizer.out
index d6fd64b18f4..57e499bb091 100644
--- a/contrib/pax_storage/src/test/regress/expected/direct_dispatch_optimizer.out
+++ b/contrib/pax_storage/src/test/regress/expected/direct_dispatch_optimizer.out
@@ -50,8 +50,9 @@ INFO: (slice 1) Dispatch command to ALL contents: 0 1 2
-- DO direct dispatch
-- Known_opt_diff: MPP-21346
update direct_test set value = 'horse' where key = 100;
-INFO: (slice 0) Dispatch command to SINGLE content
-INFO: Distributed transaction command 'Distributed Commit (one-phase)' to SINGLE content
+INFO: (slice 0) Dispatch command to ALL contents: 0 1 2
+INFO: Distributed transaction command 'Distributed Prepare' to ALL contents: 0 1 2
+INFO: Distributed transaction command 'Distributed Commit Prepared' to ALL contents: 0 1 2
-- verify
select * from direct_test order by key, value;
INFO: (slice 1) Dispatch command to ALL contents: 0 1 2
@@ -123,8 +124,9 @@ INFO: (slice 1) Dispatch command to ALL contents: 0 1 2
-- DO direct dispatch
-- Known_opt_diff: MPP-21346
update direct_test_two_column set value = 'horse' where key1 = 100 and key2 = 101;
-INFO: (slice 0) Dispatch command to SINGLE content
-INFO: Distributed transaction command 'Distributed Commit (one-phase)' to SINGLE content
+INFO: (slice 0) Dispatch command to ALL contents: 0 1 2
+INFO: Distributed transaction command 'Distributed Prepare' to ALL contents: 0 1 2
+INFO: Distributed transaction command 'Distributed Commit Prepared' to ALL contents: 0 1 2
-- verify
select * from direct_test_two_column order by key1, key2, value;
INFO: (slice 1) Dispatch command to ALL contents: 0 1 2
@@ -176,17 +178,17 @@ INFO: Distributed transaction command 'Distributed Commit Prepared' to ALL cont
-- hash all these values to the same content! (and so test would change)
--
update direct_test set value = 'pig' where key in (1,2,3,4,5);
-INFO: (slice 0) Dispatch command to ALL contents: 1 0 2
-INFO: Distributed transaction command 'Distributed Commit (one-phase)' to ALL contents: 1 0 2
+INFO: (slice 0) Dispatch command to ALL contents: 0 1 2
+INFO: Distributed transaction command 'Distributed Commit (one-phase)' to ALL contents: 0 1 2
update direct_test_two_column set value = 'pig' where key1 = 100 and key2 in (1,2,3,4);
-INFO: (slice 0) Dispatch command to PARTIAL contents: 1 2
-INFO: Distributed transaction command 'Distributed Commit (one-phase)' to PARTIAL contents: 1 2
+INFO: (slice 0) Dispatch command to ALL contents: 0 1 2
+INFO: Distributed transaction command 'Distributed Commit (one-phase)' to ALL contents: 0 1 2
update direct_test_two_column set value = 'pig' where key1 in (100,101,102,103,104) and key2 in (1);
-INFO: (slice 0) Dispatch command to ALL contents: 1 0 2
-INFO: Distributed transaction command 'Distributed Commit (one-phase)' to ALL contents: 1 0 2
+INFO: (slice 0) Dispatch command to ALL contents: 0 1 2
+INFO: Distributed transaction command 'Distributed Commit (one-phase)' to ALL contents: 0 1 2
update direct_test_two_column set value = 'pig' where key1 in (100,101) and key2 in (1,2);
-INFO: (slice 0) Dispatch command to PARTIAL contents: 1 2
-INFO: Distributed transaction command 'Distributed Commit (one-phase)' to PARTIAL contents: 1 2
+INFO: (slice 0) Dispatch command to ALL contents: 0 1 2
+INFO: Distributed transaction command 'Distributed Commit (one-phase)' to ALL contents: 0 1 2
-- Multiple row update, where clause lists values which all hash to same segment
-- DO direct dispatch
-- CAN'T IMPLEMENT THIS TEST BECAUSE THE # of segments changes again (unless we use a # of segments function, and exploit the simple nature of int4 hashing -- can we do that?)
@@ -274,8 +276,9 @@ INFO: (slice 1) Dispatch command to ALL contents: 0 1 2
prepare test_update (int) as update direct_test set value = 'boo' where key = $1;
-- Known_opt_diff: MPP-21346
execute test_update(2);
-INFO: (slice 0) Dispatch command to SINGLE content
-INFO: Distributed transaction command 'Distributed Commit (one-phase)' to SINGLE content
+INFO: (slice 0) Dispatch command to ALL contents: 0 1 2
+INFO: Distributed transaction command 'Distributed Prepare' to ALL contents: 0 1 2
+INFO: Distributed transaction command 'Distributed Commit Prepared' to ALL contents: 0 1 2
select * from direct_test;
INFO: (slice 1) Dispatch command to ALL contents: 0 1 2
key | value
@@ -342,9 +345,10 @@ INFO: (slice 1) Dispatch command to SINGLE content
-- main plan is direct dispatch and also has init plans
update direct_dispatch_bar set id2 = 1 where id1 = 1 and exists (select * from direct_dispatch_foo where id = 2);
-INFO: (slice 2) Dispatch command to SINGLE content
-INFO: (slice 0) Dispatch command to SINGLE content
-INFO: Distributed transaction command 'Distributed Commit (one-phase)' to PARTIAL contents: 0 1
+INFO: (slice 2) Dispatch command to ALL contents: 0 1 2
+INFO: (slice 0) Dispatch command to ALL contents: 0 1 2
+INFO: (slice 1) Dispatch command to SINGLE content
+INFO: Distributed transaction command 'Distributed Commit (one-phase)' to ALL contents: 0 1 2
-- init plan to see how transaction escalation happens
-- Known_opt_diff: MPP-21346
delete from direct_dispatch_foo where id = (select max(id2) from direct_dispatch_bar where id1 = 5);
@@ -528,23 +532,29 @@ INFO: Distributed transaction command 'Distributed Commit (one-phase)' to SINGL
-- update case
prepare p2 as update test_prepare set j =2 where i =$1;
execute p2(1);
-INFO: (slice 0) Dispatch command to SINGLE content
-INFO: Distributed transaction command 'Distributed Commit (one-phase)' to SINGLE content
+INFO: (slice 0) Dispatch command to ALL contents: 0 1 2
+INFO: Distributed transaction command 'Distributed Prepare' to ALL contents: 0 1 2
+INFO: Distributed transaction command 'Distributed Commit Prepared' to ALL contents: 0 1 2
execute p2(1);
-INFO: (slice 0) Dispatch command to SINGLE content
-INFO: Distributed transaction command 'Distributed Commit (one-phase)' to SINGLE content
+INFO: (slice 0) Dispatch command to ALL contents: 0 1 2
+INFO: Distributed transaction command 'Distributed Prepare' to ALL contents: 0 1 2
+INFO: Distributed transaction command 'Distributed Commit Prepared' to ALL contents: 0 1 2
execute p2(1);
-INFO: (slice 0) Dispatch command to SINGLE content
-INFO: Distributed transaction command 'Distributed Commit (one-phase)' to SINGLE content
+INFO: (slice 0) Dispatch command to ALL contents: 0 1 2
+INFO: Distributed transaction command 'Distributed Prepare' to ALL contents: 0 1 2
+INFO: Distributed transaction command 'Distributed Commit Prepared' to ALL contents: 0 1 2
execute p2(1);
-INFO: (slice 0) Dispatch command to SINGLE content
-INFO: Distributed transaction command 'Distributed Commit (one-phase)' to SINGLE content
+INFO: (slice 0) Dispatch command to ALL contents: 0 1 2
+INFO: Distributed transaction command 'Distributed Prepare' to ALL contents: 0 1 2
+INFO: Distributed transaction command 'Distributed Commit Prepared' to ALL contents: 0 1 2
execute p2(1);
-INFO: (slice 0) Dispatch command to SINGLE content
-INFO: Distributed transaction command 'Distributed Commit (one-phase)' to SINGLE content
+INFO: (slice 0) Dispatch command to ALL contents: 0 1 2
+INFO: Distributed transaction command 'Distributed Prepare' to ALL contents: 0 1 2
+INFO: Distributed transaction command 'Distributed Commit Prepared' to ALL contents: 0 1 2
execute p2(1);
-INFO: (slice 0) Dispatch command to SINGLE content
-INFO: Distributed transaction command 'Distributed Commit (one-phase)' to SINGLE content
+INFO: (slice 0) Dispatch command to ALL contents: 0 1 2
+INFO: Distributed transaction command 'Distributed Prepare' to ALL contents: 0 1 2
+INFO: Distributed transaction command 'Distributed Commit Prepared' to ALL contents: 0 1 2
-- select case
prepare p3 as select * from test_prepare where i =$1;
execute p3(1);
@@ -1526,9 +1536,9 @@ INFO: Distributed transaction command 'Distributed Abort (No Prepared)' to SING
begin;
-- orca does not handle direct dispatch for DELETE or UPDATE now
update t_hash_partition_1_prt_region1 set r_name = 'CHINA' where r_regionkey=1;
-INFO: (slice 0) Dispatch command to SINGLE content
+INFO: (slice 0) Dispatch command to ALL contents: 0 1 2
abort;
-INFO: Distributed transaction command 'Distributed Abort (No Prepared)' to SINGLE content
+INFO: Distributed transaction command 'Distributed Abort (No Prepared)' to ALL contents: 0 1 2
-- insert
begin;
-- only consider target's policy, will direct dispatch
diff --git a/contrib/pax_storage/src/test/regress/expected/gporca_optimizer.out b/contrib/pax_storage/src/test/regress/expected/gporca_optimizer.out
index de85e6ac814..5c93839b7a3 100644
--- a/contrib/pax_storage/src/test/regress/expected/gporca_optimizer.out
+++ b/contrib/pax_storage/src/test/regress/expected/gporca_optimizer.out
@@ -10509,8 +10509,6 @@ show optimizer;
(1 row)
update can_set_tag_target set y = y + 1;
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 12 not found in project list
select count(1) from can_set_tag_audit;
count
-------
@@ -14504,30 +14502,25 @@ update window_agg_test t
set i = tt.i
from (select (min(i) over (order by j)) as i, j from window_agg_test) tt
where t.j = tt.j;
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 21 not found in project list
- QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
- Update on window_agg_test t (cost=3699.81..185385.16 rows=0 width=0)
- -> Explicit Redistribute Motion 3:3 (slice1; segments: 3) (cost=3699.81..185385.16 rows=2471070 width=78)
- -> Hash Join (cost=3699.81..135963.76 rows=2471070 width=78)
- Hash Cond: (tt.j = t.j)
- -> Redistribute Motion 1:3 (slice2; segments: 1) (cost=2446.06..6966.31 rows=28700 width=40)
- Hash Key: tt.j
- -> Subquery Scan on tt (cost=2446.06..5818.31 rows=86100 width=40)
- -> WindowAgg (cost=2446.06..4957.31 rows=86100 width=8)
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------
+ Update on window_agg_test t (cost=0.00..862.02 rows=1 width=1)
+ -> Result (cost=0.00..0.00 rows=0 width=0)
+ -> Explicit Redistribute Motion 1:3 (slice1; segments: 1) (cost=0.00..862.00 rows=1 width=18)
+ -> Hash Join (cost=0.00..862.00 rows=1 width=18)
+ Hash Cond: (t.j = window_agg_test.j)
+ -> Gather Motion 3:1 (slice2; segments: 3) (cost=0.00..431.00 rows=1 width=14)
+ -> Seq Scan on window_agg_test t (cost=0.00..431.00 rows=1 width=14)
+ -> Hash (cost=431.00..431.00 rows=1 width=8)
+ -> WindowAgg (cost=0.00..431.00 rows=1 width=8)
Order By: window_agg_test.j
- -> Gather Motion 3:1 (slice3; segments: 3) (cost=2446.06..3665.81 rows=86100 width=8)
+ -> Gather Motion 3:1 (slice3; segments: 3) (cost=0.00..431.00 rows=1 width=8)
Merge Key: window_agg_test.j
- -> Sort (cost=2446.06..2517.81 rows=28700 width=8)
+ -> Sort (cost=0.00..431.00 rows=1 width=8)
Sort Key: window_agg_test.j
- -> Seq Scan on window_agg_test (cost=0.00..321.00 rows=28700 width=8)
- -> Hash (cost=895.00..895.00 rows=28700 width=46)
- -> Redistribute Motion 3:3 (slice4; segments: 3) (cost=0.00..895.00 rows=28700 width=46)
- Hash Key: t.j
- -> Seq Scan on window_agg_test t (cost=0.00..321.00 rows=28700 width=46)
- Optimizer: Postgres query optimizer
-(19 rows)
+ -> Seq Scan on window_agg_test (cost=0.00..431.00 rows=1 width=8)
+ Optimizer: GPORCA
+(16 rows)
----------------------------------
-- Test ORCA support for const TVF
diff --git a/contrib/pax_storage/src/test/regress/expected/hash_index_optimizer.out b/contrib/pax_storage/src/test/regress/expected/hash_index_optimizer.out
index 5d26bf6dfa8..c6d731598d6 100644
--- a/contrib/pax_storage/src/test/regress/expected/hash_index_optimizer.out
+++ b/contrib/pax_storage/src/test/regress/expected/hash_index_optimizer.out
@@ -156,8 +156,6 @@ SELECT * FROM hash_f8_heap
UPDATE hash_i4_heap
SET random = 1
WHERE hash_i4_heap.seqno = 1492;
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 11 not found in project list
SELECT h.seqno AS i1492, h.random AS i1
FROM hash_i4_heap h
WHERE h.random = 1;
@@ -180,8 +178,6 @@ SELECT h.seqno AS i20000
UPDATE hash_name_heap
SET random = '0123456789abcdef'::name
WHERE hash_name_heap.seqno = 6543;
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 11 not found in project list
SELECT h.seqno AS i6543, h.random AS c0_to_f
FROM hash_name_heap h
WHERE h.random = '0123456789abcdef'::name;
@@ -206,8 +202,6 @@ SELECT h.seqno AS emptyset
UPDATE hash_txt_heap
SET random = '0123456789abcdefghijklmnop'::text
WHERE hash_txt_heap.seqno = 4002;
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 11 not found in project list
SELECT h.seqno AS i4002, h.random AS c0_to_p
FROM hash_txt_heap h
WHERE h.random = '0123456789abcdefghijklmnop'::text;
@@ -230,8 +224,6 @@ SELECT h.seqno AS t20000
UPDATE hash_f8_heap
SET random = '-1234.1234'::float8
WHERE hash_f8_heap.seqno = 8906;
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 11 not found in project list
SELECT h.seqno AS i8096, h.random AS f1234_1234
FROM hash_f8_heap h
WHERE h.random = '-1234.1234'::float8;
diff --git a/contrib/pax_storage/src/test/regress/expected/partition_prune_optimizer.out b/contrib/pax_storage/src/test/regress/expected/partition_prune_optimizer.out
index f5303b87634..83c7364d7df 100644
--- a/contrib/pax_storage/src/test/regress/expected/partition_prune_optimizer.out
+++ b/contrib/pax_storage/src/test/regress/expected/partition_prune_optimizer.out
@@ -4615,13 +4615,15 @@ explain (costs off) delete from inh_lp where a = 1;
-- Ensure we don't exclude normal relations when we only expect to exclude
-- inheritance children
explain (costs off) update inh_lp1 set value = 10 where a = 2;
- QUERY PLAN
--------------------------------------
+ QUERY PLAN
+--------------------------------------------
Update on inh_lp1
- -> Seq Scan on inh_lp1
- Filter: (a = 2)
- Optimizer: Postgres query optimizer
-(4 rows)
+ -> Result
+ -> Result
+ -> Result
+ One-Time Filter: false
+ Optimizer: GPORCA
+(6 rows)
drop table inh_lp cascade;
NOTICE: drop cascades to 2 other objects
diff --git a/contrib/pax_storage/src/test/regress/expected/qp_correlated_query_optimizer.out b/contrib/pax_storage/src/test/regress/expected/qp_correlated_query_optimizer.out
index e1e10d4eb9b..84e0e4cf28b 100644
--- a/contrib/pax_storage/src/test/regress/expected/qp_correlated_query_optimizer.out
+++ b/contrib/pax_storage/src/test/regress/expected/qp_correlated_query_optimizer.out
@@ -1607,8 +1607,6 @@ select * from qp_csq_t4 order by a;
(4 rows)
update qp_csq_t4 set a = (select y from qp_csq_t2 where x=a) where b < 8;
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 21 not found in project list
select * from qp_csq_t4 order by a;
a | b
----+---
@@ -1619,8 +1617,6 @@ select * from qp_csq_t4 order by a;
(4 rows)
update qp_csq_t4 set a = 9999 where qp_csq_t4.a = (select max(x) from qp_csq_t2);
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 23 not found in project list
select * from qp_csq_t4 order by a;
a | b
------+---
@@ -1631,8 +1627,6 @@ select * from qp_csq_t4 order by a;
(4 rows)
update qp_csq_t4 set a = (select max(y) from qp_csq_t2 where x=a) where qp_csq_t4.a = (select min(x) from qp_csq_t2);
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 35 not found in project list
select * from qp_csq_t4 order by a;
a | b
------+---
@@ -1643,8 +1637,6 @@ select * from qp_csq_t4 order by a;
(4 rows)
update qp_csq_t4 set a = 8888 where (select (y*2)>b from qp_csq_t2 where a=x);
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 22 not found in project list
select * from qp_csq_t4 order by a;
a | b
------+---
@@ -1655,8 +1647,6 @@ select * from qp_csq_t4 order by a;
(4 rows)
update qp_csq_t4 set a = 3333 where qp_csq_t4.a in (select x from qp_csq_t2);
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 25 not found in project list
select * from qp_csq_t4 order by a;
a | b
------+---
@@ -1667,8 +1657,6 @@ select * from qp_csq_t4 order by a;
(4 rows)
update D set i = 11111 from C where C.i = D.i and exists (select C.j from C,B where C.j = B.j and D.j < 10);
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 45 not found in project list
select * from D;
i | j
-------+----
@@ -1680,8 +1668,6 @@ select * from D;
(5 rows)
update D set i = 22222 from C where C.i = D.i and not exists (select C.j from C,B where C.j = B.j and D.j < 10);
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 45 not found in project list
select * from D;
i | j
-------+----
@@ -2405,8 +2391,6 @@ analyze product;
commit;
UPDATE product SET product_price = product_price * .9
where product_name NOT IN (SELECT DISTINCT product_name FROM product_order);
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 35 not found in project list
SELECT * FROM product order by product_name;
product_name | product_price | quantity_on_hand | last_stock_date
--------------+---------------+------------------+-----------------
diff --git a/contrib/pax_storage/src/test/regress/expected/qp_orca_fallback_optimizer.out b/contrib/pax_storage/src/test/regress/expected/qp_orca_fallback_optimizer.out
index e0a411212f6..fb8cdce2f86 100644
--- a/contrib/pax_storage/src/test/regress/expected/qp_orca_fallback_optimizer.out
+++ b/contrib/pax_storage/src/test/regress/expected/qp_orca_fallback_optimizer.out
@@ -53,8 +53,6 @@ DETAIL: Falling back to Postgres-based planner because GPORCA does not support
set optimizer_enable_dml_constraints=on;
explain update constr_tab set b = 10;
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 13 not found in project list
QUERY PLAN
-----------------------------------------------------------------------
Update on constr_tab (cost=0.00..271.00 rows=0 width=0)
diff --git a/contrib/pax_storage/src/test/regress/expected/qp_subquery_optimizer.out b/contrib/pax_storage/src/test/regress/expected/qp_subquery_optimizer.out
index 4a5a3be132b..cacc38d3daf 100644
--- a/contrib/pax_storage/src/test/regress/expected/qp_subquery_optimizer.out
+++ b/contrib/pax_storage/src/test/regress/expected/qp_subquery_optimizer.out
@@ -1633,8 +1633,6 @@ select * from TblUp1;
(3 rows)
update TblUp2 set a=100 where a not in (select a from TblUp4);
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 26 not found in project list
select * from TblUp2;
a | b
-----+---
diff --git a/contrib/pax_storage/src/test/regress/expected/qp_targeted_dispatch_optimizer.out b/contrib/pax_storage/src/test/regress/expected/qp_targeted_dispatch_optimizer.out
index cbbbcfdce26..4464a24a003 100644
--- a/contrib/pax_storage/src/test/regress/expected/qp_targeted_dispatch_optimizer.out
+++ b/contrib/pax_storage/src/test/regress/expected/qp_targeted_dispatch_optimizer.out
@@ -87,12 +87,12 @@ INFO: (slice 1) Dispatch command to SINGLE content
(1 row)
update key_value_table set value=300 where key =200;
-INFO: (slice 0) Dispatch command to SINGLE content
+INFO: (slice 0) Dispatch command to ALL contents: 0 1 2
savepoint s;
update key_value_table set value=200 where key =300;
-INFO: (slice 0) Dispatch command to SINGLE content
+INFO: (slice 0) Dispatch command to ALL contents: 0 1 2
update key_value_table set value=300 where key =200;
-INFO: (slice 0) Dispatch command to SINGLE content
+INFO: (slice 0) Dispatch command to ALL contents: 0 1 2
rollback to s;
commit;
INFO: Distributed transaction command 'Distributed Prepare' to ALL contents: 2 0 1
@@ -107,7 +107,7 @@ INFO: (slice 1) Dispatch command to SINGLE content
savepoint s;
update key_value_table set value=200 where key =300;
-INFO: (slice 0) Dispatch command to SINGLE content
+INFO: (slice 0) Dispatch command to ALL contents: 0 1 2
rollback;
INFO: Distributed transaction command 'Distributed Abort (No Prepared)' to ALL contents: 2 0 1
savepoint s;
diff --git a/contrib/pax_storage/src/test/regress/expected/qp_union_intersect_optimizer.out b/contrib/pax_storage/src/test/regress/expected/qp_union_intersect_optimizer.out
index da58390502c..845c01846c6 100644
--- a/contrib/pax_storage/src/test/regress/expected/qp_union_intersect_optimizer.out
+++ b/contrib/pax_storage/src/test/regress/expected/qp_union_intersect_optimizer.out
@@ -1736,7 +1736,8 @@ reset optimizer_segments;
--SELECT COUNT(DISTINCT(a)) FROM dml_union_r;
-- @description union_update_test31: Negative Tests more than one row returned by a sub-query used as an expression
UPDATE dml_union_r SET b = ( SELECT a FROM dml_union_r EXCEPT ALL SELECT a FROM dml_union_s);
-ERROR: more than one row returned by a subquery used as an expression
+DETAIL: Expected no more than one row to be returned by expression
+ERROR: one or more assertions failed
--
-- Test mixing a set-returning function, which can be evaluated anywhere,
-- (it has General locus) and a diststributed table, in an Append.
diff --git a/contrib/pax_storage/src/test/regress/expected/update_gp_optimizer.out b/contrib/pax_storage/src/test/regress/expected/update_gp_optimizer.out
index 50aa3dbb064..014fc5ab235 100644
--- a/contrib/pax_storage/src/test/regress/expected/update_gp_optimizer.out
+++ b/contrib/pax_storage/src/test/regress/expected/update_gp_optimizer.out
@@ -122,36 +122,46 @@ EXPLAIN (COSTS OFF) UPDATE keo1 SET user_vie_act_cntr_marg_cum = 234.682 FROM
(SELECT min (keo4.keo_para_budget_date) FROM keo4)))
) t1
WHERE t1.user_vie_project_code_pk = keo1.user_vie_project_code_pk;
- QUERY PLAN
--------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Update on keo1
- InitPlan 3 (returns $2) (slice3)
- -> Aggregate
- InitPlan 2 (returns $1) (slice5)
- -> Gather Motion 3:1 (slice6; segments: 3)
- InitPlan 1 (returns $0) (slice7)
- -> Aggregate
- -> Gather Motion 3:1 (slice8; segments: 3)
- -> Seq Scan on keo4
- -> Seq Scan on keo4 keo4_1
- Filter: ((keo_para_budget_date)::text = $0)
- -> Gather Motion 3:1 (slice4; segments: 3)
- -> Seq Scan on keo3
- Filter: ((bky_per)::text = ($1)::text)
-> Hash Join
- Hash Cond: ((a.user_vie_project_code_pk)::text = (b.projects_pk)::text)
- -> Hash Join
- Hash Cond: ((a.user_vie_project_code_pk)::text = (keo1.user_vie_project_code_pk)::text)
- -> Broadcast Motion 3:3 (slice1; segments: 3)
- -> Seq Scan on keo1 a
- Filter: ((user_vie_fiscal_year_period_sk)::text = $2)
- -> Hash
- -> Seq Scan on keo1
+ Hash Cond: ((keo1.user_vie_project_code_pk)::text = (a.user_vie_project_code_pk)::text)
+ -> Seq Scan on keo1
-> Hash
- -> Broadcast Motion 3:3 (slice2; segments: 3)
- -> Seq Scan on keo2 b
- Optimizer: Postgres query optimizer
-(27 rows)
+ -> Broadcast Motion 3:3 (slice1; segments: 3)
+ -> Hash Join
+ Hash Cond: ((a.user_vie_project_code_pk)::text = (b.projects_pk)::text)
+ -> Hash Join
+ Hash Cond: ((max((keo3.sky_per)::text)) = (a.user_vie_fiscal_year_period_sk)::text)
+ -> Redistribute Motion 1:3 (slice2; segments: 1)
+ -> Aggregate
+ -> Hash Join
+ Hash Cond: ((keo3.bky_per)::text = (keo4_1.keo_para_required_period)::text)
+ -> Gather Motion 3:1 (slice3; segments: 3)
+ -> Seq Scan on keo3
+ -> Hash
+ -> Assert
+ Assert Cond: ((row_number() OVER (?)) = 1)
+ -> WindowAgg
+ -> Gather Motion 3:1 (slice4; segments: 3)
+ -> Hash Join
+ Hash Cond: ((min((keo4.keo_para_budget_date)::text)) = (keo4_1.keo_para_budget_date)::text)
+ -> Redistribute Motion 1:3 (slice5; segments: 1)
+ -> Aggregate
+ -> Gather Motion 3:1 (slice6; segments: 3)
+ -> Seq Scan on keo4
+ -> Hash
+ -> Broadcast Motion 3:3 (slice7; segments: 3)
+ -> Seq Scan on keo4 keo4_1
+ -> Hash
+ -> Broadcast Motion 3:3 (slice8; segments: 3)
+ -> Seq Scan on keo1 a
+ -> Hash
+ -> Broadcast Motion 3:3 (slice9; segments: 3)
+ -> Seq Scan on keo2 b
+ Optimizer: GPORCA
+(37 rows)
UPDATE keo1 SET user_vie_act_cntr_marg_cum = 234.682 FROM
( SELECT a.user_vie_project_code_pk FROM keo1 a INNER JOIN keo2 b
diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c
index 4671b2bd892..6ab7bba836a 100644
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -755,6 +755,28 @@ ExecInitUpdateProjection(ModifyTableState *mtstate,
resultRelInfo->ri_newTupleSlot,
&mtstate->ps);
+ /*
+ * The projection copies a column from the old tuple whenever the subplan
+ * does not assign it. Record whether that happens at all: when the
+ * subplan assigns every live column the old tuple is never read, and the
+ * caller can skip fetching it. ORCA emits such a target list for a
+ * non-split UPDATE, which is the only way an append-optimized table can
+ * run one, since it cannot fetch a tuple by tid.
+ */
+ resultRelInfo->ri_projectNewNeedsOldTuple = false;
+ for (int attno = 1; attno <= relDesc->natts; attno++)
+ {
+ Form_pg_attribute attr = TupleDescAttr(relDesc, attno - 1);
+
+ if (attr->attisdropped)
+ continue;
+ if (!list_member_int(updateColnos, attno))
+ {
+ resultRelInfo->ri_projectNewNeedsOldTuple = true;
+ break;
+ }
+ }
+
resultRelInfo->ri_projectNewInfoValid = true;
}
@@ -4319,6 +4341,17 @@ ExecModifyTable(PlanState *pstate)
/* Use the wholerow junk attr as the old tuple. */
ExecForceStoreHeapTuple(oldtuple, oldSlot, false);
}
+ else if (!resultRelInfo->ri_projectNewNeedsOldTuple &&
+ RelationIsNonblockRelation(resultRelInfo->ri_RelationDesc))
+ {
+ /*
+ * The plan slot already carries every column, so the
+ * old tuple is not read. Skipping the fetch is what
+ * lets append-optimized tables run a non-split UPDATE
+ * at all: they cannot fetch a tuple by tid.
+ */
+ ExecStoreAllNullTuple(oldSlot);
+ }
else
{
/* Fetch the most recent version of old tuple. */
diff --git a/src/backend/gpopt/translate/CTranslatorDXLToPlStmt.cpp b/src/backend/gpopt/translate/CTranslatorDXLToPlStmt.cpp
index 3abfd5fed5a..4b9ae09b062 100644
--- a/src/backend/gpopt/translate/CTranslatorDXLToPlStmt.cpp
+++ b/src/backend/gpopt/translate/CTranslatorDXLToPlStmt.cpp
@@ -5337,11 +5337,6 @@ CTranslatorDXLToPlStmt::TranslateDXLDml(
IMDId *mdid_target_table = phy_dml_dxlop->GetDXLTableDescr()->MDId();
const IMDRelation *md_rel = m_md_accessor->RetrieveRel(mdid_target_table);
- if (md_rel->IsNonBlockTable())
- {
- isSplit = true; // AO tables are always use split updates
- }
-
if (md_rel->IsPartitioned())
{
dml->forceTupleRouting = true;
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index ce0c00347be..ffb88de79cc 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -498,6 +498,14 @@ typedef struct ResultRelInfo
/* Have the projection and the slots above been initialized? */
bool ri_projectNewInfoValid;
+ /*
+ * Does ri_projectNew read any column from the old tuple? It does not when
+ * the subplan assigns every live column of the relation, which is what
+ * ORCA emits for a non-split UPDATE. Append-optimized tables cannot fetch
+ * a tuple by tid at all, so this is what lets them run such a plan.
+ */
+ bool ri_projectNewNeedsOldTuple;
+
/* updates do LockTuple() before oldtup read; see README.tuplock */
bool ri_needLockTagTuple;
diff --git a/src/test/isolation2/output/uao/parallel_update_optimizer.source b/src/test/isolation2/output/uao/parallel_update_optimizer.source
index 35077903916..2a4c63ec5a3 100644
--- a/src/test/isolation2/output/uao/parallel_update_optimizer.source
+++ b/src/test/isolation2/output/uao/parallel_update_optimizer.source
@@ -21,9 +21,9 @@ UPDATE 1
ao | ExclusiveLock | relation | master
(1 row)
2: SELECT * FROM locktest_segments WHERE coalesce = 'ao';
- coalesce | mode | locktype | node
-----------+---------------+----------+-----------
- ao | ExclusiveLock | relation | 1 segment
+ coalesce | mode | locktype | node
+----------+---------------+----------+------------
+ ao | ExclusiveLock | relation | n segments
(1 row)
-- The case here should update a tuple at the same seg with(2).
-- Under jump hash, (2) and (3) are on the same seg(seg0).
diff --git a/src/test/regress/expected/ao_locks_optimizer.out b/src/test/regress/expected/ao_locks_optimizer.out
index ad89902c8a1..5a723c32950 100644
--- a/src/test/regress/expected/ao_locks_optimizer.out
+++ b/src/test/regress/expected/ao_locks_optimizer.out
@@ -99,9 +99,9 @@ SELECT * FROM locktest_master where coalesce = 'ao_locks_table' or
SELECT * FROM locktest_segments where coalesce = 'ao_locks_table' or
coalesce like 'aovisimap%' or coalesce like 'aoseg%';
- coalesce | mode | locktype | node
------------------+------------------+----------+-----------
- ao_locks_table | ExclusiveLock | relation | 1 segment
+ coalesce | mode | locktype | node
+-----------------+------------------+----------+------------
+ ao_locks_table | ExclusiveLock | relation | n segments
aovisimap table | RowExclusiveLock | relation | 1 segment
aovisimap index | RowExclusiveLock | relation | 1 segment
(3 rows)
diff --git a/src/test/regress/expected/orca_split_update.out b/src/test/regress/expected/orca_split_update.out
new file mode 100644
index 00000000000..5c8d9ac53e6
--- /dev/null
+++ b/src/test/regress/expected/orca_split_update.out
@@ -0,0 +1,104 @@
+--
+-- An UPDATE that does not move the row between segments runs as an in-place
+-- update on append-optimized tables too: the storage engine turns it into a
+-- delete plus an append by itself, exactly as the Postgres planner has always
+-- assumed. ORCA used to be unable to emit that plan -- the executor fetched
+-- the old tuple by tid, which AO cannot do -- so the DXL-to-PlStmt translation
+-- forced a split update, failed to find the DMLAction column, and silently
+-- fell back to the planner.
+--
+CREATE SCHEMA orca_split_update;
+SET search_path TO orca_split_update;
+-- make a fallback visible instead of silent
+SET optimizer_trace_fallback = on;
+CREATE TABLE ao_su (id bigint, v bigint) USING ao_row DISTRIBUTED BY (id);
+CREATE TABLE aocs_su (id bigint, v bigint) USING ao_column DISTRIBUTED BY (id);
+CREATE TABLE heap_su (id bigint, v bigint) USING heap DISTRIBUTED BY (id);
+INSERT INTO ao_su SELECT g, g FROM generate_series(1, 10) g;
+INSERT INTO aocs_su SELECT g, g FROM generate_series(1, 10) g;
+INSERT INTO heap_su SELECT g, g FROM generate_series(1, 10) g;
+-- Updating a non-distribution column: the row stays on its segment, so the
+-- plan is a plain ModifyTable with neither a Split Update nor a Motion.
+EXPLAIN (COSTS OFF) UPDATE ao_su SET v = 999 WHERE id = 1;
+ QUERY PLAN
+-------------------------------------
+ Update on ao_su
+ -> Seq Scan on ao_su
+ Filter: (id = 1)
+ Optimizer: Postgres query optimizer
+(4 rows)
+
+EXPLAIN (COSTS OFF) UPDATE aocs_su SET v = 999 WHERE id = 1;
+ QUERY PLAN
+-------------------------------------
+ Update on aocs_su
+ -> Seq Scan on aocs_su
+ Filter: (id = 1)
+ Optimizer: Postgres query optimizer
+(4 rows)
+
+-- Updating the distribution column moves the row, which still needs the split
+-- and a Motion.
+EXPLAIN (COSTS OFF) UPDATE ao_su SET id = 999 WHERE id = 1;
+ QUERY PLAN
+---------------------------------------------------------------
+ Update on ao_su
+ -> Explicit Redistribute Motion 1:3 (slice1; segments: 1)
+ -> Split Update
+ -> Seq Scan on ao_su
+ Filter: (id = 1)
+ Optimizer: Postgres query optimizer
+(6 rows)
+
+-- Heap has always taken this path; it must be unaffected.
+EXPLAIN (COSTS OFF) UPDATE heap_su SET v = 999 WHERE id = 1;
+ QUERY PLAN
+-------------------------------------
+ Update on heap_su
+ -> Seq Scan on heap_su
+ Filter: (id = 1)
+ Optimizer: Postgres query optimizer
+(4 rows)
+
+-- The update must not leave the old tuple version behind.
+UPDATE ao_su SET v = 999 WHERE id = 1;
+UPDATE aocs_su SET v = 999 WHERE id = 1;
+UPDATE heap_su SET v = 999 WHERE id = 1;
+SELECT count(*) AS ao_rows FROM ao_su;
+ ao_rows
+---------
+ 10
+(1 row)
+
+SELECT count(*) AS aocs_rows FROM aocs_su;
+ aocs_rows
+-----------
+ 10
+(1 row)
+
+SELECT id, v FROM ao_su WHERE id <= 2 ORDER BY id;
+ id | v
+----+-----
+ 1 | 999
+ 2 | 2
+(2 rows)
+
+SELECT id, v FROM aocs_su WHERE id <= 2 ORDER BY id;
+ id | v
+----+-----
+ 1 | 999
+ 2 | 2
+(2 rows)
+
+SELECT id, v FROM heap_su WHERE id <= 2 ORDER BY id;
+ id | v
+----+-----
+ 1 | 999
+ 2 | 2
+(2 rows)
+
+DROP SCHEMA orca_split_update CASCADE;
+NOTICE: drop cascades to 3 other objects
+DETAIL: drop cascades to table ao_su
+drop cascades to table aocs_su
+drop cascades to table heap_su
diff --git a/src/test/regress/expected/orca_split_update_optimizer.out b/src/test/regress/expected/orca_split_update_optimizer.out
new file mode 100644
index 00000000000..6a223aa131e
--- /dev/null
+++ b/src/test/regress/expected/orca_split_update_optimizer.out
@@ -0,0 +1,108 @@
+--
+-- An UPDATE that does not move the row between segments runs as an in-place
+-- update on append-optimized tables too: the storage engine turns it into a
+-- delete plus an append by itself, exactly as the Postgres planner has always
+-- assumed. ORCA used to be unable to emit that plan -- the executor fetched
+-- the old tuple by tid, which AO cannot do -- so the DXL-to-PlStmt translation
+-- forced a split update, failed to find the DMLAction column, and silently
+-- fell back to the planner.
+--
+CREATE SCHEMA orca_split_update;
+SET search_path TO orca_split_update;
+-- make a fallback visible instead of silent
+SET optimizer_trace_fallback = on;
+CREATE TABLE ao_su (id bigint, v bigint) USING ao_row DISTRIBUTED BY (id);
+CREATE TABLE aocs_su (id bigint, v bigint) USING ao_column DISTRIBUTED BY (id);
+CREATE TABLE heap_su (id bigint, v bigint) USING heap DISTRIBUTED BY (id);
+INSERT INTO ao_su SELECT g, g FROM generate_series(1, 10) g;
+INSERT INTO aocs_su SELECT g, g FROM generate_series(1, 10) g;
+INSERT INTO heap_su SELECT g, g FROM generate_series(1, 10) g;
+-- Updating a non-distribution column: the row stays on its segment, so the
+-- plan is a plain ModifyTable with neither a Split Update nor a Motion.
+EXPLAIN (COSTS OFF) UPDATE ao_su SET v = 999 WHERE id = 1;
+ QUERY PLAN
+--------------------------
+ Update on ao_su
+ -> Seq Scan on ao_su
+ Filter: (id = 1)
+ Optimizer: GPORCA
+(4 rows)
+
+EXPLAIN (COSTS OFF) UPDATE aocs_su SET v = 999 WHERE id = 1;
+ QUERY PLAN
+---------------------------
+ Update on aocs_su
+ -> Seq Scan on aocs_su
+ Filter: (id = 1)
+ Optimizer: GPORCA
+(4 rows)
+
+-- Updating the distribution column moves the row, which still needs the split
+-- and a Motion.
+EXPLAIN (COSTS OFF) UPDATE ao_su SET id = 999 WHERE id = 1;
+ QUERY PLAN
+------------------------------------------------------------------
+ Update on ao_su
+ -> Result
+ One-Time Filter: true
+ -> Result
+ -> Redistribute Motion 3:3 (slice1; segments: 3)
+ Hash Key: id
+ -> Split Update
+ -> Seq Scan on ao_su
+ Filter: (id = 1)
+ Optimizer: GPORCA
+(10 rows)
+
+-- Heap has always taken this path; it must be unaffected.
+EXPLAIN (COSTS OFF) UPDATE heap_su SET v = 999 WHERE id = 1;
+ QUERY PLAN
+---------------------------
+ Update on heap_su
+ -> Seq Scan on heap_su
+ Filter: (id = 1)
+ Optimizer: GPORCA
+(4 rows)
+
+-- The update must not leave the old tuple version behind.
+UPDATE ao_su SET v = 999 WHERE id = 1;
+UPDATE aocs_su SET v = 999 WHERE id = 1;
+UPDATE heap_su SET v = 999 WHERE id = 1;
+SELECT count(*) AS ao_rows FROM ao_su;
+ ao_rows
+---------
+ 10
+(1 row)
+
+SELECT count(*) AS aocs_rows FROM aocs_su;
+ aocs_rows
+-----------
+ 10
+(1 row)
+
+SELECT id, v FROM ao_su WHERE id <= 2 ORDER BY id;
+ id | v
+----+-----
+ 1 | 999
+ 2 | 2
+(2 rows)
+
+SELECT id, v FROM aocs_su WHERE id <= 2 ORDER BY id;
+ id | v
+----+-----
+ 1 | 999
+ 2 | 2
+(2 rows)
+
+SELECT id, v FROM heap_su WHERE id <= 2 ORDER BY id;
+ id | v
+----+-----
+ 1 | 999
+ 2 | 2
+(2 rows)
+
+DROP SCHEMA orca_split_update CASCADE;
+NOTICE: drop cascades to 3 other objects
+DETAIL: drop cascades to table ao_su
+drop cascades to table aocs_su
+drop cascades to table heap_su
diff --git a/src/test/regress/greenplum_schedule b/src/test/regress/greenplum_schedule
index 5e2a83ee5a5..29a51740eba 100755
--- a/src/test/regress/greenplum_schedule
+++ b/src/test/regress/greenplum_schedule
@@ -65,7 +65,7 @@ test: leastsquares opr_sanity_gp decode_expr bitmapscan bitmapscan_ao case_gp li
# below test(s) inject faults so each of them need to be in a separate group
test: gpcopy
-test: orca_static_pruning orca_groupingsets_fallbacks
+test: orca_static_pruning orca_groupingsets_fallbacks orca_split_update
test: filter gpctas gpdist gpdist_opclasses gpdist_legacy_opclasses matrix sublink table_functions olap_setup complex opclass_ddl information_schema guc_env_var gp_explain distributed_transactions explain_format olap_plans gp_copy_dtx
# below test(s) inject faults so each of them need to be in a separate group
test: guc_gp
diff --git a/src/test/regress/sql/orca_split_update.sql b/src/test/regress/sql/orca_split_update.sql
new file mode 100644
index 00000000000..717cc5b7098
--- /dev/null
+++ b/src/test/regress/sql/orca_split_update.sql
@@ -0,0 +1,47 @@
+--
+-- An UPDATE that does not move the row between segments runs as an in-place
+-- update on append-optimized tables too: the storage engine turns it into a
+-- delete plus an append by itself, exactly as the Postgres planner has always
+-- assumed. ORCA used to be unable to emit that plan -- the executor fetched
+-- the old tuple by tid, which AO cannot do -- so the DXL-to-PlStmt translation
+-- forced a split update, failed to find the DMLAction column, and silently
+-- fell back to the planner.
+--
+CREATE SCHEMA orca_split_update;
+SET search_path TO orca_split_update;
+
+-- make a fallback visible instead of silent
+SET optimizer_trace_fallback = on;
+
+CREATE TABLE ao_su (id bigint, v bigint) USING ao_row DISTRIBUTED BY (id);
+CREATE TABLE aocs_su (id bigint, v bigint) USING ao_column DISTRIBUTED BY (id);
+CREATE TABLE heap_su (id bigint, v bigint) USING heap DISTRIBUTED BY (id);
+
+INSERT INTO ao_su SELECT g, g FROM generate_series(1, 10) g;
+INSERT INTO aocs_su SELECT g, g FROM generate_series(1, 10) g;
+INSERT INTO heap_su SELECT g, g FROM generate_series(1, 10) g;
+
+-- Updating a non-distribution column: the row stays on its segment, so the
+-- plan is a plain ModifyTable with neither a Split Update nor a Motion.
+EXPLAIN (COSTS OFF) UPDATE ao_su SET v = 999 WHERE id = 1;
+EXPLAIN (COSTS OFF) UPDATE aocs_su SET v = 999 WHERE id = 1;
+
+-- Updating the distribution column moves the row, which still needs the split
+-- and a Motion.
+EXPLAIN (COSTS OFF) UPDATE ao_su SET id = 999 WHERE id = 1;
+
+-- Heap has always taken this path; it must be unaffected.
+EXPLAIN (COSTS OFF) UPDATE heap_su SET v = 999 WHERE id = 1;
+
+-- The update must not leave the old tuple version behind.
+UPDATE ao_su SET v = 999 WHERE id = 1;
+UPDATE aocs_su SET v = 999 WHERE id = 1;
+UPDATE heap_su SET v = 999 WHERE id = 1;
+
+SELECT count(*) AS ao_rows FROM ao_su;
+SELECT count(*) AS aocs_rows FROM aocs_su;
+SELECT id, v FROM ao_su WHERE id <= 2 ORDER BY id;
+SELECT id, v FROM aocs_su WHERE id <= 2 ORDER BY id;
+SELECT id, v FROM heap_su WHERE id <= 2 ORDER BY id;
+
+DROP SCHEMA orca_split_update CASCADE;
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org