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

Commit89d2d8f

Browse files
committed
more regression tests for PostgreSQL 9.5
1 parent2284abc commit89d2d8f

File tree

3 files changed

+419
-52
lines changed

3 files changed

+419
-52
lines changed

‎expected/pathman_upd_del.out

Lines changed: 141 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -1,3 +1,8 @@
1+
/*
2+
* -------------------------------------------
3+
* NOTE: This test behaves differenly on 9.5
4+
* -------------------------------------------
5+
*/
16
\set VERBOSITY terse
27
SET search_path = 'public';
38
CREATE SCHEMA pathman;
@@ -8,77 +13,97 @@ SET enable_seqscan = OFF;
813
/* Temporary table for JOINs */
914
CREATE TABLE test.tmp (id INTEGER NOT NULL, value INTEGER NOT NULL);
1015
INSERT INTO test.tmp VALUES (1, 1), (2, 2);
11-
/*Range */
16+
/*Partition table by RANGE */
1217
CREATE TABLE test.range_rel (
1318
idSERIAL PRIMARY KEY,
1419
dtTIMESTAMP NOT NULL,
1520
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);
21+
INSERT INTO test.range_rel (dt, value) SELECT g, extract(day from g)
22+
FROM generate_series('2010-01-01'::date, '2010-12-31'::date, '1 day') AS g;
23+
SELECT pathman.create_range_partitions('test.range_rel', 'dt',
24+
'2010-01-01'::date, '1 month'::interval,
25+
12);
1826
create_range_partitions
1927
-------------------------
2028
12
2129
(1 row)
2230

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' */
31+
/*
32+
* Test UPDATE and DELETE
33+
*/
34+
/* have partitions for this 'dt' */
35+
EXPLAIN (COSTS OFF) UPDATE test.range_rel SET value = 111 WHERE dt = '2010-06-15';
2536
QUERY PLAN
2637
--------------------------------------------------------------------------------
2738
Update on range_rel_6
2839
-> Seq Scan on range_rel_6
2940
Filter: (dt = 'Tue Jun 15 00:00:00 2010'::timestamp without time zone)
3041
(3 rows)
3142

43+
BEGIN;
3244
UPDATE test.range_rel SET value = 111 WHERE dt = '2010-06-15';
3345
SELECT * FROM test.range_rel WHERE dt = '2010-06-15';
3446
id | dt | value
3547
-----+--------------------------+-------
3648
166 | Tue Jun 15 00:00:00 2010 | 111
3749
(1 row)
3850

39-
EXPLAIN (COSTS OFF) DELETE FROM test.range_rel WHERE dt = '2010-06-15';/* have partitions for this 'dt' */
51+
ROLLBACK;
52+
/* have partitions for this 'dt' */
53+
EXPLAIN (COSTS OFF) DELETE FROM test.range_rel WHERE dt = '2010-06-15';
4054
QUERY PLAN
4155
--------------------------------------------------------------------------------
4256
Delete on range_rel_6
4357
-> Seq Scan on range_rel_6
4458
Filter: (dt = 'Tue Jun 15 00:00:00 2010'::timestamp without time zone)
4559
(3 rows)
4660

61+
BEGIN;
4762
DELETE FROM test.range_rel WHERE dt = '2010-06-15';
4863
SELECT * FROM test.range_rel WHERE dt = '2010-06-15';
4964
id | dt | value
5065
----+----+-------
5166
(0 rows)
5267

53-
EXPLAIN (COSTS OFF) UPDATE test.range_rel SET value = 222 WHERE dt = '1990-01-01';/* no partitions for this 'dt' */
68+
ROLLBACK;
69+
/* no partitions for this 'dt' */
70+
EXPLAIN (COSTS OFF) UPDATE test.range_rel SET value = 222 WHERE dt = '1990-01-01';
5471
QUERY PLAN
5572
--------------------------------------------------------------------------------
5673
Update on range_rel
5774
-> Seq Scan on range_rel
5875
Filter: (dt = 'Mon Jan 01 00:00:00 1990'::timestamp without time zone)
5976
(3 rows)
6077

78+
BEGIN;
6179
UPDATE test.range_rel SET value = 111 WHERE dt = '1990-01-01';
6280
SELECT * FROM test.range_rel WHERE dt = '1990-01-01';
6381
id | dt | value
6482
----+----+-------
6583
(0 rows)
6684

