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

Commitc871c0b

Browse files
committed
add pathman_rebuild_deletes test suite
1 parentdde913b commitc871c0b

File tree

3 files changed

+157
-0
lines changed

3 files changed

+157
-0
lines changed

‎Makefile

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -50,6 +50,7 @@ REGRESS = pathman_array_qual \
5050
pathman_only\
5151
pathman_param_upd_del\
5252
pathman_permissions\
53+
pathman_rebuild_deletes\
5354
pathman_rebuild_updates\
5455
pathman_rowmarks\
5556
pathman_runtime_nodes\

‎expected/pathman_rebuild_deletes.out

Lines changed: 99 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,99 @@
1+
\set VERBOSITY terse
2+
SET search_path = 'public';
3+
CREATE EXTENSION pg_pathman;
4+
CREATE SCHEMA test_deletes;
5+
/*
6+
* Test DELETEs on a partition with different TupleDescriptor.
7+
*/
8+
/* create partitioned table */
9+
CREATE TABLE test_deletes.test(a FLOAT4, val INT4 NOT NULL, b FLOAT8);
10+
INSERT INTO test_deletes.test SELECT i, i, i FROM generate_series(1, 100) AS i;
11+
SELECT create_range_partitions('test_deletes.test', 'val', 1, 10);
12+
create_range_partitions
13+
-------------------------
14+
10
15+
(1 row)
16+
17+
/* drop column 'a' */
18+
ALTER TABLE test_deletes.test DROP COLUMN a;
19+
/* append new partition */
20+
SELECT append_range_partition('test_deletes.test');
21+
append_range_partition
22+
------------------------
23+
test_deletes.test_11
24+
(1 row)
25+
26+
INSERT INTO test_deletes.test_11 (val, b) VALUES (101, 10);
27+
VACUUM ANALYZE;
28+
/* tuple descs are the same */
29+
EXPLAIN (COSTS OFF) DELETE FROM test_deletes.test WHERE val = 1;
30+
QUERY PLAN
31+
---------------------------
32+
Delete on test_1
33+
-> Seq Scan on test_1
34+
Filter: (val = 1)
35+
(3 rows)
36+
37+
DELETE FROM test_deletes.test WHERE val = 1 RETURNING *, tableoid::REGCLASS;
38+
val | b | tableoid
39+
-----+---+---------------------
40+
1 | 1 | test_deletes.test_1
41+
(1 row)
42+
43+
/* tuple descs are different */
44+
EXPLAIN (COSTS OFF) DELETE FROM test_deletes.test WHERE val = 101;
45+
QUERY PLAN
46+
-----------------------------
47+
Delete on test_11
48+
-> Seq Scan on test_11
49+
Filter: (val = 101)
50+
(3 rows)
51+
52+
DELETE FROM test_deletes.test WHERE val = 101 RETURNING *, tableoid::REGCLASS;
53+
val | b | tableoid
54+
-----+----+----------------------
55+
101 | 10 | test_deletes.test_11
56+
(1 row)
57+
58+
CREATE TABLE test_deletes.test_dummy (val INT4);
59+
EXPLAIN (COSTS OFF) DELETE FROM test_deletes.test
60+
WHERE val = 101 AND val = ANY (TABLE test_deletes.test_dummy)
61+
RETURNING *, tableoid::REGCLASS;
62+
QUERY PLAN
63+
------------------------------------
64+
Delete on test_11
65+
-> Nested Loop Semi Join
66+
-> Seq Scan on test_11
67+
Filter: (val = 101)
68+
-> Seq Scan on test_dummy
69+
Filter: (val = 101)
70+
(6 rows)
71+
72+
EXPLAIN (COSTS OFF) DELETE FROM test_deletes.test t1
73+
USING test_deletes.test_dummy t2
74+
WHERE t1.val = 101 AND t1.val = t2.val
75+
RETURNING t1.*, t1.tableoid::REGCLASS;
76+
QUERY PLAN
77+
---------------------------------------
78+
Delete on test_11 t1
79+
-> Nested Loop
80+
-> Seq Scan on test_11 t1
81+
Filter: (val = 101)
82+
-> Seq Scan on test_dummy t2
83+
Filter: (val = 101)
84+
(6 rows)
85+
86+
EXPLAIN (COSTS OFF) DELETE FROM test_deletes.test
87+
WHERE val = 101 AND test >= (100, 8)
88+
RETURNING *, tableoid::REGCLASS;
89+
QUERY PLAN
90+
-----------------------------------------------------------------------------------
91+
Delete on test_11
92+
-> Seq Scan on test_11
93+
Filter: (((test_11.*)::test_deletes.test >= ROW(100, 8)) AND (val = 101))
94+
(3 rows)
95+
96+
DROP TABLE test_deletes.test_dummy;
97+
DROP SCHEMA test_deletes CASCADE;
98+
NOTICE: drop cascades to 13 other objects
99+
DROP EXTENSION pg_pathman;

‎sql/pathman_rebuild_deletes.sql

Lines changed: 57 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,57 @@
1+
\set VERBOSITY terse
2+
3+
SET search_path='public';
4+
CREATE EXTENSION pg_pathman;
5+
CREATESCHEMAtest_deletes;
6+
7+
8+
/*
9+
* Test DELETEs on a partition with different TupleDescriptor.
10+
*/
11+
12+
/* create partitioned table*/
13+
CREATETABLEtest_deletes.test(a FLOAT4, val INT4NOT NULL, b FLOAT8);
14+
INSERT INTOtest_deletes.testSELECT i, i, iFROM generate_series(1,100)AS i;
15+
SELECT create_range_partitions('test_deletes.test','val',1,10);
16+
17+
/* drop column 'a'*/
18+
ALTERTABLEtest_deletes.test DROP COLUMN a;
19+
20+
/* append new partition*/
21+
SELECT append_range_partition('test_deletes.test');
22+
INSERT INTOtest_deletes.test_11 (val, b)VALUES (101,10);
23+
24+
25+
VACUUM ANALYZE;
26+
27+
28+
/* tuple descs are the same*/
29+
EXPLAIN (COSTS OFF)DELETEFROMtest_deletes.testWHERE val=1;
30+
DELETEFROMtest_deletes.testWHERE val=1 RETURNING*, tableoid::REGCLASS;
31+
32+
33+
/* tuple descs are different*/
34+
EXPLAIN (COSTS OFF)DELETEFROMtest_deletes.testWHERE val=101;
35+
DELETEFROMtest_deletes.testWHERE val=101 RETURNING*, tableoid::REGCLASS;
36+
37+
CREATETABLEtest_deletes.test_dummy (val INT4);
38+
39+
EXPLAIN (COSTS OFF)DELETEFROMtest_deletes.test
40+
WHERE val=101AND val= ANY (TABLEtest_deletes.test_dummy)
41+
RETURNING*, tableoid::REGCLASS;
42+
43+
EXPLAIN (COSTS OFF)DELETEFROMtest_deletes.test t1
44+
USINGtest_deletes.test_dummy t2
45+
WHEREt1.val=101ANDt1.val=t2.val
46+
RETURNING t1.*,t1.tableoid::REGCLASS;
47+
48+
EXPLAIN (COSTS OFF)DELETEFROMtest_deletes.test
49+
WHERE val=101AND test>= (100,8)
50+
RETURNING*, tableoid::REGCLASS;
51+
52+
DROPTABLEtest_deletes.test_dummy;
53+
54+
55+
56+
DROPSCHEMA test_deletes CASCADE;
57+
DROP EXTENSION pg_pathman;

0 commit comments

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