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

Commita3e9ee5

Browse files
committed
regression tests are ok
1 parent17944ce commita3e9ee5

File tree

4 files changed

+101
-63
lines changed

4 files changed

+101
-63
lines changed

‎hash.sql‎

Lines changed: 0 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -24,7 +24,6 @@ DECLARE
2424
v_plain_schemaTEXT;
2525
v_plain_relnameTEXT;
2626
v_hashfuncTEXT;
27-
v_enable_parentBOOLEAN := NOT partition_data;
2827

2928
BEGIN
3029
PERFORM @extschema@.validate_relname(parent_relid);
@@ -71,8 +70,6 @@ BEGIN
7170
IF partition_data= true THEN
7271
PERFORM @extschema@.disable_parent(parent_relid);
7372
PERFORM @extschema@.partition_data(parent_relid);
74-
ELSE
75-
PERFORM @extschema@.enable_parent(parent_relid);
7673
END IF;
7774

7875
RETURN partitions_count;

‎init.sql‎

Lines changed: 77 additions & 49 deletions
Original file line numberDiff line numberDiff line change
@@ -140,63 +140,91 @@ CREATE TYPE @extschema@.PathmanRange (
140140
output= pathman_range_out
141141
);
142142

143+
/*
144+
* Copy rows to partitions
145+
*/
146+
-- CREATE OR REPLACE FUNCTION @extschema@.partition_data(
147+
-- p_relation regclass
148+
-- , p_min ANYELEMENT DEFAULT NULL::text
149+
-- , p_max ANYELEMENT DEFAULT NULL::text
150+
-- , p_limit INT DEFAULT NULL
151+
-- , OUT p_total BIGINT)
152+
-- AS
153+
-- $$
154+
-- DECLARE
155+
-- v_attr TEXT;
156+
-- v_limit_clause TEXT := '';
157+
-- v_where_clause TEXT := '';
158+
-- BEGIN
159+
-- SELECT attname INTO v_attr
160+
-- FROM @extschema@.pathman_config WHERE partrel = p_relation;
161+
162+
-- PERFORM @extschema@.debug_capture();
163+
164+
-- p_total := 0;
165+
166+
-- /* Format LIMIT clause if needed */
167+
-- IF NOT p_limit IS NULL THEN
168+
-- v_limit_clause := format('LIMIT %s', p_limit);
169+
-- END IF;
170+
171+
-- /* Format WHERE clause if needed */
172+
-- IF NOT p_min IS NULL THEN
173+
-- v_where_clause := format('%1$s >= $1', v_attr);
174+
-- END IF;
175+
176+
-- IF NOT p_max IS NULL THEN
177+
-- IF NOT p_min IS NULL THEN
178+
-- v_where_clause := v_where_clause || ' AND ';
179+
-- END IF;
180+
-- v_where_clause := v_where_clause || format('%1$s < $2', v_attr);
181+
-- END IF;
182+
183+
-- IF v_where_clause != '' THEN
184+
-- v_where_clause := 'WHERE ' || v_where_clause;
185+
-- END IF;
186+
187+
-- /* Lock rows and copy data */
188+
-- RAISE NOTICE 'Copying data to partitions...';
189+
-- EXECUTE format('
190+
-- WITH data AS (
191+
-- DELETE FROM ONLY %1$s WHERE ctid IN (
192+
-- SELECT ctid FROM ONLY %1$s %2$s %3$s FOR UPDATE NOWAIT
193+
-- ) RETURNING *)
194+
-- INSERT INTO %1$s SELECT * FROM data'
195+
-- , p_relation, v_where_clause, v_limit_clause)
196+
-- USING p_min, p_max;
197+
198+
-- GET DIAGNOSTICS p_total = ROW_COUNT;
199+
-- RETURN;
200+
-- END
201+
-- $$
202+
-- LANGUAGE plpgsql;
203+
143204
/*
144205
* Copy rows to partitions
145206
*/
146207
CREATEOR REPLACE FUNCTION @extschema@.partition_data(
147-
p_relation regclass
148-
, p_min ANYELEMENT DEFAULTNULL::text
149-
, p_max ANYELEMENT DEFAULTNULL::text
150-
, p_limitINT DEFAULTNULL
151-
, OUT p_totalBIGINT)
208+
parent_relidREGCLASS,
209+
OUT p_totalBIGINT)
152210
AS
153211
$$
154212
DECLARE
155-
v_attrTEXT;
156-
v_limit_clauseTEXT :='';
157-
v_where_clauseTEXT :='';
213+
relnameTEXT;
214+
recRECORD;
215+
cntBIGINT :=0;
216+
158217
BEGIN
159-
SELECT attname INTO v_attr
160-
FROM @extschema@.pathman_configWHERE partrel= p_relation;
161-
162-
PERFORM @extschema@.debug_capture();
163-
164-
p_total :=0;
165-
166-
/* Format LIMIT clause if needed*/
167-
IF NOT p_limit ISNULL THEN
168-
v_limit_clause := format('LIMIT %s', p_limit);
169-
END IF;
170-
171-
/* Format WHERE clause if needed*/
172-
IF NOT p_min ISNULL THEN
173-
v_where_clause := format('%1$s >= $1', v_attr);
174-
END IF;
175-
176-
IF NOT p_max ISNULL THEN
177-
IF NOT p_min ISNULL THEN
178-
v_where_clause := v_where_clause||' AND';
179-
END IF;
180-
v_where_clause := v_where_clause|| format('%1$s < $2', v_attr);
181-
END IF;
182-
183-
IF v_where_clause!='' THEN
184-
v_where_clause :='WHERE'|| v_where_clause;
185-
END IF;
186-
187-
/* Lock rows and copy data*/
188-
RAISE NOTICE'Copying data to partitions...';
189-
EXECUTE format('
190-
WITH data AS (
191-
DELETE FROM ONLY %1$s WHERE ctid IN (
192-
SELECT ctid FROM ONLY %1$s %2$s %3$s FOR UPDATE NOWAIT
193-
) RETURNING *)
194-
INSERT INTO %1$s SELECT * FROM data'
195-
, p_relation, v_where_clause, v_limit_clause)
196-
USING p_min, p_max;
197-
198-
GET DIAGNOSTICS p_total= ROW_COUNT;
199-
RETURN;
218+
p_total :=0;
219+
220+
/* Create partitions and copy rest of the data*/
221+
EXECUTE format('WITH part_data AS (DELETE FROM ONLY %1$s RETURNING *)
222+
INSERT INTO %1$s SELECT * FROM part_data',
223+
@extschema@.get_schema_qualified_name(parent_relid));
224+
225+
/* Get number of inserted rows*/
226+
GET DIAGNOSTICS p_total= ROW_COUNT;
227+
RETURN;
200228
END
201229
$$
202230
LANGUAGE plpgsql;

