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

Commit1385fb3

Browse files
committed
Merge branch 'pathman_sorting' into pathman_pgpro9_5
2 parents30d4969 +4a4cbbf commit1385fb3

File tree

9 files changed

+818
-129
lines changed

9 files changed

+818
-129
lines changed

‎contrib/pg_pathman/expected/pg_pathman.out‎

Lines changed: 173 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -12,8 +12,8 @@ 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_hash_insert_trigger_func() does not exist, skipping
16-
NOTICE: function test.hash_rel_hash_update_trigger_func() does not exist, skipping
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
1717
NOTICE: Copying data to partitions...
1818
create_hash_partitions
1919
------------------------
@@ -340,6 +340,26 @@ EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE (id >= 500 AND id < 1
340340
-> Seq Scan on num_range_rel_4
341341
(8 rows)
342342

343+
EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel ORDER BY id;
344+
QUERY PLAN
345+
----------------------------------------------------------------
346+
Append
347+
-> Index Scan using num_range_rel_1_pkey on num_range_rel_1
348+
-> Index Scan using num_range_rel_2_pkey on num_range_rel_2
349+
-> Index Scan using num_range_rel_3_pkey on num_range_rel_3
350+
-> Index Scan using num_range_rel_4_pkey on num_range_rel_4
351+
(5 rows)
352+
353+
EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE id <= 2500 ORDER BY id;
354+
QUERY PLAN
355+
----------------------------------------------------------------
356+
Append
357+
-> Index Scan using num_range_rel_1_pkey on num_range_rel_1
358+
-> Index Scan using num_range_rel_2_pkey on num_range_rel_2
359+
-> Index Scan using num_range_rel_3_pkey on num_range_rel_3
360+
Index Cond: (id <= 2500)
361+
(5 rows)
362+
343363
EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE dt > '2015-02-15';
344364
QUERY PLAN
345365
------------------------------------------------------------------------------------
@@ -380,6 +400,138 @@ EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE (dt >= '2015-01-15' AND d
380400
-> Seq Scan on range_rel_4
381401
(8 rows)
382402

403+
EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel ORDER BY dt;
404+
QUERY PLAN
405+
----------------------------------------------------------
406+
Append
407+
-> Index Scan using range_rel_1_dt_idx on range_rel_1
408+
-> Index Scan using range_rel_2_dt_idx on range_rel_2
409+
-> Index Scan using range_rel_3_dt_idx on range_rel_3
410+
-> Index Scan using range_rel_4_dt_idx on range_rel_4
411+
(5 rows)
412+
413+
EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE dt >= '2015-01-15' ORDER BY dt DESC;
414+
QUERY PLAN
415+
-------------------------------------------------------------------------------------
416+
Append
417+
-> Index Scan Backward using range_rel_4_dt_idx on range_rel_4
418+
-> Index Scan Backward using range_rel_3_dt_idx on range_rel_3
419+
-> Index Scan Backward using range_rel_2_dt_idx on range_rel_2
420+
-> Index Scan Backward using range_rel_1_dt_idx on range_rel_1
421+
Index Cond: (dt >= 'Thu Jan 15 00:00:00 2015'::timestamp without time zone)
422+
(6 rows)
423+
424+
/*
425+
* Sorting
426+
*/
427+
SET enable_indexscan = OFF;
428+
SET enable_seqscan = ON;
429+
EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE dt < '2015-03-01' ORDER BY dt;
430+
QUERY PLAN
431+
-------------------------------------
432+
Sort
433+
Sort Key: range_rel_1.dt
434+
-> Append
435+
-> Seq Scan on range_rel_1
436+
-> Seq Scan on range_rel_2
437+
(5 rows)
438+
439+
EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel_1 UNION ALL SELECT * FROM test.range_rel_2 ORDER BY dt;
440+
QUERY PLAN
441+
-------------------------------------
442+
Sort
443+
Sort Key: range_rel_1.dt
444+
-> Append
445+
-> Seq Scan on range_rel_1
446+
-> Seq Scan on range_rel_2
447+
(5 rows)
448+
449+
SET enable_indexscan = ON;
450+
SET enable_seqscan = OFF;
451+
EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE dt < '2015-03-01' ORDER BY dt;
452+
QUERY PLAN
453+
----------------------------------------------------------
454+
Append
455+
-> Index Scan using range_rel_1_dt_idx on range_rel_1
456+
-> Index Scan using range_rel_2_dt_idx on range_rel_2
457+
(3 rows)
458+
459+
EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel_1 UNION ALL SELECT * FROM test.range_rel_2 ORDER BY dt;
460+
QUERY PLAN
461+
----------------------------------------------------------
462+
Merge Append
463+
Sort Key: range_rel_1.dt
464+
-> Index Scan using range_rel_1_dt_idx on range_rel_1
465+
-> Index Scan using range_rel_2_dt_idx on range_rel_2
466+
(4 rows)
467+
468+
/*
469+
* Join
470+
*/
471+
SET enable_hashjoin = OFF;
472+
SET enable_mergejoin = ON;
473+
EXPLAIN (COSTS OFF)
474+
SELECT * FROM test.range_rel j1
475+
JOIN test.range_rel j2 on j2.id = j1.id
476+
JOIN test.num_range_rel j3 on j3.id = j1.id
477+
WHERE j1.dt < '2015-03-01' AND j2.dt >= '2015-02-01' ORDER BY j2.dt;
478+
QUERY PLAN
479+
-------------------------------------------------------------------------------------------
480+
Sort
481+
Sort Key: j2.dt
482+
-> Merge Join
483+
Merge Cond: (j3.id = j2.id)
484+
-> Append
485+
-> Index Scan using num_range_rel_1_pkey on num_range_rel_1 j3
486+
-> Index Scan using num_range_rel_2_pkey on num_range_rel_2 j3_1
487+
-> Index Scan using num_range_rel_3_pkey on num_range_rel_3 j3_2
488+
-> Index Scan using num_range_rel_4_pkey on num_range_rel_4 j3_3
489+
-> Materialize
490+
-> Merge Join
491+
Merge Cond: (j2.id = j1.id)
492+
-> Merge Append
493+
Sort Key: j2.id
494+
-> Index Scan using range_rel_2_pkey on range_rel_2 j2
495+
-> Index Scan using range_rel_3_pkey on range_rel_3 j2_1
496+
-> Index Scan using range_rel_4_pkey on range_rel_4 j2_2
497+
-> Materialize
498+
-> Merge Append
499+
Sort Key: j1.id
500+
-> Index Scan using range_rel_1_pkey on range_rel_1 j1
501+
-> Index Scan using range_rel_2_pkey on range_rel_2 j1_1
502+
(22 rows)
503+
504+
SET enable_hashjoin = ON;
505+
SET enable_mergejoin = OFF;
506+
EXPLAIN (COSTS OFF)
507+
SELECT * FROM test.range_rel j1
508+
JOIN test.range_rel j2 on j2.id = j1.id
509+
JOIN test.num_range_rel j3 on j3.id = j1.id
510+
WHERE j1.dt < '2015-03-01' AND j2.dt >= '2015-02-01' ORDER BY j2.dt;
511+
QUERY PLAN
512+
-------------------------------------------------------------------------------------------
513+
Sort
514+
Sort Key: j2.dt
515+
-> Hash Join
516+
Hash Cond: (j3.id = j2.id)
517+
-> Append
518+
-> Index Scan using num_range_rel_1_pkey on num_range_rel_1 j3
519+
-> Index Scan using num_range_rel_2_pkey on num_range_rel_2 j3_1
520+
-> Index Scan using num_range_rel_3_pkey on num_range_rel_3 j3_2
521+
-> Index Scan using num_range_rel_4_pkey on num_range_rel_4 j3_3
522+
-> Hash
523+
-> Hash Join
524+
Hash Cond: (j2.id = j1.id)
525+
-> Append
526+
-> Index Scan using range_rel_2_dt_idx on range_rel_2 j2
527+
-> Index Scan using range_rel_3_dt_idx on range_rel_3 j2_1
528+
-> Index Scan using range_rel_4_dt_idx on range_rel_4 j2_2
529+
-> Hash
530+
-> Append
531+
-> Index Scan using range_rel_1_pkey on range_rel_1 j1
532+
-> Index Scan using range_rel_2_pkey on range_rel_2 j1_1
533+
(20 rows)
534+
383535
/*
384536
* Test CTE query
385537
*/
@@ -600,15 +752,26 @@ CREATE TABLE test.range_rel_test2 (
600752
dt TIMESTAMP);
601753
SELECT pathman.attach_range_partition('test.range_rel', 'test.range_rel_test2', '2013-01-01'::DATE, '2014-01-01'::DATE);
602754
ERROR: Partition must have the exact same structure as parent P0001
755+
/*
756+
* Check that altering table columns doesn't break trigger
757+
*/
758+
ALTER TABLE test.hash_rel ADD COLUMN abc int;
759+
INSERT INTO test.hash_rel (id, value, abc) VALUES (123, 456, 789);
760+
SELECT * FROM test.hash_rel WHERE id = 123;
761+
id | value | abc
762+
-----+-------+-----
763+
123 | 456 | 789
764+
(1 row)
765+
603766
/*
604767
* Clean up
605768
*/
606769
SELECT pathman.drop_hash_partitions('test.hash_rel');
607770
NOTICE: drop cascades to trigger test_hash_rel_insert_trigger on table test.hash_rel
608-
NOTICE:function test.hash_rel_hash_update_trigger_func() does not exist, skipping
771+
NOTICE:drop cascades to 3 other objects
609772
NOTICE: 2 rows copied from test.hash_rel_2
610773
NOTICE: 3 rows copied from test.hash_rel_1
611-
NOTICE:1 rows copied from test.hash_rel_0
774+
NOTICE:2 rows copied from test.hash_rel_0
612775
drop_hash_partitions
613776
----------------------
614777
3
@@ -617,12 +780,12 @@ NOTICE: 1 rows copied from test.hash_rel_0
617780
SELECT COUNT(*) FROM ONLY test.hash_rel;
618781
count
619782
-------
620-
6
783+
7
621784
(1 row)
622785

623786
SELECT pathman.create_hash_partitions('test.hash_rel', 'value', 3);
624-
NOTICE: function test.hash_rel_hash_insert_trigger_func() does not exist, skipping
625-
NOTICE: function test.hash_rel_hash_update_trigger_func() does not exist, skipping
787+
NOTICE: function test.hash_rel_insert_trigger_func() does not exist, skipping
788+
NOTICE: function test.hash_rel_update_trigger_func() does not exist, skipping
626789
NOTICE: Copying data to partitions...
627790
create_hash_partitions
628791
------------------------
@@ -631,7 +794,7 @@ NOTICE: Copying data to partitions...
631794

632795
SELECT pathman.drop_hash_partitions('test.hash_rel', TRUE);
633796
NOTICE: drop cascades to trigger test_hash_rel_insert_trigger on table test.hash_rel
634-
NOTICE: function test.hash_rel_hash_update_trigger_func() does not exist, skipping
797+
NOTICE: function test.hash_rel_update_trigger_func() does not exist, skipping
635798
drop_hash_partitions
636799
----------------------
637800
3
@@ -770,8 +933,8 @@ CREATE TABLE hash_rel (
770933
valueINTEGER NOT NULL);
771934
INSERT INTO hash_rel (value) SELECT g FROM generate_series(1, 10000) as g;
772935
SELECT create_hash_partitions('hash_rel', 'value', 3);
773-
NOTICE: functionhash_rel_hash_insert_trigger_func() does not exist, skipping
774-
NOTICE: functionhash_rel_hash_update_trigger_func() does not exist, skipping
936+
NOTICE: functionhash_rel_insert_trigger_func() does not exist, skipping
937+
NOTICE: functionhash_rel_update_trigger_func() does not exist, skipping
775938
NOTICE: Copying data to partitions...
776939
create_hash_partitions
777940
------------------------

‎contrib/pg_pathman/hash.sql‎

Lines changed: 6 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -76,7 +76,7 @@ RETURNS VOID AS
7676
$$
7777
DECLARE
7878
funcTEXT :='
79-
CREATE OR REPLACE FUNCTION %s_hash_insert_trigger_func()
79+
CREATE OR REPLACE FUNCTION %s_insert_trigger_func()
8080
RETURNS TRIGGER AS $body$
8181
DECLARE
8282
hash INTEGER;
@@ -88,7 +88,7 @@ DECLARE
8888
triggerTEXT :='
8989
CREATE TRIGGER %s_insert_trigger
9090
BEFORE INSERT ON %s
91-
FOR EACH ROW EXECUTE PROCEDURE %2$s_hash_insert_trigger_func();';
91+
FOR EACH ROW EXECUTE PROCEDURE %2$s_insert_trigger_func();';
9292
fieldsTEXT;
9393
fields_formatTEXT;
9494
insert_stmtTEXT;
@@ -103,10 +103,8 @@ BEGIN
103103
INTO fields, fields_format;
104104

105105
/* generate INSERT statement for trigger*/
106-
insert_stmt= format('EXECUTE format(''INSERT INTO %s_%%s VALUES (%s)'', hash) USING %s;'
107-
, relation
108-
, fields_format
109-
, fields);
106+
insert_stmt= format('EXECUTE format(''INSERT INTO %s_%%s SELECT $1.*'', hash) USING NEW;'
107+
, relation);
110108

111109
/* format and create new trigger for relation*/
112110
func := format(func, relation, attr, partitions_count, insert_stmt);
@@ -164,9 +162,9 @@ CREATE OR REPLACE FUNCTION @extschema@.drop_hash_triggers(IN relation TEXT)
164162
RETURNS VOIDAS
165163
$$
166164
BEGIN
167-
EXECUTE format('DROP FUNCTION IF EXISTS %s_hash_insert_trigger_func() CASCADE'
165+
EXECUTE format('DROP FUNCTION IF EXISTS %s_insert_trigger_func() CASCADE'
168166
, relation::regclass::text);
169-
EXECUTE format('DROP FUNCTION IF EXISTS %s_hash_update_trigger_func() CASCADE'
167+
EXECUTE format('DROP FUNCTION IF EXISTS %s_update_trigger_func() CASCADE'
170168
, relation::regclass::text);
171169
END
172170
$$ LANGUAGE plpgsql;

‎contrib/pg_pathman/init.c‎

Lines changed: 26 additions & 21 deletions
Original file line numberDiff line numberDiff line change
@@ -21,6 +21,7 @@
2121
#include"utils/lsyscache.h"
2222
#include"utils/bytea.h"
2323
#include"utils/snapmgr.h"
24+
#include"optimizer/clauses.h"
2425

2526

2627
HTAB*relations=NULL;
@@ -32,6 +33,7 @@ static bool globalByVal;
3233

3334
staticboolvalidate_range_constraint(Expr*,PartRelationInfo*,Datum*,Datum*);
3435
staticboolvalidate_hash_constraint(Expr*expr,PartRelationInfo*prel,int*hash);
36+
staticboolread_opexpr_const(OpExpr*opexpr,intvarattno,Datum*val);
3537
staticintcmp_range_entries(constvoid*p1,constvoid*p2);
3638

3739
Size
@@ -272,8 +274,8 @@ create_relations_hashtable()
272274
void
273275
load_check_constraints(Oidparent_oid,Snapshotsnapshot)
274276
{
275-
PartRelationInfo*prel;
276-
RangeRelation*rangerel;
277+
PartRelationInfo*prel=NULL;
278+
RangeRelation*rangerel=NULL;
277279
SPIPlanPtrplan;
278280
boolfound;
279281
intret,
@@ -304,7 +306,7 @@ load_check_constraints(Oid parent_oid, Snapshot snapshot)
304306
{
305307
SPITupleTable*tuptable=SPI_tuptable;
306308
Oid*children;
307-
RangeEntry*ranges;
309+
RangeEntry*ranges=NULL;
308310
Datummin;
309311
Datummax;
310312
inthash;
@@ -453,11 +455,7 @@ validate_range_constraint(Expr *expr, PartRelationInfo *prel, Datum *min, Datum
453455
OpExpr*opexpr;
454456

455457
/* it should be an AND operator on top */
456-
if ( !(IsA(expr,BoolExpr)&&boolexpr->boolop==AND_EXPR) )
457-
return false;
458-
459-
/* and it should have exactly two operands */
460-
if (list_length(boolexpr->args)!=2)
458+
if (!and_clause((Node*)expr))
461459
return false;
462460

463461
tce=lookup_type_cache(prel->atttype,TYPECACHE_EQ_OPR |TYPECACHE_LT_OPR |TYPECACHE_GT_OPR);
@@ -466,36 +464,43 @@ validate_range_constraint(Expr *expr, PartRelationInfo *prel, Datum *min, Datum
466464
opexpr= (OpExpr*)linitial(boolexpr->args);
467465
if (get_op_opfamily_strategy(opexpr->opno,tce->btree_opf)==BTGreaterEqualStrategyNumber)
468466
{
469-
Node*left=linitial(opexpr->args);
470-
Node*right=lsecond(opexpr->args);
471-
if ( !IsA(left,Var)|| !IsA(right,Const) )
472-
return false;
473-
if ( ((Var*)left)->varattno!=prel->attnum )
467+
if (!read_opexpr_const(opexpr,prel->attnum,min))
474468
return false;
475-
*min= ((Const*)right)->constvalue;
476469
}
477470
else
478471
return false;
479472

480-
/* TODO: rewrite this */
481473
/* check that right operand is < operator */
482474
opexpr= (OpExpr*)lsecond(boolexpr->args);
483475
if (get_op_opfamily_strategy(opexpr->opno,tce->btree_opf)==BTLessStrategyNumber)
484476
{
485-
Node*left=linitial(opexpr->args);
486-
Node*right=lsecond(opexpr->args);
487-
if ( !IsA(left,Var)|| !IsA(right,Const) )
477+
if (!read_opexpr_const(opexpr,prel->attnum,max))
488478
return false;
489-
if ( ((Var*)left)->varattno!=prel->attnum )
490-
return false;
491-
*max= ((Const*)right)->constvalue;
492479
}
493480
else
494481
return false;
495482

496483
return true;
497484
}
498485

486+
/*
487+
* Reads const value from expressions of kind: VAR >= CONST or VAR < CONST
488+
*/
489+
staticbool
490+
read_opexpr_const(OpExpr*opexpr,intvarattno,Datum*val)
491+
{
492+
Node*left=linitial(opexpr->args);
493+
Node*right=lsecond(opexpr->args);
494+
495+
if ( !IsA(left,Var)|| !IsA(right,Const) )
496+
return false;
497+
if ( ((Var*)left)->varattno!=varattno )
498+
return false;
499+
*val= ((Const*)right)->constvalue;
500+
501+
return true;
502+
}
503+
499504
/*
500505
* Validate hash constraint. It MUST have the exact format
501506
* VARIABLE % CONST = CONST

0 commit comments

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