Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit6a9c7d3

Browse files
authored
Merge pull request#270 from postgrespro/PGPRO-8166
PGPRO-8166: Fix build with vanilla at db93e739ac.
2 parentsba2530b +4f9a602 commit6a9c7d3

File tree

4 files changed

+383
-5
lines changed

4 files changed

+383
-5
lines changed

‎expected/pathman_column_type_2.out

Lines changed: 203 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,203 @@
1+
/*
2+
* In 9ce77d75c5a (>= 13) struct Var was changed, which caused the output
3+
* of get_partition_cooked_key to change.
4+
*/
5+
\set VERBOSITY terse
6+
SET search_path = 'public';
7+
CREATE EXTENSION pg_pathman;
8+
CREATE SCHEMA test_column_type;
9+
/*
10+
* RANGE partitioning.
11+
*/
12+
/* create new table (val int) */
13+
CREATE TABLE test_column_type.test(val INT4 NOT NULL);
14+
SELECT create_range_partitions('test_column_type.test', 'val', 1, 10, 10);
15+
create_range_partitions
16+
-------------------------
17+
10
18+
(1 row)
19+
20+
/* make sure that bounds and dispatch info has been cached */
21+
SELECT * FROM test_column_type.test;
22+
val
23+
-----
24+
(0 rows)
25+
26+
SELECT context, entries FROM pathman_cache_stats
27+
WHERE context != 'partition status cache' ORDER BY context;
28+
context | entries
29+
-------------------------+---------
30+
maintenance | 0
31+
partition bounds cache | 10
32+
partition parents cache | 10
33+
(3 rows)
34+
35+
/*
36+
* Get parsed and analyzed expression.
37+
*/
38+
CREATE FUNCTION get_cached_partition_cooked_key(REGCLASS)
39+
RETURNS TEXT AS 'pg_pathman', 'get_cached_partition_cooked_key_pl'
40+
LANGUAGE C STRICT;
41+
SELECT get_partition_cooked_key('test_column_type.test'::REGCLASS);
42+
get_partition_cooked_key
43+
---------------------------------------------------------------------------------------------------------------------------------------------
44+
{VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 1 :location 8}
45+
(1 row)
46+
47+
SELECT get_cached_partition_cooked_key('test_column_type.test'::REGCLASS);
48+
get_cached_partition_cooked_key
49+
---------------------------------------------------------------------------------------------------------------------------------------------
50+
{VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 1 :location 8}
51+
(1 row)
52+
53+
SELECT get_partition_key_type('test_column_type.test'::REGCLASS);
54+
get_partition_key_type
55+
------------------------
56+
integer
57+
(1 row)
58+
59+
/* change column's type (should also flush caches) */
60+
ALTER TABLE test_column_type.test ALTER val TYPE NUMERIC;
61+
/* check that correct expression has been built */
62+
SELECT get_partition_key_type('test_column_type.test'::REGCLASS);
63+
get_partition_key_type
64+
------------------------
65+
numeric
66+
(1 row)
67+
68+
SELECT get_partition_cooked_key('test_column_type.test'::REGCLASS);
69+
get_partition_cooked_key
70+
-----------------------------------------------------------------------------------------------------------------------------------------------
71+
{VAR :varno 1 :varattno 1 :vartype 1700 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 1 :location 8}
72+
(1 row)
73+
74+
SELECT get_cached_partition_cooked_key('test_column_type.test'::REGCLASS);
75+
get_cached_partition_cooked_key
76+
-----------------------------------------------------------------------------------------------------------------------------------------------
77+
{VAR :varno 1 :varattno 1 :vartype 1700 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 1 :location 8}
78+
(1 row)
79+
80+
DROP FUNCTION get_cached_partition_cooked_key(REGCLASS);
81+
/* make sure that everything works properly */
82+
SELECT * FROM test_column_type.test;
83+
val
84+
-----
85+
(0 rows)
86+
87+
SELECT context, entries FROM pathman_cache_stats
88+
WHERE context != 'partition status cache' ORDER BY context;
89+
context | entries
90+
-------------------------+---------
91+
maintenance | 0
92+
partition bounds cache | 10
93+
partition parents cache | 10
94+
(3 rows)
95+
96+
/* check insert dispatching */
97+
INSERT INTO test_column_type.test VALUES (1);
98+
SELECT tableoid::regclass, * FROM test_column_type.test;
99+
tableoid | val
100+
-------------------------+-----
101+
test_column_type.test_1 | 1
102+
(1 row)
103+
104+
SELECT drop_partitions('test_column_type.test');
105+
NOTICE: 1 rows copied from test_column_type.test_1
106+
NOTICE: 0 rows copied from test_column_type.test_2
107+
NOTICE: 0 rows copied from test_column_type.test_3
108+
NOTICE: 0 rows copied from test_column_type.test_4
109+
NOTICE: 0 rows copied from test_column_type.test_5
110+
NOTICE: 0 rows copied from test_column_type.test_6
111+
NOTICE: 0 rows copied from test_column_type.test_7
112+
NOTICE: 0 rows copied from test_column_type.test_8
113+
NOTICE: 0 rows copied from test_column_type.test_9
114+
NOTICE: 0 rows copied from test_column_type.test_10
115+
drop_partitions
116+
-----------------
117+
10
118+
(1 row)
119+
120+
DROP TABLE test_column_type.test CASCADE;
121+
/*
122+
* HASH partitioning.
123+
*/
124+
/* create new table (id int, val int) */
125+
CREATE TABLE test_column_type.test(id INT4 NOT NULL, val INT4);
126+
SELECT create_hash_partitions('test_column_type.test', 'id', 5);
127+
create_hash_partitions
128+
------------------------
129+
5
130+
(1 row)
131+
132+
/* make sure that bounds and dispatch info has been cached */
133+
SELECT * FROM test_column_type.test;
134+
id | val
135+
----+-----
136+
(0 rows)
137+
138+
SELECT context, entries FROM pathman_cache_stats
139+
WHERE context != 'partition status cache' ORDER BY context;
140+
context | entries
141+
-------------------------+---------
142+
maintenance | 0
143+
partition bounds cache | 5
144+
partition parents cache | 5
145+
(3 rows)
146+
147+
/* change column's type (should NOT work) */
148+
ALTER TABLE test_column_type.test ALTER id TYPE NUMERIC;
149+
ERROR: cannot change type of column "id" of table "test" partitioned by HASH
150+
/* make sure that everything works properly */
151+
SELECT * FROM test_column_type.test;
152+
id | val
153+
----+-----
154+
(0 rows)
155+
156+
SELECT context, entries FROM pathman_cache_stats
157+
WHERE context != 'partition status cache' ORDER BY context;
158+
context | entries
159+
-------------------------+---------
160+
maintenance | 0
161+
partition bounds cache | 5
162+
partition parents cache | 5
163+
(3 rows)
164+
165+
/* change column's type (should flush caches) */
166+
ALTER TABLE test_column_type.test ALTER val TYPE NUMERIC;
167+
/* make sure that everything works properly */
168+
SELECT * FROM test_column_type.test;
169+
id | val
170+
----+-----
171+
(0 rows)
172+
173+
SELECT context, entries FROM pathman_cache_stats
174+
WHERE context != 'partition status cache' ORDER BY context;
175+
context | entries
176+
-------------------------+---------
177+
maintenance | 0
178+
partition bounds cache | 5
179+
partition parents cache | 5
180+
(3 rows)
181+
182+
/* check insert dispatching */
183+
INSERT INTO test_column_type.test VALUES (1);
184+
SELECT tableoid::regclass, * FROM test_column_type.test;
185+
tableoid | id | val
186+
-------------------------+----+-----
187+
test_column_type.test_0 | 1 |
188+
(1 row)
189+
190+
SELECT drop_partitions('test_column_type.test');
191+
NOTICE: 1 rows copied from test_column_type.test_0
192+
NOTICE: 0 rows copied from test_column_type.test_1
193+
NOTICE: 0 rows copied from test_column_type.test_2
194+
NOTICE: 0 rows copied from test_column_type.test_3
195+
NOTICE: 0 rows copied from test_column_type.test_4
196+
drop_partitions
197+
-----------------
198+
5
199+
(1 row)
200+
201+
DROP TABLE test_column_type.test CASCADE;
202+
DROP SCHEMA test_column_type;
203+
DROP EXTENSION pg_pathman;

