[Bug] hot standby, testcase in mpp/gpdb/tests/queries/basic/external_partition with temp table possile failed

Open tuhaihe opened this issue 2 years ago • 0 comments

Cloudberry Database version

main

What happened

case1:

mpp/gpdb/tests/queries/basic/external_partition/output/copy.sql
tinc.py discover  mpp/gpdb/tests/queries/basic/external_partition

output file

-- start_ignore
-- end_ignore
-- @author yaok1
-- @created 2015-07-11 12:00:00
-- @modified 2015-07-11 12:00:00
-- @tags external_partition 
-- @gpdiff true 
-- @description check copy's behavior regarding external partition.
--start_ignore
drop table if exists pt_ext;
psql:/home/gpadmin/workspace/cbdb_testrepo/mpp/gpdb/tests/queries/basic/external_partition/output/copy.sql:13: NOTICE:  table "pt_ext" does not exist, skipping
DROP TABLE
CREATE TABLE pt_ext
(
  col1 int,
  col2 decimal,
  col3 text,
  col4 bool
)
distributed by (col1)
partition by list(col2)
(
	partition part1 values(1,2,3,4,5,6,7,8,9,10),
	partition part2 values(11,12,13,14,15,16,17,18,19,20),
	partition part3 values(21,22,23,24,25,26,27,28,29,30),
	partition part4 values(31,32,33,34,35,36,37,38,39,40),
	partition part5 values(41,42,43,44,45,46,47,48,49,50)
);
CREATE TABLE
insert into pt_ext select i,i,'test',true from generate_series(1,50) i;
INSERT 0 50
create temp table tmp as select * from pt_ext where col1 < 11;
psql:/home/gpadmin/workspace/cbdb_testrepo/mpp/gpdb/tests/queries/basic/external_partition/output/copy.sql:32: NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause. Creating a NULL policy entry.
psql:/home/gpadmin/workspace/cbdb_testrepo/mpp/gpdb/tests/queries/basic/external_partition/output/copy.sql:32: NOTICE:  One or more columns in the following table(s) do not have statistics: pt_ext
HINT:  For non-partitioned tables, run analyze <table_name>(<column_list>). For partitioned tables, run analyze rootpartition <table_name>(<column_list>). See log for columns missing statistics.
psql:/home/gpadmin/workspace/cbdb_testrepo/mpp/gpdb/tests/queries/basic/external_partition/output/copy.sql:32: ERROR:  relation "tmp" already exists  (seg0 127.0.0.1:7002 pid=22793)
\! rm /tmp/exttab_list
rm: cannot remove ‘/tmp/exttab_list’: No such file or directory
copy tmp to '/tmp/exttab_list' csv;
psql:/home/gpadmin/workspace/cbdb_testrepo/mpp/gpdb/tests/queries/basic/external_partition/output/copy.sql:34: ERROR:  relation "tmp" does not exist
create readable external table ret(like pt_ext) location('file://localhost/tmp/exttab_list') format 'csv';
CREATE EXTERNAL TABLE
alter table pt_ext exchange partition part1 with table ret without validation;
psql:/home/gpadmin/workspace/cbdb_testrepo/mpp/gpdb/tests/queries/basic/external_partition/output/copy.sql:36: NOTICE:  specifying "WITHOUT VALIDATION" acts as no operation. If the new partition is a regular table, validation is performed to make sure all the rows obey partition constraint. If the new partition is external or foreign table, no validation is performed.
ALTER TABLE
drop table ret;
DROP TABLE
--end_ignore
copy (select * from pt_ext order by col1) to stdout;
psql:/home/gpadmin/workspace/cbdb_testrepo/mpp/gpdb/tests/queries/basic/external_partition/output/copy.sql:40: NOTICE:  gfile stat /tmp/exttab_list failure: No such file or directory  (seg0 slice1 127.0.0.1:7002 pid=22793)
psql:/home/gpadmin/workspace/cbdb_testrepo/mpp/gpdb/tests/queries/basic/external_partition/output/copy.sql:40: NOTICE:  fstream unable to open file /tmp/exttab_list  (seg0 slice1 127.0.0.1:7002 pid=22793)
psql:/home/gpadmin/workspace/cbdb_testrepo/mpp/gpdb/tests/queries/basic/external_partition/output/copy.sql:40: ERROR:  could not open file "/tmp/exttab_list": 404 file not found  (seg0 slice1 127.0.0.1:7002 pid=22793)
copy pt_ext to '/tmp/exttab_list' ignore external partitions;
psql:/home/gpadmin/workspace/cbdb_testrepo/mpp/gpdb/tests/queries/basic/external_partition/output/copy.sql:42: NOTICE:  COPY ignores external partition(s)
COPY 40
copy pt_ext from '/tmp/exttab_list';
COPY 40
\! rm /tmp/exttab_list

