Skip to content

Commit 44e09c4

Browse files
author
Alexander Korotkov
committed
Merge branch 'pathman_delete' into pathman_pgpro9_5
2 parents f6bb11c + 9e17f2a commit 44e09c4

File tree

6 files changed

+1275
-1143
lines changed

6 files changed

+1275
-1143
lines changed

‎contrib/pg_pathman/expected/pg_pathman.out

Lines changed: 79 additions & 19 deletions
Original file line numberDiff line numberDiff line change
@@ -3,8 +3,8 @@ CREATE SCHEMA pathman;
33
CREATE EXTENSION pg_pathman SCHEMA pathman;
44
CREATE SCHEMA test;
55
CREATE TABLE test.hash_rel (
6-
id SERIAL PRIMARY KEY,
7-
value INTEGER);
6+
id SERIAL PRIMARY KEY,
7+
value INTEGER);
88
INSERT INTO test.hash_rel VALUES (1, 1);
99
INSERT INTO test.hash_rel VALUES (2, 2);
1010
INSERT INTO test.hash_rel VALUES (3, 3);
@@ -48,9 +48,9 @@ SELECT COUNT(*) FROM ONLY test.hash_rel;
4848
(1 row)
4949

5050
CREATE TABLE test.range_rel (
51-
id SERIAL PRIMARY KEY,
52-
dt TIMESTAMP,
53-
txt TEXT);
51+
id SERIAL PRIMARY KEY,
52+
dt TIMESTAMP,
53+
txt TEXT);
5454
CREATE INDEX ON test.range_rel (dt);
5555
INSERT INTO test.range_rel (dt, txt)
5656
SELECT g, md5(g::TEXT) FROM generate_series('2015-01-01', '2015-04-30', '1 day'::interval) as g;
@@ -80,8 +80,8 @@ SELECT COUNT(*) FROM ONLY test.range_rel;
8080
(1 row)
8181

8282
CREATE TABLE test.num_range_rel (
83-
id SERIAL PRIMARY KEY,
84-
txt TEXT);
83+
id SERIAL PRIMARY KEY,
84+
txt TEXT);
8585
SELECT pathman.create_range_partitions('test.num_range_rel', 'id', 0, 1000, 4);
8686
NOTICE: sequence "num_range_rel_seq" does not exist, skipping
8787
NOTICE: Copying data to partitions...
@@ -103,7 +103,7 @@ SELECT COUNT(*) FROM ONLY test.num_range_rel;
103103
(1 row)
104104

105105
INSERT INTO test.num_range_rel
106-
SELECT g, md5(g::TEXT) FROM generate_series(1, 3000) as g;
106+
SELECT g, md5(g::TEXT) FROM generate_series(1, 3000) as g;
107107
SELECT COUNT(*) FROM test.num_range_rel;
108108
count
109109
-------
@@ -648,8 +648,8 @@ DROP TABLE test.range_rel CASCADE;
648648
NOTICE: drop cascades to 7 other objects
649649
/* Test automatic partition creation */
650650
CREATE TABLE test.range_rel (
651-
id SERIAL PRIMARY KEY,
652-
dt TIMESTAMP NOT NULL);
651+
id SERIAL PRIMARY KEY,
652+
dt TIMESTAMP NOT NULL);
653653
SELECT pathman.create_range_partitions('test.range_rel', 'dt', '2015-01-01'::DATE, '10 days'::INTERVAL, 1);
654654
NOTICE: Copying data to partitions...
655655
create_range_partitions
@@ -698,8 +698,8 @@ SELECT * FROM pathman.pathman_config;
698698

699699
/* Check overlaps */
700700
CREATE TABLE test.num_range_rel (
701-
id SERIAL PRIMARY KEY,
702-
txt TEXT);
701+
id SERIAL PRIMARY KEY,
702+
txt TEXT);
703703
SELECT pathman.create_range_partitions('test.num_range_rel', 'id', 1000, 1000, 4);
704704
NOTICE: Copying data to partitions...
705705
create_range_partitions
@@ -754,8 +754,8 @@ DROP EXTENSION pg_pathman;
754754
CREATE EXTENSION pg_pathman;
755755
/* Hash */
756756
CREATE TABLE hash_rel (
757-
id SERIAL PRIMARY KEY,
758-
value INTEGER NOT NULL);
757+
id SERIAL PRIMARY KEY,
758+
value INTEGER NOT NULL);
759759
INSERT INTO hash_rel (value) SELECT g FROM generate_series(1, 10000) as g;
760760
SELECT create_hash_partitions('hash_rel', 'value', 3);
761761
NOTICE: function hash_rel_hash_insert_trigger_func() does not exist, skipping
@@ -780,9 +780,10 @@ EXPLAIN (COSTS OFF) SELECT * FROM hash_rel WHERE id = 1234;
780780