‎expected/pathman_join_clause_5.out

Lines changed: 160 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,160 @@
1+
/*
2+
* Since 8edd0e794 (>= 12) Append nodes with single subplan are eliminated,
3+
* causing different output; pathman_gaps_1.out is the updated version.
4+
*/
5+
\set VERBOSITY terse
6+
SET search_path = 'public';
7+
CREATE SCHEMA pathman;
8+
CREATE EXTENSION pg_pathman SCHEMA pathman;
9+
CREATE SCHEMA test;
10+
/*
11+
* Test push down a join clause into child nodes of append
12+
*/
13+
/* create test tables */
14+
CREATE TABLE test.fk (
15+
id1 INT NOT NULL,
16+
id2 INT NOT NULL,
17+
start_key INT,
18+
end_key INT,
19+
PRIMARY KEY (id1, id2));
20+
CREATE TABLE test.mytbl (
21+
id1 INT NOT NULL,
22+
id2 INT NOT NULL,
23+
key INT NOT NULL,
24+
CONSTRAINT fk_fk FOREIGN KEY (id1, id2) REFERENCES test.fk(id1, id2),
25+
PRIMARY KEY (id1, key));
26+
SELECT pathman.create_hash_partitions('test.mytbl', 'id1', 8);
27+
create_hash_partitions
28+
------------------------
29+
8
30+
(1 row)
31+
32+
/* ...fill out with test data */
33+
INSERT INTO test.fk VALUES (1, 1);
34+
INSERT INTO test.mytbl VALUES (1, 1, 5), (1, 1, 6);
35+
/* gather statistics on test tables to have deterministic plans */
36+
ANALYZE;
37+
/* run test queries */
38+
EXPLAIN (COSTS OFF) /* test plan */
39+
SELECT m.tableoid::regclass, id1, id2, key, start_key, end_key
40+
FROM test.mytbl m JOIN test.fk USING(id1, id2)
41+
WHERE NOT key <@ int4range(6, end_key);
42+
QUERY PLAN
43+
-------------------------------------------------------------------------------------------------------
44+
Nested Loop
45+
-> Seq Scan on fk
46+
-> Custom Scan (RuntimeAppend)
47+
Prune by: (m.id1 = fk.id1)
48+
-> Seq Scan on mytbl_0 m
49+
Filter: ((id1 = fk.id1) AND (fk.id2 = id2) AND (NOT (key <@ int4range(6, fk.end_key))))
50+
-> Seq Scan on mytbl_1 m
51+
Filter: ((id1 = fk.id1) AND (fk.id2 = id2) AND (NOT (key <@ int4range(6, fk.end_key))))
52+
-> Seq Scan on mytbl_2 m
53+
Filter: ((id1 = fk.id1) AND (fk.id2 = id2) AND (NOT (key <@ int4range(6, fk.end_key))))
54+
-> Seq Scan on mytbl_3 m
55+
Filter: ((id1 = fk.id1) AND (fk.id2 = id2) AND (NOT (key <@ int4range(6, fk.end_key))))
56+
-> Seq Scan on mytbl_4 m
57+
Filter: ((id1 = fk.id1) AND (fk.id2 = id2) AND (NOT (key <@ int4range(6, fk.end_key))))
58+
-> Seq Scan on mytbl_5 m
59+
Filter: ((id1 = fk.id1) AND (fk.id2 = id2) AND (NOT (key <@ int4range(6, fk.end_key))))
60+
-> Seq Scan on mytbl_6 m
61+
Filter: ((id1 = fk.id1) AND (fk.id2 = id2) AND (NOT (key <@ int4range(6, fk.end_key))))
62+
-> Seq Scan on mytbl_7 m
63+
Filter: ((id1 = fk.id1) AND (fk.id2 = id2) AND (NOT (key <@ int4range(6, fk.end_key))))
64+
(20 rows)
65+
66+
/* test joint data */
67+
SELECT m.tableoid::regclass, id1, id2, key, start_key, end_key
68+
FROM test.mytbl m JOIN test.fk USING(id1, id2)
69+
WHERE NOT key <@ int4range(6, end_key);
70+
tableoid | id1 | id2 | key | start_key | end_key
71+
--------------+-----+-----+-----+-----------+---------
72+
test.mytbl_6 | 1 | 1 | 5 | |
73+
(1 row)
74+
75+
/*
76+
* Test case by @dimarick
77+
*/
78+
CREATE TABLE test.parent (
79+
id SERIAL NOT NULL,
80+
owner_id INTEGER NOT NULL
81+
);
82+
CREATE TABLE test.child (
83+
parent_id INTEGER NOT NULL,
84+
owner_id INTEGER NOT NULL
85+
);
86+
CREATE TABLE test.child_nopart (
87+
parent_id INTEGER NOT NULL,
88+
owner_id INTEGER NOT NULL
89+
);
90+
INSERT INTO test.parent (owner_id) VALUES (1), (2), (3), (3);
91+
INSERT INTO test.child (parent_id, owner_id) VALUES (1, 1), (2, 2), (3, 3), (5, 3);
92+
INSERT INTO test.child_nopart (parent_id, owner_id) VALUES (1, 1), (2, 2), (3, 3), (5, 3);
93+
SELECT pathman.create_hash_partitions('test.child', 'owner_id', 2);
94+
create_hash_partitions
95+
------------------------
96+
2
97+
(1 row)
98+
99+
/* gather statistics on test tables to have deterministic plans */
100+
ANALYZE;
101+
/* Query #1 */
102+
EXPLAIN (COSTS OFF) SELECT * FROM test.parent
103+
LEFT JOIN test.child ON test.child.parent_id = test.parent.id AND
104+
test.child.owner_id = test.parent.owner_id
105+
WHERE test.parent.owner_id = 3 and test.parent.id IN (3, 4);
106+
QUERY PLAN
107+
----------------------------------------------------------------------
108+
Nested Loop Left Join
109+
Join Filter: (child.parent_id = parent.id)
110+
-> Seq Scan on parent
111+
Filter: ((id = ANY ('{3,4}'::integer[])) AND (owner_id = 3))
112+
-> Seq Scan on child_1 child
113+
Filter: (owner_id = 3)
114+
(6 rows)
115+
116+
SELECT * FROM test.parent
117+
LEFT JOIN test.child ON test.child.parent_id = test.parent.id AND
118+
test.child.owner_id = test.parent.owner_id
119+
WHERE test.parent.owner_id = 3 and test.parent.id IN (3, 4);
120+
id | owner_id | parent_id | owner_id
121+
----+----------+-----------+----------
122+
3 | 3 | 3 | 3
123+
4 | 3 | |
124+
(2 rows)
125+
126+
/* Query #2 */
127+
EXPLAIN (COSTS OFF) SELECT * FROM test.parent
128+
LEFT JOIN test.child ON test.child.parent_id = test.parent.id AND
129+
test.child.owner_id = 3
130+
WHERE test.parent.owner_id = 3 and test.parent.id IN (3, 4);
131+
QUERY PLAN
132+
----------------------------------------------------------------------
133+
Nested Loop Left Join
134+
Join Filter: (child.parent_id = parent.id)
135+
-> Seq Scan on parent
136+
Filter: ((id = ANY ('{3,4}'::integer[])) AND (owner_id = 3))
137+
-> Seq Scan on child_1 child
138+
Filter: (owner_id = 3)
139+
(6 rows)
140+
141+
SELECT * FROM test.parent
142+
LEFT JOIN test.child ON test.child.parent_id = test.parent.id AND
143+
test.child.owner_id = 3
144+
WHERE test.parent.owner_id = 3 and test.parent.id IN (3, 4);
145+
id | owner_id | parent_id | owner_id
146+
----+----------+-----------+----------
147+
3 | 3 | 3 | 3
148+
4 | 3 | |
149+
(2 rows)
150+
151+
DROP TABLE test.child CASCADE;
152+
NOTICE: drop cascades to 2 other objects
153+
DROP TABLE test.child_nopart CASCADE;
154+
DROP TABLE test.mytbl CASCADE;
155+
NOTICE: drop cascades to 8 other objects
156+
DROP TABLE test.fk CASCADE;
157+
DROP TABLE test.parent CASCADE;
158+
DROP SCHEMA test;
159+
DROP EXTENSION pg_pathman CASCADE;
160+
DROP SCHEMA pathman;

‎src/hooks.c

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -449,12 +449,12 @@ pathman_rel_pathlist_hook(PlannerInfo *root,
449449
tce=lookup_type_cache(prel->ev_type,TYPECACHE_LT_OPR |TYPECACHE_GT_OPR);
450450

451451
/* Make pathkeys */
452-
pathkeys=build_expression_pathkey(root, (Expr*)part_expr,NULL,
453-
tce->lt_opr,NULL, false);
452+
pathkeys=build_expression_pathkey_compat(root, (Expr*)part_expr,NULL,
453+
tce->lt_opr,NULL, false);
454454
if (pathkeys)
455455
pathkeyAsc= (PathKey*)linitial(pathkeys);
456-
pathkeys=build_expression_pathkey(root, (Expr*)part_expr,NULL,
457-
tce->gt_opr,NULL, false);
456+
pathkeys=build_expression_pathkey_compat(root, (Expr*)part_expr,NULL,
457+
tce->gt_opr,NULL, false);
458458
if (pathkeys)
459459
pathkeyDesc= (PathKey*)linitial(pathkeys);
460460
}

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp