Skip to content

Commit c6df8b2

Browse files
zilderzilder
committed
pathman: pl-functions fixes
1 parent 3e9c206 commit c6df8b2

File tree

9 files changed

+57
-38
lines changed

9 files changed

+57
-38
lines changed

‎contrib/pg_pathman/Makefile

Lines changed: 1 addition & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,6 +1,5 @@
11
# contrib/pg_pathman/Makefile
22

3-
43
MODULE_big = pg_pathman
54
OBJS = init.o pg_pathman.o dsm_array.o rangeset.o pl_funcs.o worker.o $(WIN32RES)
65

@@ -37,4 +36,4 @@ isolationcheck: | submake-isolation
3736
$(pg_isolation_regress_check) \
3837
--temp-config=$(top_srcdir)/$(subdir)/conf.add \
3938
--outputdir=./isolation_output \
40-
$(ISOLATIONCHECKS)
39+
$(ISOLATIONCHECKS)

‎contrib/pg_pathman/expected/pg_pathman.out

Lines changed: 23 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -11,9 +11,9 @@ INSERT INTO test.hash_rel VALUES (3, 3);
1111
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;
14-
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
14+
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
1717
NOTICE: Copying data to partitions...
1818
create_hash_partitions
1919
------------------------
@@ -59,7 +59,7 @@ ERROR: Partitioning key 'dt' must be NOT NULL P0001
5959
ALTER TABLE test.range_rel ALTER COLUMN dt SET NOT NULL;
6060
SELECT pathman.create_range_partitions('test.range_rel', 'dt', '2015-01-01'::DATE, '1 month'::INTERVAL, 2);
6161
ERROR: Not enough partitions to fit all the values of 'dt' P0001
62-
SELECT pathman.create_range_partitions('test.range_rel', 'dt', '2015-01-01'::DATE, '1 month'::INTERVAL);
62+
SELECT pathman.create_range_partitions('test.range_rel', 'DT', '2015-01-01'::DATE, '1 month'::INTERVAL);
6363
NOTICE: sequence "range_rel_seq" does not exist, skipping
6464
NOTICE: Copying data to partitions...
6565
create_range_partitions
@@ -752,15 +752,26 @@ CREATE TABLE test.range_rel_test2 (
752752
dt TIMESTAMP);
753753
SELECT pathman.attach_range_partition('test.range_rel', 'test.range_rel_test2', '2013-01-01'::DATE, '2014-01-01'::DATE);
754754
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+
755766
/*
756767
* Clean up
757768
*/
758769
SELECT pathman.drop_hash_partitions('test.hash_rel');
759770
NOTICE: drop cascades to trigger test_hash_rel_insert_trigger on table test.hash_rel
760-
NOTICE: function test.hash_rel_hash_update_trigger_func() does not exist, skipping
771+
NOTICE: drop cascades to 3 other objects
761772
NOTICE: 2 rows copied from test.hash_rel_2
762773
NOTICE: 3 rows copied from test.hash_rel_1
763-
NOTICE: 1 rows copied from test.hash_rel_0
774+
NOTICE: 2 rows copied from test.hash_rel_0
764775
drop_hash_partitions
765776
----------------------
766777
3
@@ -769,12 +780,12 @@ NOTICE: 1 rows copied from test.hash_rel_0
769780
SELECT COUNT(*) FROM ONLY test.hash_rel;
770781
count
771782
-------
772-
6
783+
7
773784
(1 row)
774785

775786
SELECT pathman.create_hash_partitions('test.hash_rel', 'value', 3);
776-
NOTICE: function test.hash_rel_hash_insert_trigger_func() does not exist, skipping
777-
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
778789
NOTICE: Copying data to partitions...
779790
create_hash_partitions
780791
------------------------
@@ -783,7 +794,7 @@ NOTICE: Copying data to partitions...
783794

