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

Commit4a594aa

Browse files
committed
pathman: auto data partitioning on partitions creation
1 parentb48e34f commit4a594aa

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
casePT_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, (constvoid*)&key,HASH_REMOVE,0);
482+
hash_search(range_restrictions, (constvoid*)&key,HASH_REMOVE,NULL);
483+
hash_search(relations, (constvoid*)&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-
CREATEOR REPLACE FUNCTION @extschema@.partition_data(p_parenttext, OUT p_totalBIGINT)
44+
CREATEOR REPLACE FUNCTION @extschema@.partition_data(
45+
p_parenttext
46+
, p_invalidate_cache_on_errorBOOLEAN DEFAULT FALSE
47+
, OUT p_totalBIGINT)
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 recIN (SELECT inhrelidas child_id,pg_constraint.consrc
55-
FROM @extschema@.pathman_configas cfg
56-
JOIN pg_classAS parentONparent.relfilenode=cfg.relname::regclass::oid
57-
JOIN pg_inheritsON inhparent=parent.relfilenode
58-
JOIN pg_constraintON conrelid= inhrelidAND contype='c'
59-
WHEREcfg.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)
[8]ページ先頭
©2009-2025 Movatter.jp