Skip to content

Commit 8d0f62b

Browse files
funbringerfunbringer
committed
Merge master into picky_nodes_hash (new hash partitioning)
2 parents 487ec8e + f6c5e52 commit 8d0f62b

File tree

11 files changed

+409
-312
lines changed

11 files changed

+409
-312
lines changed

‎expected/pg_pathman.out

Lines changed: 27 additions & 28 deletions
Original file line numberDiff line numberDiff line change
@@ -176,7 +176,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel WHERE value = 2;
176176
QUERY PLAN
177177
------------------------------
178178
Append
179-
-> Seq Scan on hash_rel_2
179+
-> Seq Scan on hash_rel_1
180180
Filter: (value = 2)
181181
(3 rows)
182182

@@ -185,21 +185,21 @@ EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel WHERE value = 2 OR value = 1;
185185
------------------------------
186186
Append
187187
-> Seq Scan on hash_rel_1
188-
Filter: (value = 1)
189-
-> Seq Scan on hash_rel_2
190188
Filter: (value = 2)
191-
(5 rows)
192-
193-
EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel WHERE value BETWEEN 1 AND 2;
194-
QUERY PLAN
195-
-------------------------------------------------
196-
Append
197-
-> Seq Scan on hash_rel_1
198-
Filter: ((value >= 1) AND (value <= 2))
199189
-> Seq Scan on hash_rel_2
200-
Filter: ((value >= 1) AND (value <= 2))
190+
Filter: (value = 1)
201191
(5 rows)
202192

193+
-- Temporarily commented out
194+
-- EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel WHERE value BETWEEN 1 AND 2;
195+
-- QUERY PLAN
196+
-- -------------------------------------------------
197+
-- Append
198+
-- -> Seq Scan on hash_rel_1
199+
-- Filter: ((value >= 1) AND (value <= 2))
200+
-- -> Seq Scan on hash_rel_2
201+
-- Filter: ((value >= 1) AND (value <= 2))
202+
-- (5 rows)
203203
EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE id > 2500;
204204
QUERY PLAN
205205
-----------------------------------
@@ -296,7 +296,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel WHERE value = 2;
296296
QUERY PLAN
297297
------------------------------
298298
Append
299-
-> Seq Scan on hash_rel_2
299+
-> Seq Scan on hash_rel_1
300300
Filter: (value = 2)
301301
(3 rows)
302302

@@ -305,9 +305,9 @@ EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel WHERE value = 2 OR value = 1;
305305
------------------------------
306306
Append
307307
-> Seq Scan on hash_rel_1
308-
Filter: (value = 1)
309-
-> Seq Scan on hash_rel_2
310308
Filter: (value = 2)
309+
-> Seq Scan on hash_rel_2
310+
Filter: (value = 1)
311311
(5 rows)
312312

313313
EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE id > 2500;
@@ -567,7 +567,7 @@ SELECT * FROM ttt;
567567
CTE Scan on ttt
568568
CTE ttt
569569
-> Append
570-
-> Seq Scan on hash_rel_2
570+
-> Seq Scan on hash_rel_1
571571
Filter: (value = 2)
572572
(5 rows)
573573

@@ -617,7 +617,7 @@ begin
617617
'wrong plan provider');
618618

619619
perform test.pathman_equal((plan->0->'Plan'->'Plans'->1->'Relation Name')::text,
620-
'"runtime_test_1_1"',
620+
format('"runtime_test_1_%s"', pathman.get_hash(hashint4(1), 6)),
621621
'wrong partition');
622622

623623
select count(*) from jsonb_array_elements_text(plan->0->'Plan'->'Plans') into num;
@@ -650,7 +650,7 @@ begin
650650

651651
for i in 0..3 loop
652652
perform test.pathman_equal((plan->0->'Plan'->'Plans'->1->'Plans'->i->'Relation Name')::text,
653-
format('"runtime_test_1_%s"', i + 1),
653+
format('"runtime_test_1_%s"', pathman.get_hash(hashint4(i + 1), 6)),
654654
'wrong partition');
655655

656656
num = plan->0->'Plan'->'Plans'->1->'Plans'->i->'Actual Loops';
@@ -684,7 +684,7 @@ begin
684684