784795
SELECT pathman.drop_hash_partitions('test.hash_rel', TRUE);
785796
NOTICE: drop cascades to trigger test_hash_rel_insert_trigger on table test.hash_rel
786-
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
787798
drop_hash_partitions
788799
----------------------
789800
3
@@ -922,8 +933,8 @@ CREATE TABLE hash_rel (
922933
value INTEGER NOT NULL);
923934
INSERT INTO hash_rel (value) SELECT g FROM generate_series(1, 10000) as g;
924935
SELECT create_hash_partitions('hash_rel', 'value', 3);
925-
NOTICE: function hash_rel_hash_insert_trigger_func() does not exist, skipping
926-
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
927938
NOTICE: Copying data to partitions...
928939
create_hash_partitions
929940
------------------------

‎contrib/pg_pathman/hash.sql

Lines changed: 6 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -75,7 +75,7 @@ RETURNS VOID AS
7575
$$
7676
DECLARE
7777
func TEXT := '
78-
CREATE OR REPLACE FUNCTION %s_hash_insert_trigger_func()
78+
CREATE OR REPLACE FUNCTION %s_insert_trigger_func()
7979
RETURNS TRIGGER AS $body$
8080
DECLARE
8181
hash INTEGER;
@@ -87,7 +87,7 @@ DECLARE
8787
trigger TEXT := '
8888
CREATE TRIGGER %s_insert_trigger
8989
BEFORE INSERT ON %s
90-
FOR EACH ROW EXECUTE PROCEDURE %2$s_hash_insert_trigger_func();';
90+
FOR EACH ROW EXECUTE PROCEDURE %2$s_insert_trigger_func();';
9191
fields TEXT;
9292
fields_format TEXT;
9393
insert_stmt TEXT;
@@ -102,10 +102,8 @@ BEGIN
102102
INTO fields, fields_format;
103103

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

