Skip to content

Commit 1385fb3

Browse files
zilderzilder
committed
Merge branch 'pathman_sorting' into pathman_pgpro9_5
2 parents 30d4969 + 4a4cbbf commit 1385fb3

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
value INTEGER 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: function hash_rel_hash_insert_trigger_func() does not exist, skipping
774-
NOTICE: function hash_rel_hash_update_trigger_func() does not exist, skipping
936+
NOTICE: function hash_rel_insert_trigger_func() does not exist, skipping
937+
NOTICE: function hash_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
func TEXT := '
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
trigger TEXT := '
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
fields TEXT;
9393
fields_format TEXT;
9494
insert_stmt TEXT;
@@ -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 VOID AS
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
static bool validate_range_constraint(Expr *, PartRelationInfo *, Datum *, Datum *);
3435
static bool validate_hash_constraint(Expr *expr, PartRelationInfo *prel, int *hash);
36+
static bool read_opexpr_const(OpExpr *opexpr, int varattno, Datum *val);
3537
static int cmp_range_entries(const void *p1, const void *p2);
3638

3739
Size
@@ -272,8 +274,8 @@ create_relations_hashtable()
272274
void
273275
load_check_constraints(Oid parent_oid, Snapshot snapshot)
274276
{
275-
PartRelationInfo *prel;
276-
RangeRelation *rangerel;
277+
PartRelationInfo *prel = NULL;
278+
RangeRelation *rangerel = NULL;
277279
SPIPlanPtr plan;
278280
bool found;
279281
int ret,
@@ -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
Datum min;
309311
Datum max;
310312
int hash;
@@ -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+
static bool
490+
read_opexpr_const(OpExpr *opexpr, int varattno, 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)