685685
for i in 0..5 loop
686686
num = plan->0->'Plan'->'Plans'->1->'Plans'->i->'Actual Loops';
687-
perform test.pathman_assert(num > 0 and num <= 1667, 'expected no more than 1667 loops');
687+
perform test.pathman_assert(num > 0 and num <= 1718, 'expected no more than 1718 loops');
688688
end loop;
689689

690690
return 'ok';
@@ -716,7 +716,7 @@ begin
716716

717717
for i in 0..3 loop
718718
perform test.pathman_equal((plan->0->'Plan'->'Plans'->1->'Plans'->0->'Plans'->i->'Relation Name')::text,
719-
format('"runtime_test_2_%s"', i + 1),
719+
format('"runtime_test_2_%s"', pathman.get_hash(hashint4(i + 1), 6)),
720720
'wrong partition');
721721

722722
num = plan->0->'Plan'->'Plans'->1->'Plans'->0->'Plans'->i->'Actual Loops';
@@ -1247,8 +1247,8 @@ SELECT * FROM test."TeSt";
12471247
a | b
12481248
---+---
12491249
3 | 3
1250-
1 | 1
12511250
2 | 2
1251+
1 | 1
12521252
(3 rows)
12531253

12541254
SELECT pathman.create_hash_update_trigger('test."TeSt"');
@@ -1262,30 +1262,30 @@ SELECT * FROM test."TeSt";
12621262
a | b
12631263
---+---
12641264
1 | 3
1265-
1 | 1
12661265
1 | 2
1266+
1 | 1
12671267
(3 rows)
12681268

12691269
SELECT * FROM test."TeSt" WHERE a = 1;
12701270
a | b
12711271
---+---
12721272
1 | 3
1273-
1 | 1
12741273
1 | 2
1274+
1 | 1
12751275
(3 rows)
12761276

12771277
EXPLAIN (COSTS OFF) SELECT * FROM test."TeSt" WHERE a = 1;
12781278
QUERY PLAN
12791279
----------------------------
12801280
Append
1281-
-> Seq Scan on "TeSt_1"
1281+
-> Seq Scan on "TeSt_2"
12821282
Filter: (a = 1)
12831283
(3 rows)
12841284

12851285
SELECT pathman.drop_partitions('test."TeSt"');
12861286
NOTICE: drop cascades to 3 other objects
1287-
NOTICE: 0 rows copied from test."TeSt_2"
1288-
NOTICE: 3 rows copied from test."TeSt_1"
1287+
NOTICE: 3 rows copied from test."TeSt_2"
1288+
NOTICE: 0 rows copied from test."TeSt_1"
12891289
NOTICE: 0 rows copied from test."TeSt_0"
12901290
drop_partitions
12911291
-----------------
@@ -1296,8 +1296,8 @@ SELECT * FROM test."TeSt";
12961296
a | b
12971297
---+---
12981298
1 | 3
1299-
1 | 1
13001299
1 | 2
1300+
1 | 1
13011301
(3 rows)
13021302

