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

Commitc9b203e

Browse files
committed
Separate tests related with UPDATE and DELETE from pathman_basic
1 parent1a572f3 commitc9b203e

File tree

6 files changed

+305
-136
lines changed

6 files changed

+305
-136
lines changed

‎Makefile‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -47,6 +47,7 @@ REGRESS = pathman_array_qual \
4747
pathman_rowmarks\
4848
pathman_runtime_nodes\
4949
pathman_update_trigger\
50+
pathman_upd_del\
5051
pathman_utility_stmt
5152

5253
EXTRA_REGRESS_OPTS=--temp-config=$(top_srcdir)/$(subdir)/conf.add

‎expected/pathman_basic.out‎

Lines changed: 3 additions & 104 deletions
Original file line numberDiff line numberDiff line change
@@ -1653,114 +1653,13 @@ EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE dt > '2010-12-15';
16531653
-> Seq Scan on range_rel_14
16541654
(4 rows)
16551655

1656-
/* Temporary table for JOINs */
1657-
CREATE TABLE test.tmp (id INTEGER NOT NULL, value INTEGER NOT NULL);
1658-
INSERT INTO test.tmp VALUES (1, 1), (2, 2);
1659-
/* Test UPDATE and DELETE */
1660-
EXPLAIN (COSTS OFF) UPDATE test.range_rel SET value = 111 WHERE dt = '2010-06-15';/* have partitions for this 'dt' */
1661-
QUERY PLAN
1662-
--------------------------------------------------------------------------------
1663-
Update on range_rel_6
1664-
-> Seq Scan on range_rel_6
1665-
Filter: (dt = 'Tue Jun 15 00:00:00 2010'::timestamp without time zone)
1666-
(3 rows)
1667-
1668-
UPDATE test.range_rel SET value = 111 WHERE dt = '2010-06-15';
1669-
SELECT * FROM test.range_rel WHERE dt = '2010-06-15';
1670-
id | dt | value
1671-
-----+--------------------------+-------
1672-
166 | Tue Jun 15 00:00:00 2010 | 111
1673-
(1 row)
1674-
1675-
EXPLAIN (COSTS OFF) DELETE FROM test.range_rel WHERE dt = '2010-06-15';/* have partitions for this 'dt' */
1676-
QUERY PLAN
1677-
--------------------------------------------------------------------------------
1678-
Delete on range_rel_6
1679-
-> Seq Scan on range_rel_6
1680-
Filter: (dt = 'Tue Jun 15 00:00:00 2010'::timestamp without time zone)
1681-
(3 rows)
1682-
1683-
DELETE FROM test.range_rel WHERE dt = '2010-06-15';
1684-
SELECT * FROM test.range_rel WHERE dt = '2010-06-15';
1685-
id | dt | value
1686-
----+----+-------
1687-
(0 rows)
1688-
1689-
EXPLAIN (COSTS OFF) UPDATE test.range_rel SET value = 222 WHERE dt = '1990-01-01';/* no partitions for this 'dt' */
1690-
QUERY PLAN
1691-
--------------------------------------------------------------------------------
1692-
Update on range_rel
1693-
-> Seq Scan on range_rel
1694-
Filter: (dt = 'Mon Jan 01 00:00:00 1990'::timestamp without time zone)
1695-
(3 rows)
1696-
1697-
UPDATE test.range_rel SET value = 111 WHERE dt = '1990-01-01';
1698-
SELECT * FROM test.range_rel WHERE dt = '1990-01-01';
1699-
id | dt | value
1700-
----+----+-------
1701-
(0 rows)
1702-
1703-
EXPLAIN (COSTS OFF) DELETE FROM test.range_rel WHERE dt < '1990-01-01';/* no partitions for this 'dt' */
1704-
QUERY PLAN
1705-
--------------------------------------------------------------------------------
1706-
Delete on range_rel
1707-
-> Seq Scan on range_rel
1708-
Filter: (dt < 'Mon Jan 01 00:00:00 1990'::timestamp without time zone)
1709-
(3 rows)
1710-
1711-
DELETE FROM test.range_rel WHERE dt < '1990-01-01';
1712-
SELECT * FROM test.range_rel WHERE dt < '1990-01-01';
1713-
id | dt | value
1714-
----+----+-------
1715-
(0 rows)
1716-
1717-
EXPLAIN (COSTS OFF) UPDATE test.range_rel r SET value = t.value FROM test.tmp t WHERE r.dt = '2010-01-01' AND r.id = t.id;
1718-
QUERY PLAN
1719-
--------------------------------------------------------------------------------------------
1720-
Update on range_rel_1 r
1721-
-> Hash Join
1722-
Hash Cond: (t.id = r.id)
1723-
-> Seq Scan on tmp t
1724-
-> Hash
1725-
-> Index Scan using range_rel_1_pkey on range_rel_1 r
1726-
Filter: (dt = 'Fri Jan 01 00:00:00 2010'::timestamp without time zone)
1727-
(7 rows)
1728-
1729-
UPDATE test.range_rel r SET value = t.value FROM test.tmp t WHERE r.dt = '2010-01-01' AND r.id = t.id;
1730-
EXPLAIN (COSTS OFF) DELETE FROM test.range_rel r USING test.tmp t WHERE r.dt = '2010-01-02' AND r.id = t.id;
1731-
QUERY PLAN
1732-
--------------------------------------------------------------------------------------------
1733-
Delete on range_rel_1 r
1734-
-> Hash Join
1735-
Hash Cond: (t.id = r.id)
1736-
-> Seq Scan on tmp t
1737-
-> Hash
1738-
-> Index Scan using range_rel_1_pkey on range_rel_1 r
1739-
Filter: (dt = 'Sat Jan 02 00:00:00 2010'::timestamp without time zone)
1740-
(7 rows)
1741-
1742-
DELETE FROM test.range_rel r USING test.tmp t WHERE r.dt = '2010-01-02' AND r.id = t.id;
1743-
EXPLAIN (COSTS OFF) DELETE FROM test.tmp t USING test.range_rel r WHERE r.dt = '2010-01-02' AND r.id = t.id;
1744-
QUERY PLAN
1745-
--------------------------------------------------------------------------------------------------
1746-
Delete on tmp t
1747-
-> Hash Join
1748-
Hash Cond: (t.id = r.id)
1749-
-> Seq Scan on tmp t
1750-
-> Hash
1751-
-> Append
1752-
-> Index Scan using range_rel_1_pkey on range_rel_1 r
1753-
Filter: (dt = 'Sat Jan 02 00:00:00 2010'::timestamp without time zone)
1754-
(8 rows)
1755-
1756-
DELETE FROM test.tmp t USING test.range_rel r WHERE r.dt = '2010-01-02' AND r.id = t.id;
17571656
/* Create range partitions from whole range */
17581657
SELECT drop_partitions('test.range_rel');
1759-
NOTICE:44 rows copied from test.range_rel_1
1658+
NOTICE:45 rows copied from test.range_rel_1
17601659
NOTICE: 31 rows copied from test.range_rel_3
17611660
NOTICE: 30 rows copied from test.range_rel_4
17621661
NOTICE: 31 rows copied from test.range_rel_5
1763-
NOTICE:29 rows copied from test.range_rel_6
1662+
NOTICE:30 rows copied from test.range_rel_6
17641663
NOTICE: 31 rows copied from test.range_rel_7
17651664
NOTICE: 31 rows copied from test.range_rel_8
17661665
NOTICE: 30 rows copied from test.range_rel_9
@@ -1953,6 +1852,6 @@ ORDER BY partition;
19531852
DROP TABLE test.provided_part_names CASCADE;
19541853
NOTICE: drop cascades to 2 other objects
19551854
DROP SCHEMA test CASCADE;
1956-
NOTICE: drop cascades to29 other objects
1855+
NOTICE: drop cascades to28 other objects
19571856
DROP EXTENSION pg_pathman CASCADE;
19581857
DROP SCHEMA pathman CASCADE;

