This is an automated email from the ASF dual-hosted git repository. maxyang pushed a commit to branch main in repository https://gitbox.apache.org/repos/asf/cloudberry.git
commit b43dfb64bf2217272b2a5d1684901157778e1e59 Author: David Kimura <[email protected]> AuthorDate: Mon Sep 19 23:06:43 2022 +0000 Allow ORCA to generate DML plans on GENERATED column tables Postgres commit fc22b6623b6b implemented SQL-standard feature for generated columns. This was turned off in ORCA during the merge. Afer this commit the following SQL works as expected: ```sql CREATE TABLE t_gencol(a int, b int GENERATED ALWAYS AS (a * 2) stored); EXPLAIN ANALYZE INSERT INTO t_gencol (a) VALUES (1), (2); SELECT * FROM t_gencol; a | b ---+--- 1 | 2 2 | 4 (2 rows) ``` --- .../gpopt/translate/CTranslatorRelcacheToDXL.cpp | 14 +- src/test/regress/expected/generated.out | 2 + .../{generated.out => generated_optimizer.out} | 576 ++++++++++----------- src/test/regress/sql/generated.sql | 2 + 4 files changed, 265 insertions(+), 329 deletions(-) diff --git a/src/backend/gpopt/translate/CTranslatorRelcacheToDXL.cpp b/src/backend/gpopt/translate/CTranslatorRelcacheToDXL.cpp index 62e08fe08b..2453d5f86c 100644 --- a/src/backend/gpopt/translate/CTranslatorRelcacheToDXL.cpp +++ b/src/backend/gpopt/translate/CTranslatorRelcacheToDXL.cpp @@ -495,18 +495,6 @@ CTranslatorRelcacheToDXL::RetrieveRelColumns(CMemoryPool *mp, { CMDColumnArray *mdcol_array = GPOS_NEW(mp) CMDColumnArray(mp); - for (ULONG ul = 0; ul < (ULONG) rel->rd_att->natts; ul++) - { - // GPDB_12_MERGE_FIXME: need to add support in ORCA to support GENERATED columns in DML - // FIXME: XXX in hindsight, we can fallback less often. - // We _really_ should only fallback on DML, not *all the time* - if (rel->rd_att->attrs[ul].attgenerated) - { - GPOS_RAISE(gpdxl::ExmaMD, gpdxl::ExmiMDObjUnsupported, - GPOS_WSZ_LIT("column has GENERATED default value")); - } - } - for (ULONG ul = 0; ul < (ULONG) rel->rd_att->natts; ul++) { Form_pg_attribute att = &rel->rd_att->attrs[ul]; @@ -516,7 +504,7 @@ CTranslatorRelcacheToDXL::RetrieveRelColumns(CMemoryPool *mp, // translate the default column value CDXLNode *dxl_default_col_val = nullptr; - if (!att->attisdropped) + if (!att->attisdropped && !rel->rd_att->attrs[ul].attgenerated) { dxl_default_col_val = GetDefaultColumnValue( mp, md_accessor, rel->rd_att, att->attnum); diff --git a/src/test/regress/expected/generated.out b/src/test/regress/expected/generated.out index 7096eec391..982c1d71c0 100644 --- a/src/test/regress/expected/generated.out +++ b/src/test/regress/expected/generated.out @@ -1,3 +1,4 @@ +set optimizer_trace_fallback=on; -- sanity check of system catalog SELECT attrelid, attname, attgenerated FROM pg_attribute WHERE attgenerated NOT IN ('', 's'); attrelid | attname | attgenerated @@ -1098,3 +1099,4 @@ Distributed randomly x | integer | | | generated always as (b * 2) stored Distributed randomly +reset optimizer_trace_fallback; diff --git a/src/test/regress/expected/generated.out b/src/test/regress/expected/generated_optimizer.out similarity index 66% copy from src/test/regress/expected/generated.out copy to src/test/regress/expected/generated_optimizer.out index 7096eec391..c7a5f5f5a5 100644 --- a/src/test/regress/expected/generated.out +++ b/src/test/regress/expected/generated_optimizer.out @@ -1,5 +1,8 @@ +set optimizer_trace_fallback=on; -- sanity check of system catalog SELECT attrelid, attname, attgenerated FROM pg_attribute WHERE attgenerated NOT IN ('', 's'); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Queries on master-only tables attrelid | attname | attgenerated ----------+---------+-------------- (0 rows) @@ -7,6 +10,8 @@ SELECT attrelid, attname, attgenerated FROM pg_attribute WHERE attgenerated NOT CREATE TABLE gtest0 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (55) STORED); CREATE TABLE gtest1 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STORED); SELECT table_name, column_name, column_default, is_nullable, is_generated, generation_expression FROM information_schema.columns WHERE table_name LIKE 'gtest_' ORDER BY 1, 2; +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation table_name | column_name | column_default | is_nullable | is_generated | generation_expression ------------+-------------+----------------+-------------+--------------+----------------------- gtest0 | a | | NO | NEVER | @@ -16,12 +21,36 @@ SELECT table_name, column_name, column_default, is_nullable, is_generated, gener (4 rows) SELECT table_name, column_name, dependent_column FROM information_schema.column_column_usage ORDER BY 1, 2, 3; +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation table_name | column_name | dependent_column ------------+-------------+------------------ gtest1 | a | b (1 row) \d gtest1 +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Queries on master-only tables +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Queries on master-only tables +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation Table "public.gtest1" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+------------------------------------ @@ -46,13 +75,6 @@ ERROR: cannot use generated column "b" in column generation expression LINE 1: ...AYS AS (a * 2) STORED, c int GENERATED ALWAYS AS (b * 3) STO... ^ DETAIL: A generated column cannot reference another generated column. --- a whole-row var is a self-reference on steroids, so disallow that too -CREATE TABLE gtest_err_2c (a int PRIMARY KEY, - b int GENERATED ALWAYS AS (num_nulls(gtest_err_2c)) STORED); -ERROR: cannot use whole-row variable in column generation expression -LINE 2: b int GENERATED ALWAYS AS (num_nulls(gtest_err_2c)) STOR... - ^ -DETAIL: This would cause the generated column to depend on its own value. -- invalid reference CREATE TABLE gtest_err_3 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (c * 2) STORED); ERROR: column "c" does not exist @@ -71,7 +93,6 @@ ERROR: both identity and generation expression specified for column "b" of tabl LINE 1: ...t PRIMARY KEY, b int GENERATED ALWAYS AS identity GENERATED ... ^ -- reference to system column not allowed in generated column --- (except tableoid, which we test below) CREATE TABLE gtest_err_6a (a int PRIMARY KEY, b bool GENERATED ALWAYS AS (xmin <> 37) STORED); ERROR: cannot use system column "xmin" in column generation expression LINE 1: ...a (a int PRIMARY KEY, b bool GENERATED ALWAYS AS (xmin <> 37... @@ -108,30 +129,17 @@ CREATE TABLE gtest_err_9b (a int GENERATED ALWAYS AS (b * 2) STORED, b int); NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'b' as the Greenplum Database data distribution key for this table. HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. INSERT INTO gtest1 VALUES (1); -INSERT INTO gtest1 VALUES (2, DEFAULT); -- ok +INSERT INTO gtest1 VALUES (2, DEFAULT); INSERT INTO gtest1 VALUES (3, 33); -- error -ERROR: cannot insert a non-DEFAULT value into column "b" +ERROR: cannot insert into column "b" DETAIL: Column "b" is a generated column. -INSERT INTO gtest1 VALUES (3, 33), (4, 44); -- error -ERROR: cannot insert a non-DEFAULT value into column "b" -DETAIL: Column "b" is a generated column. -INSERT INTO gtest1 VALUES (3, DEFAULT), (4, 44); -- error -ERROR: cannot insert a non-DEFAULT value into column "b" -DETAIL: Column "b" is a generated column. -INSERT INTO gtest1 VALUES (3, 33), (4, DEFAULT); -- error -ERROR: cannot insert a non-DEFAULT value into column "b" -DETAIL: Column "b" is a generated column. -INSERT INTO gtest1 VALUES (3, DEFAULT), (4, DEFAULT); -- ok SELECT * FROM gtest1 ORDER BY a; a | b ---+--- 1 | 2 2 | 4 - 3 | 6 - 4 | 8 -(4 rows) +(2 rows) -DELETE FROM gtest1 WHERE a >= 3; UPDATE gtest1 SET b = DEFAULT WHERE a = 1; UPDATE gtest1 SET b = 11 WHERE a = 1; -- error ERROR: column "b" can only be updated to DEFAULT @@ -209,37 +217,9 @@ SELECT * FROM gtest1v; 3 | 6 (1 row) -INSERT INTO gtest1v VALUES (4, 8); -- error -ERROR: cannot insert a non-DEFAULT value into column "b" +INSERT INTO gtest1v VALUES (4, 8); -- fails +ERROR: cannot insert into column "b" DETAIL: Column "b" is a generated column. -INSERT INTO gtest1v VALUES (5, DEFAULT); -- ok -INSERT INTO gtest1v VALUES (6, 66), (7, 77); -- error -ERROR: cannot insert a non-DEFAULT value into column "b" -DETAIL: Column "b" is a generated column. -INSERT INTO gtest1v VALUES (6, DEFAULT), (7, 77); -- error -ERROR: cannot insert a non-DEFAULT value into column "b" -DETAIL: Column "b" is a generated column. -INSERT INTO gtest1v VALUES (6, 66), (7, DEFAULT); -- error -ERROR: cannot insert a non-DEFAULT value into column "b" -DETAIL: Column "b" is a generated column. -INSERT INTO gtest1v VALUES (6, DEFAULT), (7, DEFAULT); -- ok -ALTER VIEW gtest1v ALTER COLUMN b SET DEFAULT 100; -INSERT INTO gtest1v VALUES (8, DEFAULT); -- error -ERROR: cannot insert a non-DEFAULT value into column "b" -DETAIL: Column "b" is a generated column. -INSERT INTO gtest1v VALUES (8, DEFAULT), (9, DEFAULT); -- error -ERROR: cannot insert a non-DEFAULT value into column "b" -DETAIL: Column "b" is a generated column. -SELECT * FROM gtest1v; - a | b ----+---- - 3 | 6 - 5 | 10 - 6 | 12 - 7 | 14 -(4 rows) - -DELETE FROM gtest1v WHERE a >= 5; DROP VIEW gtest1v; -- CTEs WITH foo AS (SELECT * FROM gtest1) SELECT * FROM foo; @@ -256,6 +236,26 @@ SELECT * FROM gtest1_1; (0 rows) \d gtest1_1 +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Queries on master-only tables +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Queries on master-only tables +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation Table "public.gtest1_1" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+------------------------------------ @@ -271,81 +271,20 @@ SELECT * FROM gtest1_1; (1 row) SELECT * FROM gtest1; +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Inherited tables a | b ---+--- 3 | 6 4 | 8 (2 rows) -CREATE TABLE gtest_normal (a int, b int); -CREATE TABLE gtest_normal_child (a int, b int GENERATED ALWAYS AS (a * 2) STORED) INHERITS (gtest_normal); -NOTICE: merging column "a" with inherited definition -NOTICE: merging column "b" with inherited definition -\d gtest_normal_child - Table "public.gtest_normal_child" - Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+------------------------------------ - a | integer | | | - b | integer | | | generated always as (a * 2) stored -Inherits: gtest_normal - -INSERT INTO gtest_normal (a) VALUES (1); -INSERT INTO gtest_normal_child (a) VALUES (2); -SELECT * FROM gtest_normal; - a | b ----+--- - 1 | - 2 | 4 -(2 rows) - -CREATE TABLE gtest_normal_child2 (a int, b int GENERATED ALWAYS AS (a * 3) STORED); -ALTER TABLE gtest_normal_child2 INHERIT gtest_normal; -INSERT INTO gtest_normal_child2 (a) VALUES (3); -SELECT * FROM gtest_normal; - a | b ----+--- - 1 | - 2 | 4 - 3 | 9 -(3 rows) - --- test inheritance mismatches between parent and child -CREATE TABLE gtestx (x int, b int GENERATED ALWAYS AS (a * 22) STORED) INHERITS (gtest1); -- error -NOTICE: merging column "b" with inherited definition -ERROR: child column "b" specifies generation expression -HINT: Omit the generation expression in the definition of the child table column to inherit the generation expression from the parent table. -CREATE TABLE gtestx (x int, b int DEFAULT 10) INHERITS (gtest1); -- error -NOTICE: merging column "b" with inherited definition -ERROR: column "b" inherits from generated column but specifies default -CREATE TABLE gtestx (x int, b int GENERATED ALWAYS AS IDENTITY) INHERITS (gtest1); -- error -NOTICE: merging column "b" with inherited definition -ERROR: column "b" inherits from generated column but specifies identity -CREATE TABLE gtestxx_1 (a int NOT NULL, b int); -ALTER TABLE gtestxx_1 INHERIT gtest1; -- error -ERROR: column "b" in child table must be a generated column -CREATE TABLE gtestxx_2 (a int NOT NULL, b int GENERATED ALWAYS AS (a * 22) STORED); -ALTER TABLE gtestxx_2 INHERIT gtest1; -- error -ERROR: column "b" in child table has a conflicting generation expression -CREATE TABLE gtestxx_3 (a int NOT NULL, b int GENERATED ALWAYS AS (a * 2) STORED); -ALTER TABLE gtestxx_3 INHERIT gtest1; -- ok -CREATE TABLE gtestxx_4 (b int GENERATED ALWAYS AS (a * 2) STORED, a int NOT NULL); -ALTER TABLE gtestxx_4 INHERIT gtest1; -- ok --- test multiple inheritance mismatches +-- test inheritance mismatch CREATE TABLE gtesty (x int, b int); CREATE TABLE gtest1_2 () INHERITS (gtest1, gtesty); -- error NOTICE: merging multiple inherited definitions of column "b" ERROR: inherited column "b" has a generation conflict DROP TABLE gtesty; -CREATE TABLE gtesty (x int, b int GENERATED ALWAYS AS (x * 22) STORED); -CREATE TABLE gtest1_2 () INHERITS (gtest1, gtesty); -- error -NOTICE: merging multiple inherited definitions of column "b" -ERROR: column "b" inherits conflicting generation expressions -DROP TABLE gtesty; -CREATE TABLE gtesty (x int, b int DEFAULT 55); -CREATE TABLE gtest1_2 () INHERITS (gtest0, gtesty); -- error -NOTICE: merging multiple inherited definitions of column "b" -ERROR: inherited column "b" has a generation conflict -DROP TABLE gtesty; -- test stored update CREATE TABLE gtest3 (a int, b int GENERATED ALWAYS AS (a * 3) STORED); INSERT INTO gtest3 (a) VALUES (1), (2), (3), (NULL); @@ -392,6 +331,8 @@ SELECT * FROM gtest3a ORDER BY a; -- COPY TRUNCATE gtest1; INSERT INTO gtest1 (a) VALUES (1), (2); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Inherited tables COPY gtest1 TO stdout; 1 2 @@ -403,6 +344,8 @@ COPY gtest1 (a, b) FROM stdin; ERROR: column "b" is a generated column DETAIL: Generated columns cannot be used in COPY. SELECT * FROM gtest1 ORDER BY a; +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Inherited tables a | b ---+--- 1 | 2 @@ -441,18 +384,6 @@ SELECT * FROM gtest2; 1 | (1 row) --- simple column reference for varlena types -CREATE TABLE gtest_varlena (a varchar, b varchar GENERATED ALWAYS AS (a) STORED); -INSERT INTO gtest_varlena (a) VALUES('01234567890123456789'); -INSERT INTO gtest_varlena (a) VALUES(NULL); -SELECT * FROM gtest_varlena ORDER BY a; - a | b -----------------------+---------------------- - 01234567890123456789 | 01234567890123456789 - | -(2 rows) - -DROP TABLE gtest_varlena; -- composite types CREATE TYPE double_int as (a int, b int); CREATE TABLE gtest4 ( @@ -472,22 +403,42 @@ DROP TYPE double_int; -- using tableoid is allowed CREATE TABLE gtest_tableoid ( a int PRIMARY KEY, - b bool GENERATED ALWAYS AS (tableoid = 'gtest_tableoid'::regclass) STORED + b bool GENERATED ALWAYS AS (tableoid <> 0) STORED ); INSERT INTO gtest_tableoid VALUES (1), (2); -ALTER TABLE gtest_tableoid ADD COLUMN - c regclass GENERATED ALWAYS AS (tableoid) STORED; SELECT * FROM gtest_tableoid; - a | b | c ----+---+---------------- - 1 | t | gtest_tableoid - 2 | t | gtest_tableoid + a | b +---+--- + 1 | t + 2 | t (2 rows) -- drop column behavior CREATE TABLE gtest10 (a int PRIMARY KEY, b int, c int GENERATED ALWAYS AS (b * 2) STORED); ALTER TABLE gtest10 DROP COLUMN b; \d gtest10 +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Queries on master-only tables +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Queries on master-only tables +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation Table "public.gtest10" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- @@ -541,24 +492,24 @@ CREATE TABLE gtest20a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STOR INSERT INTO gtest20a (a) VALUES (10); INSERT INTO gtest20a (a) VALUES (30); ALTER TABLE gtest20a ADD CHECK (b < 50); -- fails on existing row -ERROR: check constraint "gtest20a_b_check" of relation "gtest20a" is violated by some row +ERROR: check constraint "gtest20a_b_check" is violated by some row CREATE TABLE gtest20b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STORED); INSERT INTO gtest20b (a) VALUES (10); INSERT INTO gtest20b (a) VALUES (30); ALTER TABLE gtest20b ADD CONSTRAINT chk CHECK (b < 50) NOT VALID; ALTER TABLE gtest20b VALIDATE CONSTRAINT chk; -- fails on existing row -ERROR: check constraint "chk" of relation "gtest20b" is violated by some row +ERROR: check constraint "chk" is violated by some row -- not-null constraints CREATE TABLE gtest21a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0)) STORED NOT NULL); INSERT INTO gtest21a (a) VALUES (1); -- ok INSERT INTO gtest21a (a) VALUES (0); -- violates constraint -ERROR: null value in column "b" of relation "gtest21a" violates not-null constraint +ERROR: null value in column "b" violates not-null constraint DETAIL: Failing row contains (0, null). CREATE TABLE gtest21b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0)) STORED); ALTER TABLE gtest21b ALTER COLUMN b SET NOT NULL; INSERT INTO gtest21b (a) VALUES (1); -- ok INSERT INTO gtest21b (a) VALUES (0); -- violates constraint -ERROR: null value in column "b" of relation "gtest21b" violates not-null constraint +ERROR: null value in column "b" violates not-null constraint DETAIL: Failing row contains (0, null). ALTER TABLE gtest21b ALTER COLUMN b DROP NOT NULL; INSERT INTO gtest21b (a) VALUES (0); -- ok now @@ -580,6 +531,28 @@ CREATE INDEX gtest22c_b_idx ON gtest22c (b); CREATE INDEX gtest22c_expr_idx ON gtest22c ((b * 3)); CREATE INDEX gtest22c_pred_idx ON gtest22c (a) WHERE b > 0; \d gtest22c +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Queries on master-only tables +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Queries on master-only tables +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation Table "public.gtest22c" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+------------------------------------ @@ -599,7 +572,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b = 4; Gather Motion 3:1 (slice1; segments: 3) -> Index Scan using gtest22c_b_idx on gtest22c Index Cond: (b = 4) - Optimizer: Postgres query optimizer + Optimizer: Pivotal Optimizer (GPORCA) (4 rows) SELECT * FROM gtest22c WHERE b = 4; @@ -609,12 +582,12 @@ SELECT * FROM gtest22c WHERE b = 4; (1 row) EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b * 3 = 6; - QUERY PLAN ------------------------------------------------------- + QUERY PLAN +------------------------------------------ Gather Motion 3:1 (slice1; segments: 3) - -> Index Scan using gtest22c_expr_idx on gtest22c - Index Cond: ((b * 3) = 6) - Optimizer: Postgres query optimizer + -> Seq Scan on gtest22c + Filter: ((b * 3) = 6) + Optimizer: Pivotal Optimizer (GPORCA) (4 rows) SELECT * FROM gtest22c WHERE b * 3 = 6; @@ -624,13 +597,14 @@ SELECT * FROM gtest22c WHERE b * 3 = 6; (1 row) EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE a = 1 AND b > 0; - QUERY PLAN ------------------------------------------------------- + QUERY PLAN +--------------------------------------------------- Gather Motion 1:1 (slice1; segments: 1) - -> Index Scan using gtest22c_pred_idx on gtest22c - Index Cond: (a = 1) - Optimizer: Postgres query optimizer -(4 rows) + -> Index Scan using gtest22c_b_idx on gtest22c + Index Cond: (b > 0) + Filter: (a = 1) + Optimizer: Pivotal Optimizer (GPORCA) +(5 rows) SELECT * FROM gtest22c WHERE a = 1 AND b > 0; a | b @@ -649,6 +623,34 @@ CREATE TABLE gtest23x (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STOR ERROR: invalid ON DELETE action for foreign key constraint containing generated column CREATE TABLE gtest23b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STORED REFERENCES gtest23a (x)); \d gtest23b +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Queries on master-only tables +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Queries on master-only tables +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Queries on master-only tables +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Queries on master-only tables +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Queries on master-only tables +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation Table "public.gtest23b" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+------------------------------------ @@ -732,185 +734,82 @@ ERROR: cannot use generated column "b" in column generation expression DETAIL: A generated column cannot reference another generated column. ALTER TABLE gtest25 ADD COLUMN x int GENERATED ALWAYS AS (z * 4) STORED; -- error ERROR: column "z" does not exist -ALTER TABLE gtest25 ADD COLUMN c int DEFAULT 42, - ADD COLUMN x int GENERATED ALWAYS AS (c * 4) STORED; -ALTER TABLE gtest25 ADD COLUMN d int DEFAULT 101; -ALTER TABLE gtest25 ALTER COLUMN d SET DATA TYPE float8, - ADD COLUMN y float8 GENERATED ALWAYS AS (d * 4) STORED; -SELECT * FROM gtest25 ORDER BY a; - a | b | c | x | d | y ----+----+----+-----+-----+----- - 3 | 9 | 42 | 168 | 101 | 404 - 4 | 12 | 42 | 168 | 101 | 404 -(2 rows) - -\d gtest25 - Table "public.gtest25" - Column | Type | Collation | Nullable | Default ---------+------------------+-----------+----------+------------------------------------------------------ - a | integer | | not null | - b | integer | | | generated always as (a * 3) stored - c | integer | | | 42 - x | integer | | | generated always as (c * 4) stored - d | double precision | | | 101 - y | double precision | | | generated always as (d * 4::double precision) stored -Indexes: - "gtest25_pkey" PRIMARY KEY, btree (a) - -- ALTER TABLE ... ALTER COLUMN CREATE TABLE gtest27 ( a int, - b int, - x int GENERATED ALWAYS AS ((a + b) * 2) STORED + b int GENERATED ALWAYS AS (a * 2) STORED ); ALTER TABLE gtest27 SET DISTRIBUTED RANDOMLY; -INSERT INTO gtest27 (a, b) VALUES (3, 7), (4, 11); +INSERT INTO gtest27 (a) VALUES (3), (4); ALTER TABLE gtest27 ALTER COLUMN a TYPE text; -- error ERROR: cannot alter type of a column used by a generated column -DETAIL: Column "a" is used by generated column "x". -ALTER TABLE gtest27 ALTER COLUMN x TYPE numeric; +DETAIL: Column "a" is used by generated column "b". +ALTER TABLE gtest27 ALTER COLUMN b TYPE numeric; \d gtest27 - Table "public.gtest27" - Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+-------------------------------------------- +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Queries on master-only tables +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Queries on master-only tables +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation + Table "public.gtest27" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+-------------------------------------- a | integer | | | - b | integer | | | - x | numeric | | | generated always as (((a + b) * 2)) stored + b | numeric | | | generated always as ((a * 2)) stored SELECT * FROM gtest27; - a | b | x ----+----+---- - 3 | 7 | 20 - 4 | 11 | 30 + a | b +---+--- + 3 | 6 + 4 | 8 (2 rows) -ALTER TABLE gtest27 ALTER COLUMN x TYPE boolean USING x <> 0; -- error -ERROR: generation expression for column "x" cannot be cast automatically to type boolean -ALTER TABLE gtest27 ALTER COLUMN x DROP DEFAULT; -- error -ERROR: column "x" of relation "gtest27" is a generated column -HINT: Use ALTER TABLE ... ALTER COLUMN ... DROP EXPRESSION instead. --- It's possible to alter the column types this way: -ALTER TABLE gtest27 - DROP COLUMN x, - ALTER COLUMN a TYPE bigint, - ALTER COLUMN b TYPE bigint, - ADD COLUMN x bigint GENERATED ALWAYS AS ((a + b) * 2) STORED; -\d gtest27 - Table "public.gtest27" - Column | Type | Collation | Nullable | Default ---------+--------+-----------+----------+------------------------------------------ - a | bigint | | | - b | bigint | | | - x | bigint | | | generated always as ((a + b) * 2) stored - --- Ideally you could just do this, but not today (and should x change type?): -ALTER TABLE gtest27 - ALTER COLUMN a TYPE float8, - ALTER COLUMN b TYPE float8; -- error -ERROR: cannot alter type of a column used by a generated column -DETAIL: Column "a" is used by generated column "x". +ALTER TABLE gtest27 ALTER COLUMN b TYPE boolean USING b <> 0; -- error +ERROR: generation expression for column "b" cannot be cast automatically to type boolean +ALTER TABLE gtest27 ALTER COLUMN b DROP DEFAULT; -- error +ERROR: column "b" of relation "gtest27" is a generated column \d gtest27 - Table "public.gtest27" - Column | Type | Collation | Nullable | Default ---------+--------+-----------+----------+------------------------------------------ - a | bigint | | | - b | bigint | | | - x | bigint | | | generated always as ((a + b) * 2) stored - -SELECT * FROM gtest27; - a | b | x ----+----+---- - 3 | 7 | 20 - 4 | 11 | 30 -(2 rows) - --- ALTER TABLE ... ALTER COLUMN ... DROP EXPRESSION -CREATE TABLE gtest29 ( - a int, - b int GENERATED ALWAYS AS (a * 2) STORED -); -INSERT INTO gtest29 (a) VALUES (3), (4); -ALTER TABLE gtest29 ALTER COLUMN a DROP EXPRESSION; -- error -ERROR: column "a" of relation "gtest29" is not a stored generated column -ALTER TABLE gtest29 ALTER COLUMN a DROP EXPRESSION IF EXISTS; -- notice -NOTICE: column "a" of relation "gtest29" is not a stored generated column, skipping -ALTER TABLE gtest29 ALTER COLUMN b DROP EXPRESSION; -INSERT INTO gtest29 (a) VALUES (5); -INSERT INTO gtest29 (a, b) VALUES (6, 66); -SELECT * FROM gtest29; - a | b ----+---- - 3 | 6 - 4 | 8 - 5 | - 6 | 66 -(4 rows) - -\d gtest29 - Table "public.gtest29" - Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Queries on master-only tables +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Queries on master-only tables +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation + Table "public.gtest27" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+-------------------------------------- a | integer | | | - b | integer | | | - --- check that dependencies between columns have also been removed -ALTER TABLE gtest29 DROP COLUMN a; -- should not drop b -\d gtest29 - Table "public.gtest29" - Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- - b | integer | | | - --- with inheritance -CREATE TABLE gtest30 ( - a int, - b int GENERATED ALWAYS AS (a * 2) STORED -); -CREATE TABLE gtest30_1 () INHERITS (gtest30); -ALTER TABLE gtest30 ALTER COLUMN b DROP EXPRESSION; -\d gtest30 - Table "public.gtest30" - Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- - a | integer | | | - b | integer | | | -Number of child tables: 1 (Use \d+ to list them.) - -\d gtest30_1 - Table "public.gtest30_1" - Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- - a | integer | | | - b | integer | | | -Inherits: gtest30 - -DROP TABLE gtest30 CASCADE; -NOTICE: drop cascades to table gtest30_1 -CREATE TABLE gtest30 ( - a int, - b int GENERATED ALWAYS AS (a * 2) STORED -); -CREATE TABLE gtest30_1 () INHERITS (gtest30); -ALTER TABLE ONLY gtest30 ALTER COLUMN b DROP EXPRESSION; -- error -ERROR: ALTER TABLE / DROP EXPRESSION must be applied to child tables too -\d gtest30 - Table "public.gtest30" - Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+------------------------------------ - a | integer | | | - b | integer | | | generated always as (a * 2) stored -Number of child tables: 1 (Use \d+ to list them.) - -\d gtest30_1 - Table "public.gtest30_1" - Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+------------------------------------ - a | integer | | | - b | integer | | | generated always as (a * 2) stored -Inherits: gtest30 + b | numeric | | | generated always as ((a * 2)) stored -ALTER TABLE gtest30_1 ALTER COLUMN b DROP EXPRESSION; -- error -ERROR: cannot drop generation expression from inherited column -- triggers CREATE TABLE gtest26 ( a int PRIMARY KEY, @@ -996,6 +895,8 @@ INFO: gtest4: AFTER: new = (-2,-4,) (seg0 127.0.1.1:7002 pid=179471) alter table gtest26 drop column distkey; NOTICE: dropping a column that is part of the distribution policy forces a NULL distribution policy UPDATE gtest26 SET a = a * -2; +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: UPDATE on a table with UPDATE triggers INFO: gtest1: BEFORE: old = (-2,-4) (seg0 127.0.1.1:7002 pid=179471) INFO: gtest1: BEFORE: new = (4,) (seg0 127.0.1.1:7002 pid=179471) INFO: gtest3: AFTER: old = (-2,-4) (seg0 127.0.1.1:7002 pid=179471) @@ -1037,6 +938,8 @@ CREATE TRIGGER gtest11 BEFORE UPDATE OF b ON gtest26 FOR EACH ROW EXECUTE PROCEDURE gtest_trigger_func3(); UPDATE gtest26 SET a = 1 WHERE a = 0; +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: UPDATE on a table with UPDATE triggers NOTICE: OK DROP TRIGGER gtest11 ON gtest26; TRUNCATE gtest26; @@ -1062,6 +965,8 @@ CREATE TRIGGER gtest12_03 BEFORE UPDATE ON gtest26 EXECUTE PROCEDURE gtest_trigger_func(); INSERT INTO gtest26 (a) VALUES (1); UPDATE gtest26 SET a = 11 WHERE a = 1; +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: UPDATE on a table with UPDATE triggers INFO: gtest12_01: BEFORE: old = (1,2) INFO: gtest12_01: BEFORE: new = (11,) INFO: gtest12_03: BEFORE: old = (1,2) @@ -1082,6 +987,26 @@ CREATE TABLE gtest28a ( ALTER TABLE gtest28a DROP COLUMN a; CREATE TABLE gtest28b (LIKE gtest28a INCLUDING GENERATED); \d gtest28* +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Queries on master-only tables +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Queries on master-only tables +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation Table "public.gtest28a" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+------------------------------------ @@ -1090,6 +1015,24 @@ CREATE TABLE gtest28b (LIKE gtest28a INCLUDING GENERATED); x | integer | | | generated always as (b * 2) stored Distributed randomly +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Queries on master-only tables +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Queries on master-only tables +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Non-default collation Table "public.gtest28b" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+------------------------------------ @@ -1098,3 +1041,4 @@ Distributed randomly x | integer | | | generated always as (b * 2) stored Distributed randomly +reset optimizer_trace_fallback; diff --git a/src/test/regress/sql/generated.sql b/src/test/regress/sql/generated.sql index 6392458370..bfe655bdf5 100644 --- a/src/test/regress/sql/generated.sql +++ b/src/test/regress/sql/generated.sql @@ -1,3 +1,4 @@ +set optimizer_trace_fallback=on; -- sanity check of system catalog SELECT attrelid, attname, attgenerated FROM pg_attribute WHERE attgenerated NOT IN ('', 's'); @@ -605,3 +606,4 @@ ALTER TABLE gtest28a DROP COLUMN a; CREATE TABLE gtest28b (LIKE gtest28a INCLUDING GENERATED); \d gtest28* +reset optimizer_trace_fallback; --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
