Skip to content

Commit 4a594aa

Browse files
zilderzilder
committed
pathman: auto data partitioning on partitions creation
1 parent b48e34f commit 4a594aa

File tree

10 files changed

+925
-66
lines changed

10 files changed

+925
-66
lines changed

‎contrib/pg_pathman/expected/pg_pathman.out

Lines changed: 78 additions & 36 deletions
Original file line numberDiff line numberDiff line change
@@ -8,11 +8,25 @@ CREATE TABLE test.hash_rel (
88
SELECT pathman.create_hash_partitions('test.hash_rel', 'value', 3);
99
NOTICE: function test.hash_rel_hash_insert_trigger_func() does not exist, skipping
1010
NOTICE: function test.hash_rel_hash_update_trigger_func() does not exist, skipping
11+
NOTICE: Copying data to partitions...
12+
NOTICE: 0 rows have been copied
1113
create_hash_partitions
1214
------------------------
1315
3
1416
(1 row)
1517

18+
SELECT COUNT(*) FROM test.hash_rel;
19+
count
20+
-------
21+
0
22+
(1 row)
23+
24+
SELECT COUNT(*) FROM ONLY test.hash_rel;
25+
count
26+
-------
27+
0
28+
(1 row)
29+
1630
CREATE TABLE test.range_rel (
1731
id SERIAL PRIMARY KEY,
1832
dt TIMESTAMP,
@@ -22,33 +36,63 @@ INSERT INTO test.range_rel (dt, txt)
2236
SELECT g, md5(g::TEXT) FROM generate_series('2015-01-01', '2015-04-30', '1 day'::interval) as g;
2337
SELECT pathman.create_range_partitions('test.range_rel', 'dt', '2015-01-01'::DATE, '1 month'::INTERVAL, 4);
2438
NOTICE: sequence "range_rel_seq" does not exist, skipping
39+
NOTICE: Copying data to partitions...
40+
NOTICE: 0 rows have been copied
2541
create_range_partitions
2642
-------------------------
2743
4
2844
(1 row)
2945

30-
SELECT pathman.partition_data('test.range_rel');
31-
NOTICE: Copying data to test.range_rel_4 (condition: ((dt >= 'Wed Apr 01 00:00:00 2015'::timestamp without time zone) AND (dt < 'Fri May 01 00:00:00 2015'::timestamp without time zone)))
32-
NOTICE: Copying data to test.range_rel_3 (condition: ((dt >= 'Sun Mar 01 00:00:00 2015'::timestamp without time zone) AND (dt < 'Wed Apr 01 00:00:00 2015'::timestamp without time zone)))
33-
NOTICE: Copying data to test.range_rel_2 (condition: ((dt >= 'Sun Feb 01 00:00:00 2015'::timestamp without time zone) AND (dt < 'Sun Mar 01 00:00:00 2015'::timestamp without time zone)))
34-
NOTICE: Copying data to test.range_rel_1 (condition: ((dt >= 'Thu Jan 01 00:00:00 2015'::timestamp without time zone) AND (dt < 'Sun Feb 01 00:00:00 2015'::timestamp without time zone)))
35-
partition_data
36-
----------------
37-
120
46+
SELECT COUNT(*) FROM test.range_rel;
47+
count
48+
-------
49+
120
50+
(1 row)
51+
52+
SELECT COUNT(*) FROM ONLY test.range_rel;
53+
count
54+
-------
55+
0
3856
(1 row)
3957

4058
CREATE TABLE test.num_range_rel (
4159
id SERIAL PRIMARY KEY,
4260
txt TEXT);
4361
SELECT pathman.create_range_partitions('test.num_range_rel', 'id', 0, 1000, 4);
4462
NOTICE: sequence "num_range_rel_seq" does not exist, skipping
63+
NOTICE: Copying data to partitions...
64+
NOTICE: 0 rows have been copied
4565
create_range_partitions
4666
-------------------------
4767
4
4868
(1 row)
4969

70+
SELECT COUNT(*) FROM test.num_range_rel;
71+
count
72+
-------
73+
0
74+
(1 row)
75+
76+
SELECT COUNT(*) FROM ONLY test.num_range_rel;
77+
count
78+
-------
79+
0
80+
(1 row)
81+
5082
INSERT INTO test.num_range_rel
51-
SELECT g, md5(g::TEXT) FROM generate_series(1, 3000) as g;
83+
SELECT g, md5(g::TEXT) FROM generate_series(1, 3000) as g;
84+
SELECT COUNT(*) FROM test.num_range_rel;
85+
count
86+
-------
87+
3000
88+
(1 row)
89+
90+
SELECT COUNT(*) FROM ONLY test.num_range_rel;
91+
count
92+
-------
93+
0
94+
(1 row)
95+
5296
INSERT INTO test.hash_rel VALUES (1, 1);
5397
INSERT INTO test.hash_rel VALUES (2, 2);
5498
INSERT INTO test.hash_rel VALUES (3, 3);
@@ -441,6 +485,8 @@ CREATE TABLE test.range_rel (
441485
id SERIAL PRIMARY KEY,
442486
dt TIMESTAMP);
443487
SELECT pathman.create_range_partitions('test.range_rel', 'dt', '2015-01-01'::DATE, '10 days'::INTERVAL, 1);
488+
NOTICE: Copying data to partitions...
489+
NOTICE: 0 rows have been copied
444490
create_range_partitions
445491
-------------------------
446492
1
@@ -511,20 +557,13 @@ INSERT INTO hash_rel (value) SELECT g FROM generate_series(1, 10000) as g;
511557
SELECT create_hash_partitions('hash_rel', 'value', 3);
512558
NOTICE: function hash_rel_hash_insert_trigger_func() does not exist, skipping
513559
NOTICE: function hash_rel_hash_update_trigger_func() does not exist, skipping
560+
NOTICE: Copying data to partitions...
561+
NOTICE: 0 rows have been copied
514562
create_hash_partitions
515563
------------------------
516564
3
517565
(1 row)
518566

519-
SELECT partition_data('hash_rel');
520-
NOTICE: Copying data to hash_rel_0 (condition: ((value % 3) = 0))
521-
NOTICE: Copying data to hash_rel_1 (condition: ((value % 3) = 1))
522-
NOTICE: Copying data to hash_rel_2 (condition: ((value % 3) = 2))
523-
partition_data
524-
----------------
525-
10000
526-
(1 row)
527-
528567
EXPLAIN (COSTS OFF) SELECT * FROM hash_rel WHERE id = 1234;
529568
QUERY PLAN
530569
------------------------------------------------------
@@ -544,29 +583,13 @@ CREATE TABLE range_rel (
544583
INSERT INTO range_rel (dt) SELECT g FROM generate_series('2010-01-01'::date, '2010-12-31'::date, '1 day') as g;
545584
SELECT create_range_partitions('range_rel', 'dt', '2010-01-01'::date, '1 month'::interval, 12);
546585
NOTICE: sequence "range_rel_seq" does not exist, skipping
586+
NOTICE: Copying data to partitions...
587+
NOTICE: 0 rows have been copied
547588
create_range_partitions
548589
-------------------------
549590
12
550591
(1 row)
551592

552-
SELECT partition_data('range_rel');
553-
NOTICE: Copying data to range_rel_1 (condition: ((dt >= 'Fri Jan 01 00:00:00 2010'::timestamp without time zone) AND (dt < 'Mon Feb 01 00:00:00 2010'::timestamp without time zone)))
554-
NOTICE: Copying data to range_rel_2 (condition: ((dt >= 'Mon Feb 01 00:00:00 2010'::timestamp without time zone) AND (dt < 'Mon Mar 01 00:00:00 2010'::timestamp without time zone)))
555-
NOTICE: Copying data to range_rel_3 (condition: ((dt >= 'Mon Mar 01 00:00:00 2010'::timestamp without time zone) AND (dt < 'Thu Apr 01 00:00:00 2010'::timestamp without time zone)))
556-
NOTICE: Copying data to range_rel_4 (condition: ((dt >= 'Thu Apr 01 00:00:00 2010'::timestamp without time zone) AND (dt < 'Sat May 01 00:00:00 2010'::timestamp without time zone)))
557-
NOTICE: Copying data to range_rel_5 (condition: ((dt >= 'Sat May 01 00:00:00 2010'::timestamp without time zone) AND (dt < 'Tue Jun 01 00:00:00 2010'::timestamp without time zone)))
558-
NOTICE: Copying data to range_rel_6 (condition: ((dt >= 'Tue Jun 01 00:00:00 2010'::timestamp without time zone) AND (dt < 'Thu Jul 01 00:00:00 2010'::timestamp without time zone)))
559-
NOTICE: Copying data to range_rel_7 (condition: ((dt >= 'Thu Jul 01 00:00:00 2010'::timestamp without time zone) AND (dt < 'Sun Aug 01 00:00:00 2010'::timestamp without time zone)))
560-
NOTICE: Copying data to range_rel_8 (condition: ((dt >= 'Sun Aug 01 00:00:00 2010'::timestamp without time zone) AND (dt < 'Wed Sep 01 00:00:00 2010'::timestamp without time zone)))
561-
NOTICE: Copying data to range_rel_9 (condition: ((dt >= 'Wed Sep 01 00:00:00 2010'::timestamp without time zone) AND (dt < 'Fri Oct 01 00:00:00 2010'::timestamp without time zone)))
562-
NOTICE: Copying data to range_rel_10 (condition: ((dt >= 'Fri Oct 01 00:00:00 2010'::timestamp without time zone) AND (dt < 'Mon Nov 01 00:00:00 2010'::timestamp without time zone)))
563-
NOTICE: Copying data to range_rel_11 (condition: ((dt >= 'Mon Nov 01 00:00:00 2010'::timestamp without time zone) AND (dt < 'Wed Dec 01 00:00:00 2010'::timestamp without time zone)))
564-
NOTICE: Copying data to range_rel_12 (condition: ((dt >= 'Wed Dec 01 00:00:00 2010'::timestamp without time zone) AND (dt < 'Sat Jan 01 00:00:00 2011'::timestamp without time zone)))
565-
partition_data
566-
----------------
567-
365
568-
(1 row)
569-
570593
SELECT merge_range_partitions('range_rel_1', 'range_rel_2');
571594
NOTICE: Altering first partition...
572595
NOTICE: Copying data...
@@ -645,6 +668,8 @@ SELECT drop_range_partitions('range_rel');
645668
(1 row)
646669

647670
SELECT create_partitions_from_range('range_rel', 'id', 1, 1000, 100);
671+
NOTICE: Copying data to partitions...
672+
NOTICE: 0 rows have been copied
648673
create_partitions_from_range
649674
------------------------------
650675
10
@@ -657,6 +682,8 @@ SELECT drop_range_partitions('range_rel');
657682
(1 row)
658683

659684
SELECT create_partitions_from_range('range_rel', 'dt', '2015-01-01'::date, '2015-12-01'::date, '1 month'::interval);
685+
NOTICE: Copying data to partitions...
686+
NOTICE: 0 rows have been copied
660687
create_partitions_from_range
661688
------------------------------
662689
12
@@ -670,4 +697,19 @@ EXPLAIN (COSTS OFF) SELECT * FROM range_rel WHERE dt = '2015-12-15';
670697
Filter: (dt = 'Tue Dec 15 00:00:00 2015'::timestamp without time zone)
671698
(3 rows)
672699

700+
/* Test exception handling on partitioning */
701+
CREATE TABLE messages(id SERIAL PRIMARY KEY, msg TEXT);
702+
CREATE TABLE replies(id SERIAL PRIMARY KEY, message_id INTEGER REFERENCES messages(id), msg TEXT);
703+
INSERT INTO messages SELECT g, md5(g::text) FROM generate_series(1, 10) as g;
704+
INSERT INTO replies SELECT g, g, md5(g::text) FROM generate_series(1, 10) as g;
705+
SELECT create_range_partitions('messages', 'id', 1, 100, 2);
706+
NOTICE: sequence "messages_seq" does not exist, skipping
707+
NOTICE: Copying data to partitions...
708+
ERROR: update or delete on table "messages" violates foreign key constraint "replies_message_id_fkey" on table "replies" 23503
709+
EXPLAIN (COSTS OFF) SELECT * FROM messages;
710+
QUERY PLAN
711+
----------------------
712+
Seq Scan on messages
713+
(1 row)
714+
673715
DROP EXTENSION pg_pathman;

‎contrib/pg_pathman/hash.sql

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -50,6 +50,9 @@ BEGIN
5050
/* Notify backend about changes */
5151
PERFORM @extschema@.on_create_partitions(relation::regclass::oid);
5252

53+
/* Copy data */
54+
PERFORM @extschema@.partition_data(relation);
55+
5356
RETURN partitions_count;
5457
END
5558
$$ LANGUAGE plpgsql;

‎contrib/pg_pathman/init.c

Lines changed: 6 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -11,6 +11,8 @@
1111
#include "utils/typcache.h"
1212
#include "utils/lsyscache.h"
1313
#include "utils/bytea.h"
14+
#include "utils/snapmgr.h"
15+
1416

1517
HTAB *relations = NULL;
1618
HTAB *range_restrictions = NULL;
@@ -141,15 +143,15 @@ load_relations_hashtable(bool reinitialize)
141143
free_dsm_array(&rangerel->ranges);
142144
prel->children_count = 0;
143145
}
144-
load_check_constraints(oid, InvalidSnapshot);
146+
load_check_constraints(oid, GetCatalogSnapshot(oid));
145147
break;
146148
case PT_HASH:
147149
if (reinitialize && prel->children.length > 0)
148150
{
149151
free_dsm_array(&prel->children);
150152
prel->children_count = 0;
151153
}
152-
load_check_constraints(oid, InvalidSnapshot);
154+
load_check_constraints(oid, GetCatalogSnapshot(oid));
153155
break;
154156
}
155157
}
@@ -477,7 +479,8 @@ remove_relation_info(Oid relid)
477479
rangerel = get_pathman_range_relation(relid, NULL);
478480
free_dsm_array(&rangerel->ranges);
479481
free_dsm_array(&prel->children);
480-
hash_search(range_restrictions, (const void *) &key, HASH_REMOVE, 0);
482+
hash_search(range_restrictions, (const void *) &key, HASH_REMOVE, NULL);
483+
hash_search(relations, (const void *) &key, HASH_REMOVE, NULL);
481484
break;
482485
}
483486
prel->children_count = 0;