67-
EXPLAIN (COSTS OFF) DELETE FROM test.range_rel WHERE dt < '1990-01-01';/* no partitions for this 'dt' */
85+
ROLLBACK;
86+
/* no partitions for this 'dt' */
87+
EXPLAIN (COSTS OFF) DELETE FROM test.range_rel WHERE dt < '1990-01-01';
6888
QUERY PLAN
6989
--------------------------------------------------------------------------------
7090
Delete on range_rel
7191
-> Seq Scan on range_rel
7292
Filter: (dt < 'Mon Jan 01 00:00:00 1990'::timestamp without time zone)
7393
(3 rows)
7494

95+
BEGIN;
7596
DELETE FROM test.range_rel WHERE dt < '1990-01-01';
7697
SELECT * FROM test.range_rel WHERE dt < '1990-01-01';
7798
id | dt | value
7899
----+----+-------
79100
(0 rows)
80101

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;
102+
ROLLBACK;
103+
/* UPDATE + FROM, partitioned table */
104+
EXPLAIN (COSTS OFF)
105+
UPDATE test.range_rel r SET value = t.value
106+
FROM test.tmp t WHERE r.dt = '2010-01-01' AND r.id = t.id;
82107
QUERY PLAN
83108
--------------------------------------------------------------------------------------------
84109
Update on range_rel_1 r
@@ -90,8 +115,34 @@ EXPLAIN (COSTS OFF) UPDATE test.range_rel r SET value = t.value FROM test.tmp t
90115
Filter: (dt = 'Fri Jan 01 00:00:00 2010'::timestamp without time zone)
91116
(7 rows)
92117

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;
118+
BEGIN;
119+
UPDATE test.range_rel r SET value = t.value
120+
FROM test.tmp t WHERE r.dt = '2010-01-01' AND r.id = t.id;
121+
ROLLBACK;
122+
/* UPDATE + FROM, single table */
123+
EXPLAIN (COSTS OFF)
124+
UPDATE test.tmp t SET value = r.value
125+
FROM test.range_rel r WHERE r.dt = '2010-01-01' AND r.id = t.id;
126+
QUERY PLAN
127+
--------------------------------------------------------------------------------------------------
128+
Update on tmp t
129+
-> Hash Join
130+
Hash Cond: (t.id = r.id)
131+
-> Seq Scan on tmp t
132+
-> Hash
133+
-> Append
134+
-> Index Scan using range_rel_1_pkey on range_rel_1 r
135+
Filter: (dt = 'Fri Jan 01 00:00:00 2010'::timestamp without time zone)
136+
(8 rows)
137+
138+
BEGIN;
139+
UPDATE test.tmp t SET value = r.value
140+
FROM test.range_rel r WHERE r.dt = '2010-01-01' AND r.id = t.id;
141+
ROLLBACK;
142+
/* DELETE + USING, partitioned table */
143+
EXPLAIN (COSTS OFF)
144+
DELETE FROM test.range_rel r USING test.tmp t
145+
WHERE r.dt = '2010-01-02' AND r.id = t.id;
95146
QUERY PLAN
96147
--------------------------------------------------------------------------------------------
97148
Delete on range_rel_1 r
@@ -103,8 +154,14 @@ EXPLAIN (COSTS OFF) DELETE FROM test.range_rel r USING test.tmp t WHERE r.dt = '
103154
Filter: (dt = 'Sat Jan 02 00:00:00 2010'::timestamp without time zone)
104155
(7 rows)
105156

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;
157+
BEGIN;
158+
DELETE FROM test.range_rel r USING test.tmp t
159+
WHERE r.dt = '2010-01-02' AND r.id = t.id;
160+
ROLLBACK;
161+
/* DELETE + USING, single table */
162+
EXPLAIN (COSTS OFF)
163+
DELETE FROM test.tmp t USING test.range_rel r
164+
WHERE r.dt = '2010-01-02' AND r.id = t.id;
108165
QUERY PLAN
109166
--------------------------------------------------------------------------------------------------
110167
Delete on tmp t
@@ -117,8 +174,15 @@ EXPLAIN (COSTS OFF) DELETE FROM test.tmp t USING test.range_rel r WHERE r.dt = '
117174
Filter: (dt = 'Sat Jan 02 00:00:00 2010'::timestamp without time zone)
118175
(8 rows)
119176