781781
/* Range */
782782
CREATE TABLE range_rel (
783-
id SERIAL PRIMARY KEY,
784-
dt TIMESTAMP NOT NULL);
785-
INSERT INTO range_rel (dt) SELECT g FROM generate_series('2010-01-01'::date, '2010-12-31'::date, '1 day') as g;
783+
id SERIAL PRIMARY KEY,
784+
dt TIMESTAMP NOT NULL,
785+
value INTEGER);
786+
INSERT INTO range_rel (dt, value) SELECT g, extract(day from g) FROM generate_series('2010-01-01'::date, '2010-12-31'::date, '1 day') as g;
786787
SELECT create_range_partitions('range_rel', 'dt', '2010-01-01'::date, '1 month'::interval, 12);
787788
NOTICE: sequence "range_rel_seq" does not exist, skipping
788789
NOTICE: Copying data to partitions...
@@ -845,6 +846,65 @@ EXPLAIN (COSTS OFF) SELECT * FROM range_rel WHERE dt > '2010-12-15';
845846
-> Seq Scan on range_rel_14
846847
(4 rows)
847848

849+
/* Temporary table for JOINs */
850+
CREATE TABLE tmp (id INTEGER NOT NULL, value INTEGER NOT NULL);
851+
INSERT INTO tmp VALUES (1, 1), (2, 2);
852+
/* Test UPDATE and DELETE */
853+
EXPLAIN (COSTS OFF) UPDATE range_rel SET value = 111 WHERE dt = '2010-06-15';
854+
QUERY PLAN
855+
--------------------------------------------------------------------------------
856+
Update on range_rel_6
857+
-> Seq Scan on range_rel_6
858+
Filter: (dt = 'Tue Jun 15 00:00:00 2010'::timestamp without time zone)
859+
(3 rows)
860+
861+
UPDATE range_rel SET value = 111 WHERE dt = '2010-06-15';
862+
SELECT * FROM range_rel WHERE dt = '2010-06-15';
863+
id | dt | value
864+
-----+--------------------------+-------
865+
166 | Tue Jun 15 00:00:00 2010 | 111
866+
(1 row)
867+
868+
EXPLAIN (COSTS OFF) DELETE FROM range_rel WHERE dt = '2010-06-15';
869+
QUERY PLAN
870+
--------------------------------------------------------------------------------
871+
Delete on range_rel_6
872+
-> Seq Scan on range_rel_6
873+
Filter: (dt = 'Tue Jun 15 00:00:00 2010'::timestamp without time zone)
874+
(3 rows)
875+
876+
DELETE FROM range_rel WHERE dt = '2010-06-15';
877+
SELECT * FROM range_rel WHERE dt = '2010-06-15';
878+
id | dt | value
879+
----+----+-------
880+
(0 rows)
881+
882+
EXPLAIN (COSTS OFF) UPDATE range_rel r SET value = t.value FROM tmp t WHERE r.dt = '2010-01-01' AND r.id = t.id;
883+
QUERY PLAN
884+
--------------------------------------------------------------------------------------------
885+
Update on range_rel_1 r
886+
-> Hash Join
887+
Hash Cond: (t.id = r.id)
888+
-> Seq Scan on tmp t
889+
-> Hash
890+
-> Index Scan using range_rel_1_pkey on range_rel_1 r
891+
Filter: (dt = 'Fri Jan 01 00:00:00 2010'::timestamp without time zone)
892+
(7 rows)
893+
894+
UPDATE range_rel r SET value = t.value FROM tmp t WHERE r.dt = '2010-01-01' AND r.id = t.id;
895+
EXPLAIN (COSTS OFF) DELETE FROM range_rel r USING tmp t WHERE r.dt = '2010-01-02' AND r.id = t.id;
896+
QUERY PLAN
897+
--------------------------------------------------------------------------------------------
898+
Delete on range_rel_1 r
899+
-> Hash Join
900+
Hash Cond: (t.id = r.id)
901+
-> Seq Scan on tmp t
902+
-> Hash
903+
-> Index Scan using range_rel_1_pkey on range_rel_1 r
904+
Filter: (dt = 'Sat Jan 02 00:00:00 2010'::timestamp without time zone)
905+
(7 rows)
906+
907+
DELETE FROM range_rel r USING tmp t WHERE r.dt = '2010-01-02' AND r.id = t.id;
848908
/* Create range partitions from whole range */
849909
SELECT drop_range_partitions('range_rel');
850910
NOTICE: 0 rows copied from range_rel_15
@@ -856,11 +916,11 @@ NOTICE: 31 rows copied from range_rel_10
856916
NOTICE: 30 rows copied from range_rel_9
857917
NOTICE: 31 rows copied from range_rel_8
858918
NOTICE: 31 rows copied from range_rel_7
859-
NOTICE: 30 rows copied from range_rel_6
919+
NOTICE: 29 rows copied from range_rel_6
860920
NOTICE: 31 rows copied from range_rel_5
861921
NOTICE: 30 rows copied from range_rel_4
862922
NOTICE: 31 rows copied from range_rel_3
863-
NOTICE: 45 rows copied from range_rel_1
923+
NOTICE: 44 rows copied from range_rel_1
864924
drop_range_partitions
865925
-----------------------
866926
14

0 commit comments

Comments
 (0)