‎contrib/pg_pathman/init.sql

Lines changed: 20 additions & 18 deletions
Original file line numberDiff line numberDiff line change
@@ -41,7 +41,10 @@ RETURNS ANYARRAY AS 'pg_pathman', 'get_range_by_idx' LANGUAGE C STRICT;
4141
/*
4242
* Copy rows to partitions
4343
*/
44-
CREATE OR REPLACE FUNCTION @extschema@.partition_data(p_parent text, OUT p_total BIGINT)
44+
CREATE OR REPLACE FUNCTION @extschema@.partition_data(
45+
p_parent text
46+
, p_invalidate_cache_on_error BOOLEAN DEFAULT FALSE
47+
, OUT p_total BIGINT)
4548
AS
4649
$$
4750
DECLARE
@@ -51,23 +54,22 @@ BEGIN
5154
p_parent := @extschema@.validate_relname(p_parent);
5255

5356
p_total := 0;
54-
FOR rec IN (SELECT inhrelid as child_id, pg_constraint.consrc
55-
FROM @extschema@.pathman_config as cfg
56-
JOIN pg_class AS parent ON parent.relfilenode = cfg.relname::regclass::oid
57-
JOIN pg_inherits ON inhparent = parent.relfilenode
58-
JOIN pg_constraint ON conrelid = inhrelid AND contype='c'
59-
WHERE cfg.relname = p_parent)
60-
LOOP
61-
RAISE NOTICE 'Copying data to % (condition: %)', rec.child_id::regclass::text, rec.consrc;
62-
EXECUTE format('WITH part_data AS (
63-
DELETE FROM ONLY %s WHERE %s RETURNING *)
64-
INSERT INTO %s SELECT * FROM part_data'
65-
, p_parent
66-
, rec.consrc
67-
, rec.child_id::regclass::text);
68-
GET DIAGNOSTICS cnt = ROW_COUNT;
69-
p_total := p_total + cnt;
70-
END LOOP;
57+
58+
/* Create partitions and copy rest of the data */
59+
RAISE NOTICE 'Copying data to partitions...';
60+
EXECUTE format('
61+
WITH part_data AS (
62+
DELETE FROM ONLY %s RETURNING *)
63+
INSERT INTO %s SELECT * FROM part_data'
64+
, p_parent
65+
, p_parent);
66+
GET DIAGNOSTICS p_total = ROW_COUNT;
67+
RAISE NOTICE '% rows have been copied', p_total;
68+
RETURN;
69+
70+
EXCEPTION WHEN others THEN
71+
PERFORM on_remove_partitions(p_parent::regclass::integer);
72+
RAISE EXCEPTION '% %', SQLERRM, SQLSTATE;
7173
END
7274
$$
7375
LANGUAGE plpgsql;

