Skip to content

Commit a3e9ee5

Browse files
zilderzilder
committed
regression tests are ok
1 parent 17944ce commit a3e9ee5

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_schema TEXT;
2525
v_plain_relname TEXT;
2626
v_hashfunc TEXT;
27-
v_enable_parent BOOLEAN := 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
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)
208+
parent_relid REGCLASS,
209+
OUT p_total BIGINT)
152210
AS
153211
$$
154212
DECLARE
155-
v_attr TEXT;
156-
v_limit_clause TEXT := '';
157-
v_where_clause TEXT := '';
213+
relname TEXT;
214+
rec RECORD;
215+
cnt BIGINT := 0;
216+
158217
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;
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_attribute TEXT,
4343
p_start_value ANYELEMENT,
4444
p_interval INTERVAL,
45-
p_count INTEGER DEFAULT NULL)
45+
p_count INTEGER DEFAULT NULL,
46+
p_partition_data BOOLEAN DEFAULT true)
4647
RETURNS INTEGER AS
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_attribute TEXT,
125129
p_start_value ANYELEMENT,
126130
p_interval ANYELEMENT,
127-
p_count INTEGER DEFAULT NULL)
131+
p_count INTEGER DEFAULT NULL,
132+
p_partition_data BOOLEAN DEFAULT true)
128133
RETURNS INTEGER AS
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_attribute TEXT,
212220
p_start_value ANYELEMENT,
213221
p_end_value ANYELEMENT,
214-
p_interval ANYELEMENT)
222+
p_interval ANYELEMENT,
223+
p_partition_data BOOLEAN DEFAULT true)
215224
RETURNS INTEGER AS
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_attribute TEXT,
275287
p_start_value ANYELEMENT,
276288
p_end_value ANYELEMENT,
277-
p_interval INTERVAL)
289+
p_interval INTERVAL,
290+
p_partition_data BOOLEAN DEFAULT true)
278291
RETURNS INTEGER AS
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
CREATE SCHEMA pathman;

0 commit comments

Comments
 (0)