What you think should happen instead

expected ans file

-- start_ignore
-- end_ignore
-- @author yaok1
-- @created 2015-07-11 12:00:00
-- @modified 2015-07-11 12:00:00
-- @tags external_partition 
-- @gpdiff true 
-- @description check copy's behavior regarding external partition.
--start_ignore
drop table if exists pt_ext;
DROP TABLE
CREATE TABLE pt_ext
(
  col1 int,
  col2 decimal,
  col3 text,
  col4 bool
)
distributed by (col1)
partition by list(col2)
(
	partition part1 values(1,2,3,4,5,6,7,8,9,10),
	partition part2 values(11,12,13,14,15,16,17,18,19,20),
	partition part3 values(21,22,23,24,25,26,27,28,29,30),
	partition part4 values(31,32,33,34,35,36,37,38,39,40),
	partition part5 values(41,42,43,44,45,46,47,48,49,50)
);
psql:/data/home/gpadmin/workspace/tinc_main/tincmetro/partitioning/externalpartition/output/copy.sql:29: NOTICE:  CREATE TABLE will create partition "pt_ext_1_prt_part1" for table "pt_ext"
psql:/data/home/gpadmin/workspace/tinc_main/tincmetro/partitioning/externalpartition/output/copy.sql:29: NOTICE:  CREATE TABLE will create partition "pt_ext_1_prt_part2" for table "pt_ext"
psql:/data/home/gpadmin/workspace/tinc_main/tincmetro/partitioning/externalpartition/output/copy.sql:29: NOTICE:  CREATE TABLE will create partition "pt_ext_1_prt_part3" for table "pt_ext"
psql:/data/home/gpadmin/workspace/tinc_main/tincmetro/partitioning/externalpartition/output/copy.sql:29: NOTICE:  CREATE TABLE will create partition "pt_ext_1_prt_part4" for table "pt_ext"
psql:/data/home/gpadmin/workspace/tinc_main/tincmetro/partitioning/externalpartition/output/copy.sql:29: NOTICE:  CREATE TABLE will create partition "pt_ext_1_prt_part5" for table "pt_ext"
CREATE TABLE
insert into pt_ext select i,i,'test',true from generate_series(1,50) i;
INSERT 0 50
create temp table tmp as select * from pt_ext where col1 < 11;
psql:/data/home/gpadmin/workspace/tinc_main/tincmetro/partitioning/externalpartition/output/copy.sql:32: NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'col1' 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.
SELECT 10
\! rm /tmp/exttab_list
copy tmp to '/tmp/exttab_list' csv;
COPY 10
create readable external table ret(like pt_ext) location('file://mdw/tmp/exttab_list') format 'csv';
CREATE EXTERNAL TABLE
alter table pt_ext exchange partition part1 with table ret without validation;
ALTER TABLE
drop table ret;
DROP TABLE
--end_ignore
copy (select * from pt_ext order by col1) to stdout;
1	1	test	t
2	2	test	t
3	3	test	t
4	4	test	t
5	5	test	t
6	6	test	t
7	7	test	t
8	8	test	t
9	9	test	t
10	10	test	t
11	11	test	t
12	12	test	t
13	13	test	t
14	14	test	t
15	15	test	t
16	16	test	t
17	17	test	t
18	18	test	t
19	19	test	t
20	20	test	t
21	21	test	t
22	22	test	t
23	23	test	t
24	24	test	t
25	25	test	t
26	26	test	t
27	27	test	t
28	28	test	t
29	29	test	t
30	30	test	t
31	31	test	t
32	32	test	t
33	33	test	t
34	34	test	t
35	35	test	t
36	36	test	t
37	37	test	t
38	38	test	t
39	39	test	t
40	40	test	t
41	41	test	t
42	42	test	t
43	43	test	t
44	44	test	t
45	45	test	t
46	46	test	t
47	47	test	t
48	48	test	t
49	49	test	t
50	50	test	t
copy pt_ext to '/tmp/exttab_list' ignore external partitions;
psql:/data/home/gpadmin/workspace/tinc_main/tincmetro/partitioning/externalpartition/output/copy.sql:44: NOTICE:  COPY ignores external partition(s)
COPY 40
copy pt_ext from '/tmp/exttab_list';
COPY 40
\! rm /tmp/exttab_list

How to reproduce

Run the above cmds.

Operating System

Default

Anything else

No response

Are you willing to submit PR?

[ ] Yes, I am willing to submit a PR!

Code of Conduct

[X] I agree to follow this project's Code of Conduct.

Jul 24 '23 08:07 tuhaihe