‎contrib/pg_pathman/log/initdb.log

Lines changed: 48 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,48 @@
1+
Running in noclean mode. Mistakes will not be cleaned up.
2+
The files belonging to this database system will be owned by user "zilder".
3+
This user must also own the server process.
4+
5+
The database cluster will be initialized with locales
6+
COLLATE: ru_RU.UTF-8
7+
CTYPE: ru_RU.UTF-8
8+
MESSAGES: C
9+
MONETARY: ru_RU.UTF-8
10+
NUMERIC: ru_RU.UTF-8
11+
TIME: ru_RU.UTF-8
12+
The default database encoding has accordingly been set to "UTF8".
13+
The default text search configuration will be set to "russian".
14+
15+
Data page checksums are disabled.
16+
17+
creating directory /home/zilder/projects/postgres/contrib/pg_pathman/./tmp_check/data ... ok
18+
creating subdirectories ... ok
19+
selecting default max_connections ... 100
20+
selecting default shared_buffers ... 128MB
21+
selecting dynamic shared memory implementation ... posix
22+
creating configuration files ... ok
23+
creating template1 database in /home/zilder/projects/postgres/contrib/pg_pathman/./tmp_check/data/base/1 ... ok
24+
initializing pg_authid ... ok
25+
initializing dependencies ... ok
26+
creating system views ... ok
27+
loading system objects' descriptions ... ok
28+
creating collations ... ok
29+
creating conversions ... ok
30+
creating dictionaries ... ok
31+
setting privileges on built-in objects ... ok
32+
creating information schema ... ok
33+
loading PL/pgSQL server-side language ... ok
34+
vacuuming database template1 ... ok
35+
copying template1 to template0 ... ok
36+
copying template1 to postgres ... ok
37+
38+
Sync to disk skipped.
39+
The data directory might become corrupt if the operating system crashes.
40+
41+
WARNING: enabling "trust" authentication for local connections
42+
You can change this by editing pg_hba.conf or using the option -A, or
43+
--auth-local and --auth-host, the next time you run initdb.
44+
45+
Success. You can now start the database server using:
46+
47+
pg_ctl -D /home/zilder/projects/postgres/contrib/pg_pathman/./tmp_check/data -l logfile start
48+

