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

Commit8274bac

Browse files
committed
migration script
1 parentbcaeca0 commit8274bac

File tree

5 files changed

+2609
-4
lines changed

5 files changed

+2609
-4
lines changed

‎.gitignore

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -6,4 +6,4 @@ regression.out
66
*.o
77
*.so
88
*.pyc
9-
pg_pathman--*.sql
9+
pg_pathman--1.1.sql

‎Makefile

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@ OBJS = src/init.o src/relation_info.o src/utils.o src/partition_filter.o \
88
src/pg_compat.o$(WIN32RES)
99

1010
EXTENSION = pg_pathman
11-
EXTVERSION = 1.0
11+
EXTVERSION = 1.1
1212
DATA_built =$(EXTENSION)--$(EXTVERSION).sql
1313
PGFILEDESC = "pg_pathman - partitioning tool"
1414

‎pg_pathman--1.0--1.1.sql

Lines changed: 199 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,199 @@
1+
/***********************************************************************
2+
* Modify config params table
3+
**********************************************************************/
4+
ALTERTABLE @extschema@.pathman_config_params ADD COLUMN init_callback REGPROCEDURENOT NULL DEFAULT0;
5+
ALTERTABLE @extschema@.pathman_config_params ALTER COLUMN enable_parentSET DEFAULT FALSE;
6+
7+
/* Enable permissions*/
8+
GRANTSELECT, INSERT,UPDATE,DELETE
9+
ON @extschema@.pathman_config, @extschema@.pathman_config_params
10+
TO public;
11+
12+
CREATEOR REPLACE FUNCTION @extschema@.check_security_policy(relation regclass)
13+
RETURNS BOOLAS'pg_pathman','check_security_policy' LANGUAGE C STRICT;
14+
15+
CREATE POLICY deny_modificationON @extschema@.pathman_config
16+
FOR ALL USING (check_security_policy(partrel));
17+
18+
CREATE POLICY deny_modificationON @extschema@.pathman_config_params
19+
FOR ALL USING (check_security_policy(partrel));
20+
21+
CREATE POLICY allow_selectON @extschema@.pathman_config FORSELECT USING (true);
22+
23+
CREATE POLICY allow_selectON @extschema@.pathman_config_params FORSELECT USING (true);
24+
25+
ALTERTABLE @extschema@.pathman_config ENABLE ROW LEVEL SECURITY;
26+
ALTERTABLE @extschema@.pathman_config_params ENABLE ROW LEVEL SECURITY;
27+
28+
GRANTSELECTON @extschema@.pathman_concurrent_part_tasks TO PUBLIC;
29+
30+
/* Drop irrelevant functions*/
31+
DROPFUNCTION @extschema@.invalidate_relcache(OID);
32+
DROPFUNCTION @extschema@.pathman_set_param(REGCLASS,TEXT,BOOLEAN);
33+
DROPFUNCTION @extschema@.enable_parent(REGCLASS);
34+
DROPFUNCTION @extschema@.disable_parent(relation REGCLASS);
35+
DROPFUNCTION @extschema@.enable_auto(relation REGCLASS);
36+
DROPFUNCTION @extschema@.disable_auto(relation REGCLASS);
37+
DROPFUNCTION @extschema@.partition_table_concurrently(relation regclass);
38+
DROPFUNCTION @extschema@._partition_data_concurrent(REGCLASS, ANYELEMENT, ANYELEMENT,INT, OUTBIGINT);
39+
DROPFUNCTION @extschema@.common_relation_checks(REGCLASS,TEXT);
40+
41+
/* Alter functions' modifiers*/
42+
ALTERFUNCTION @extschema@.partitions_count(REGCLASS) STRICT;
43+
ALTERFUNCTION @extschema@.partition_data(REGCLASS, OUTBIGINT) STRICT;
44+
ALTERFUNCTION @extschema@.disable_pathman_for(REGCLASS) STRICT;
45+
ALTERFUNCTION @extschema@.get_plain_schema_and_relname(REGCLASS, OUTTEXT, OUTTEXT) STRICT;
46+
47+
/* Create functions*/
48+
CREATEOR REPLACE FUNCTION @extschema@.pathman_set_param(
49+
relationREGCLASS,
50+
paramTEXT,
51+
valueANYELEMENT)
52+
RETURNS VOIDAS
53+
$$
54+
BEGIN
55+
EXECUTE format('INSERT INTO @extschema@.pathman_config_params
56+
(partrel, %1$s) VALUES ($1, $2)
57+
ON CONFLICT (partrel) DO UPDATE SET %1$s = $2', param)
58+
USING relation, value;
59+
END
60+
$$
61+
LANGUAGE plpgsql;
62+
63+
CREATEOR REPLACE FUNCTION @extschema@.set_enable_parent(
64+
relationREGCLASS,
65+
valueBOOLEAN)
66+
RETURNS VOIDAS
67+
$$
68+
BEGIN
69+
PERFORM @extschema@.pathman_set_param(relation,'enable_parent', value);
70+
END
71+
$$
72+
LANGUAGE plpgsql STRICT;
73+
74+
/*
75+
* Partition table using ConcurrentPartWorker.
76+
*/
77+
CREATEOR REPLACE FUNCTION @extschema@.partition_table_concurrently(
78+
relationREGCLASS,
79+
batch_sizeINTEGER DEFAULT1000,
80+
sleep_timeFLOAT8 DEFAULT1.0)
81+
RETURNS VOIDAS'pg_pathman','partition_table_concurrently'
82+
LANGUAGE C STRICT;
83+
84+
/*
85+
* Copy rows to partitions concurrently.
86+
*/
87+
CREATEOR REPLACE FUNCTION @extschema@._partition_data_concurrent(
88+
relationREGCLASS,
89+
p_minANYELEMENT DEFAULTNULL::text,
90+
p_maxANYELEMENT DEFAULTNULL::text,
91+
p_limitINT DEFAULTNULL,
92+
OUT p_totalBIGINT)
93+
AS
94+
$$
95+
DECLARE
96+
v_attrTEXT;
97+
v_limit_clauseTEXT :='';
98+
v_where_clauseTEXT :='';
99+
ctidsTID[];
100+
101+
BEGIN
102+
SELECT attname INTO v_attr
103+
FROM @extschema@.pathman_configWHERE partrel= relation;
104+
105+
p_total :=0;
106+
107+
/* Format LIMIT clause if needed*/
108+
IF NOT p_limit ISNULL THEN
109+
v_limit_clause := format('LIMIT %s', p_limit);
110+
END IF;
111+
112+
/* Format WHERE clause if needed*/
113+
IF NOT p_min ISNULL THEN
114+
v_where_clause := format('%1$s >= $1', v_attr);
115+
END IF;
116+
117+
IF NOT p_max ISNULL THEN
118+
IF NOT p_min ISNULL THEN
119+
v_where_clause := v_where_clause||' AND';
120+
END IF;
121+
v_where_clause := v_where_clause|| format('%1$s < $2', v_attr);
122+
END IF;
123+
124+
IF v_where_clause!='' THEN
125+
v_where_clause :='WHERE'|| v_where_clause;
126+
END IF;
127+
128+
/* Lock rows and copy data*/
129+
RAISE NOTICE'Copying data to partitions...';
130+
EXECUTE format('SELECT array(SELECT ctid FROM ONLY %1$s %2$s %3$s FOR UPDATE NOWAIT)',
131+
relation, v_where_clause, v_limit_clause)
132+
USING p_min, p_max
133+
INTO ctids;
134+
135+
EXECUTE format('
136+
WITH data AS (
137+
DELETE FROM ONLY %1$s WHERE ctid = ANY($1) RETURNING *)
138+
INSERT INTO %1$s SELECT * FROM data',
139+
relation)
140+
USING ctids;
141+
142+
/* Get number of inserted rows*/
143+
GET DIAGNOSTICS p_total= ROW_COUNT;
144+
RETURN;
145+
END
146+
$$
147+
LANGUAGE plpgsql
148+
SETpg_pathman.enable_partitionfilter=on;/* ensures that PartitionFilter is ON*/
149+
150+
/*
151+
* Aggregates several common relation checks before partitioning.
152+
* Suitable for every partitioning type.
153+
*/
154+
CREATEOR REPLACE FUNCTION @extschema@.common_relation_checks(
155+
relationREGCLASS,
156+
p_attributeTEXT)
157+
RETURNSBOOLEANAS
158+
$$
159+
DECLARE
160+
v_recRECORD;
161+
is_referencedBOOLEAN;
162+
rel_persistenceCHAR;
163+
164+
BEGIN
165+
/* Ignore temporary tables*/
166+
SELECT relpersistenceFROMpg_catalog.pg_class
167+
WHEREoid= relation INTO rel_persistence;
168+
169+
IF rel_persistence='t'::CHAR THEN
170+
RAISE EXCEPTION'temporary table "%" cannot be partitioned',
171+
relation::TEXT;
172+
END IF;
173+
174+
IF EXISTS (SELECT*FROM @extschema@.pathman_config
175+
WHERE partrel= relation) THEN
176+
RAISE EXCEPTION'relation "%" has already been partitioned', relation;
177+
END IF;
178+
179+
IF @extschema@.is_attribute_nullable(relation, p_attribute) THEN
180+
RAISE EXCEPTION'partitioning key''%'' must be NOT NULL', p_attribute;
181+
END IF;
182+
183+
/* Check if there are foreign keys that reference the relation*/
184+
FOR v_recIN (SELECT*FROMpg_catalog.pg_constraint
185+
WHERE confrelid= relation::REGCLASS::OID)
186+
LOOP
187+
is_referenced := TRUE;
188+
RAISE WARNING'foreign key "%" references relation "%"',
189+
v_rec.conname, relation;
190+
END LOOP;
191+
192+
IF is_referenced THEN
193+
RAISE EXCEPTION'relation "%" is referenced from other relations', relation;
194+
END IF;
195+
196+
RETURN TRUE;
197+
END
198+
$$
199+
LANGUAGE plpgsql;

0 commit comments

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