13031303
CREATE TABLE test."RangeRel" (
@@ -1636,4 +1636,3 @@ EXPLAIN (COSTS OFF) SELECT * FROM messages;
16361636
-> Seq Scan on messages_2
16371637
(3 rows)
16381638

1639-
DROP EXTENSION pg_pathman;

‎hash.sql

Lines changed: 29 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -18,23 +18,35 @@ CREATE OR REPLACE FUNCTION @extschema@.create_hash_partitions(
1818
) RETURNS INTEGER AS
1919
$$
2020
DECLARE
21-
v_relname TEXT;
21+
v_relname TEXT;
2222
v_child_relname TEXT;
23-
v_type TEXT;
23+
v_type TEXT;
24+
v_plain_schema TEXT;
25+
v_plain_relname TEXT;
26+
v_hashfunc TEXT;
2427
BEGIN
2528
v_relname := @extschema@.validate_relname(relation);
2629
attribute := lower(attribute);
2730
PERFORM @extschema@.common_relation_checks(relation, attribute);
2831

2932
v_type := @extschema@.get_attribute_type_name(v_relname, attribute);
30-
IF v_type::regtype != 'integer'::regtype THEN
31-
RAISE EXCEPTION 'Attribute type must be INTEGER';
32-
END IF;
33+
-- IF v_type::regtype != 'integer'::regtype THEN
34+
-- RAISE EXCEPTION 'Attribute type must be INTEGER';
35+
-- END IF;
36+
37+
SELECT * INTO v_plain_schema, v_plain_relname
38+
FROM @extschema@.get_plain_schema_and_relname(relation);
39+
40+
v_hashfunc := @extschema@.get_type_hash_func(v_type::regtype::oid)::regproc;
3341

3442
/* Create partitions and update pg_pathman configuration */
3543
FOR partnum IN 0..partitions_count-1
3644
LOOP
37-
v_child_relname := @extschema@.get_schema_qualified_name(relation, '.', suffix := '_' || partnum);
45+
-- v_child_relname := @extschema@.get_schema_qualified_name(relation, '.', suffix := '_' || partnum);
46+
v_child_relname := format('%s.%s',
47+
v_plain_schema,
48+
quote_ident(v_plain_relname || '_' || partnum));
49+
3850
EXECUTE format('CREATE TABLE %s (LIKE %s INCLUDING ALL)'
3951
, v_child_relname
4052
, v_relname);
@@ -43,8 +55,9 @@ BEGIN
4355
, v_child_relname
4456
, v_relname);
4557

46-
EXECUTE format('ALTER TABLE %s ADD CHECK (%s %% %s = %s)'
58+
EXECUTE format('ALTER TABLE %s ADD CHECK (@extschema@.get_hash(%s(%s), %s) = %s)'
4759
, v_child_relname
60+
, v_hashfunc
4861
, attribute
4962
, partitions_count
5063
, partnum);
@@ -80,8 +93,8 @@ DECLARE
8093
$body$
8194
DECLARE old_hash INTEGER; new_hash INTEGER; q TEXT;
8295
BEGIN
83-
old_hash := OLD.%2$s %% %3$s;
84-
new_hash := NEW.%2$s %% %3$s;
96+
old_hash := @extschema@.get_hash(%9$s(OLD.%2$s), %3$s);
97+
new_hash := @extschema@.get_hash(%9$s(NEW.%2$s), %3$s);
8598
IF old_hash = new_hash THEN RETURN NEW; END IF;
8699
q := format(''DELETE FROM %8$s WHERE %4$s'', old_hash);
87100
EXECUTE q USING %5$s;
@@ -106,6 +119,8 @@ DECLARE
106119
funcname TEXT;
107120
triggername TEXT;
108121
child_relname_format TEXT;
122+
atttype TEXT;
123+
hashfunc TEXT;
109124
BEGIN
110125
relation := @extschema@.validate_relname(relation);
111126

@@ -135,9 +150,13 @@ BEGIN
135150
child_relname_format := plain_schema || '.' || quote_ident(plain_relname || '_%s');
136151
triggername := quote_ident(format('%s_%s_update_trigger', plain_schema, plain_relname));
137152

153+
/* base hash function for type */
154+
atttype := @extschema@.get_attribute_type_name(relation, attr);
155+
hashfunc := @extschema@.get_type_hash_func(atttype::regtype::oid)::regproc;
156+
138157
/* Format function definition and execute it */
139158
func := format(func, funcname, attr, partitions_count, att_val_fmt,
140-
old_fields, att_fmt, new_fields, child_relname_format);
159+
old_fields, att_fmt, new_fields, child_relname_format, hashfunc);
141160
EXECUTE func;
142161

143162
/* Create triggers on child relations */

‎init.sql

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -406,3 +406,14 @@ END
406406
$$ LANGUAGE plpgsql
407407
SET pg_pathman.enable_partitionfilter = off;
408408

409+
/*
410+
* Returns hash function OID for specified type
411+
*/
412+
CREATE OR REPLACE FUNCTION @extschema@.get_type_hash_func(OID)
413+
RETURNS OID AS 'pg_pathman', 'get_type_hash_func' LANGUAGE C STRICT;
414+
415+
/*
416+
* Calculates hash for integer value
417+
*/
418+
CREATE OR REPLACE FUNCTION @extschema@.get_hash(INTEGER, INTEGER)
419+
RETURNS INTEGER AS 'pg_pathman', 'get_hash' LANGUAGE C STRICT;