‎contrib/pg_pathman/log/postmaster.log

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,20 @@
1+
FATAL: the database system is starting up
2+
LOG: database system was shut down at 2016-02-01 12:58:17 MSK
3+
LOG: MultiXact member wraparound protections are now enabled
4+
LOG: autovacuum launcher started
5+
LOG: database system is ready to accept connections
6+
LOG: checkpoint starting: immediate force wait flush-all
7+
LOG: checkpoint complete: wrote 3 buffers (0.0%); 0 transaction log file(s) added, 0 removed, 0 recycled; write=0.001 s, sync=0.000 s, total=0.001 s; sync files=0, longest=0.000 s, average=0.000 s; distance=1 kB, estimate=1 kB
8+
LOG: checkpoint starting: immediate force wait
9+
LOG: checkpoint complete: wrote 0 buffers (0.0%); 0 transaction log file(s) added, 0 removed, 0 recycled; write=0.000 s, sync=0.000 s, total=0.000 s; sync files=0, longest=0.000 s, average=0.000 s; distance=0 kB, estimate=1 kB
10+
ERROR: update or delete on table "messages" violates foreign key constraint "replies_message_id_fkey" on table "replies" 23503
11+
CONTEXT: SQL statement "SELECT public.partition_data(p_relation)"
12+
PL/pgSQL function create_range_partitions(text,text,anyelement,anyelement,integer) line 38 at PERFORM
13+
STATEMENT: SELECT create_range_partitions('messages', 'id', 1, 100, 2);
14+
LOG: received fast shutdown request
15+
LOG: aborting any active transactions
16+
LOG: autovacuum launcher shutting down
17+
LOG: shutting down
18+
LOG: checkpoint starting: shutdown immediate
19+
LOG: checkpoint complete: wrote 431 buffers (2.6%); 0 transaction log file(s) added, 0 removed, 0 recycled; write=0.008 s, sync=0.000 s, total=0.010 s; sync files=0, longest=0.000 s, average=0.000 s; distance=6221 kB, estimate=6221 kB
20+
LOG: database system is shut down

0 commit comments

Comments
 (0)