‎range.sql‎

Lines changed: 24 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -42,7 +42,8 @@ CREATE OR REPLACE FUNCTION @extschema@.create_range_partitions(
4242
p_attributeTEXT,
4343
p_start_valueANYELEMENT,
4444
p_intervalINTERVAL,
45-
p_countINTEGER DEFAULTNULL)
45+
p_countINTEGER DEFAULTNULL,
46+
p_partition_dataBOOLEAN DEFAULT true)
4647
RETURNSINTEGERAS
4748
$$
4849
DECLARE
@@ -107,7 +108,10 @@ BEGIN
107108
PERFORM @extschema@.on_create_partitions(parent_relid);
108109

109110
/* Copy data*/
110-
PERFORM @extschema@.partition_data(parent_relid);
111+
IF p_partition_data= true THEN
112+
PERFORM @extschema@.disable_parent(parent_relid);
113+
PERFORM @extschema@.partition_data(parent_relid);
114+
END IF;
111115

112116
RETURN p_count;
113117

@@ -124,7 +128,8 @@ CREATE OR REPLACE FUNCTION @extschema@.create_range_partitions(
124128
p_attributeTEXT,
125129
p_start_valueANYELEMENT,
126130
p_intervalANYELEMENT,
127-
p_countINTEGER DEFAULTNULL)
131+
p_countINTEGER DEFAULTNULL,
132+
p_partition_dataBOOLEAN DEFAULT true)
128133
RETURNSINTEGERAS
129134
$$
130135
DECLARE
@@ -194,7 +199,10 @@ BEGIN
194199
PERFORM @extschema@.on_create_partitions(parent_relid);
195200

196201
/* Copy data*/
197-
PERFORM @extschema@.partition_data(parent_relid);
202+
IF p_partition_data= true THEN
203+
PERFORM @extschema@.disable_parent(parent_relid);
204+
PERFORM @extschema@.partition_data(parent_relid);
205+
END IF;
198206

199207
RETURN p_count;
200208

@@ -211,7 +219,8 @@ CREATE OR REPLACE FUNCTION @extschema@.create_partitions_from_range(
211219
p_attributeTEXT,
212220
p_start_valueANYELEMENT,
213221
p_end_valueANYELEMENT,
214-
p_intervalANYELEMENT)
222+
p_intervalANYELEMENT,
223+
p_partition_dataBOOLEAN DEFAULT true)
215224
RETURNSINTEGERAS
216225
$$
217226
DECLARE
@@ -257,7 +266,10 @@ BEGIN
257266
PERFORM @extschema@.on_create_partitions(parent_relid);
258267

259268
/* Copy data*/
260-
PERFORM @extschema@.partition_data(parent_relid);
269+
IF p_partition_data= true THEN
270+
PERFORM @extschema@.disable_parent(parent_relid);
271+
PERFORM @extschema@.partition_data(parent_relid);
272+
END IF;
261273

262274
RETURN part_count;/* number of created partitions*/
263275

@@ -274,7 +286,8 @@ CREATE OR REPLACE FUNCTION @extschema@.create_partitions_from_range(
274286
p_attributeTEXT,
275287
p_start_valueANYELEMENT,
276288
p_end_valueANYELEMENT,
277-
p_intervalINTERVAL)
289+
p_intervalINTERVAL,
290+
p_partition_dataBOOLEAN DEFAULT true)
278291
RETURNSINTEGERAS
279292
$$
280293
DECLARE
@@ -317,7 +330,10 @@ BEGIN
317330
PERFORM @extschema@.on_create_partitions(parent_relid);
318331

319332
/* Copy data*/
320-
PERFORM @extschema@.partition_data(parent_relid);
333+
IF p_partition_data= true THEN
334+
PERFORM @extschema@.disable_parent(parent_relid);
335+
PERFORM @extschema@.partition_data(parent_relid);
336+
END IF;
321337

322338
RETURN part_count;/* number of created partitions*/
323339

‎sql/pg_pathman.sql‎

Lines changed: 0 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1,6 +1,3 @@
1-
2-
--:gdb
3-
41
\set VERBOSITY terse
52

63
CREATESCHEMApathman;

0 commit comments

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