Skip to content

Commit 487ec8e

Browse files
funbringerfunbringer
committed
fix create_append_plan_common() for 'for share\update', add regression tests
1 parent 92e46ac commit 487ec8e

File tree

6 files changed

+233
-98
lines changed

6 files changed

+233
-98
lines changed

‎expected/pg_pathman.out

Lines changed: 111 additions & 50 deletions
Original file line numberDiff line numberDiff line change
@@ -12,8 +12,6 @@ SELECT pathman.create_hash_partitions('test.hash_rel', 'value', 3);
1212
ERROR: Partitioning key 'value' must be NOT NULL
1313
ALTER TABLE test.hash_rel ALTER COLUMN value SET NOT NULL;
1414
SELECT pathman.create_hash_partitions('test.hash_rel', 'Value', 3);
15-
NOTICE: function test.hash_rel_insert_trigger_func() does not exist, skipping
16-
NOTICE: function test.hash_rel_update_trigger_func() does not exist, skipping
1715
NOTICE: Copying data to partitions...
1816
create_hash_partitions
1917
------------------------
@@ -728,12 +726,60 @@ begin
728726
return 'ok';
729727
end;
730728
$$ language plpgsql;
729+
create or replace function test.pathman_test_5() returns text as $$
730+
declare
731+
res record;
732+
begin
733+
select
734+
from test.runtime_test_3
735+
where id = (select * from test.vals order by val limit 1)
736+
limit 1
737+
into res; /* test empty tlist */
738+
739+
740+
select id, generate_series(1, 2) gen, val
741+
from test.runtime_test_3
742+
where id = any (select * from test.vals order by val limit 5)
743+
order by id, gen, val
744+
offset 1 limit 1
745+
into res; /* without IndexOnlyScan */
746+
747+
perform test.pathman_equal(res.id::text, '1', 'id is incorrect (t2)');
748+
perform test.pathman_equal(res.gen::text, '2', 'gen is incorrect (t2)');
749+
perform test.pathman_equal(res.val::text, 'k = 1', 'val is incorrect (t2)');
750+
751+
752+
select id
753+
from test.runtime_test_3
754+
where id = any (select * from test.vals order by val limit 5)
755+
order by id
756+
offset 3 limit 1
757+
into res; /* with IndexOnlyScan */
758+
759+
perform test.pathman_equal(res.id::text, '4', 'id is incorrect (t3)');
760+
761+
762+
select v.val v1, generate_series(2, 2) gen, t.val v2
763+
from test.runtime_test_3 t join test.vals v on id = v.val
764+
order by v1, gen, v2
765+
limit 1
766+
into res;
767+
768+
perform test.pathman_equal(res.v1::text, '1', 'v1 is incorrect (t4)');
769+
perform test.pathman_equal(res.gen::text, '2', 'gen is incorrect (t4)');
770+
perform test.pathman_equal(res.v2::text, 'k = 1', 'v2 is incorrect (t4)');
771+
772+
return 'ok';
773+
end;
774+
$$ language plpgsql
775+
set pg_pathman.enable = true
776+
set enable_hashjoin = off
777+
set enable_mergejoin = off;
778+
NOTICE: RuntimeAppend, RuntimeMergeAppend and PartitionFilter nodes have been enabled
731779
create table test.run_values as select generate_series(1, 10000) val;
732780
create table test.runtime_test_1(id serial primary key, val real);
733781
insert into test.runtime_test_1 select generate_series(1, 10000), random();
734782
select pathman.create_hash_partitions('test.runtime_test_1', 'id', 6);
735-
NOTICE: function test.runtime_test_1_insert_trigger_func() does not exist, skipping
736-
NOTICE: function test.runtime_test_1_update_trigger_func() does not exist, skipping
737783
NOTICE: Copying data to partitions...
738784
create_hash_partitions
739785
------------------------
@@ -745,16 +791,29 @@ create table test.runtime_test_2 (id serial, category_id int not null, name text
745791
insert into test.runtime_test_2 (select id, (id % 6) + 1 as category_id, 'good' || id::text as name, random() as rating from generate_series(1, 100000) id);
746792
create index on test.runtime_test_2 (category_id, rating);
747793
select pathman.create_hash_partitions('test.runtime_test_2', 'category_id', 6);
748-
NOTICE: function test.runtime_test_2_insert_trigger_func() does not exist, skipping
749-
NOTICE: function test.runtime_test_2_update_trigger_func() does not exist, skipping
750794
NOTICE: Copying data to partitions...
751795
create_hash_partitions
752796
------------------------
753797
6
754798
(1 row)
755799

800+
create table test.vals as (select generate_series(1, 10000) as val);
801+
create table test.runtime_test_3(val text, id serial not null);
802+
insert into test.runtime_test_3(id, val) select * from generate_series(1, 10000) k, format('k = %s', k);
803+
select pathman.create_hash_partitions('test.runtime_test_3', 'id', 4);
804+
NOTICE: Copying data to partitions...
805+
create_hash_partitions
806+
------------------------
807+
4
808+
(1 row)
809+
810+
create index on test.runtime_test_3 (id);
811+
create index on test.runtime_test_3_0 (id);
756812
analyze test.run_values;
757813
analyze test.runtime_test_1;
814+
analyze test.runtime_test_2;
815+
analyze test.runtime_test_3;
816+
analyze test.runtime_test_3_0;
758817
set enable_mergejoin = off;
759818
set enable_hashjoin = off;
760819
set pg_pathman.enable_runtimeappend = on;
@@ -783,12 +842,18 @@ select test.pathman_test_4(); /* RuntimeMergeAppend (lateral) */
783842
ok
784843
(1 row)
785844

845+
select test.pathman_test_5(); /* projection tests for RuntimeXXX nodes */
846+
pathman_test_5
847+
----------------
848+
ok
849+
(1 row)
850+
786851
set pg_pathman.enable_runtimeappend = off;
787852
set pg_pathman.enable_runtimemergeappend = off;
788853
set enable_mergejoin = on;
789854
set enable_hashjoin = on;
790-
drop table test.run_values, test.runtime_test_1, test.runtime_test_2 cascade;
791-
NOTICE: drop cascades to 12 other objects
855+
drop table test.run_values, test.runtime_test_1, test.runtime_test_2, test.runtime_test_3, test.vals cascade;
856+
NOTICE: drop cascades to 16 other objects
792857
/*
793858
* Test split and merge
794859
*/
@@ -1009,15 +1074,14 @@ SELECT * FROM test.hash_rel WHERE id = 123;
10091074
/*
10101075
* Clean up
10111076
*/
1012-
SELECT pathman.drop_hash_partitions('test.hash_rel');
1013-
NOTICE: drop cascades to trigger test_hash_rel_insert_trigger on table test.hash_rel
1077+
SELECT pathman.drop_partitions('test.hash_rel');
10141078
NOTICE: drop cascades to 3 other objects
10151079
NOTICE: 2 rows copied from test.hash_rel_2
10161080
NOTICE: 3 rows copied from test.hash_rel_1
10171081
NOTICE: 2 rows copied from test.hash_rel_0
1018-
drop_hash_partitions
1019-
----------------------
1020-
3
1082+
drop_partitions
1083+
-----------------
1084+
3
10211085
(1 row)
10221086

10231087
SELECT COUNT(*) FROM ONLY test.hash_rel;
@@ -1027,20 +1091,17 @@ SELECT COUNT(*) FROM ONLY test.hash_rel;
10271091
(1 row)
10281092

10291093
SELECT pathman.create_hash_partitions('test.hash_rel', 'value', 3);
1030-
NOTICE: function test.hash_rel_insert_trigger_func() does not exist, skipping
1031-
NOTICE: function test.hash_rel_update_trigger_func() does not exist, skipping
10321094
NOTICE: Copying data to partitions...
10331095
create_hash_partitions
10341096
------------------------
10351097
3
10361098
(1 row)
10371099

1038-
SELECT pathman.drop_hash_partitions('test.hash_rel', TRUE);
1039-
NOTICE: drop cascades to trigger test_hash_rel_insert_trigger on table test.hash_rel
1100+
SELECT pathman.drop_partitions('test.hash_rel', TRUE);
10401101
NOTICE: function test.hash_rel_update_trigger_func() does not exist, skipping
1041-
drop_hash_partitions
1042-
----------------------
1043-
3
1102+
drop_partitions
1103+
-----------------
1104+
3
10441105
(1 row)
10451106

10461107
SELECT COUNT(*) FROM ONLY test.hash_rel;
@@ -1050,15 +1111,16 @@ SELECT COUNT(*) FROM ONLY test.hash_rel;
10501111
(1 row)
10511112

10521113
DROP TABLE test.hash_rel CASCADE;
1053-
SELECT pathman.drop_range_partitions('test.num_range_rel');
1114+
SELECT pathman.drop_partitions('test.num_range_rel');
1115+
NOTICE: drop cascades to 4 other objects
10541116
NOTICE: 0 rows copied from test.num_range_rel_6
10551117
NOTICE: 2 rows copied from test.num_range_rel_4
10561118
NOTICE: 1000 rows copied from test.num_range_rel_3
10571119
NOTICE: 1000 rows copied from test.num_range_rel_2
10581120
NOTICE: 998 rows copied from test.num_range_rel_1
1059-
drop_range_partitions
1060-
-----------------------
1061-
5
1121+
drop_partitions
1122+
-----------------
1123+
5
10621124
(1 row)
10631125

10641126
DROP TABLE test.num_range_rel CASCADE;
@@ -1172,8 +1234,6 @@ CREATE TABLE test."TeSt" (a INT NOT NULL, b INT);
11721234
SELECT pathman.create_hash_partitions('test.TeSt', 'a', 3);
11731235
ERROR: relation "test.test" does not exist at character 39
11741236
SELECT pathman.create_hash_partitions('test."TeSt"', 'a', 3);
1175-
NOTICE: function test.TeSt_insert_trigger_func() does not exist, skipping
1176-
NOTICE: function test.TeSt_update_trigger_func() does not exist, skipping
11771237
NOTICE: Copying data to partitions...
11781238
create_hash_partitions
11791239
------------------------
@@ -1222,15 +1282,14 @@ EXPLAIN (COSTS OFF) SELECT * FROM test."TeSt" WHERE a = 1;
12221282
Filter: (a = 1)
12231283
(3 rows)
12241284

1225-
SELECT pathman.drop_hash_partitions('test."TeSt"');
1226-
NOTICE: drop cascades to trigger test_TeSt_insert_trigger on table test."TeSt"
1285+
SELECT pathman.drop_partitions('test."TeSt"');
12271286
NOTICE: drop cascades to 3 other objects
12281287
NOTICE: 0 rows copied from test."TeSt_2"
12291288
NOTICE: 3 rows copied from test."TeSt_1"
12301289
NOTICE: 0 rows copied from test."TeSt_0"
1231-
drop_hash_partitions
1232-
----------------------
1233-
3
1290+
drop_partitions
1291+
-----------------
1292+
3
12341293
(1 row)
12351294

12361295
SELECT * FROM test."TeSt";
@@ -1291,15 +1350,16 @@ NOTICE: Done!
12911350
{12-31-2014,01-02-2015}
12921351
(1 row)
12931352

1294-
SELECT pathman.drop_range_partitions('test."RangeRel"');
1353+
SELECT pathman.drop_partitions('test."RangeRel"');
1354+
NOTICE: function test.RangeRel_update_trigger_func() does not exist, skipping
12951355
NOTICE: 1 rows copied from test."RangeRel_6"
12961356
NOTICE: 0 rows copied from test."RangeRel_4"
12971357
NOTICE: 1 rows copied from test."RangeRel_3"
12981358
NOTICE: 1 rows copied from test."RangeRel_2"
12991359
NOTICE: 0 rows copied from test."RangeRel_1"
1300-
drop_range_partitions
1301-
-----------------------
1302-
5
1360+
drop_partitions
1361+
-----------------
1362+
5
13031363
(1 row)
13041364

13051365
SELECT pathman.create_partitions_from_range('test."RangeRel"', 'dt', '2015-01-01'::DATE, '2015-01-05'::DATE, '1 day'::INTERVAL);
@@ -1314,7 +1374,7 @@ NOTICE: drop cascades to 5 other objects
13141374
SELECT * FROM pathman.pathman_config;
13151375
id | relname | attname | parttype | range_interval
13161376
----+--------------------+---------+----------+----------------
1317-
8 | test.num_range_rel | id | 2 | 1000
1377+
9 | test.num_range_rel | id | 2 | 1000
13181378
(1 row)
13191379

13201380
CREATE TABLE test."RangeRel" (
@@ -1328,13 +1388,14 @@ NOTICE: Copying data to partitions...
13281388
3
13291389
(1 row)
13301390

1331-
SELECT pathman.drop_range_partitions('test."RangeRel"');
1391+
SELECT pathman.drop_partitions('test."RangeRel"');
1392+
NOTICE: function test.RangeRel_update_trigger_func() does not exist, skipping
13321393
NOTICE: 0 rows copied from test."RangeRel_3"
13331394
NOTICE: 0 rows copied from test."RangeRel_2"
13341395
NOTICE: 0 rows copied from test."RangeRel_1"
1335-
drop_range_partitions
1336-
-----------------------
1337-
3
1396+
drop_partitions
1397+
-----------------
1398+
3
13381399
(1 row)
13391400

13401401
SELECT pathman.create_partitions_from_range('test."RangeRel"', 'id', 1, 300, 100);
@@ -1355,8 +1416,6 @@ CREATE TABLE hash_rel (
13551416
value INTEGER NOT NULL);
13561417
INSERT INTO hash_rel (value) SELECT g FROM generate_series(1, 10000) as g;
13571418
SELECT create_hash_partitions('hash_rel', 'value', 3);
1358-
NOTICE: function public.hash_rel_insert_trigger_func() does not exist, skipping
1359-
NOTICE: function public.hash_rel_update_trigger_func() does not exist, skipping
13601419
NOTICE: Copying data to partitions...
13611420
create_hash_partitions
13621421
------------------------
@@ -1503,7 +1562,8 @@ EXPLAIN (COSTS OFF) DELETE FROM range_rel r USING tmp t WHERE r.dt = '2010-01-02
15031562

15041563
DELETE FROM range_rel r USING tmp t WHERE r.dt = '2010-01-02' AND r.id = t.id;
15051564
/* Create range partitions from whole range */
1506-
SELECT drop_range_partitions('range_rel');
1565+
SELECT drop_partitions('range_rel');
1566+
NOTICE: function public.range_rel_update_trigger_func() does not exist, skipping
15071567
NOTICE: 0 rows copied from range_rel_15
15081568
NOTICE: 0 rows copied from range_rel_14
15091569
NOTICE: 14 rows copied from range_rel_13
@@ -1518,9 +1578,9 @@ NOTICE: 31 rows copied from range_rel_5
15181578
NOTICE: 30 rows copied from range_rel_4
15191579
NOTICE: 31 rows copied from range_rel_3
15201580
NOTICE: 44 rows copied from range_rel_1
1521-
drop_range_partitions
1522-
-----------------------
1523-
14
1581+
drop_partitions
1582+
-----------------
1583+
14
15241584
(1 row)
15251585

15261586
SELECT create_partitions_from_range('range_rel', 'id', 1, 1000, 100);
@@ -1530,10 +1590,11 @@ NOTICE: Copying data to partitions...
15301590
10
15311591
(1 row)
15321592

1533-
SELECT drop_range_partitions('range_rel', TRUE);
1534-
drop_range_partitions
1535-
-----------------------
1536-
10
1593+
SELECT drop_partitions('range_rel', TRUE);
1594+
NOTICE: function public.range_rel_update_trigger_func() does not exist, skipping
1595+
drop_partitions
1596+
-----------------
1597+
10
15371598
(1 row)
15381599

15391600
SELECT create_partitions_from_range('range_rel', 'dt', '2015-01-01'::date, '2015-12-01'::date, '1 month'::interval);

0 commit comments

Comments
 (0)