110108
/* format and create new trigger for relation */
111109
func := format(func, relation, attr, partitions_count, insert_stmt);
@@ -163,9 +161,9 @@ CREATE OR REPLACE FUNCTION @extschema@.drop_hash_triggers(IN relation TEXT)
163161
RETURNS VOID AS
164162
$$
165163
BEGIN
166-
EXECUTE format('DROP FUNCTION IF EXISTS %s_hash_insert_trigger_func() CASCADE'
164+
EXECUTE format('DROP FUNCTION IF EXISTS %s_insert_trigger_func() CASCADE'
167165
, relation::regclass::text);
168-
EXECUTE format('DROP FUNCTION IF EXISTS %s_hash_update_trigger_func() CASCADE'
166+
EXECUTE format('DROP FUNCTION IF EXISTS %s_update_trigger_func() CASCADE'
169167
, relation::regclass::text);
170168
END
171169
$$ LANGUAGE plpgsql;

‎contrib/pg_pathman/init.c

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -177,7 +177,7 @@ load_relations_hashtable(bool reinitialize)
177177
char sql[] = "SELECT pg_class.relfilenode, pg_attribute.attnum, cfg.parttype, pg_attribute.atttypid "
178178
"FROM %s.pathman_config as cfg "
179179
"JOIN pg_class ON pg_class.relfilenode = cfg.relname::regclass::oid "
180-
"JOIN pg_attribute ON pg_attribute.attname = cfg.attname "
180+
"JOIN pg_attribute ON pg_attribute.attname = lower(cfg.attname) "
181181
"AND attrelid = pg_class.relfilenode";
182182
char *query;
183183

@@ -274,7 +274,7 @@ create_relations_hashtable()
274274
void
275275
load_check_constraints(Oid parent_oid, Snapshot snapshot)
276276
{
277-
PartRelationInfo *prel;
277+
PartRelationInfo *prel = NULL;
278278
RangeRelation *rangerel = NULL;
279279
SPIPlanPtr plan;
280280
bool found;

‎contrib/pg_pathman/init.sql

Lines changed: 8 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -119,11 +119,18 @@ LANGUAGE plpgsql;
119119
CREATE OR REPLACE FUNCTION @extschema@.disable_partitioning(IN relation TEXT)
120120
RETURNS VOID AS
121121
$$
122+
DECLARE
123+
parttype INTEGER;
122124
BEGIN
123125
relation := @extschema@.validate_relname(relation);
126+
parttype := parttype FROM pathman_config WHERE relname = relation;
124127

125128
DELETE FROM @extschema@.pathman_config WHERE relname = relation;
126-
EXECUTE format('DROP FUNCTION IF EXISTS %s_insert_trigger_func() CASCADE', relation);
129+
IF parttype = 1 THEN
130+
PERFORM @extschema@.drop_hash_triggers(relation);
131+
ELSIF parttype = 2 THEN
132+
PERFORM @extschema@.drop_range_triggers(relation);
133+
END IF;
127134

128135
/* Notify backend about changes */
129136
PERFORM on_remove_partitions(relation::regclass::integer);

‎contrib/pg_pathman/pg_pathman.c

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -342,6 +342,7 @@ handle_modification_query(Query *parse)
342342
if (!found)
343343
return;
344344

345+
/* Parse syntax tree and extract partition ranges */
345346
ranges = list_make1_int(make_irange(0, prel->children_count - 1, false));
346347
expr = (Expr *) eval_const_expressions(NULL, parse->jointree->quals);
347348
if (!expr)
@@ -417,8 +418,8 @@ pathman_set_rel_pathlist_hook(PlannerInfo *root, RelOptInfo *rel, Index rti, Ran
417418
if (prel->parttype == PT_RANGE)
418419
{
419420
/*
420-
* Get pathkeys for ascending and descending sort by patition
421-
* column.
421+
* Get pathkeys for ascending and descending sort by partition
422+
* column
422423
*/
423424
List *pathkeys;
424425
Var *var;

‎contrib/pg_pathman/range.sql

Lines changed: 4 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -26,6 +26,7 @@ DECLARE
2626
i INTEGER;
2727
BEGIN
2828
p_relation := @extschema@.validate_relname(p_relation);
29+
p_attribute := lower(p_attribute);
2930
PERFORM @extschema@.common_relation_checks(p_relation, p_attribute);
3031

3132
/* Try to determine partitions count if not set */
@@ -110,6 +111,7 @@ DECLARE
110111
i INTEGER;
111112
BEGIN
112113
p_relation := @extschema@.validate_relname(p_relation);
114+
p_attribute := lower(p_attribute);
113115
PERFORM @extschema@.common_relation_checks(p_relation, p_attribute);
114116

115117
IF p_count <= 0 THEN
@@ -195,6 +197,7 @@ DECLARE
195197
i INTEGER := 0;
196198
BEGIN
197199
p_relation := @extschema@.validate_relname(p_relation);
200+
p_attribute := lower(p_attribute);
198201
PERFORM @extschema@.common_relation_checks(p_relation, p_attribute);
199202

200203
IF p_interval <= 0 THEN
@@ -258,6 +261,7 @@ DECLARE
258261
i INTEGER := 0;
259262
BEGIN
260263
p_relation := @extschema@.validate_relname(p_relation);
264+
p_attribute := lower(p_attribute);
261265
PERFORM @extschema@.common_relation_checks(p_relation, p_attribute);
262266

263267
EXECUTE format('DROP SEQUENCE IF EXISTS %s_seq', p_relation);
@@ -402,9 +406,6 @@ BEGIN
402406
LIMIT 1;
403407
EXIT WHEN v_child_relname_exists = 0;
404408
END LOOP;
405-
-- v_child_relname := format('%s_%s'
406-
-- , p_parent_relname
407-
-- , regexp_replace(p_start_value::text, '[ :-]*', '', 'g'));
408409

409410
/* Skip existing partitions */
410411
IF EXISTS (SELECT * FROM pg_tables WHERE tablename = v_child_relname) THEN
@@ -427,7 +428,6 @@ BEGIN
427428
, v_cond);
428429

429430
EXECUTE v_sql;
430-
-- RAISE NOTICE 'partition % created', v_child_relname;
431431
RETURN v_child_relname;
432432
END
433433
$$ LANGUAGE plpgsql;
@@ -759,9 +759,6 @@ DECLARE
759759
BEGIN
760760
p_range := @extschema@.get_range_by_idx(p_relation::regclass::oid, 0, 0);
761761
RAISE NOTICE 'Prepending new partition...';
762-
-- v_part_name := @extschema@.create_single_range_partition(p_relation
763-
-- , p_range[1] - (p_range[2] - p_range[1])
764-
-- , p_range[1]);
765762

766763
IF @extschema@.is_date(p_atttype::regtype) THEN
767764
v_part_name := @extschema@.create_single_range_partition(p_relation
@@ -1113,7 +1110,6 @@ BEGIN
11131110
END LOOP;
11141111

11151112
DELETE FROM @extschema@.pathman_config WHERE relname = relation;
1116-
-- DELETE FROM pg_pathman_range_rels WHERE parent = relation;
11171113

11181114
/* Notify backend about changes */
11191115
PERFORM @extschema@.on_remove_partitions(relation::regclass::oid);

‎contrib/pg_pathman/sql/pg_pathman.sql

Lines changed: 9 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -12,7 +12,7 @@ INSERT INTO test.hash_rel VALUES (2, 2);
1212
INSERT INTO test.hash_rel VALUES (3, 3);
1313
SELECT pathman.create_hash_partitions('test.hash_rel', 'value', 3);
1414
ALTER TABLE test.hash_rel ALTER COLUMN value SET NOT NULL;
15-
SELECT pathman.create_hash_partitions('test.hash_rel', 'value', 3);
15+
SELECT pathman.create_hash_partitions('test.hash_rel', 'Value', 3);
1616
SELECT COUNT(*) FROM test.hash_rel;
1717
SELECT COUNT(*) FROM ONLY test.hash_rel;
1818
INSERT INTO test.hash_rel VALUES (4, 4);
@@ -31,7 +31,7 @@ SELECT g, md5(g::TEXT) FROM generate_series('2015-01-01', '2015-04-30', '1 day':
3131
SELECT pathman.create_range_partitions('test.range_rel', 'dt', '2015-01-01'::DATE, '1 month'::INTERVAL, 2);
3232
ALTER TABLE test.range_rel ALTER COLUMN dt SET NOT NULL;
3333
SELECT pathman.create_range_partitions('test.range_rel', 'dt', '2015-01-01'::DATE, '1 month'::INTERVAL, 2);
34-
SELECT pathman.create_range_partitions('test.range_rel', 'dt', '2015-01-01'::DATE, '1 month'::INTERVAL);
34+
SELECT pathman.create_range_partitions('test.range_rel', 'DT', '2015-01-01'::DATE, '1 month'::INTERVAL);
3535
SELECT COUNT(*) FROM test.range_rel;
3636
SELECT COUNT(*) FROM ONLY test.range_rel;
3737

@@ -183,6 +183,13 @@ CREATE TABLE test.range_rel_test2 (
183183
dt TIMESTAMP);
184184
SELECT pathman.attach_range_partition('test.range_rel', 'test.range_rel_test2', '2013-01-01'::DATE, '2014-01-01'::DATE);
185185

186+
/*
187+
* Check that altering table columns doesn't break trigger
188+
*/
189+
ALTER TABLE test.hash_rel ADD COLUMN abc int;
190+
INSERT INTO test.hash_rel (id, value, abc) VALUES (123, 456, 789);
191+
SELECT * FROM test.hash_rel WHERE id = 123;
192+
186193
/*
187194
* Clean up
188195
*/

‎contrib/pg_pathman/worker.c

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -124,7 +124,7 @@ bg_worker_main(Datum main_arg)
124124
if (!handle)
125125
{
126126
ereport(WARNING,
127-
(errmsg("pg_pathman worker: ivalid dsm_handle")));
127+
(errmsg("pg_pathman worker: invalid dsm_handle")));
128128
}
129129
segment = dsm_attach(handle);
130130
args = dsm_segment_address(segment);

0 commit comments

Comments
 (0)