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

Commitef1e444

Browse files
committed
fix upd_del tests for 9.5
1 parent9949eee commitef1e444

File tree

1 file changed

+143
-62
lines changed

1 file changed

+143
-62
lines changed

‎expected/pathman_upd_del_1.out

Lines changed: 143 additions & 62 deletions
Original file line numberDiff line numberDiff line change
@@ -10,9 +10,16 @@ CREATE EXTENSION pg_pathman SCHEMA pathman;
1010
CREATE SCHEMA test;
1111
SET enable_indexscan = ON;
1212
SET enable_seqscan = OFF;
13-
/* Temporarytable for JOINs */
13+
/* Temporarytables for JOINs */
1414
CREATE TABLE test.tmp (id INTEGER NOT NULL, value INTEGER NOT NULL);
1515
INSERT INTO test.tmp VALUES (1, 1), (2, 2);
16+
CREATE TABLE test.tmp2 (id INTEGER NOT NULL, value INTEGER NOT NULL);
17+
SELECT pathman.create_range_partitions('test.tmp2', 'id', 1, 1, 10);
18+
create_range_partitions
19+
-------------------------
20+
10
21+
(1 row)
22+
1623
/* Partition table by RANGE */
1724
CREATE TABLE test.range_rel (
1825
idSERIAL PRIMARY KEY,
@@ -28,6 +35,7 @@ SELECT pathman.create_range_partitions('test.range_rel', 'dt',
2835
12
2936
(1 row)
3037

38+
VACUUM ANALYZE;
3139
/*
3240
* Test UPDATE and DELETE
3341
*/
@@ -104,16 +112,15 @@ ROLLBACK;
104112
EXPLAIN (COSTS OFF)
105113
UPDATE test.range_rel r SET value = t.value
106114
FROM test.tmp t WHERE r.dt = '2010-01-01' AND r.id = t.id;
107-
QUERY PLAN
108-
--------------------------------------------------------------------------------------------
115+
QUERY PLAN
116+
--------------------------------------------------------------------------------------
109117
Update on range_rel_1 r
110-
-> Hash Join
111-
Hash Cond: (t.id = r.id)
118+
-> Nested Loop
119+
Join Filter: (r.id = t.id)
120+
-> Index Scan using range_rel_1_pkey on range_rel_1 r
121+
Filter: (dt = 'Fri Jan 01 00:00:00 2010'::timestamp without time zone)
112122
-> Seq Scan on tmp t
113-
-> Hash
114-
-> Index Scan using range_rel_1_pkey on range_rel_1 r
115-
Filter: (dt = 'Fri Jan 01 00:00:00 2010'::timestamp without time zone)
116-
(7 rows)
123+
(6 rows)
117124

118125
BEGIN;
119126
UPDATE test.range_rel r SET value = t.value
@@ -123,19 +130,21 @@ ROLLBACK;
123130
EXPLAIN (COSTS OFF)
124131
UPDATE test.tmp t SET value = r.value
125132
FROM test.range_rel r WHERE r.dt = '2010-01-01' AND r.id = t.id;
126-
QUERY PLAN
127-
--------------------------------------------------------------------------------------------------
133+
QUERY PLAN
134+
--------------------------------------------------------------------------------------------
128135
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_pkey on range_rel r
135-
Filter: (dt = 'Fri Jan 01 00:00:00 2010'::timestamp without time zone)
136-
-> Index Scan using range_rel_1_pkey on range_rel_1 r_1
137-
Filter: (dt = 'Fri Jan 01 00:00:00 2010'::timestamp without time zone)
138-
(10 rows)
136+
-> Merge Join
137+
Merge Cond: (r.id = t.id)
138+
-> Merge Append
139+
Sort Key: r.id
140+
-> Index Scan using range_rel_pkey on range_rel r
141+
Filter: (dt = 'Fri Jan 01 00:00:00 2010'::timestamp without time zone)
142+
-> Index Scan using range_rel_1_pkey on range_rel_1 r_1
143+
Filter: (dt = 'Fri Jan 01 00:00:00 2010'::timestamp without time zone)
144+
-> Sort
145+
Sort Key: t.id
146+
-> Seq Scan on tmp t
147+
(12 rows)
139148

140149
BEGIN;
141150
UPDATE test.tmp t SET value = r.value
@@ -145,16 +154,15 @@ ROLLBACK;
145154
EXPLAIN (COSTS OFF)
146155
DELETE FROM test.range_rel r USING test.tmp t
147156
WHERE r.dt = '2010-01-02' AND r.id = t.id;
148-
QUERY PLAN
149-
--------------------------------------------------------------------------------------------
157+
QUERY PLAN
158+
--------------------------------------------------------------------------------------
150159
Delete on range_rel_1 r
151-
-> Hash Join
152-
Hash Cond: (t.id = r.id)
160+
-> Nested Loop
161+
Join Filter: (r.id = t.id)
162+
-> Index Scan using range_rel_1_pkey on range_rel_1 r
163+
Filter: (dt = 'Sat Jan 02 00:00:00 2010'::timestamp without time zone)
153164
-> Seq Scan on tmp t
154-
-> Hash
155-
-> Index Scan using range_rel_1_pkey on range_rel_1 r
156-
Filter: (dt = 'Sat Jan 02 00:00:00 2010'::timestamp without time zone)
157-
(7 rows)
165+
(6 rows)
158166

159167
BEGIN;
160168
DELETE FROM test.range_rel r USING test.tmp t
@@ -164,24 +172,98 @@ ROLLBACK;
164172
EXPLAIN (COSTS OFF)
165173
DELETE FROM test.tmp t USING test.range_rel r
166174
WHERE r.dt = '2010-01-02' AND r.id = t.id;
167-
QUERY PLAN
168-
--------------------------------------------------------------------------------------------------
175+
QUERY PLAN
176+
--------------------------------------------------------------------------------------------
169177
Delete on tmp t
170-
-> Hash Join
171-
Hash Cond: (t.id = r.id)
172-
-> Seq Scan on tmp t
173-
-> Hash
174-
-> Append
175-
-> Index Scan using range_rel_pkey on range_rel r
176-
Filter: (dt = 'Sat Jan 02 00:00:00 2010'::timestamp without time zone)
177-
-> Index Scan using range_rel_1_pkey on range_rel_1 r_1
178-
Filter: (dt = 'Sat Jan 02 00:00:00 2010'::timestamp without time zone)
179-
(10 rows)
178+
-> Merge Join
179+
Merge Cond: (r.id = t.id)
180+
-> Merge Append
181+
Sort Key: r.id
182+
-> Index Scan using range_rel_pkey on range_rel r
183+
Filter: (dt = 'Sat Jan 02 00:00:00 2010'::timestamp without time zone)
184+
-> Index Scan using range_rel_1_pkey on range_rel_1 r_1
185+
Filter: (dt = 'Sat Jan 02 00:00:00 2010'::timestamp without time zone)
186+
-> Sort
187+
Sort Key: t.id
188+
-> Seq Scan on tmp t
189+
(12 rows)
180190

181191
BEGIN;
182192
DELETE FROM test.tmp t USING test.range_rel r
183193
WHERE r.dt = '2010-01-02' AND r.id = t.id;
184194
ROLLBACK;
195+
/* DELETE + USING, two partitioned tables */
196+
EXPLAIN (COSTS OFF)
197+
DELETE FROM test.range_rel r USING test.tmp2 t
198+
WHERE t.id = r.id;
199+
ERROR: DELETE and UPDATE queries with a join of partitioned tables are not supported
200+
BEGIN;
201+
DELETE FROM test.range_rel r USING test.tmp2 t
202+
WHERE t.id = r.id;
203+
ERROR: DELETE and UPDATE queries with a join of partitioned tables are not supported
204+
ROLLBACK;
205+
/* DELETE + USING, partitioned table + two partitioned tables in subselect */
206+
EXPLAIN (COSTS OFF)
207+
DELETE FROM test.range_rel r
208+
USING (SELECT *
209+
FROM test.tmp2 a1
210+
JOIN test.tmp2 a2
211+
USING(id)) t
212+
WHERE t.id = r.id;
213+
ERROR: DELETE and UPDATE queries with a join of partitioned tables are not supported
214+
BEGIN;
215+
DELETE FROM test.range_rel r
216+
USING (SELECT *
217+
FROM test.tmp2 a1
218+
JOIN test.tmp2 a2
219+
USING(id)) t
220+
WHERE t.id = r.id;
221+
ERROR: DELETE and UPDATE queries with a join of partitioned tables are not supported
222+
ROLLBACK;
223+
/* DELETE + USING, single table + two partitioned tables in subselect */
224+
EXPLAIN (COSTS OFF)
225+
DELETE FROM test.tmp r
226+
USING (SELECT *
227+
FROM test.tmp2 a1
228+
JOIN test.tmp2 a2
229+
USING(id)) t
230+
WHERE t.id = r.id;
231+
QUERY PLAN
232+
---------------------------------------------
233+
Delete on tmp r
234+
-> Merge Join
235+
Merge Cond: (a1.id = a2.id)
236+
-> Merge Join
237+
Merge Cond: (r.id = a1.id)
238+
-> Sort
239+
Sort Key: r.id
240+
-> Seq Scan on tmp r
241+
-> Sort
242+
Sort Key: a1.id
243+
-> Seq Scan on tmp2 a1
244+
-> Sort
245+
Sort Key: a2.id
246+
-> Seq Scan on tmp2 a2
247+
(14 rows)
248+
249+
BEGIN;
250+
DELETE FROM test.tmp r
251+
USING (SELECT *
252+
FROM test.tmp2 a1
253+
JOIN test.tmp2 a2
254+
USING(id)) t
255+
WHERE t.id = r.id;
256+
ROLLBACK;
257+
/* UPDATE + FROM, two partitioned tables */
258+
EXPLAIN (COSTS OFF)
259+
UPDATE test.range_rel r SET value = 1 FROM test.tmp2 t
260+
WHERE t.id = r.id;
261+
ERROR: DELETE and UPDATE queries with a join of partitioned tables are not supported
262+
BEGIN;
263+
UPDATE test.range_rel r SET value = 1 FROM test.tmp2 t
264+
WHERE t.id = r.id;
265+
ERROR: DELETE and UPDATE queries with a join of partitioned tables are not supported
266+
ROLLBACK;
185267
/* Test special rule for CTE; SELECT (PostgreSQL 9.5) */
186268
EXPLAIN (COSTS OFF)
187269
WITH q AS (SELECT * FROM test.range_rel r
@@ -195,10 +277,9 @@ DELETE FROM test.tmp USING q;
195277
-> Seq Scan on range_rel_1 r
196278
Filter: (dt = 'Sat Jan 02 00:00:00 2010'::timestamp without time zone)
197279
-> Nested Loop
280+
-> Seq Scan on tmp
198281
-> CTE Scan on q
199-
-> Materialize
200-
-> Seq Scan on tmp
201-
(9 rows)
282+
(8 rows)
202283

203284
BEGIN;
204285
WITH q AS (SELECT * FROM test.range_rel r
@@ -219,10 +300,9 @@ DELETE FROM test.tmp USING q;
219300
-> Seq Scan on range_rel_1 r
220301
Filter: (dt = 'Sat Jan 02 00:00:00 2010'::timestamp without time zone)
221302
-> Nested Loop
303+
-> Seq Scan on tmp
222304
-> CTE Scan on q
223-
-> Materialize
224-
-> Seq Scan on tmp
225-
(9 rows)
305+
(8 rows)
226306

227307
BEGIN;
228308
WITH q AS (DELETE FROM test.range_rel r
@@ -237,25 +317,26 @@ WITH q AS (DELETE FROM test.tmp t
237317
WHERE r.dt = '2010-01-02' AND r.id = t.id
238318
RETURNING *)
239319
DELETE FROM test.tmp USING q;
240-
QUERY PLAN
241-
----------------------------------------------------------------------------------------------------------
320+
QUERY PLAN
321+
----------------------------------------------------------------------------------------------------
242322
Delete on tmp
243323
CTE q
244324
-> Delete on tmp t
245-
-> Hash Join
246-
Hash Cond: (t.id = r.id)
247-
-> Seq Scan on tmp t
248-
-> Hash
249-
-> Append
250-
-> Index Scan using range_rel_pkey on range_rel r
251-
Filter: (dt = 'Sat Jan 02 00:00:00 2010'::timestamp without time zone)
252-
-> Index Scan using range_rel_1_pkey on range_rel_1 r_1
253-
Filter: (dt = 'Sat Jan 02 00:00:00 2010'::timestamp without time zone)
325+
-> Merge Join
326+
Merge Cond: (r.id = t.id)
327+
-> Merge Append
328+
Sort Key: r.id
329+
-> Index Scan using range_rel_pkey on range_rel r
330+
Filter: (dt = 'Sat Jan 02 00:00:00 2010'::timestamp without time zone)
331+
-> Index Scan using range_rel_1_pkey on range_rel_1 r_1
332+
Filter: (dt = 'Sat Jan 02 00:00:00 2010'::timestamp without time zone)
333+
-> Sort
334+
Sort Key: t.id
335+
-> Seq Scan on tmp t
254336
-> Nested Loop
337+
-> Seq Scan on tmp
255338
-> CTE Scan on q
256-
-> Materialize
257-
-> Seq Scan on tmp
258-
(16 rows)
339+
(17 rows)
259340

260341
BEGIN;
261342
WITH q AS (DELETE FROM test.tmp t
@@ -265,6 +346,6 @@ WITH q AS (DELETE FROM test.tmp t
265346
DELETE FROM test.tmp USING q;
266347
ROLLBACK;
267348
DROP SCHEMA test CASCADE;
268-
NOTICE: drop cascades to15 other objects
349+
NOTICE: drop cascades to27 other objects
269350
DROP EXTENSION pg_pathman CASCADE;
270351
DROP SCHEMA pathman CASCADE;

0 commit comments

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