This is an automated email from the ASF dual-hosted git repository.
chenjinbao1989 pushed a commit to branch cbdb-postgres-merge
in repository https://gitbox.apache.org/repos/asf/cloudberry.git
The following commit(s) were added to refs/heads/cbdb-postgres-merge by this
push:
new 592addf7a86 The single node test using planner has been fixed
592addf7a86 is described below
commit 592addf7a867e23d7c048c4dae20c029579137f9
Author: Jinbao Chen <[email protected]>
AuthorDate: Mon Apr 6 11:20:22 2026 -0400
The single node test using planner has been fixed
---
src/test/singlenode_regress/Makefile | 9 +-
src/test/singlenode_regress/expected/cluster.out | 1 -
.../expected/collate.icu.utf8_1.out | 2 +-
.../singlenode_regress/expected/create_view.out | 36 +-
.../singlenode_regress/expected/dependency.out | 4 +
.../singlenode_regress/expected/foreign_data.out | 57 +-
src/test/singlenode_regress/expected/generated.out | 62 +-
.../singlenode_regress/expected/partition_join.out | 30 +-
.../singlenode_regress/expected/privileges.out | 168 ++--
.../expected/select_parallel.out | 882 +++++++++------------
src/test/singlenode_regress/expected/with.out | 2 +-
src/test/singlenode_regress/input/createdb.source | 10 +-
src/test/singlenode_regress/output/createdb.source | 12 +-
src/test/singlenode_regress/output/gpcopy.source | 3 +-
src/test/singlenode_regress/sql/create_view.sql | 27 -
15 files changed, 586 insertions(+), 719 deletions(-)
diff --git a/src/test/singlenode_regress/Makefile
b/src/test/singlenode_regress/Makefile
index 185bf13ae68..2fdfc203036 100644
--- a/src/test/singlenode_regress/Makefile
+++ b/src/test/singlenode_regress/Makefile
@@ -38,19 +38,22 @@ clean distclean:
## Run tests
##
-REGRESS_OPTS = --dlpath=. --max-concurrent-tests=24
--init-file=$(srcdir)/init_file --make-testtablespace-dir \
+REGRESS_OPTS = --dlpath=. --max-concurrent-tests=24
--init-file=$(srcdir)/init_file \
$(EXTRA_REGRESS_OPTS)
+tablespace-setup:
+ rm -rf ./testtablespace
+ mkdir -p ./testtablespace
installcheck-singlenode: installcheck-good-singlenode
-installcheck-small-singlenode: all
+installcheck-small-singlenode: all tablespace-setup
(\
gpconfig -c gp_appendonly_insert_files -v 0; \
gpstop -u; \
$(pg_regress_installcheck) $(REGRESS_OPTS)
--schedule=$(srcdir)/parallel_schedule $(EXTRA_TESTS) \
)
-installcheck-good-singlenode: all
+installcheck-good-singlenode: all tablespace-setup
(\
gpconfig -c gp_appendonly_insert_files -v 0; \
gpstop -u; \
diff --git a/src/test/singlenode_regress/expected/cluster.out
b/src/test/singlenode_regress/expected/cluster.out
index 88c90d22578..c2abef9fe56 100644
--- a/src/test/singlenode_regress/expected/cluster.out
+++ b/src/test/singlenode_regress/expected/cluster.out
@@ -448,7 +448,6 @@ CREATE INDEX clstrpart_idx ON clstrpart (a);
ALTER TABLE clstrpart CLUSTER ON clstrpart_idx;
ERROR: cannot mark index clustered in partitioned table
CLUSTER clstrpart USING clstrpart_idx;
-ERROR: cannot cluster a partitioned table
DROP TABLE clstrpart;
-- Test CLUSTER with external tuplesorting
create table clstr_4 as select * from tenk1;
diff --git a/src/test/singlenode_regress/expected/collate.icu.utf8_1.out
b/src/test/singlenode_regress/expected/collate.icu.utf8_1.out
index a6a33b39aba..25c99c4abf0 100644
--- a/src/test/singlenode_regress/expected/collate.icu.utf8_1.out
+++ b/src/test/singlenode_regress/expected/collate.icu.utf8_1.out
@@ -3,7 +3,7 @@
*/
/* skip test if not UTF8 server encoding or no ICU collations installed */
SELECT getdatabaseencoding() <> 'UTF8' OR
- (SELECT count(*) FROM pg_collation WHERE collprovider = 'i') = 0
+ (SELECT count(*) FROM pg_collation WHERE collprovider = 'i' AND
collname <> 'unicode') = 0
AS skip_test \gset
\if :skip_test
\quit
diff --git a/src/test/singlenode_regress/expected/create_view.out
b/src/test/singlenode_regress/expected/create_view.out
index d9f97043b64..84533660331 100644
--- a/src/test/singlenode_regress/expected/create_view.out
+++ b/src/test/singlenode_regress/expected/create_view.out
@@ -8,27 +8,6 @@ set Debug_print_plan=on;
-- Virtual class definitions
-- (this also tests the query rewrite system)
--
--- directory paths and dlsuffix are passed to us in environment variables
-\getenv abs_srcdir PG_ABS_SRCDIR
-\getenv libdir PG_LIBDIR
-\getenv dlsuffix PG_DLSUFFIX
-\set regresslib :libdir '/regress' :dlsuffix
-CREATE FUNCTION interpt_pp(path, path)
- RETURNS point
- AS :'regresslib'
- LANGUAGE C STRICT;
-CREATE TABLE real_city (
- pop int4,
- cname text,
- outline path
-);
-\set filename :abs_srcdir '/data/real_city.data'
-COPY real_city FROM :'filename';
-ANALYZE real_city;
-SELECT *
- INTO TABLE ramp
- FROM ONLY road
- WHERE name ~ '.*Ramp';
CREATE VIEW street AS
SELECT r.name, r.thepath, c.cname AS cname
FROM ONLY road r, real_city c
@@ -1876,14 +1855,13 @@ select pg_get_viewdef('tt18v', true);
(1 row)
explain (costs off) select * from tt18v;
- QUERY PLAN
---------------------------------------------------------------------------------------------------
- Gather Motion 3:1 (slice1; segments: 3)
- -> Append
- -> Seq Scan on int8_tbl
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
- -> Seq Scan on int8_tbl
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx_1
+ QUERY PLAN
+--------------------------------------------------------------------------------------------
+ Append
+ -> Seq Scan on int8_tbl
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
+ -> Seq Scan on int8_tbl
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx_1
Optimizer: Postgres query optimizer
-(5 rows)
+(4 rows)
-- check display of ScalarArrayOp with a sub-select
select 'foo'::text = any(array['abc','def','foo']::text[]);
@@ -2166,7 +2144,7 @@ select * from tt24v;
Hash Join
Output: (share0_ref1.r).column2, ((ROW("*VALUES*_1".column1,
"*VALUES*_1".column2))).column2
Hash Cond: ((share0_ref1.r).column1 = ((ROW("*VALUES*_1".column1,
"*VALUES*_1".column2))).column1)
- -> Shared Scan (share slice:id 0:0)
+ -> Shared Scan (share slice:id -1:0)
Output: share0_ref1.r
-> Values Scan on "*VALUES*"
Output: ROW("*VALUES*".column1, "*VALUES*".column2)
diff --git a/src/test/singlenode_regress/expected/dependency.out
b/src/test/singlenode_regress/expected/dependency.out
index 8c6612f8f74..32a445923fc 100644
--- a/src/test/singlenode_regress/expected/dependency.out
+++ b/src/test/singlenode_regress/expected/dependency.out
@@ -48,12 +48,16 @@ SET SESSION AUTHORIZATION regress_dep_user0;
-- permission denied
DROP OWNED BY regress_dep_user1;
ERROR: permission denied to drop objects
+DETAIL: Only roles with privileges of role "regress_dep_user1" may drop
objects owned by it.
DROP OWNED BY regress_dep_user0, regress_dep_user2;
ERROR: permission denied to drop objects
+DETAIL: Only roles with privileges of role "regress_dep_user2" may drop
objects owned by it.
REASSIGN OWNED BY regress_dep_user0 TO regress_dep_user1;
ERROR: permission denied to reassign objects
+DETAIL: Only roles with privileges of role "regress_dep_user1" may reassign
objects to it.
REASSIGN OWNED BY regress_dep_user1 TO regress_dep_user0;
ERROR: permission denied to reassign objects
+DETAIL: Only roles with privileges of role "regress_dep_user1" may reassign
objects owned by it.
-- this one is allowed
DROP OWNED BY regress_dep_user0;
CREATE TABLE deptest1 (f1 int unique);
diff --git a/src/test/singlenode_regress/expected/foreign_data.out
b/src/test/singlenode_regress/expected/foreign_data.out
index 47275f6f1a2..018bdd722e1 100644
--- a/src/test/singlenode_regress/expected/foreign_data.out
+++ b/src/test/singlenode_regress/expected/foreign_data.out
@@ -109,6 +109,8 @@ CREATE FOREIGN DATA WRAPPER test_fdw HANDLER
invalid_fdw_handler; -- ERROR
ERROR: function invalid_fdw_handler must return type fdw_handler
CREATE FOREIGN DATA WRAPPER test_fdw HANDLER test_fdw_handler HANDLER
invalid_fdw_handler; -- ERROR
ERROR: conflicting or redundant options
+LINE 1: ...GN DATA WRAPPER test_fdw HANDLER test_fdw_handler HANDLER in...
+ ^
CREATE FOREIGN DATA WRAPPER test_fdw HANDLER test_fdw_handler;
DROP FOREIGN DATA WRAPPER test_fdw;
-- ALTER FOREIGN DATA WRAPPER
@@ -215,6 +217,8 @@ ALTER FOREIGN DATA WRAPPER foo HANDLER invalid_fdw_handler;
-- ERROR
ERROR: function invalid_fdw_handler must return type fdw_handler
ALTER FOREIGN DATA WRAPPER foo HANDLER test_fdw_handler HANDLER anything; --
ERROR
ERROR: conflicting or redundant options
+LINE 1: ...FOREIGN DATA WRAPPER foo HANDLER test_fdw_handler HANDLER an...
+ ^
ALTER FOREIGN DATA WRAPPER foo HANDLER test_fdw_handler;
WARNING: changing the foreign-data wrapper handler can change behavior of
existing foreign tables
DROP FUNCTION invalid_fdw_handler();
@@ -328,7 +332,6 @@ CREATE SERVER s6 VERSION '16.0' FOREIGN DATA WRAPPER foo
OPTIONS (host 'a', dbna
CREATE SERVER s7 TYPE 'oracle' VERSION '17.0' FOREIGN DATA WRAPPER foo OPTIONS
(host 'a', dbname 'b');
CREATE SERVER s8 FOREIGN DATA WRAPPER postgresql OPTIONS (foo '1'); -- ERROR
ERROR: invalid option "foo"
-HINT: Valid options in this context are: authtype, service, connect_timeout,
dbname, host, hostaddr, port, tty, options, requiressl, sslmode, gsslib
CREATE SERVER s8 FOREIGN DATA WRAPPER postgresql OPTIONS (host 'localhost',
dbname 's8db');
\des+ :NO_BUILTINS
List of foreign
servers
@@ -439,11 +442,10 @@ ERROR: permission denied for foreign-data wrapper foo
RESET ROLE;
ALTER SERVER s8 OPTIONS (foo '1'); -- ERROR option
validation
ERROR: invalid option "foo"
-HINT: Valid options in this context are: authtype, service, connect_timeout,
dbname, host, hostaddr, port, tty, options, requiressl, sslmode, gsslib
ALTER SERVER s8 OPTIONS (connect_timeout '30', SET dbname 'db1', DROP host);
SET ROLE regress_test_role;
ALTER SERVER s1 OWNER TO regress_test_indirect; -- ERROR
-ERROR: must be member of role "regress_test_indirect"
+ERROR: must be able to SET ROLE "regress_test_indirect"
RESET ROLE;
GRANT regress_test_indirect TO regress_test_role;
SET ROLE regress_test_role;
@@ -596,7 +598,7 @@ ERROR: user mapping for "regress_foreign_data_user"
already exists for server "
CREATE USER MAPPING FOR public SERVER s4 OPTIONS ("this mapping" 'is public');
CREATE USER MAPPING FOR user SERVER s8 OPTIONS (username 'test', password
'secret'); -- ERROR
ERROR: invalid option "username"
-HINT: Valid options in this context are: user, password
+HINT: Perhaps you meant the option "user".
CREATE USER MAPPING FOR user SERVER s8 OPTIONS (user 'test', password
'secret');
ALTER SERVER s5 OWNER TO regress_test_role;
ALTER SERVER s6 OWNER TO regress_test_indirect;
@@ -635,7 +637,7 @@ ALTER USER MAPPING FOR public SERVER s5 OPTIONS (gotcha
'true'); -- E
ERROR: user mapping for "public" does not exist for server "s5"
ALTER USER MAPPING FOR current_user SERVER s8 OPTIONS (username 'test'); --
ERROR
ERROR: invalid option "username"
-HINT: Valid options in this context are: user, password
+HINT: Perhaps you meant the option "user".
ALTER USER MAPPING FOR current_user SERVER s8 OPTIONS (DROP user, SET password
'public');
SET ROLE regress_test_role;
ALTER USER MAPPING FOR current_user SERVER s5 OPTIONS (ADD modified '1');
@@ -754,7 +756,8 @@ FDW options: (delimiter ',', quote '"', "be quoted" 'value')
(1 row)
CREATE INDEX id_ft1_c2 ON ft1 (c2); -- ERROR
-ERROR: cannot create index on foreign table "ft1"
+ERROR: cannot create index on relation "ft1"
+DETAIL: This operation is not supported for foreign tables.
SELECT * FROM ft1; -- ERROR
ERROR: foreign-data wrapper "dummy" has no handler
EXPLAIN SELECT * FROM ft1; -- ERROR
@@ -878,7 +881,8 @@ LINE 1: ALTER FOREIGN TABLE ft1 ADD PRIMARY KEY (c7);
^
ALTER FOREIGN TABLE ft1 ADD CONSTRAINT ft1_c9_check CHECK (c9 < 0) NOT VALID;
ALTER FOREIGN TABLE ft1 ALTER CONSTRAINT ft1_c9_check DEFERRABLE; -- ERROR
-ERROR: "ft1" is not a table
+ERROR: ALTER action ALTER CONSTRAINT cannot be performed on relation "ft1"
+DETAIL: This operation is not supported for foreign tables.
ALTER FOREIGN TABLE ft1 DROP CONSTRAINT ft1_c9_check;
ALTER FOREIGN TABLE ft1 DROP CONSTRAINT no_const; -- ERROR
ERROR: constraint "no_const" of relation "ft1" does not exist
@@ -895,7 +899,8 @@ ALTER FOREIGN TABLE ft1 SET SCHEMA foreign_schema;
ALTER FOREIGN TABLE ft1 SET TABLESPACE ts; -- ERROR
ERROR: relation "ft1" does not exist
ALTER FOREIGN TABLE foreign_schema.ft1 SET TABLESPACE ts; -- ERROR
-ERROR: "ft1" is not a table, materialized view, index, or partitioned index
+ERROR: ALTER action SET TABLESPACE cannot be performed on relation "ft1"
+DETAIL: This operation is not supported for foreign tables.
ALTER FOREIGN TABLE foreign_schema.ft1 RENAME c1 TO foreign_column_1;
ALTER FOREIGN TABLE foreign_schema.ft1 RENAME TO foreign_table_1;
\d foreign_schema.foreign_table_1
@@ -1411,7 +1416,7 @@ CREATE FOREIGN TABLE ft2 () INHERITS (fd_pt1)
c1 | integer | | not null | | plain | |
c2 | text | | | | extended | |
c3 | date | | | | plain | |
-Child tables: ft2
+Child tables: ft2, FOREIGN
\d+ ft2
Foreign table "public.ft2"
@@ -1456,7 +1461,7 @@ ALTER FOREIGN TABLE ft2 INHERIT fd_pt1;
c1 | integer | | not null | | plain | |
c2 | text | | | | extended | |
c3 | date | | | | plain | |
-Child tables: ft2
+Child tables: ft2, FOREIGN
\d+ ft2
Foreign table "public.ft2"
@@ -1491,7 +1496,7 @@ NOTICE: merging column "c3" with inherited definition
Server: s0
FDW options: (delimiter ',', quote '"', "be quoted" 'value')
Inherits: fd_pt1
-Child tables: ft3
+Child tables: ft3, FOREIGN
-- start_ignore
-- GPDB: ct3 is not created.
@@ -1525,7 +1530,7 @@ ALTER TABLE fd_pt1 ADD COLUMN c8 integer;
c6 | integer | | | | plain | |
c7 | integer | | not null | | plain | |
c8 | integer | | | | plain | |
-Child tables: ft2
+Child tables: ft2, FOREIGN
\d+ ft2
Foreign table "public.ft2"
@@ -1542,7 +1547,7 @@ Child tables: ft2
Server: s0
FDW options: (delimiter ',', quote '"', "be quoted" 'value')
Inherits: fd_pt1
-Child tables: ft3
+Child tables: ft3, FOREIGN
-- start_ignore
-- GPDB: ct3 is not created.
@@ -1588,7 +1593,7 @@ ALTER TABLE fd_pt1 ALTER COLUMN c8 SET STORAGE EXTERNAL;
c6 | integer | | not null | | plain | |
c7 | integer | | | | plain | |
c8 | text | | | | external | |
-Child tables: ft2
+Child tables: ft2, FOREIGN
\d+ ft2
Foreign table "public.ft2"
@@ -1605,7 +1610,7 @@ Child tables: ft2
Server: s0
FDW options: (delimiter ',', quote '"', "be quoted" 'value')
Inherits: fd_pt1
-Child tables: ft3
+Child tables: ft3, FOREIGN
-- drop attributes recursively
ALTER TABLE fd_pt1 DROP COLUMN c4;
@@ -1620,7 +1625,7 @@ ALTER TABLE fd_pt1 DROP COLUMN c8;
c1 | integer | | not null | | plain | 10000 |
c2 | text | | | | extended | |
c3 | date | | | | plain | |
-Child tables: ft2
+Child tables: ft2, FOREIGN
\d+ ft2
Foreign table "public.ft2"
@@ -1632,7 +1637,7 @@ Child tables: ft2
Server: s0
FDW options: (delimiter ',', quote '"', "be quoted" 'value')
Inherits: fd_pt1
-Child tables: ft3
+Child tables: ft3, FOREIGN
-- add constraints recursively
ALTER TABLE fd_pt1 ADD CONSTRAINT fd_pt1chk1 CHECK (c1 > 0) NO INHERIT;
@@ -1659,7 +1664,7 @@ SELECT relname, conname, contype, conislocal,
coninhcount, connoinherit
Check constraints:
"fd_pt1chk1" CHECK (c1 > 0) NO INHERIT
"fd_pt1chk2" CHECK (c2 <> ''::text)
-Child tables: ft2
+Child tables: ft2, FOREIGN
\d+ ft2
Foreign table "public.ft2"
@@ -1673,7 +1678,7 @@ Check constraints:
Server: s0
FDW options: (delimiter ',', quote '"', "be quoted" 'value')
Inherits: fd_pt1
-Child tables: ft3
+Child tables: ft3, FOREIGN
DROP FOREIGN TABLE ft2; -- ERROR
ERROR: cannot drop foreign table ft2 because other objects depend on it
@@ -1702,7 +1707,7 @@ ALTER FOREIGN TABLE ft2 INHERIT fd_pt1;
Check constraints:
"fd_pt1chk1" CHECK (c1 > 0) NO INHERIT
"fd_pt1chk2" CHECK (c2 <> ''::text)
-Child tables: ft2
+Child tables: ft2, FOREIGN
\d+ ft2
Foreign table "public.ft2"
@@ -1733,7 +1738,7 @@ ALTER TABLE fd_pt1 ADD CONSTRAINT fd_pt1chk3 CHECK (c2 <>
'') NOT VALID;
c3 | date | | | | plain | |
Check constraints:
"fd_pt1chk3" CHECK (c2 <> ''::text) NOT VALID
-Child tables: ft2
+Child tables: ft2, FOREIGN
\d+ ft2
Foreign table "public.ft2"
@@ -1760,7 +1765,7 @@ ALTER TABLE fd_pt1 VALIDATE CONSTRAINT fd_pt1chk3;
c3 | date | | | | plain | |
Check constraints:
"fd_pt1chk3" CHECK (c2 <> ''::text)
-Child tables: ft2
+Child tables: ft2, FOREIGN
\d+ ft2
Foreign table "public.ft2"
@@ -1791,7 +1796,7 @@ ALTER TABLE fd_pt1 RENAME CONSTRAINT fd_pt1chk3 TO
f2_check;
f3 | date | | | | plain | |
Check constraints:
"f2_check" CHECK (f2 <> ''::text)
-Child tables: ft2
+Child tables: ft2, FOREIGN
\d+ ft2
Foreign table "public.ft2"
@@ -1854,7 +1859,7 @@ CREATE FOREIGN TABLE fd_pt2_1 PARTITION OF fd_pt2 FOR
VALUES IN (1)
c2 | text | | | | extended | |
c3 | date | | | | plain | |
Partition key: LIST (c1)
-Partitions: fd_pt2_1 FOR VALUES IN (1)
+Partitions: fd_pt2_1 FOR VALUES IN (1), FOREIGN
\d+ fd_pt2_1
Foreign table "public.fd_pt2_1"
@@ -1926,7 +1931,7 @@ ALTER TABLE fd_pt2 ATTACH PARTITION fd_pt2_1 FOR VALUES
IN (1);
c2 | text | | | | extended | |
c3 | date | | | | plain | |
Partition key: LIST (c1)
-Partitions: fd_pt2_1 FOR VALUES IN (1)
+Partitions: fd_pt2_1 FOR VALUES IN (1), FOREIGN
\d+ fd_pt2_1
Foreign table "public.fd_pt2_1"
@@ -1954,7 +1959,7 @@ ALTER TABLE fd_pt2_1 ADD CONSTRAINT p21chk CHECK (c2 <>
'');
c2 | text | | | | extended | |
c3 | date | | | | plain | |
Partition key: LIST (c1)
-Partitions: fd_pt2_1 FOR VALUES IN (1)
+Partitions: fd_pt2_1 FOR VALUES IN (1), FOREIGN
\d+ fd_pt2_1
Foreign table "public.fd_pt2_1"
diff --git a/src/test/singlenode_regress/expected/generated.out
b/src/test/singlenode_regress/expected/generated.out
index 9e0422f92a7..f4a3fb2d2f4 100644
--- a/src/test/singlenode_regress/expected/generated.out
+++ b/src/test/singlenode_regress/expected/generated.out
@@ -268,43 +268,20 @@ SELECT * FROM gtest1;
4 | 8
(2 rows)
+-- can't have generated column that is a child of normal column
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);
+CREATE TABLE gtest_normal_child (a int, b int GENERATED ALWAYS AS (a * 2)
STORED) INHERITS (gtest_normal); -- error
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)
-
+ERROR: child column "b" specifies generation expression
+HINT: A child table column cannot be generated unless its parent column is.
+CREATE TABLE gtest_normal_child (a int, b int GENERATED ALWAYS AS (a * 2)
STORED);
+ALTER TABLE gtest_normal_child INHERIT gtest_normal; -- error
+ERROR: column "b" in child table must not be a generated column
+DROP TABLE gtest_normal, gtest_normal_child;
-- 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
@@ -316,7 +293,6 @@ 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);
@@ -331,6 +307,7 @@ 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
+HINT: To resolve the conflict, specify a generation expression explicitly.
DROP TABLE gtesty;
CREATE TABLE gtesty (x int, b int DEFAULT 55);
CREATE TABLE gtest1_2 () INHERITS (gtest0, gtesty); -- error
@@ -478,11 +455,16 @@ SELECT * FROM gtest_tableoid;
-- 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;
+ERROR: cannot drop column b of table gtest10 because other objects depend on
it
+DETAIL: column c of table gtest10 depends on column b of table gtest10
+HINT: Use DROP ... CASCADE to drop the dependent objects too.
\d gtest10
- Table "public.gtest10"
- Column | Type | Collation | Nullable | Default
---------+---------+-----------+----------+---------
+ Table "public.gtest10"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+------------------------------------
a | integer | | not null |
+ b | integer | | |
+ c | integer | | | generated always as (b * 2) stored
Indexes:
"gtest10_pkey" PRIMARY KEY, btree (a)
@@ -676,7 +658,8 @@ CREATE TABLE gtest_parent (f1 date NOT NULL, f2 text, f3
bigint) PARTITION BY RA
CREATE TABLE gtest_child PARTITION OF gtest_parent (
f3 WITH OPTIONS GENERATED ALWAYS AS (f2 * 2) STORED
) FOR VALUES FROM ('2016-07-01') TO ('2016-08-01'); -- error
-ERROR: generated columns are not supported on partitions
+ERROR: child column "f3" specifies generation expression
+HINT: A child table column cannot be generated unless its parent column is.
DROP TABLE gtest_parent;
-- partitioned table
CREATE TABLE gtest_parent (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED
ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE (f1);
@@ -774,7 +757,8 @@ SELECT * FROM gtest27;
(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
+ERROR: cannot specify USING when altering type of generated column
+DETAIL: Column "x" is a generated column.
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.
@@ -931,7 +915,7 @@ CREATE TRIGGER gtest2a BEFORE INSERT OR UPDATE ON gtest26
WHEN (NEW.b < 0) -- error
EXECUTE PROCEDURE gtest_trigger_func();
ERROR: BEFORE trigger's WHEN condition cannot reference NEW generated columns
-LINE 3: WHEN (NEW.b < 0)
+LINE 3: WHEN (NEW.b < 0) -- error
^
DETAIL: Column "b" is a generated column.
CREATE TRIGGER gtest2b BEFORE INSERT OR UPDATE ON gtest26
@@ -939,7 +923,7 @@ CREATE TRIGGER gtest2b BEFORE INSERT OR UPDATE ON gtest26
WHEN (NEW.* IS NOT NULL) -- error
EXECUTE PROCEDURE gtest_trigger_func();
ERROR: BEFORE trigger's WHEN condition cannot reference NEW generated columns
-LINE 3: WHEN (NEW.* IS NOT NULL)
+LINE 3: WHEN (NEW.* IS NOT NULL) -- error
^
DETAIL: A whole-row reference is used and the table contains generated
columns.
CREATE TRIGGER gtest2 BEFORE INSERT ON gtest26
diff --git a/src/test/singlenode_regress/expected/partition_join.out
b/src/test/singlenode_regress/expected/partition_join.out
index b64b0c8535b..f9f2213b87f 100644
--- a/src/test/singlenode_regress/expected/partition_join.out
+++ b/src/test/singlenode_regress/expected/partition_join.out
@@ -2441,24 +2441,24 @@ SELECT t1.* FROM prt1_adv t1 WHERE NOT EXISTS (SELECT 1
FROM prt2_adv t2 WHERE t
Sort
Sort Key: t1.a
-> Append
- -> Hash Anti Join
- Hash Cond: (t1_1.a = t2_1.b)
- -> Seq Scan on prt1_adv_p1 t1_1
- Filter: (b = 0)
+ -> Hash Right Anti Join
+ Hash Cond: (t2_1.b = t1_1.a)
+ -> Seq Scan on prt2_adv_p1 t2_1
-> Hash
- -> Seq Scan on prt2_adv_p1 t2_1
- -> Hash Anti Join
- Hash Cond: (t1_2.a = t2_2.b)
- -> Seq Scan on prt1_adv_p2 t1_2
- Filter: (b = 0)
+ -> Seq Scan on prt1_adv_p1 t1_1
+ Filter: (b = 0)
+ -> Hash Right Anti Join
+ Hash Cond: (t2_2.b = t1_2.a)
+ -> Seq Scan on prt2_adv_p2 t2_2
-> Hash
- -> Seq Scan on prt2_adv_p2 t2_2
- -> Hash Anti Join
- Hash Cond: (t1_3.a = t2_3.b)
- -> Seq Scan on prt1_adv_p3 t1_3
- Filter: (b = 0)
+ -> Seq Scan on prt1_adv_p2 t1_2
+ Filter: (b = 0)
+ -> Hash Right Anti Join
+ Hash Cond: (t2_3.b = t1_3.a)
+ -> Seq Scan on prt2_adv_p3 t2_3
-> Hash
- -> Seq Scan on prt2_adv_p3 t2_3
+ -> Seq Scan on prt1_adv_p3 t1_3
+ Filter: (b = 0)
Optimizer: Postgres query optimizer
(22 rows)
diff --git a/src/test/singlenode_regress/expected/privileges.out
b/src/test/singlenode_regress/expected/privileges.out
index 8edbe4a41d7..35fea0bc7f9 100644
--- a/src/test/singlenode_regress/expected/privileges.out
+++ b/src/test/singlenode_regress/expected/privileges.out
@@ -44,7 +44,7 @@ CREATE GROUP regress_priv_group1;
CREATE GROUP regress_priv_group2 WITH USER regress_priv_user1,
regress_priv_user2;
ALTER GROUP regress_priv_group1 ADD USER regress_priv_user4;
ALTER GROUP regress_priv_group2 ADD USER regress_priv_user2; -- duplicate
-NOTICE: role "regress_priv_user2" is already a member of role
"regress_priv_group2"
+NOTICE: role "regress_priv_user2" has already been granted membership in role
"regress_priv_group2" by role "gpadmin"
ALTER GROUP regress_priv_group2 DROP USER regress_priv_user2;
GRANT regress_priv_group2 TO regress_priv_user4 WITH ADMIN OPTION;
-- prepare non-leakproof function for later
@@ -55,7 +55,9 @@ ALTER FUNCTION leak(integer,integer) OWNER TO
regress_priv_user1;
-- test owner privileges
GRANT regress_priv_role TO regress_priv_user1 WITH ADMIN OPTION GRANTED BY
CURRENT_ROLE;
REVOKE ADMIN OPTION FOR regress_priv_role FROM regress_priv_user1 GRANTED BY
foo; -- error
+ERROR: role "foo" does not exist
REVOKE ADMIN OPTION FOR regress_priv_role FROM regress_priv_user1 GRANTED BY
regress_priv_user2; -- error
+WARNING: role "regress_priv_user1" has not been granted membership in role
"regress_priv_role" by role "regress_priv_user2"
REVOKE ADMIN OPTION FOR regress_priv_role FROM regress_priv_user1 GRANTED BY
CURRENT_USER;
REVOKE regress_priv_role FROM regress_priv_user1 GRANTED BY CURRENT_ROLE;
DROP ROLE regress_priv_role;
@@ -421,7 +423,6 @@ RESET SESSION AUTHORIZATION;
ALTER ROLE regress_priv_user1 NOINHERIT;
SET SESSION AUTHORIZATION regress_priv_user1;
DELETE FROM atest3;
-ERROR: permission denied for table atest3
ROLLBACK;
-- views
SET SESSION AUTHORIZATION regress_priv_user3;
@@ -1581,7 +1582,8 @@ REFRESH MATERIALIZED VIEW sro_mv;
ERROR: cannot fire deferred trigger within security-restricted operation
CONTEXT: SQL function "mv_action" statement 1
BEGIN; SET allow_segment_DML = ON; SET CONSTRAINTS ALL IMMEDIATE; REFRESH
MATERIALIZED VIEW sro_mv; COMMIT;
-ERROR: must have admin option on role "regress_priv_group2"
+ERROR: permission denied to grant role "regress_priv_group2"
+DETAIL: Only roles with the ADMIN option on role "regress_priv_group2" may
grant this role.
CONTEXT: SQL function "unwanted_grant" statement 1
SQL statement "SELECT unwanted_grant()"
PL/pgSQL function sro_trojan() line 1 at PERFORM
@@ -1617,12 +1619,14 @@ CREATE FUNCTION dogrant_ok() RETURNS void LANGUAGE sql
SECURITY DEFINER AS
GRANT regress_priv_group2 TO regress_priv_user5; -- ok: had ADMIN OPTION
SET ROLE regress_priv_group2;
GRANT regress_priv_group2 TO regress_priv_user5; -- fails: SET ROLE suspended
privilege
-ERROR: must have admin option on role "regress_priv_group2"
+ERROR: permission denied to grant role "regress_priv_group2"
+DETAIL: Only roles with the ADMIN option on role "regress_priv_group2" may
grant this role.
SET SESSION AUTHORIZATION regress_priv_user1;
GRANT regress_priv_group2 TO regress_priv_user5; -- fails: no ADMIN OPTION
-ERROR: must have admin option on role "regress_priv_group2"
+ERROR: permission denied to grant role "regress_priv_group2"
+DETAIL: Only roles with the ADMIN option on role "regress_priv_group2" may
grant this role.
SELECT dogrant_ok(); -- ok: SECURITY DEFINER conveys ADMIN
-NOTICE: role "regress_priv_user5" is already a member of role
"regress_priv_group2"
+NOTICE: role "regress_priv_user5" has already been granted membership in role
"regress_priv_group2" by role "regress_priv_user4"
dogrant_ok
------------
@@ -1630,14 +1634,17 @@ NOTICE: role "regress_priv_user5" is already a member
of role "regress_priv_gro
SET ROLE regress_priv_group2;
GRANT regress_priv_group2 TO regress_priv_user5; -- fails: SET ROLE did not
help
-ERROR: must have admin option on role "regress_priv_group2"
+ERROR: permission denied to grant role "regress_priv_group2"
+DETAIL: Only roles with the ADMIN option on role "regress_priv_group2" may
grant this role.
SET SESSION AUTHORIZATION regress_priv_group2;
GRANT regress_priv_group2 TO regress_priv_user5; -- ok: a role can self-admin
-NOTICE: role "regress_priv_user5" is already a member of role
"regress_priv_group2"
+ERROR: permission denied to grant role "regress_priv_group2"
+DETAIL: Only roles with the ADMIN option on role "regress_priv_group2" may
grant this role.
CREATE FUNCTION dogrant_fails() RETURNS void LANGUAGE sql SECURITY DEFINER AS
'GRANT regress_priv_group2 TO regress_priv_user5';
SELECT dogrant_fails(); -- fails: no self-admin in
SECURITY DEFINER
-ERROR: must have admin option on role "regress_priv_group2"
+ERROR: permission denied to grant role "regress_priv_group2"
+DETAIL: Only roles with the ADMIN option on role "regress_priv_group2" may
grant this role.
CONTEXT: SQL function "dogrant_fails" statement 1
DROP FUNCTION dogrant_fails();
SET SESSION AUTHORIZATION regress_priv_user4;
@@ -1699,63 +1706,114 @@ ERROR: large objects are not supported
SET SESSION AUTHORIZATION regress_priv_user2;
-- start_ignore
SELECT lo_create(2001);
-ERROR: large objects are not supported
+ lo_create
+-----------
+ 2001
+(1 row)
+
SELECT lo_create(2002);
-ERROR: large objects are not supported
+ lo_create
+-----------
+ 2002
+(1 row)
+
SELECT loread(lo_open(1001, x'20000'::int), 32); -- allowed, for now
-ERROR: large objects are not supported
+ loread
+--------
+ \x
+(1 row)
+
SELECT lowrite(lo_open(1001, x'40000'::int), 'abcd'); -- fail, wrong mode
-ERROR: large objects are not supported
+ERROR: large object descriptor 0 was not opened for writing
SELECT loread(lo_open(1001, x'40000'::int), 32);
-ERROR: large objects are not supported
+ loread
+--------
+ \x
+(1 row)
+
SELECT loread(lo_open(1002, x'40000'::int), 32); -- to be denied
-ERROR: large objects are not supported
+ERROR: permission denied for large object 1002
SELECT loread(lo_open(1003, x'40000'::int), 32);
-ERROR: large objects are not supported
+ loread
+--------
+ \x
+(1 row)
+
SELECT loread(lo_open(1004, x'40000'::int), 32);
-ERROR: large objects are not supported
+ loread
+--------
+ \x
+(1 row)
+
SELECT lowrite(lo_open(1001, x'20000'::int), 'abcd');
-ERROR: large objects are not supported
+ lowrite
+---------
+ 4
+(1 row)
+
SELECT lowrite(lo_open(1002, x'20000'::int), 'abcd'); -- to be denied
-ERROR: large objects are not supported
+ERROR: permission denied for large object 1002
SELECT lowrite(lo_open(1003, x'20000'::int), 'abcd'); -- to be denied
-ERROR: large objects are not supported
+ERROR: permission denied for large object 1003
SELECT lowrite(lo_open(1004, x'20000'::int), 'abcd');
-ERROR: large objects are not supported
+ lowrite
+---------
+ 4
+(1 row)
+
GRANT SELECT ON LARGE OBJECT 1005 TO regress_priv_user3;
-ERROR: large objects are not supported
GRANT UPDATE ON LARGE OBJECT 1006 TO regress_priv_user3; -- to be denied
-ERROR: large objects are not supported
+ERROR: large object 1006 does not exist
REVOKE ALL ON LARGE OBJECT 2001, 2002 FROM PUBLIC;
-ERROR: large objects are not supported
GRANT ALL ON LARGE OBJECT 2001 TO regress_priv_user3;
-ERROR: large objects are not supported
SELECT lo_unlink(1001); -- to be denied
-ERROR: large objects are not supported
+ERROR: must be owner of large object 1001
SELECT lo_unlink(2002);
-ERROR: large objects are not supported
+ lo_unlink
+-----------
+ 1
+(1 row)
+
-- end_ignore
\c -
-- start_ignore
-- confirm ACL setting
SELECT oid, pg_get_userbyid(lomowner) ownername, lomacl FROM
pg_largeobject_metadata WHERE oid >= 1000 AND oid < 3000 ORDER BY oid;
- oid | ownername | lomacl
------+-----------+--------
-(0 rows)
+ oid | ownername |
lomacl
+------+--------------------+------------------------------------------------------------------------------------------------------------------------------
+ 1001 | regress_priv_user1 |
{regress_priv_user1=rw/regress_priv_user1,=rw/regress_priv_user1}
+ 1002 | regress_priv_user1 |
+ 1003 | regress_priv_user1 |
{regress_priv_user1=rw/regress_priv_user1,regress_priv_user2=r/regress_priv_user1}
+ 1004 | regress_priv_user1 |
{regress_priv_user1=rw/regress_priv_user1,regress_priv_user2=rw/regress_priv_user1}
+ 1005 | regress_priv_user1 |
{regress_priv_user1=rw/regress_priv_user1,regress_priv_user2=r*w/regress_priv_user1,regress_priv_user3=r/regress_priv_user2}
+ 2001 | regress_priv_user2 |
{regress_priv_user2=rw/regress_priv_user2,regress_priv_user3=rw/regress_priv_user2}
+(6 rows)
-- end_ignore
SET SESSION AUTHORIZATION regress_priv_user3;
-- start_ignore
SELECT loread(lo_open(1001, x'40000'::int), 32);
-ERROR: large objects are not supported
+ loread
+------------
+ \x61626364
+(1 row)
+
SELECT loread(lo_open(1003, x'40000'::int), 32); -- to be denied
-ERROR: large objects are not supported
+ERROR: permission denied for large object 1003
SELECT loread(lo_open(1005, x'40000'::int), 32);
-ERROR: large objects are not supported
+ loread
+--------
+ \x
+(1 row)
+
SELECT lo_truncate(lo_open(1005, x'20000'::int), 10); -- to be denied
-ERROR: large objects are not supported
+ERROR: permission denied for large object 1005
SELECT lo_truncate(lo_open(2001, x'20000'::int), 10);
-ERROR: large objects are not supported
+ lo_truncate
+-------------
+ 0
+(1 row)
+
-- end_ignore
-- compatibility mode in largeobject permission
\c -
@@ -1763,15 +1821,15 @@ SET lo_compat_privileges = false; -- default
setting
SET SESSION AUTHORIZATION regress_priv_user4;
-- start_ignore
SELECT loread(lo_open(1002, x'40000'::int), 32); -- to be denied
-ERROR: large objects are not supported
+ERROR: permission denied for large object 1002
SELECT lowrite(lo_open(1002, x'20000'::int), 'abcd'); -- to be denied
-ERROR: large objects are not supported
+ERROR: permission denied for large object 1002
SELECT lo_truncate(lo_open(1002, x'20000'::int), 10); -- to be denied
-ERROR: large objects are not supported
+ERROR: permission denied for large object 1002
SELECT lo_put(1002, 1, 'abcd'); -- to be denied
-ERROR: large object 1002 does not exist
+ERROR: permission denied for large object 1002
SELECT lo_unlink(1002); -- to be denied
-ERROR: large objects are not supported
+ERROR: must be owner of large object 1002
SELECT lo_export(1001, '/dev/null'); -- to be denied
ERROR: permission denied for function lo_export
SELECT lo_import('/dev/null'); -- to be denied
@@ -1784,13 +1842,29 @@ SET lo_compat_privileges = true; --
compatibility mode
SET SESSION AUTHORIZATION regress_priv_user4;
-- start_ignore
SELECT loread(lo_open(1002, x'40000'::int), 32);
-ERROR: large objects are not supported
+ loread
+--------
+ \x
+(1 row)
+
SELECT lowrite(lo_open(1002, x'20000'::int), 'abcd');
-ERROR: large objects are not supported
+ lowrite
+---------
+ 4
+(1 row)
+
SELECT lo_truncate(lo_open(1002, x'20000'::int), 10);
-ERROR: large objects are not supported
+ lo_truncate
+-------------
+ 0
+(1 row)
+
SELECT lo_unlink(1002);
-ERROR: large objects are not supported
+ lo_unlink
+-----------
+ 1
+(1 row)
+
SELECT lo_export(1001, '/dev/null'); -- to be denied
ERROR: permission denied for function lo_export
-- end_ignore
@@ -1807,7 +1881,7 @@ ERROR: permission denied for table pg_largeobject
-- test pg_database_owner
RESET SESSION AUTHORIZATION;
GRANT pg_database_owner TO regress_priv_user1;
-ERROR: role "pg_database_owner" cannot have explicit members
+ERROR: role "pg_database_owner" cannot have explicit members (user.c:3722)
GRANT regress_priv_user1 TO pg_database_owner;
ERROR: role "pg_database_owner" cannot be a member of any role
CREATE TABLE datdba_only ();
@@ -2405,7 +2479,6 @@ LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should
pass
COMMIT;
BEGIN;
LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should fail
-ERROR: permission denied for table lock_table
ROLLBACK;
BEGIN;
LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should fail
@@ -2421,7 +2494,6 @@ LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should
pass
COMMIT;
BEGIN;
LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should fail
-ERROR: permission denied for table lock_table
ROLLBACK;
BEGIN;
LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should pass
@@ -2436,7 +2508,6 @@ LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should
pass
COMMIT;
BEGIN;
LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should fail
-ERROR: permission denied for table lock_table
ROLLBACK;
BEGIN;
LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should pass
@@ -2451,7 +2522,6 @@ LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should
pass
COMMIT;
BEGIN;
LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should fail
-ERROR: permission denied for table lock_table
ROLLBACK;
BEGIN;
LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should pass
diff --git a/src/test/singlenode_regress/expected/select_parallel.out
b/src/test/singlenode_regress/expected/select_parallel.out
index 697ed067121..faebf05c822 100644
--- a/src/test/singlenode_regress/expected/select_parallel.out
+++ b/src/test/singlenode_regress/expected/select_parallel.out
@@ -15,20 +15,18 @@ set max_parallel_workers_per_gather=4;
-- Parallel Append with partial-subplans
explain (costs off)
select round(avg(aa)), sum(aa) from a_star;
- QUERY PLAN
------------------------------------------------------
- Finalize Aggregate
- -> Gather Motion 9:1 (slice1; segments: 9)
- -> Partial Aggregate
- -> Parallel Append
- -> Seq Scan on d_star a_star_4
- -> Seq Scan on f_star a_star_6
- -> Seq Scan on e_star a_star_5
- -> Seq Scan on b_star a_star_2
- -> Seq Scan on c_star a_star_3
- -> Seq Scan on a_star a_star_1
+ QUERY PLAN
+-----------------------------------------
+ Aggregate
+ -> Append
+ -> Seq Scan on a_star a_star_1
+ -> Seq Scan on b_star a_star_2
+ -> Seq Scan on c_star a_star_3
+ -> Seq Scan on d_star a_star_4
+ -> Seq Scan on e_star a_star_5
+ -> Seq Scan on f_star a_star_6
Optimizer: Postgres query optimizer
-(11 rows)
+(9 rows)
select round(avg(aa)), sum(aa) from a_star a1;
round | sum
@@ -41,20 +39,18 @@ alter table c_star set (parallel_workers = 0);
alter table d_star set (parallel_workers = 0);
explain (costs off)
select round(avg(aa)), sum(aa) from a_star;
- QUERY PLAN
------------------------------------------------------
- Finalize Aggregate
- -> Gather Motion 9:1 (slice1; segments: 9)
- -> Partial Aggregate
- -> Parallel Append
- -> Seq Scan on d_star a_star_4
- -> Seq Scan on f_star a_star_6
- -> Seq Scan on e_star a_star_5
- -> Seq Scan on b_star a_star_2
- -> Seq Scan on c_star a_star_3
- -> Seq Scan on a_star a_star_1
+ QUERY PLAN
+-----------------------------------------
+ Aggregate
+ -> Append
+ -> Seq Scan on a_star a_star_1
+ -> Seq Scan on b_star a_star_2
+ -> Seq Scan on c_star a_star_3
+ -> Seq Scan on d_star a_star_4
+ -> Seq Scan on e_star a_star_5
+ -> Seq Scan on f_star a_star_6
Optimizer: Postgres query optimizer
-(11 rows)
+(9 rows)
select round(avg(aa)), sum(aa) from a_star a2;
round | sum
@@ -69,20 +65,18 @@ alter table e_star set (parallel_workers = 0);
alter table f_star set (parallel_workers = 0);
explain (costs off)
select round(avg(aa)), sum(aa) from a_star;
- QUERY PLAN
------------------------------------------------------
- Finalize Aggregate
- -> Gather Motion 9:1 (slice1; segments: 9)
- -> Partial Aggregate
- -> Parallel Append
- -> Seq Scan on d_star a_star_4
- -> Seq Scan on f_star a_star_6
- -> Seq Scan on e_star a_star_5
- -> Seq Scan on b_star a_star_2
- -> Seq Scan on c_star a_star_3
- -> Seq Scan on a_star a_star_1
+ QUERY PLAN
+-----------------------------------------
+ Aggregate
+ -> Append
+ -> Seq Scan on a_star a_star_1
+ -> Seq Scan on b_star a_star_2
+ -> Seq Scan on c_star a_star_3
+ -> Seq Scan on d_star a_star_4
+ -> Seq Scan on e_star a_star_5
+ -> Seq Scan on f_star a_star_6
Optimizer: Postgres query optimizer
-(11 rows)
+(9 rows)
select round(avg(aa)), sum(aa) from a_star a3;
round | sum
@@ -100,20 +94,18 @@ alter table f_star reset (parallel_workers);
set enable_parallel_append to off;
explain (costs off)
select round(avg(aa)), sum(aa) from a_star;
- QUERY PLAN
------------------------------------------------------
- Finalize Aggregate
- -> Gather Motion 3:1 (slice1; segments: 3)
- -> Partial Aggregate
- -> Append
- -> Seq Scan on a_star a_star_1
- -> Seq Scan on b_star a_star_2
- -> Seq Scan on c_star a_star_3
- -> Seq Scan on d_star a_star_4
- -> Seq Scan on e_star a_star_5
- -> Seq Scan on f_star a_star_6
+ QUERY PLAN
+-----------------------------------------
+ Aggregate
+ -> Append
+ -> Seq Scan on a_star a_star_1
+ -> Seq Scan on b_star a_star_2
+ -> Seq Scan on c_star a_star_3
+ -> Seq Scan on d_star a_star_4
+ -> Seq Scan on e_star a_star_5
+ -> Seq Scan on f_star a_star_6
Optimizer: Postgres query optimizer
-(11 rows)
+(9 rows)
select round(avg(aa)), sum(aa) from a_star a4;
round | sum
@@ -135,50 +127,41 @@ select sp_test_func() order by 1;
-- Parallel Append is not to be used when the subpath depends on the outer
param
create table part_pa_test(a int, b int) partition by range(a);
-NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a'
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.
create table part_pa_test_p1 partition of part_pa_test for values from
(minvalue) to (0);
-NOTICE: table has parent, setting distribution columns to match parent table
create table part_pa_test_p2 partition of part_pa_test for values from (0) to
(maxvalue);
NOTICE: table has parent, setting distribution columns to match parent table
explain (costs off)
select (select max((select pa1.b from part_pa_test pa1 where pa1.a =
pa2.a)))
from part_pa_test pa2;
- QUERY PLAN
--------------------------------------------------------------------------------
- Finalize Aggregate
- -> Gather Motion 3:1 (slice1; segments: 3)
- -> Partial Aggregate
- -> Append
- -> Seq Scan on part_pa_test_p1 pa2_1
- -> Seq Scan on part_pa_test_p2 pa2_2
- SubPlan 1
- -> Result
- Filter: (pa1.a = pa2.a)
- -> Materialize
- -> Broadcast Motion 3:3 (slice2; segments: 3)
- -> Append
- -> Seq Scan on part_pa_test_p1 pa1_1
- -> Seq Scan on part_pa_test_p2 pa1_2
+ QUERY PLAN
+-------------------------------------------------
+ Aggregate
+ -> Append
+ -> Seq Scan on part_pa_test_p1 pa2_1
+ -> Seq Scan on part_pa_test_p2 pa2_2
SubPlan 2
-> Result
+ SubPlan 1
+ -> Append
+ -> Seq Scan on part_pa_test_p1 pa1_1
+ Filter: (a = pa2.a)
+ -> Seq Scan on part_pa_test_p2 pa1_2
+ Filter: (a = pa2.a)
Optimizer: Postgres query optimizer
-(17 rows)
+(13 rows)
drop table part_pa_test;
-- test with leader participation disabled
set parallel_leader_participation = off;
explain (costs off)
select count(*) from tenk1 where stringu1 = 'GRAAAA';
- QUERY PLAN
----------------------------------------------------------
- Finalize Aggregate
- -> Gather Motion 12:1 (slice1; segments: 12)
- -> Partial Aggregate
- -> Parallel Seq Scan on tenk1
- Filter: (stringu1 = 'GRAAAA'::name)
+ QUERY PLAN
+---------------------------------------------
+ Aggregate
+ -> Seq Scan on tenk1
+ Filter: (stringu1 = 'GRAAAA'::name)
Optimizer: Postgres query optimizer
-(6 rows)
+(4 rows)
select count(*) from tenk1 where stringu1 = 'GRAAAA';
count
@@ -191,15 +174,13 @@ select count(*) from tenk1 where stringu1 = 'GRAAAA';
set max_parallel_workers = 0;
explain (costs off)
select count(*) from tenk1 where stringu1 = 'GRAAAA';
- QUERY PLAN
----------------------------------------------------------
- Finalize Aggregate
- -> Gather Motion 12:1 (slice1; segments: 12)
- -> Partial Aggregate
- -> Parallel Seq Scan on tenk1
- Filter: (stringu1 = 'GRAAAA'::name)
+ QUERY PLAN
+---------------------------------------------
+ Aggregate
+ -> Seq Scan on tenk1
+ Filter: (stringu1 = 'GRAAAA'::name)
Optimizer: Postgres query optimizer
-(6 rows)
+(4 rows)
select count(*) from tenk1 where stringu1 = 'GRAAAA';
count
@@ -216,33 +197,26 @@ select sp_parallel_restricted(unique1) from tenk1
where stringu1 = 'GRAAAA' order by 1;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
- Gather Motion 3:1 (slice1; segments: 3)
+ Sort
Output: (sp_parallel_restricted(unique1))
- Merge Key: (sp_parallel_restricted(unique1))
- -> Sort
- Output: (sp_parallel_restricted(unique1))
- Sort Key: (sp_parallel_restricted(tenk1.unique1))
- -> Seq Scan on public.tenk1
- Output: sp_parallel_restricted(unique1)
- Filter: (tenk1.stringu1 = 'GRAAAA'::name)
- Settings: enable_parallel = 'on', min_parallel_table_scan_size = '0',
optimizer = 'off', parallel_setup_cost = '0', parallel_tuple_cost = '0'
+ Sort Key: (sp_parallel_restricted(tenk1.unique1))
+ -> Seq Scan on public.tenk1
+ Output: sp_parallel_restricted(unique1)
+ Filter: (tenk1.stringu1 = 'GRAAAA'::name)
+ Settings: optimizer = 'off', enable_parallel = 'on', parallel_setup_cost =
'0', parallel_tuple_cost = '0', min_parallel_table_scan_size = '0'
Optimizer: Postgres query optimizer
-(11 rows)
+(8 rows)
-- test parallel plan when group by expression is in target list.
explain (costs off)
select length(stringu1) from tenk1 group by length(stringu1);
- QUERY PLAN
----------------------------------------------------------------
- Gather Motion 12:1 (slice1; segments: 12)
- -> HashAggregate
- Group Key: (length((stringu1)::text))
- -> Redistribute Motion 12:12 (slice2; segments: 12)
- Hash Key: (length((stringu1)::text))
- Hash Module: 3
- -> Parallel Seq Scan on tenk1
+ QUERY PLAN
+---------------------------------------
+ HashAggregate
+ Group Key: length((stringu1)::text)
+ -> Seq Scan on tenk1
Optimizer: Postgres query optimizer
-(8 rows)
+(4 rows)
select length(stringu1) from tenk1 group by length(stringu1);
length
@@ -252,49 +226,39 @@ select length(stringu1) from tenk1 group by
length(stringu1);
explain (costs off)
select stringu1, count(*) from tenk1 group by stringu1 order by
stringu1;
- QUERY PLAN
----------------------------------------------------------------------
- Gather Motion 12:1 (slice1; segments: 12)
- Merge Key: stringu1
- -> Sort
- Sort Key: stringu1
- -> HashAggregate
- Group Key: stringu1
- -> Redistribute Motion 12:12 (slice2; segments: 12)
- Hash Key: stringu1
- Hash Module: 3
- -> Parallel Seq Scan on tenk1
+ QUERY PLAN
+-------------------------------------
+ Sort
+ Sort Key: stringu1
+ -> HashAggregate
+ Group Key: stringu1
+ -> Seq Scan on tenk1
Optimizer: Postgres query optimizer
-(11 rows)
+(6 rows)
-- test that parallel plan for aggregates is not selected when
-- target list contains parallel restricted clause.
explain (costs off)
select sum(sp_parallel_restricted(unique1)) from tenk1
group by(sp_parallel_restricted(unique1));
- QUERY PLAN
-------------------------------------------------------------
- Gather Motion 3:1 (slice1; segments: 3)
- -> HashAggregate
- Group Key: (sp_parallel_restricted(unique1))
- -> Redistribute Motion 3:3 (slice2; segments: 3)
- Hash Key: (sp_parallel_restricted(unique1))
- -> Seq Scan on tenk1
+ QUERY PLAN
+----------------------------------------------
+ HashAggregate
+ Group Key: sp_parallel_restricted(unique1)
+ -> Seq Scan on tenk1
Optimizer: Postgres query optimizer
-(7 rows)
+(4 rows)
-- test prepared statement
prepare tenk1_count(integer) As select count((unique1)) from tenk1 where
hundred > $1;
explain (costs off) execute tenk1_count(1);
- QUERY PLAN
---------------------------------------------------
- Finalize Aggregate
- -> Gather Motion 12:1 (slice1; segments: 12)
- -> Partial Aggregate
- -> Parallel Seq Scan on tenk1
- Filter: (hundred > 1)
+ QUERY PLAN
+-------------------------------------
+ Aggregate
+ -> Seq Scan on tenk1
+ Filter: (hundred > 1)
Optimizer: Postgres query optimizer
-(6 rows)
+(4 rows)
execute tenk1_count(1);
count
@@ -308,19 +272,17 @@ alter table tenk2 set (parallel_workers = 0);
explain (costs off)
select count(*) from tenk1 where (two, four) not in
(select hundred, thousand from tenk2 where thousand > 100);
- QUERY PLAN
---------------------------------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------------
Aggregate
- -> Gather Motion 12:1 (slice1; segments: 12)
- -> Nested Loop Left Anti Semi (Not-In) Join
- Join Filter: ((tenk1.two = tenk2.hundred) AND (tenk1.four =
tenk2.thousand))
- -> Parallel Seq Scan on tenk1
- -> Materialize
- -> Broadcast Motion 3:12 (slice2; segments: 3)
- -> Seq Scan on tenk2
- Filter: (thousand > 100)
+ -> Nested Loop Left Anti Semi (Not-In) Join
+ Join Filter: ((tenk1.two = tenk2.hundred) AND (tenk1.four =
tenk2.thousand))
+ -> Seq Scan on tenk1
+ -> Materialize
+ -> Seq Scan on tenk2
+ Filter: (thousand > 100)
Optimizer: Postgres query optimizer
-(10 rows)
+(8 rows)
select count(*) from tenk1 where (two, four) not in
(select hundred, thousand from tenk2 where thousand > 100);
@@ -333,17 +295,15 @@ select count(*) from tenk1 where (two, four) not in
explain (costs off)
select * from tenk1 where (unique1 + random())::integer not in
(select ten from tenk2);
- QUERY PLAN
------------------------------------------------------------------
- Gather Motion 3:1 (slice1; segments: 3)
- -> Seq Scan on tenk1
- Filter: (SubPlan 1)
- SubPlan 1
- -> Materialize
- -> Broadcast Motion 3:3 (slice2; segments: 3)
- -> Seq Scan on tenk2
+ QUERY PLAN
+-------------------------------------
+ Seq Scan on tenk1
+ Filter: (SubPlan 1)
+ SubPlan 1
+ -> Materialize
+ -> Seq Scan on tenk2
Optimizer: Postgres query optimizer
-(8 rows)
+(6 rows)
alter table tenk2 reset (parallel_workers);
-- test parallel plan for a query containing initplan.
@@ -354,19 +314,16 @@ alter table tenk2 set (parallel_workers = 2);
explain (costs off)
select count(*) from tenk1
where tenk1.unique1 = (Select max(tenk2.unique1) from tenk2);
- QUERY PLAN
---------------------------------------------------------
+ QUERY PLAN
+-------------------------------------
Aggregate
- InitPlan 1 (returns $1) (slice2)
- -> Finalize Aggregate
- -> Gather Motion 6:1 (slice3; segments: 6)
- -> Partial Aggregate
- -> Parallel Seq Scan on tenk2
- -> Gather Motion 12:1 (slice1; segments: 12)
- -> Parallel Seq Scan on tenk1
- Filter: (unique1 = $1)
+ InitPlan 1 (returns $1)
+ -> Aggregate
+ -> Seq Scan on tenk2
+ -> Seq Scan on tenk1
+ Filter: (unique1 = $1)
Optimizer: Postgres query optimizer
-(10 rows)
+(7 rows)
select count(*) from tenk1
where tenk1.unique1 = (Select max(tenk2.unique1) from tenk2);
@@ -384,15 +341,13 @@ set enable_seqscan to off;
set enable_bitmapscan to off;
explain (costs off)
select count((unique1)) from tenk1 where hundred > 1;
- QUERY PLAN
---------------------------------------------------------------------
- Finalize Aggregate
- -> Gather Motion 12:1 (slice1; segments: 12)
- -> Partial Aggregate
- -> Parallel Index Scan using tenk1_hundred on tenk1
- Index Cond: (hundred > 1)
+ QUERY PLAN
+-----------------------------------------------
+ Aggregate
+ -> Index Scan using tenk1_hundred on tenk1
+ Index Cond: (hundred > 1)
Optimizer: Postgres query optimizer
-(6 rows)
+(4 rows)
select count((unique1)) from tenk1 where hundred > 1;
count
@@ -403,15 +358,13 @@ select count((unique1)) from tenk1 where hundred > 1;
-- test parallel index-only scans.
explain (costs off)
select count(*) from tenk1 where thousand > 95;
- QUERY PLAN
---------------------------------------------------------------------------------
- Finalize Aggregate
- -> Gather Motion 12:1 (slice1; segments: 12)
- -> Partial Aggregate
- -> Parallel Index Only Scan using tenk1_thous_tenthous on tenk1
- Index Cond: (thousand > 95)
+ QUERY PLAN
+-----------------------------------------------------------
+ Aggregate
+ -> Index Only Scan using tenk1_thous_tenthous on tenk1
+ Index Cond: (thousand > 95)
Optimizer: Postgres query optimizer
-(6 rows)
+(4 rows)
select count(*) from tenk1 where thousand > 95;
count
@@ -425,18 +378,16 @@ explain (costs off)
select * from
(select count(unique1) from tenk1 where hundred > 10) ss
right join (values (1),(2),(3)) v(x) on true;
- QUERY PLAN
------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------
Nested Loop Left Join
-> Values Scan on "*VALUES*"
-> Materialize
- -> Finalize Aggregate
- -> Gather Motion 3:1 (slice1; segments: 3)
- -> Partial Aggregate
- -> Index Scan using tenk1_hundred on tenk1
- Index Cond: (hundred > 10)
+ -> Aggregate
+ -> Index Scan using tenk1_hundred on tenk1
+ Index Cond: (hundred > 10)
Optimizer: Postgres query optimizer
-(9 rows)
+(7 rows)
select * from
(select count(unique1) from tenk1 where hundred > 10) ss
@@ -452,18 +403,16 @@ explain (costs off)
select * from
(select count(*) from tenk1 where thousand > 99) ss
right join (values (1),(2),(3)) v(x) on true;
- QUERY PLAN
------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------
Nested Loop Left Join
-> Values Scan on "*VALUES*"
-> Materialize
- -> Finalize Aggregate
- -> Gather Motion 3:1 (slice1; segments: 3)
- -> Partial Aggregate
- -> Index Only Scan using tenk1_thous_tenthous on
tenk1
- Index Cond: (thousand > 99)
+ -> Aggregate
+ -> Index Only Scan using tenk1_thous_tenthous on tenk1
+ Index Cond: (thousand > 99)
Optimizer: Postgres query optimizer
-(9 rows)
+(7 rows)
select * from
(select count(*) from tenk1 where thousand > 99) ss
@@ -486,24 +435,20 @@ select count(*) from tenk1
left join (select tenk2.unique1 from tenk2 order by 1 limit 1000) ss
on tenk1.unique1 < ss.unique1 + 1
where tenk1.unique1 < 2;
- QUERY PLAN
-------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------
Aggregate
-> Nested Loop Left Join
Join Filter: (tenk1.unique1 < (tenk2.unique1 + 1))
- -> Gather Motion 3:1 (slice1; segments: 3)
- -> Seq Scan on tenk1
- Filter: (unique1 < 2)
+ -> Seq Scan on tenk1
+ Filter: (unique1 < 2)
-> Materialize
-> Limit
- -> Gather Motion 3:1 (slice2; segments: 3)
- Merge Key: tenk2.unique1
- -> Limit
- -> Sort
- Sort Key: tenk2.unique1
- -> Seq Scan on tenk2
+ -> Sort
+ Sort Key: tenk2.unique1
+ -> Seq Scan on tenk2
Optimizer: Postgres query optimizer
-(15 rows)
+(11 rows)
select count(*) from tenk1
left join (select tenk2.unique1 from tenk2 order by 1 limit 1000) ss
@@ -536,22 +481,18 @@ END $$;
set work_mem='64kB'; --set small work mem to force lossy pages
explain (costs off)
select count(*) from tenk1, tenk2 where tenk1.hundred > 1 and
tenk2.thousand=0;
- QUERY PLAN
-------------------------------------------------------------------------------
- Finalize Aggregate
- -> Gather Motion 12:1 (slice1; segments: 12)
- -> Partial Aggregate
- -> Nested Loop
- -> Parallel Seq Scan on tenk2
- Filter: (thousand = 0)
- -> Materialize
- -> Broadcast Motion 3:12 (slice2; segments: 3)
- -> Bitmap Heap Scan on tenk1
- Recheck Cond: (hundred > 1)
- -> Bitmap Index Scan on tenk1_hundred
- Index Cond: (hundred > 1)
+ QUERY PLAN
+------------------------------------------------------
+ Aggregate
+ -> Nested Loop
+ -> Seq Scan on tenk2
+ Filter: (thousand = 0)
+ -> Bitmap Heap Scan on tenk1
+ Recheck Cond: (hundred > 1)
+ -> Bitmap Index Scan on tenk1_hundred
+ Index Cond: (hundred > 1)
Optimizer: Postgres query optimizer
-(13 rows)
+(9 rows)
select count(*) from tenk1, tenk2 where tenk1.hundred > 1 and tenk2.thousand=0;
count
@@ -560,8 +501,6 @@ select count(*) from tenk1, tenk2 where tenk1.hundred > 1
and tenk2.thousand=0;
(1 row)
create table bmscantest (a int, t text);
-NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a'
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 bmscantest select r,
'fooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo' FROM
generate_series(1,100000) r;
create index i_bmtest ON bmscantest(a);
select count(*) from bmscantest where a>1;
@@ -576,22 +515,18 @@ alter table tenk2 set (parallel_workers = 0);
explain (analyze, timing off, summary off, costs off)
select count(*) from tenk1, tenk2 where tenk1.hundred > 1
and tenk2.thousand=0;
- QUERY PLAN
-------------------------------------------------------------------------------------------------------
- Finalize Aggregate (actual rows=1 loops=1)
- -> Gather Motion 3:1 (slice1; segments: 3) (actual rows=3 loops=1)
- -> Partial Aggregate (actual rows=1 loops=1)
- -> Nested Loop (actual rows=49000 loops=1)
- -> Seq Scan on tenk2 (actual rows=5 loops=1)
- Filter: (thousand = 0)
- Rows Removed by Filter: 3363
- -> Materialize (actual rows=9800 loops=5)
- -> Broadcast Motion 3:3 (slice2; segments: 3)
(actual rows=9800 loops=1)
- -> Seq Scan on tenk1 (actual rows=3320
loops=1)
- Filter: (hundred > 1)
- Rows Removed by Filter: 66
+ QUERY PLAN
+-----------------------------------------------------------
+ Aggregate (actual rows=1 loops=1)
+ -> Nested Loop (actual rows=98000 loops=1)
+ -> Seq Scan on tenk2 (actual rows=10 loops=1)
+ Filter: (thousand = 0)
+ Rows Removed by Filter: 9990
+ -> Seq Scan on tenk1 (actual rows=9800 loops=10)
+ Filter: (hundred > 1)
+ Rows Removed by Filter: 200
Optimizer: Postgres query optimizer
-(13 rows)
+(9 rows)
alter table tenk2 reset (parallel_workers);
reset work_mem;
@@ -614,20 +549,18 @@ $$;
-- test sort stats plan, disable parallel
set max_parallel_workers_per_gather = 0;
select * from explain_parallel_sort_stats();
- explain_parallel_sort_stats
-----------------------------------------------------------------------------------
+ explain_parallel_sort_stats
+-----------------------------------------------------------------
Nested Loop Left Join (actual rows=30000 loops=1)
-> Values Scan on "*VALUES*" (actual rows=3 loops=1)
-> Materialize (actual rows=10000 loops=3)
- -> Gather Motion 3:1 (slice1; segments: 3) (actual rows=10000
loops=1)
- Merge Key: tenk1.ten
- -> Sort (actual rows=3386 loops=1)
- Sort Key: tenk1.ten
- Sort Method: quicksort Memory: xxx
- -> Seq Scan on tenk1 (actual rows=3386 loops=1)
- Filter: (ten < 100)
+ -> Sort (actual rows=10000 loops=1)
+ Sort Key: tenk1.ten
+ Sort Method: quicksort Memory: xxx
+ -> Seq Scan on tenk1 (actual rows=10000 loops=1)
+ Filter: (ten < 100)
Optimizer: Postgres query optimizer
-(11 rows)
+(9 rows)
reset max_parallel_workers_per_gather;
reset enable_indexscan;
@@ -643,17 +576,15 @@ set enable_hashjoin to off;
set enable_nestloop to off;
explain (costs off)
select count(*) from tenk1, tenk2 where tenk1.unique1 = tenk2.unique1;
- QUERY PLAN
-----------------------------------------------------------------------
- Finalize Aggregate
- -> Gather Motion 3:1 (slice1; segments: 3)
- -> Partial Aggregate
- -> Merge Join
- Merge Cond: (tenk1.unique1 = tenk2.unique1)
- -> Index Only Scan using tenk1_unique1 on tenk1
- -> Index Only Scan using tenk2_unique1 on tenk2
+ QUERY PLAN
+----------------------------------------------------------
+ Aggregate
+ -> Merge Join
+ Merge Cond: (tenk1.unique1 = tenk2.unique1)
+ -> Index Only Scan using tenk1_unique1 on tenk1
+ -> Index Only Scan using tenk2_unique1 on tenk2
Optimizer: Postgres query optimizer
-(10 rows)
+(6 rows)
select count(*) from tenk1, tenk2 where tenk1.unique1 = tenk2.unique1;
count
@@ -667,23 +598,15 @@ reset enable_nestloop;
set enable_hashagg = false;
explain (costs off)
select count(*) from tenk1 group by twenty;
- QUERY PLAN
-------------------------------------------------------------------
- Gather Motion 6:1 (slice1; segments: 6)
- -> Finalize GroupAggregate
- Group Key: twenty
- -> Sort
- Sort Key: twenty
- -> Redistribute Motion 6:6 (slice2; segments: 6)
- Hash Key: twenty
- Hash Module: 3
- -> Partial GroupAggregate
- Group Key: twenty
- -> Sort
- Sort Key: twenty
- -> Parallel Seq Scan on tenk1
+ QUERY PLAN
+-------------------------------------
+ GroupAggregate
+ Group Key: twenty
+ -> Sort
+ Sort Key: twenty
+ -> Seq Scan on tenk1
Optimizer: Postgres query optimizer
-(14 rows)
+(6 rows)
select count(*) from tenk1 group by twenty;
count
@@ -721,43 +644,32 @@ explain (costs off, verbose)
select ten, sp_simple_func(ten) from tenk1 where ten < 100 order by ten;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
- Gather Motion 6:1 (slice1; segments: 6)
- Output: ten, (sp_simple_func(ten))
- Merge Key: ten
- -> Result
- Output: ten, sp_simple_func(ten)
- -> Sort
+ Result
+ Output: ten, sp_simple_func(ten)
+ -> Sort
+ Output: ten
+ Sort Key: tenk1.ten
+ -> Seq Scan on public.tenk1
Output: ten
- Sort Key: tenk1.ten
- -> Parallel Seq Scan on public.tenk1
- Output: ten
- Filter: (tenk1.ten < 100)
- Settings: enable_hashagg = 'off', enable_parallel = 'on',
min_parallel_table_scan_size = '0', optimizer = 'off', parallel_setup_cost =
'0', parallel_tuple_cost = '0'
+ Filter: (tenk1.ten < 100)
+ Settings: optimizer = 'off', enable_parallel = 'on', parallel_setup_cost =
'0', parallel_tuple_cost = '0', min_parallel_table_scan_size = '0',
enable_hashagg = 'off'
Optimizer: Postgres query optimizer
-(13 rows)
+(10 rows)
drop function sp_simple_func(integer);
-- test handling of SRFs in targetlist (bug in 10.0)
explain (costs off)
select count(*), generate_series(1,2) from tenk1 group by twenty;
- QUERY PLAN
-------------------------------------------------------------------------
- Gather Motion 6:1 (slice1; segments: 6)
- -> ProjectSet
- -> Finalize GroupAggregate
- Group Key: twenty
- -> Sort
- Sort Key: twenty
- -> Redistribute Motion 6:6 (slice2; segments: 6)
- Hash Key: twenty
- Hash Module: 3
- -> Partial GroupAggregate
- Group Key: twenty
- -> Sort
- Sort Key: twenty
- -> Parallel Seq Scan on tenk1
+ QUERY PLAN
+-------------------------------------
+ ProjectSet
+ -> GroupAggregate
+ Group Key: twenty
+ -> Sort
+ Sort Key: twenty
+ -> Seq Scan on tenk1
Optimizer: Postgres query optimizer
-(15 rows)
+(7 rows)
select count(*), generate_series(1,2) from tenk1 group by twenty;
count | generate_series
@@ -808,23 +720,15 @@ select count(*), generate_series(1,2) from tenk1 group by
twenty;
set parallel_leader_participation = off;
explain (costs off)
select count(*) from tenk1 group by twenty;
- QUERY PLAN
-------------------------------------------------------------------
- Gather Motion 6:1 (slice1; segments: 6)
- -> Finalize GroupAggregate
- Group Key: twenty
- -> Sort
- Sort Key: twenty
- -> Redistribute Motion 6:6 (slice2; segments: 6)
- Hash Key: twenty
- Hash Module: 3
- -> Partial GroupAggregate
- Group Key: twenty
- -> Sort
- Sort Key: twenty
- -> Parallel Seq Scan on tenk1
+ QUERY PLAN
+-------------------------------------
+ GroupAggregate
+ Group Key: twenty
+ -> Sort
+ Sort Key: twenty
+ -> Seq Scan on tenk1
Optimizer: Postgres query optimizer
-(14 rows)
+(6 rows)
select count(*) from tenk1 group by twenty;
count
@@ -859,22 +763,18 @@ select * from
(select string4, count(unique2)
from tenk1 group by string4 order by string4) ss
right join (values (1),(2),(3)) v(x) on true;
- QUERY PLAN
-------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------
Nested Loop Left Join
-> Values Scan on "*VALUES*"
-> Materialize
- -> Finalize GroupAggregate
+ -> GroupAggregate
Group Key: tenk1.string4
- -> Gather Motion 3:1 (slice1; segments: 3)
- Merge Key: tenk1.string4
- -> Partial GroupAggregate
- Group Key: tenk1.string4
- -> Sort
- Sort Key: tenk1.string4
- -> Seq Scan on tenk1
+ -> Sort
+ Sort Key: tenk1.string4
+ -> Seq Scan on tenk1
Optimizer: Postgres query optimizer
-(13 rows)
+(9 rows)
select * from
(select string4, count(unique2)
@@ -901,14 +801,12 @@ reset enable_hashagg;
-- check parallelized int8 aggregate (bug #14897)
explain (costs off)
select avg(unique1::int8) from tenk1;
- QUERY PLAN
-------------------------------------------------
- Finalize Aggregate
- -> Gather Motion 6:1 (slice1; segments: 6)
- -> Partial Aggregate
- -> Parallel Seq Scan on tenk1
+ QUERY PLAN
+-------------------------------------
+ Aggregate
+ -> Seq Scan on tenk1
Optimizer: Postgres query optimizer
-(5 rows)
+(3 rows)
select avg(unique1::int8) from tenk1;
avg
@@ -919,17 +817,14 @@ select avg(unique1::int8) from tenk1;
-- gather merge test with a LIMIT
explain (costs off)
select fivethous from tenk1 order by fivethous limit 4;
- QUERY PLAN
-----------------------------------------------------
+ QUERY PLAN
+-------------------------------------
Limit
- -> Gather Motion 6:1 (slice1; segments: 6)
- Merge Key: fivethous
- -> Limit
- -> Sort
- Sort Key: fivethous
- -> Parallel Seq Scan on tenk1
+ -> Sort
+ Sort Key: fivethous
+ -> Seq Scan on tenk1
Optimizer: Postgres query optimizer
-(8 rows)
+(5 rows)
select fivethous from tenk1 order by fivethous limit 4;
fivethous
@@ -944,17 +839,14 @@ select fivethous from tenk1 order by fivethous limit 4;
set max_parallel_workers = 0;
explain (costs off)
select string4 from tenk1 order by string4 limit 5;
- QUERY PLAN
-----------------------------------------------------
+ QUERY PLAN
+-------------------------------------
Limit
- -> Gather Motion 6:1 (slice1; segments: 6)
- Merge Key: string4
- -> Limit
- -> Sort
- Sort Key: string4
- -> Parallel Seq Scan on tenk1
+ -> Sort
+ Sort Key: string4
+ -> Seq Scan on tenk1
Optimizer: Postgres query optimizer
-(8 rows)
+(5 rows)
select string4 from tenk1 order by string4 limit 5;
string4
@@ -972,17 +864,14 @@ select string4 from tenk1 order by string4 limit 5;
set parallel_leader_participation = off;
explain (costs off)
select string4 from tenk1 order by string4 limit 5;
- QUERY PLAN
-----------------------------------------------------
+ QUERY PLAN
+-------------------------------------
Limit
- -> Gather Motion 6:1 (slice1; segments: 6)
- Merge Key: string4
- -> Limit
- -> Sort
- Sort Key: string4
- -> Parallel Seq Scan on tenk1
+ -> Sort
+ Sort Key: string4
+ -> Seq Scan on tenk1
Optimizer: Postgres query optimizer
-(8 rows)
+(5 rows)
select string4 from tenk1 order by string4 limit 5;
string4
@@ -1000,13 +889,12 @@ SAVEPOINT settings;
SET LOCAL debug_parallel_query = 1;
explain (costs off)
select stringu1::int2 from tenk1 where unique1 = 1;
- QUERY PLAN
---------------------------------------------------------
- Gather Motion 2:1 (slice1; segments: 2)
- -> Parallel Index Scan using tenk1_unique1 on tenk1
- Index Cond: (unique1 = 1)
+ QUERY PLAN
+-----------------------------------------
+ Index Scan using tenk1_unique1 on tenk1
+ Index Cond: (unique1 = 1)
Optimizer: Postgres query optimizer
-(4 rows)
+(3 rows)
ROLLBACK TO SAVEPOINT settings;
-- exercise record typmod remapping between backends
@@ -1039,6 +927,7 @@ ROLLBACK TO SAVEPOINT settings;
DROP function make_record(n int);
-- test the sanity of parallel query after the active role is dropped.
drop role if exists regress_parallel_worker;
+NOTICE: role "regress_parallel_worker" does not exist, skipping
create role regress_parallel_worker;
set role regress_parallel_worker;
reset session authorization;
@@ -1056,66 +945,50 @@ reset role;
explain (costs off, verbose)
select count(*) from tenk1 a where (unique1, two) in
(select unique1, row_number() over() from tenk1 b);
-
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- Finalize Aggregate
+
QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Aggregate
Output: count(*)
- -> Gather Motion 3:1 (slice1; segments: 3)
- Output: (PARTIAL count(*))
- -> Partial Aggregate
- Output: PARTIAL count(*)
- -> Hash Semi Join
- Hash Cond: ((a.unique1 = b.unique1) AND (a.two =
(row_number() OVER (?))))
- -> Seq Scan on public.tenk1 a
- Output: a.unique1, a.unique2, a.two, a.four, a.ten,
a.twenty, a.hundred, a.thousand, a.twothousand, a.fivethous, a.tenthous, a.odd,
a.even, a.stringu1, a.stringu2, a.string4
- -> Hash
- Output: b.unique1, (row_number() OVER (?))
- -> Redistribute Motion 1:3 (slice2; segments: 1)
- Output: b.unique1, (row_number() OVER (?))
- Hash Key: b.unique1
- -> WindowAgg
- Output: b.unique1, row_number() OVER (?)
- -> Gather Motion 3:1 (slice3;
segments: 3)
- Output: b.unique1
- -> Seq Scan on public.tenk1 b
- Output: b.unique1
- Settings: enable_parallel = 'on', min_parallel_table_scan_size = '0',
optimizer = 'off', parallel_setup_cost = '0', parallel_tuple_cost = '0'
+ -> Hash Semi Join
+ Hash Cond: ((a.unique1 = b.unique1) AND (a.two = (row_number() OVER
(?))))
+ -> Seq Scan on public.tenk1 a
+ Output: a.unique1, a.unique2, a.two, a.four, a.ten, a.twenty,
a.hundred, a.thousand, a.twothousand, a.fivethous, a.tenthous, a.odd, a.even,
a.stringu1, a.stringu2, a.string4
+ -> Hash
+ Output: b.unique1, (row_number() OVER (?))
+ -> WindowAgg
+ Output: b.unique1, row_number() OVER (?)
+ -> Seq Scan on public.tenk1 b
+ Output: b.unique1
+ Settings: optimizer = 'off', enable_parallel = 'on', parallel_setup_cost =
'0', parallel_tuple_cost = '0', min_parallel_table_scan_size = '0'
Optimizer: Postgres query optimizer
-(23 rows)
+(14 rows)
-- LIMIT/OFFSET within sub-selects can't be pushed to workers.
explain (costs off)
select * from tenk1 a where two in
(select two from tenk1 b where stringu1 like '%AAAA' limit 3);
- QUERY PLAN
----------------------------------------------------------------------------------------
- Gather Motion 3:1 (slice1; segments: 3)
- -> Hash Join
- Hash Cond: (a.two = b.two)
- -> Seq Scan on tenk1 a
- -> Hash
- -> Broadcast Motion 1:3 (slice2; segments: 1)
- -> HashAggregate
- Group Key: b.two
- -> Limit
- -> Gather Motion 3:1 (slice3; segments: 3)
- -> Limit
- -> Seq Scan on tenk1 b
- Filter: (stringu1 ~~
'%AAAA'::text)
+ QUERY PLAN
+---------------------------------------------------------
+ Hash Semi Join
+ Hash Cond: (a.two = b.two)
+ -> Seq Scan on tenk1 a
+ -> Hash
+ -> Limit
+ -> Seq Scan on tenk1 b
+ Filter: (stringu1 ~~ '%AAAA'::text)
Optimizer: Postgres query optimizer
-(14 rows)
+(8 rows)
-- to increase the parallel query test coverage
SAVEPOINT settings;
SET LOCAL debug_parallel_query = 1;
-- CBDB_PARALLEL_FIXME: analyze actual rows may be different by running
multiple times.
EXPLAIN (timing off, summary off, costs off) SELECT * FROM tenk1;
- QUERY PLAN
-------------------------------------------
- Gather Motion 6:1 (slice1; segments: 6)
- -> Parallel Seq Scan on tenk1
+ QUERY PLAN
+-------------------------------------
+ Seq Scan on tenk1
Optimizer: Postgres query optimizer
-(3 rows)
+(2 rows)
ROLLBACK TO SAVEPOINT settings;
-- provoke error in worker
@@ -1123,7 +996,7 @@ ROLLBACK TO SAVEPOINT settings;
SAVEPOINT settings;
SET LOCAL debug_parallel_query = 1;
select (stringu1 || repeat('abcd', 5000))::int2 from tenk1 where unique1 = 1;
-ERROR: invalid input syntax for type smallint:
"BAAAAAabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcda
[...]
+ERROR: invalid input syntax for type smallint:
"BAAAAAabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcda
[...]
ROLLBACK TO SAVEPOINT settings;
-- test interaction with set-returning functions
SAVEPOINT settings;
@@ -1134,16 +1007,15 @@ EXPLAIN (COSTS OFF)
SELECT unique1 FROM tenk1 WHERE fivethous = tenthous + 1
UNION ALL
SELECT unique1 FROM tenk1 WHERE fivethous = tenthous + 1;
- QUERY PLAN
-----------------------------------------------------
- Gather Motion 6:1 (slice1; segments: 6)
- -> Parallel Append
- -> Parallel Seq Scan on tenk1
- Filter: (fivethous = (tenthous + 1))
- -> Parallel Seq Scan on tenk1 tenk1_1
- Filter: (fivethous = (tenthous + 1))
+ QUERY PLAN
+----------------------------------------------
+ Append
+ -> Seq Scan on tenk1
+ Filter: (fivethous = (tenthous + 1))
+ -> Seq Scan on tenk1 tenk1_1
+ Filter: (fivethous = (tenthous + 1))
Optimizer: Postgres query optimizer
-(7 rows)
+(6 rows)
ROLLBACK TO SAVEPOINT settings;
-- can't use multiple subqueries under a single Gather node due to initPlans
@@ -1154,31 +1026,25 @@ UNION ALL
SELECT unique1 FROM tenk1 WHERE fivethous =
(SELECT unique2 FROM tenk1 WHERE fivethous = 1 LIMIT 1)
ORDER BY 1;
- QUERY PLAN
---------------------------------------------------------------------------
- Gather Motion 6:1 (slice1; segments: 6)
- Merge Key: tenk1.unique1
- -> Sort
- Sort Key: tenk1.unique1
- -> Parallel Append
- -> Parallel Seq Scan on tenk1
- Filter: (fivethous = $1)
- InitPlan 2 (returns $1) (slice2)
- -> Limit
- -> Gather Motion 6:1 (slice3; segments: 6)
- -> Limit
- -> Parallel Seq Scan on tenk1 tenk1_3
- Filter: (fivethous = 1)
- -> Parallel Seq Scan on tenk1 tenk1_1
- Filter: (fivethous = $0)
- InitPlan 1 (returns $0) (slice4)
- -> Limit
- -> Gather Motion 6:1 (slice5; segments: 6)
- -> Limit
- -> Parallel Seq Scan on tenk1 tenk1_2
- Filter: (fivethous = 1)
+ QUERY PLAN
+-----------------------------------------------------
+ Sort
+ Sort Key: tenk1.unique1
+ -> Append
+ -> Seq Scan on tenk1
+ Filter: (fivethous = $0)
+ InitPlan 1 (returns $0)
+ -> Limit
+ -> Seq Scan on tenk1 tenk1_2
+ Filter: (fivethous = 1)
+ -> Seq Scan on tenk1 tenk1_1
+ Filter: (fivethous = $1)
+ InitPlan 2 (returns $1)
+ -> Limit
+ -> Seq Scan on tenk1 tenk1_3
+ Filter: (fivethous = 1)
Optimizer: Postgres query optimizer
-(22 rows)
+(16 rows)
-- test interaction with SRFs
SELECT * FROM information_schema.foreign_data_wrapper_options
@@ -1192,52 +1058,45 @@ SELECT generate_series(1, two), array(select
generate_series(1, two))
FROM tenk1 ORDER BY tenthous;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
- Gather Motion 3:1 (slice1; segments: 3)
- Output: (generate_series(1, tenk1.two)), ((SubPlan 1)), tenk1.tenthous
- Merge Key: tenk1.tenthous
- -> ProjectSet
- Output: generate_series(1, tenk1.two), (SubPlan 1), tenk1.tenthous
- -> Result
- Output: tenk1.two, tenk1.tenthous
- -> Sort
+ ProjectSet
+ Output: generate_series(1, tenk1.two), (SubPlan 1), tenk1.tenthous
+ -> Result
+ Output: tenk1.two, tenk1.tenthous
+ -> Sort
+ Output: tenk1.tenthous, tenk1.two
+ Sort Key: tenk1.tenthous
+ -> Seq Scan on public.tenk1
Output: tenk1.tenthous, tenk1.two
- Sort Key: tenk1.tenthous
- -> Seq Scan on public.tenk1
- Output: tenk1.tenthous, tenk1.two
- SubPlan 1
- -> ProjectSet
- Output: generate_series(1, tenk1.two)
- -> Result
- Settings: enable_parallel = 'on', min_parallel_table_scan_size = '0',
optimizer = 'off', parallel_setup_cost = '0', parallel_tuple_cost = '0'
+ SubPlan 1
+ -> ProjectSet
+ Output: generate_series(1, tenk1.two)
+ -> Result
+ Settings: optimizer = 'off', enable_parallel = 'on', parallel_setup_cost =
'0', parallel_tuple_cost = '0', min_parallel_table_scan_size = '0'
Optimizer: Postgres query optimizer
-(18 rows)
+(15 rows)
-- must disallow pushing sort below gather when pathkey contains an SRF
EXPLAIN (VERBOSE, COSTS OFF)
SELECT unnest(ARRAY[]::integer[]) + 1 AS pathkey
FROM tenk1 t1 JOIN tenk1 t2 ON TRUE
ORDER BY pathkey;
-
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- Gather Motion 6:1 (slice1; segments: 6)
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------------------
+ Sort
Output: (((unnest('{}'::integer[])) + 1))
- Merge Key: (((unnest('{}'::integer[])) + 1))
- -> Sort
- Output: (((unnest('{}'::integer[])) + 1))
- Sort Key: (((unnest('{}'::integer[])) + 1))
- -> Result
- Output: ((unnest('{}'::integer[])) + 1)
- -> ProjectSet
- Output: unnest('{}'::integer[])
- -> Nested Loop
- -> Parallel Seq Scan on public.tenk1 t1
- Output: t1.unique1, t1.unique2, t1.two,
t1.four, t1.ten, t1.twenty, t1.hundred, t1.thousand, t1.twothousand,
t1.fivethous, t1.tenthous, t1.odd, t1.even, t1.stringu1, t1.stringu2, t1.string4
- -> Materialize
- -> Broadcast Motion 3:6 (slice2; segments:
3)
- -> Seq Scan on public.tenk1 t2
- Settings: enable_parallel = 'on', optimizer = 'off', parallel_setup_cost =
'0', parallel_tuple_cost = '0', min_parallel_table_scan_size = '0'
+ Sort Key: (((unnest('{}'::integer[])) + 1))
+ -> Result
+ Output: ((unnest('{}'::integer[])) + 1)
+ -> ProjectSet
+ Output: unnest('{}'::integer[])
+ -> Nested Loop
+ -> Index Only Scan using tenk1_hundred on public.tenk1 t1
+ Output: t1.hundred
+ -> Materialize
+ -> Index Only Scan using tenk1_hundred on
public.tenk1 t2
+ Settings: optimizer = 'off', enable_parallel = 'on', parallel_setup_cost =
'0', parallel_tuple_cost = '0', min_parallel_table_scan_size = '0'
Optimizer: Postgres query optimizer
-(18 rows)
+(14 rows)
-- test passing expanded-value representations to workers
CREATE FUNCTION make_some_array(int,int) returns int[] as
@@ -1248,18 +1107,15 @@ $$declare x int[];
return x;
end$$ language plpgsql parallel safe;
CREATE TABLE fooarr(f1 text, f2 int[], f3 text);
-NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'f1'
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 fooarr VALUES('1', ARRAY[1,2], 'one');
PREPARE pstmt(text, int[]) AS SELECT * FROM fooarr WHERE f1 = $1 AND f2 = $2;
EXPLAIN (COSTS OFF) EXECUTE pstmt('1', make_some_array(1,2));
- QUERY PLAN
-------------------------------------------------------------------
- Gather Motion 2:1 (slice1; segments: 2)
- -> Parallel Seq Scan on fooarr
- Filter: ((f1 = '1'::text) AND (f2 = '{1,2}'::integer[]))
+ QUERY PLAN
+------------------------------------------------------------
+ Seq Scan on fooarr
+ Filter: ((f1 = '1'::text) AND (f2 = '{1,2}'::integer[]))
Optimizer: Postgres query optimizer
-(4 rows)
+(3 rows)
EXECUTE pstmt('1', make_some_array(1,2));
f1 | f2 | f3
@@ -1273,21 +1129,17 @@ CREATE VIEW tenk1_vw_sec WITH (security_barrier) AS
SELECT * FROM tenk1;
EXPLAIN (COSTS OFF)
SELECT 1 FROM tenk1_vw_sec
WHERE (SELECT sum(f1) FROM int4_tbl WHERE f1 < unique1) < 100;
- QUERY PLAN
------------------------------------------------------------------------------
- Gather Motion 3:1 (slice1; segments: 3)
- -> Subquery Scan on tenk1_vw_sec
- Filter: ((SubPlan 1) < 100)
- -> Seq Scan on tenk1
- SubPlan 1
- -> Aggregate
- -> Result
- Filter: (int4_tbl.f1 < tenk1_vw_sec.unique1)
- -> Materialize
- -> Broadcast Motion 3:3 (slice2; segments: 3)
- -> Seq Scan on int4_tbl
+ QUERY PLAN
+-----------------------------------------------------
+ Subquery Scan on tenk1_vw_sec
+ Filter: ((SubPlan 1) < 100)
+ -> Seq Scan on tenk1
+ SubPlan 1
+ -> Aggregate
+ -> Seq Scan on int4_tbl
+ Filter: (f1 < tenk1_vw_sec.unique1)
Optimizer: Postgres query optimizer
-(12 rows)
+(8 rows)
rollback;
-- test that function option SET ROLE works in parallel workers.
diff --git a/src/test/singlenode_regress/expected/with.out
b/src/test/singlenode_regress/expected/with.out
index 2ecebb197ac..069b9e8a840 100644
--- a/src/test/singlenode_regress/expected/with.out
+++ b/src/test/singlenode_regress/expected/with.out
@@ -2607,7 +2607,7 @@ CREATE TEMP TABLE bug6051_3 AS
SELECT a FROM generate_series(11,13) AS a;
CREATE RULE bug6051_3_ins AS ON INSERT TO bug6051_3 DO INSTEAD
SELECT i FROM bug6051_2;
-BEGIN; SET LOCAL force_parallel_mode = on;
+BEGIN; SET LOCAL debug_parallel_query = on;
WITH t1 AS ( DELETE FROM bug6051_3 RETURNING * )
INSERT INTO bug6051_3 SELECT * FROM t1;
ERROR: writable CTE queries cannot be themselves writable
diff --git a/src/test/singlenode_regress/input/createdb.source
b/src/test/singlenode_regress/input/createdb.source
index 8f43d67f6b7..fbdce83fccd 100644
--- a/src/test/singlenode_regress/input/createdb.source
+++ b/src/test/singlenode_regress/input/createdb.source
@@ -17,14 +17,14 @@ $$;
--
--CASE 0: createdb do well
--
-create database dowell;
+create database dowell STRATEGY = file_copy;
select force_mirrors_to_catch_up();
select count(*)=0 as result from
(select db_dirs(oid) from pg_database where datname = 'dowell') as foo;
-\! psql -d dowell -c "create table test1(a int, b text)"
-\! psql -d dowell -c "insert into test1 values (1, '111'), (2, '222'), (3,
'333')"
-\! psql -d dowell -c "select * from test1"
+\! psql -d dowell -Xc "create table test1(a int, b text)"
+\! psql -d dowell -Xc "insert into test1 values (1, '111'), (2, '222'), (3,
'333')"
+\! psql -d dowell -Xc "select * from test1"
drop database dowell;
@@ -35,7 +35,7 @@ select gp_inject_fault('all', 'reset', dbid) from
gp_segment_configuration;
select gp_inject_fault('after_xlog_create_database', 'error', dbid)
from gp_segment_configuration where content=-1 and role='p';
-- should fail
-create database db2;
+create database db2 STRATEGY = file_copy;
select force_mirrors_to_catch_up();
diff --git a/src/test/singlenode_regress/output/createdb.source
b/src/test/singlenode_regress/output/createdb.source
index af4a311ccd7..9bfd914637c 100644
--- a/src/test/singlenode_regress/output/createdb.source
+++ b/src/test/singlenode_regress/output/createdb.source
@@ -20,7 +20,7 @@ $$;
--
--CASE 0: createdb do well
--
-create database dowell;
+create database dowell STRATEGY = file_copy;
select force_mirrors_to_catch_up();
force_mirrors_to_catch_up
---------------------------
@@ -34,13 +34,11 @@ select count(*)=0 as result from
f
(1 row)
-\! psql -d dowell -c "create table test1(a int, b text)"
-NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a'
as the Apache Cloudberry 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.
+\! psql -d dowell -Xc "create table test1(a int, b text)"
CREATE TABLE
-\! psql -d dowell -c "insert into test1 values (1, '111'), (2, '222'), (3,
'333')"
+\! psql -d dowell -Xc "insert into test1 values (1, '111'), (2, '222'), (3,
'333')"
INSERT 0 3
-\! psql -d dowell -c "select * from test1"
+\! psql -d dowell -Xc "select * from test1"
a | b
---+-----
2 | 222
@@ -67,7 +65,7 @@ from gp_segment_configuration where content=-1 and role='p';
(1 row)
-- should fail
-create database db2;
+create database db2 STRATEGY = file_copy;
ERROR: fault triggered, fault name:'after_xlog_create_database' fault
type:'error'
select force_mirrors_to_catch_up();
force_mirrors_to_catch_up
diff --git a/src/test/singlenode_regress/output/gpcopy.source
b/src/test/singlenode_regress/output/gpcopy.source
index d38794e81d2..14bb9557722 100755
--- a/src/test/singlenode_regress/output/gpcopy.source
+++ b/src/test/singlenode_regress/output/gpcopy.source
@@ -480,8 +480,9 @@ CONTEXT: COPY copy_regression_newline, line 1: "1|1
-- negative: csv
COPY copy_regression_newline from stdin with delimiter '|' newline 'cr' csv;
ERROR: extra data after last expected column
-CONTEXT: COPY xxxxx line x: xxx
+CONTEXT: COPY copy_regression_newline, line 1: "1|1
2|2
+\.
"
-- negative: invalid newline
COPY copy_regression_newline from stdin with delimiter '|' newline 'blah';
diff --git a/src/test/singlenode_regress/sql/create_view.sql
b/src/test/singlenode_regress/sql/create_view.sql
index 9569e3a181d..674f361e2c8 100644
--- a/src/test/singlenode_regress/sql/create_view.sql
+++ b/src/test/singlenode_regress/sql/create_view.sql
@@ -11,33 +11,6 @@ set Debug_print_plan=on;
-- (this also tests the query rewrite system)
--
--- directory paths and dlsuffix are passed to us in environment variables
-\getenv abs_srcdir PG_ABS_SRCDIR
-\getenv libdir PG_LIBDIR
-\getenv dlsuffix PG_DLSUFFIX
-
-\set regresslib :libdir '/regress' :dlsuffix
-
-CREATE FUNCTION interpt_pp(path, path)
- RETURNS point
- AS :'regresslib'
- LANGUAGE C STRICT;
-
-CREATE TABLE real_city (
- pop int4,
- cname text,
- outline path
-);
-
-\set filename :abs_srcdir '/data/real_city.data'
-COPY real_city FROM :'filename';
-ANALYZE real_city;
-
-SELECT *
- INTO TABLE ramp
- FROM ONLY road
- WHERE name ~ '.*Ramp';
-
CREATE VIEW street AS
SELECT r.name, r.thepath, c.cname AS cname
FROM ONLY road r, real_city c
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