‎sql/pg_pathman.sql

Lines changed: 14 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -69,7 +69,16 @@ SET enable_seqscan = ON;
6969
EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel;
7070
EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel WHERE value = 2;
7171
EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel WHERE value = 2 OR value = 1;
72-
EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel WHERE value BETWEEN 1 AND 2;
72+
-- Temporarily commented out
73+
-- EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel WHERE value BETWEEN 1 AND 2;
74+
-- QUERY PLAN
75+
-- -------------------------------------------------
76+
-- Append
77+
-- -> Seq Scan on hash_rel_1
78+
-- Filter: ((value >= 1) AND (value <= 2))
79+
-- -> Seq Scan on hash_rel_2
80+
-- Filter: ((value >= 1) AND (value <= 2))
81+
-- (5 rows)
7382
EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE id > 2500;
7483
EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE id >= 1000 AND id < 3000;
7584
EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE id >= 1500 AND id < 2500;
@@ -194,7 +203,7 @@ begin
194203
'wrong plan provider');
195204

196205
perform test.pathman_equal((plan->0->'Plan'->'Plans'->1->'Relation Name')::text,
197-
'"runtime_test_1_1"',
206+
format('"runtime_test_1_%s"', pathman.get_hash(hashint4(1), 6)),
198207
'wrong partition');
199208

200209
select count(*) from jsonb_array_elements_text(plan->0->'Plan'->'Plans') into num;
@@ -228,7 +237,7 @@ begin
228237

229238
for i in 0..3 loop
230239
perform test.pathman_equal((plan->0->'Plan'->'Plans'->1->'Plans'->i->'Relation Name')::text,
231-
format('"runtime_test_1_%s"', i + 1),
240+
format('"runtime_test_1_%s"', pathman.get_hash(hashint4(i + 1), 6)),
232241
'wrong partition');
233242

234243
num = plan->0->'Plan'->'Plans'->1->'Plans'->i->'Actual Loops';
@@ -263,7 +272,7 @@ begin
263272

264273
for i in 0..5 loop
265274
num = plan->0->'Plan'->'Plans'->1->'Plans'->i->'Actual Loops';
266-
perform test.pathman_assert(num > 0 and num <= 1667, 'expected no more than 1667 loops');
275+
perform test.pathman_assert(num > 0 and num <= 1718, 'expected no more than 1718 loops');
267276
end loop;
268277

269278
return 'ok';
@@ -296,7 +305,7 @@ begin
296305

297306
for i in 0..3 loop
298307
perform test.pathman_equal((plan->0->'Plan'->'Plans'->1->'Plans'->0->'Plans'->i->'Relation Name')::text,
299-
format('"runtime_test_2_%s"', i + 1),
308+
format('"runtime_test_2_%s"', pathman.get_hash(hashint4(i + 1), 6)),
300309
'wrong partition');
301310

302311
num = plan->0->'Plan'->'Plans'->1->'Plans'->0->'Plans'->i->'Actual Loops';
@@ -602,5 +611,3 @@ SELECT create_range_partitions('messages', 'id', 1, 100, 2);
602611
ALTER TABLE replies DROP CONSTRAINT replies_message_id_fkey;
603612
SELECT create_range_partitions('messages', 'id', 1, 100, 2);
604613
EXPLAIN (COSTS OFF) SELECT * FROM messages;
605-
606-
DROP EXTENSION pg_pathman;

‎src/dsm_array.c

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -30,7 +30,7 @@ static DsmConfig *dsm_cfg = NULL;
3030
/*
3131
* Block header
3232
*
33-
* Its size must be 4 bytes for 32bit and 8 bytes for 64bit.
33+
* Its size must be equal to 4 bytes for 32bit and 8 bytes for 64bit.
3434
* Otherwise it could screw up an alignment (for example on Sparc9)
3535
*/
3636
typedef uintptr_t BlockHeader;

0 commit comments

Comments
 (0)