‎expected/pathman_upd_del.out‎

Lines changed: 124 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,124 @@
1+
\set VERBOSITY terse
2+
SET search_path = 'public';
3+
CREATE SCHEMA pathman;
4+
CREATE EXTENSION pg_pathman SCHEMA pathman;
5+
CREATE SCHEMA test;
6+
SET enable_indexscan = ON;
7+
SET enable_seqscan = OFF;
8+
/* Temporary table for JOINs */
9+
CREATE TABLE test.tmp (id INTEGER NOT NULL, value INTEGER NOT NULL);
10+
INSERT INTO test.tmp VALUES (1, 1), (2, 2);
11+
/* Range */
12+
CREATE TABLE test.range_rel (
13+
idSERIAL PRIMARY KEY,
14+
dtTIMESTAMP NOT NULL,
15+
valueINTEGER);
16+
INSERT INTO test.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;
17+
SELECT pathman.create_range_partitions('test.range_rel', 'dt', '2010-01-01'::date, '1 month'::interval, 12);
18+
create_range_partitions
19+
-------------------------
20+
12
21+
(1 row)
22+
23+
/* Test UPDATE and DELETE */
24+
EXPLAIN (COSTS OFF) UPDATE test.range_rel SET value = 111 WHERE dt = '2010-06-15';/* have partitions for this 'dt' */
25+
QUERY PLAN
26+
--------------------------------------------------------------------------------
27+
Update on range_rel_6
28+
-> Seq Scan on range_rel_6
29+
Filter: (dt = 'Tue Jun 15 00:00:00 2010'::timestamp without time zone)
30+
(3 rows)
31+
32+
UPDATE test.range_rel SET value = 111 WHERE dt = '2010-06-15';
33+
SELECT * FROM test.range_rel WHERE dt = '2010-06-15';
34+
id | dt | value
35+
-----+--------------------------+-------
36+
166 | Tue Jun 15 00:00:00 2010 | 111
37+
(1 row)
38+
39+
EXPLAIN (COSTS OFF) DELETE FROM test.range_rel WHERE dt = '2010-06-15';/* have partitions for this 'dt' */
40+
QUERY PLAN
41+
--------------------------------------------------------------------------------
42+
Delete on range_rel_6
43+
-> Seq Scan on range_rel_6
44+
Filter: (dt = 'Tue Jun 15 00:00:00 2010'::timestamp without time zone)
45+
(3 rows)
46+
47+
DELETE FROM test.range_rel WHERE dt = '2010-06-15';
48+
SELECT * FROM test.range_rel WHERE dt = '2010-06-15';
49+
id | dt | value
50+
----+----+-------
51+
(0 rows)
52+
53+
EXPLAIN (COSTS OFF) UPDATE test.range_rel SET value = 222 WHERE dt = '1990-01-01';/* no partitions for this 'dt' */
54+
QUERY PLAN
55+
--------------------------------------------------------------------------------
56+
Update on range_rel
57+
-> Seq Scan on range_rel
58+
Filter: (dt = 'Mon Jan 01 00:00:00 1990'::timestamp without time zone)
59+
(3 rows)
60+
61+
UPDATE test.range_rel SET value = 111 WHERE dt = '1990-01-01';
62+
SELECT * FROM test.range_rel WHERE dt = '1990-01-01';
63+
id | dt | value
64+
----+----+-------
65+
(0 rows)
66+
67+
EXPLAIN (COSTS OFF) DELETE FROM test.range_rel WHERE dt < '1990-01-01';/* no partitions for this 'dt' */
68+
QUERY PLAN
69+
--------------------------------------------------------------------------------
70+
Delete on range_rel
71+
-> Seq Scan on range_rel
72+
Filter: (dt < 'Mon Jan 01 00:00:00 1990'::timestamp without time zone)
73+
(3 rows)
74+
75+
DELETE FROM test.range_rel WHERE dt < '1990-01-01';
76+
SELECT * FROM test.range_rel WHERE dt < '1990-01-01';
77+
id | dt | value
78+
----+----+-------
79+
(0 rows)
80+
81+
EXPLAIN (COSTS OFF) UPDATE test.range_rel r SET value = t.value FROM test.tmp t WHERE r.dt = '2010-01-01' AND r.id = t.id;
82+
QUERY PLAN
83+
--------------------------------------------------------------------------------------------
84+
Update on range_rel_1 r
85+
-> Hash Join
86+
Hash Cond: (t.id = r.id)
87+
-> Seq Scan on tmp t
88+
-> Hash
89+
-> Index Scan using range_rel_1_pkey on range_rel_1 r
90+
Filter: (dt = 'Fri Jan 01 00:00:00 2010'::timestamp without time zone)
91+
(7 rows)
92+
93+
UPDATE test.range_rel r SET value = t.value FROM test.tmp t WHERE r.dt = '2010-01-01' AND r.id = t.id;
94+
EXPLAIN (COSTS OFF) DELETE FROM test.range_rel r USING test.tmp t WHERE r.dt = '2010-01-02' AND r.id = t.id;
95+
QUERY PLAN
96+
--------------------------------------------------------------------------------------------
97+
Delete on range_rel_1 r
98+
-> Hash Join
99+
Hash Cond: (t.id = r.id)
100+
-> Seq Scan on tmp t
101+
-> Hash
102+
-> Index Scan using range_rel_1_pkey on range_rel_1 r
103+
Filter: (dt = 'Sat Jan 02 00:00:00 2010'::timestamp without time zone)
104+
(7 rows)
105+
106+
DELETE FROM test.range_rel r USING test.tmp t WHERE r.dt = '2010-01-02' AND r.id = t.id;
107+
EXPLAIN (COSTS OFF) DELETE FROM test.tmp t USING test.range_rel r WHERE r.dt = '2010-01-02' AND r.id = t.id;
108+
QUERY PLAN
109+
--------------------------------------------------------------------------------------------------
110+
Delete on tmp t
111+
-> Hash Join
112+
Hash Cond: (t.id = r.id)
113+
-> Seq Scan on tmp t
114+
-> Hash
115+
-> Append
116+
-> Index Scan using range_rel_1_pkey on range_rel_1 r
117+
Filter: (dt = 'Sat Jan 02 00:00:00 2010'::timestamp without time zone)
118+
(8 rows)
119+
120+
DELETE FROM test.tmp t USING test.range_rel r WHERE r.dt = '2010-01-02' AND r.id = t.id;
121+
DROP SCHEMA test CASCADE;
122+
NOTICE: drop cascades to 15 other objects
123+
DROP EXTENSION pg_pathman CASCADE;
124+
DROP SCHEMA pathman CASCADE;

