liang8283 commented on issue #1867:
URL: https://github.com/apache/cloudberry/issues/1867#issuecomment-5112440863
Hi @adnanhamdussalam ,
This error happens when these conditions are met:
1. The table is an AO/AOCO/PAX table.
2. The updated column is not the distribution key.
3. optimizer=on
The minimum reproducible steps:
```
gpadmin=# CREATE TABLE t (a int, b int) WITH (appendonly=true) DISTRIBUTED
BY (a);
CREATE TABLE
gpadmin=# SET optimizer_trace_fallback = on;
SET
gpadmin=# EXPLAIN UPDATE t SET b = 1;
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
QUERY PLAN
--------------------------------------------------------------
Update on t (cost=0.00..480.50 rows=0 width=0)
-> Seq Scan on t (cost=0.00..480.50 rows=43050 width=46)
Optimizer: Postgres query optimizer
(3 rows)
```
Workaround:
- Change the following tables in the SP to heap tables.
```
- crm_crm_new__
- crm_crm_new_
- crm_crm_new
- crm_crm2_scd_new_key1 / crm_crm1_scd_new_key1
```
- Use CTAS instead of UPDATE if possible.
```
UPDATE crm_crm_new__ SET STATO_COMMERCIALE_NEW='CESSATO' WHERE ... IN (...);
==>
CREATE TABLE crm_crm_new__x WITH (appendonly=true, compresstype=zstd) AS
SELECT ..., CASE WHEN RELAZ_ABILITANTE_ABILITATO IN (...) THEN 'CESSATO'
ELSE STATO_COMMERCIALE_NEW END AS STATO_COMMERCIALE_NEW
FROM crm_crm_new__;
```
Hi @my-ship-it ,
Could you please help have someone look into this issue? The repro steps
have been mentioned above. Thanks.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