120-
DELETE FROM test.tmp t USING test.range_rel r WHERE r.dt = '2010-01-02' AND r.id = t.id;
121-
EXPLAIN (COSTS OFF) WITH q AS (SELECT * FROM test.range_rel r WHERE r.dt = '2010-01-02') DELETE FROM test.tmp USING q;
177+
BEGIN;
178+
DELETE FROM test.tmp t USING test.range_rel r
179+
WHERE r.dt = '2010-01-02' AND r.id = t.id;
180+
ROLLBACK;
181+
/* Test special rule for CTE; SELECT (PostgreSQL 9.5) */
182+
EXPLAIN (COSTS OFF)
183+
WITH q AS (SELECT * FROM test.range_rel r
184+
WHERE r.dt = '2010-01-02')
185+
DELETE FROM test.tmp USING q;
122186
QUERY PLAN
123187
----------------------------------------------------------------------------------------
124188
Delete on tmp
@@ -132,7 +196,68 @@ EXPLAIN (COSTS OFF) WITH q AS (SELECT * FROM test.range_rel r WHERE r.dt = '2010
132196
-> Seq Scan on tmp
133197
(9 rows)
134198

135-
WITH q AS (SELECT * FROM test.range_rel r WHERE r.dt = '2010-01-02') DELETE FROM test.tmp USING q;
199+
BEGIN;
200+
WITH q AS (SELECT * FROM test.range_rel r
201+
WHERE r.dt = '2010-01-02')
202+
DELETE FROM test.tmp USING q;
203+
ROLLBACK;
204+
/* Test special rule for CTE; DELETE (PostgreSQL 9.5) */
205+
EXPLAIN (COSTS OFF)
206+
WITH q AS (DELETE FROM test.range_rel r
207+
WHERE r.dt = '2010-01-02'
208+
RETURNING *)
209+
DELETE FROM test.tmp USING q;
210+
QUERY PLAN
211+
----------------------------------------------------------------------------------------
212+
Delete on tmp
213+
CTE q
214+
-> Delete on range_rel_1 r
215+
-> Seq Scan on range_rel_1 r
216+
Filter: (dt = 'Sat Jan 02 00:00:00 2010'::timestamp without time zone)
217+
-> Nested Loop
218+
-> CTE Scan on q
219+
-> Materialize
220+
-> Seq Scan on tmp
221+
(9 rows)
222+
223+
BEGIN;
224+
WITH q AS (DELETE FROM test.range_rel r
225+
WHERE r.dt = '2010-01-02'
226+
RETURNING *)
227+
DELETE FROM test.tmp USING q;
228+
ROLLBACK;
229+
/* Test special rule for CTE; DELETE + USING (PostgreSQL 9.5) */
230+
EXPLAIN (COSTS OFF)
231+
WITH q AS (DELETE FROM test.tmp t
232+
USING test.range_rel r
233+
WHERE r.dt = '2010-01-02' AND r.id = t.id
234+
RETURNING *)
235+
DELETE FROM test.tmp USING q;
236+
QUERY PLAN
237+
----------------------------------------------------------------------------------------------------------
238+
Delete on tmp
239+
CTE q
240+
-> Delete on tmp t
241+
-> Hash Join
242+
Hash Cond: (t.id = r.id)
243+
-> Seq Scan on tmp t
244+
-> Hash
245+
-> Append
246+
-> Index Scan using range_rel_1_pkey on range_rel_1 r
247+
Filter: (dt = 'Sat Jan 02 00:00:00 2010'::timestamp without time zone)
248+
-> Nested Loop
249+
-> CTE Scan on q
250+
-> Materialize
251+
-> Seq Scan on tmp
252+
(14 rows)
253+
254+
BEGIN;
255+
WITH q AS (DELETE FROM test.tmp t
256+
USING test.range_rel r
257+
WHERE r.dt = '2010-01-02' AND r.id = t.id
258+
RETURNING *)
259+
DELETE FROM test.tmp USING q;
260+
ROLLBACK;
136261
DROP SCHEMA test CASCADE;
137262
NOTICE: drop cascades to 15 other objects
138263
DROP EXTENSION pg_pathman CASCADE;

0 commit comments

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