‎expected/pathman_upd_del_1.out‎

Lines changed: 126 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,126 @@
1+
\set VERBOSITY terse
2+
SET search_path = 'public';
3+
CREATE SCHEMA pathman;
4+
CREATE EXTENSION pg_pathman SCHEMA pathman;
5+
CREATE SCHEMA test;
6+
SET enable_indexscan = ON;
7+
SET enable_seqscan = OFF;
8+
/* Temporary table for JOINs */
9+
CREATE TABLE test.tmp (id INTEGER NOT NULL, value INTEGER NOT NULL);
10+
INSERT INTO test.tmp VALUES (1, 1), (2, 2);
11+
/* Range */
12+
CREATE TABLE test.range_rel (
13+
idSERIAL PRIMARY KEY,
14+
dtTIMESTAMP NOT NULL,
15+
valueINTEGER);
16+
INSERT INTO test.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;
17+
SELECT pathman.create_range_partitions('test.range_rel', 'dt', '2010-01-01'::date, '1 month'::interval, 12);
18+
create_range_partitions
19+
-------------------------
20+
12
21+
(1 row)
22+
23+
/* Test UPDATE and DELETE */
24+
EXPLAIN (COSTS OFF) UPDATE test.range_rel SET value = 111 WHERE dt = '2010-06-15';/* have partitions for this 'dt' */
25+
QUERY PLAN
26+
--------------------------------------------------------------------------------
27+
Update on range_rel_6
28+
-> Seq Scan on range_rel_6
29+
Filter: (dt = 'Tue Jun 15 00:00:00 2010'::timestamp without time zone)
30+
(3 rows)
31+
32+
UPDATE test.range_rel SET value = 111 WHERE dt = '2010-06-15';
33+
SELECT * FROM test.range_rel WHERE dt = '2010-06-15';
34+
id | dt | value
35+
-----+--------------------------+-------
36+
166 | Tue Jun 15 00:00:00 2010 | 111
37+
(1 row)
38+
39+
EXPLAIN (COSTS OFF) DELETE FROM test.range_rel WHERE dt = '2010-06-15';/* have partitions for this 'dt' */
40+
QUERY PLAN
41+
--------------------------------------------------------------------------------
42+
Delete on range_rel_6
43+
-> Seq Scan on range_rel_6
44+
Filter: (dt = 'Tue Jun 15 00:00:00 2010'::timestamp without time zone)
45+
(3 rows)
46+
47+
DELETE FROM test.range_rel WHERE dt = '2010-06-15';
48+
SELECT * FROM test.range_rel WHERE dt = '2010-06-15';
49+
id | dt | value
50+
----+----+-------
51+
(0 rows)
52+
53+
EXPLAIN (COSTS OFF) UPDATE test.range_rel SET value = 222 WHERE dt = '1990-01-01';/* no partitions for this 'dt' */
54+
QUERY PLAN
55+
--------------------------------------------------------------------------------
56+
Update on range_rel
57+
-> Seq Scan on range_rel
58+
Filter: (dt = 'Mon Jan 01 00:00:00 1990'::timestamp without time zone)
59+
(3 rows)
60+
61+
UPDATE test.range_rel SET value = 111 WHERE dt = '1990-01-01';
62+
SELECT * FROM test.range_rel WHERE dt = '1990-01-01';
63+
id | dt | value
64+
----+----+-------
65+
(0 rows)
66+
67+
EXPLAIN (COSTS OFF) DELETE FROM test.range_rel WHERE dt < '1990-01-01';/* no partitions for this 'dt' */
68+
QUERY PLAN
69+
--------------------------------------------------------------------------------
70+
Delete on range_rel
71+
-> Seq Scan on range_rel
72+
Filter: (dt < 'Mon Jan 01 00:00:00 1990'::timestamp without time zone)
73+
(3 rows)
74+
75+
DELETE FROM test.range_rel WHERE dt < '1990-01-01';
76+
SELECT * FROM test.range_rel WHERE dt < '1990-01-01';
77+
id | dt | value
78+
----+----+-------
79+
(0 rows)
80+
81+
EXPLAIN (COSTS OFF) UPDATE test.range_rel r SET value = t.value FROM test.tmp t WHERE r.dt = '2010-01-01' AND r.id = t.id;
82+
QUERY PLAN
83+
--------------------------------------------------------------------------------------------
84+
Update on range_rel_1 r
85+
-> Hash Join
86+
Hash Cond: (t.id = r.id)
87+
-> Seq Scan on tmp t
88+
-> Hash
89+
-> Index Scan using range_rel_1_pkey on range_rel_1 r
90+
Filter: (dt = 'Fri Jan 01 00:00:00 2010'::timestamp without time zone)
91+
(7 rows)
92+
93+
UPDATE test.range_rel r SET value = t.value FROM test.tmp t WHERE r.dt = '2010-01-01' AND r.id = t.id;
94+
EXPLAIN (COSTS OFF) DELETE FROM test.range_rel r USING test.tmp t WHERE r.dt = '2010-01-02' AND r.id = t.id;
95+
QUERY PLAN
96+
--------------------------------------------------------------------------------------------
97+
Delete on range_rel_1 r
98+
-> Hash Join
99+
Hash Cond: (t.id = r.id)
100+
-> Seq Scan on tmp t
101+
-> Hash
102+
-> Index Scan using range_rel_1_pkey on range_rel_1 r
103+
Filter: (dt = 'Sat Jan 02 00:00:00 2010'::timestamp without time zone)
104+
(7 rows)
105+
106+
DELETE FROM test.range_rel r USING test.tmp t WHERE r.dt = '2010-01-02' AND r.id = t.id;
107+
EXPLAIN (COSTS OFF) DELETE FROM test.tmp t USING test.range_rel r WHERE r.dt = '2010-01-02' AND r.id = t.id;
108+
QUERY PLAN
109+
--------------------------------------------------------------------------------------------------
110+
Delete on tmp t
111+
-> Hash Join
112+
Hash Cond: (t.id = r.id)
113+
-> Seq Scan on tmp t
114+
-> Hash
115+
-> Append
116+
-> Index Scan using range_rel_pkey on range_rel r
117+
Filter: (dt = 'Sat Jan 02 00:00:00 2010'::timestamp without time zone)
118+
-> Index Scan using range_rel_1_pkey on range_rel_1 r_1
119+
Filter: (dt = 'Sat Jan 02 00:00:00 2010'::timestamp without time zone)
120+
(10 rows)
121+
122+
DELETE FROM test.tmp t USING test.range_rel r WHERE r.dt = '2010-01-02' AND r.id = t.id;
123+
DROP SCHEMA test CASCADE;
124+
NOTICE: drop cascades to 15 other objects
125+
DROP EXTENSION pg_pathman CASCADE;
126+
DROP SCHEMA pathman CASCADE;

0 commit comments

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