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

Commitbd2e71c

Browse files
committed
add more tests for UPDATE/DELETE on PostgreSQL 9.5 (issue#77)
1 parentf1b5316 commitbd2e71c

File tree

4 files changed

+315
-1
lines changed

4 files changed

+315
-1
lines changed

‎Makefile

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -53,7 +53,8 @@ REGRESS = pathman_array_qual \
5353
pathman_runtime_nodes\
5454
pathman_update_trigger\
5555
pathman_upd_del\
56-
pathman_utility_stmt
56+
pathman_utility_stmt\
57+
pathman_views
5758

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

‎expected/pathman_views.out

Lines changed: 107 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,107 @@
1+
/*
2+
* -------------------------------------------
3+
* NOTE: This test behaves differenly on 9.5
4+
* -------------------------------------------
5+
*/
6+
\set VERBOSITY terse
7+
SET search_path = 'public';
8+
CREATE EXTENSION pg_pathman;
9+
CREATE SCHEMA views;
10+
/* create a partitioned table */
11+
create table views._abc(id int4 not null);
12+
select create_hash_partitions('views._abc', 'id', 10);
13+
create_hash_partitions
14+
------------------------
15+
10
16+
(1 row)
17+
18+
insert into views._abc select generate_series(1, 100);
19+
/* create a facade view */
20+
create view views.abc as select * from views._abc;
21+
create or replace function views.disable_modification()
22+
returns trigger as
23+
$$
24+
BEGIN
25+
RAISE EXCEPTION '%', TG_OP;
26+
RETURN NULL;
27+
END;
28+
$$
29+
language 'plpgsql';
30+
create trigger abc_mod_tr
31+
instead of insert or update or delete
32+
on views.abc for each row
33+
execute procedure views.disable_modification();
34+
/* Test SELECT */
35+
explain (costs off) select * from views.abc;
36+
QUERY PLAN
37+
--------------------------
38+
Append
39+
-> Seq Scan on _abc_0
40+
-> Seq Scan on _abc_1
41+
-> Seq Scan on _abc_2
42+
-> Seq Scan on _abc_3
43+
-> Seq Scan on _abc_4
44+
-> Seq Scan on _abc_5
45+
-> Seq Scan on _abc_6
46+
-> Seq Scan on _abc_7
47+
-> Seq Scan on _abc_8
48+
-> Seq Scan on _abc_9
49+
(11 rows)
50+
51+
explain (costs off) select * from views.abc where id = 1;
52+
QUERY PLAN
53+
--------------------------
54+
Append
55+
-> Seq Scan on _abc_0
56+
Filter: (id = 1)
57+
(3 rows)
58+
59+
select count (*) from views.abc;
60+
count
61+
-------
62+
100
63+
(1 row)
64+
65+
/* Test INSERT */
66+
explain (costs off) insert into views.abc values (1);
67+
QUERY PLAN
68+
---------------
69+
Insert on abc
70+
-> Result
71+
(2 rows)
72+
73+
insert into views.abc values (1);
74+
ERROR: INSERT
75+
/* Test UPDATE */
76+
explain (costs off) update views.abc set id = 2 where id = 1 or id = 2;
77+
QUERY PLAN
78+
--------------------------------------
79+
Update on abc
80+
-> Result
81+
-> Append
82+
-> Seq Scan on _abc_0
83+
Filter: (id = 1)
84+
-> Seq Scan on _abc_6
85+
Filter: (id = 2)
86+
(7 rows)
87+
88+
update views.abc set id = 2 where id = 1 or id = 2;
89+
ERROR: UPDATE
90+
/* Test DELETE */
91+
explain (costs off) delete from views.abc where id = 1 or id = 2;
92+
QUERY PLAN
93+
--------------------------------------
94+
Delete on abc
95+
-> Result
96+
-> Append
97+
-> Seq Scan on _abc_0
98+
Filter: (id = 1)
99+
-> Seq Scan on _abc_6
100+
Filter: (id = 2)
101+
(7 rows)
102+
103+
delete from views.abc where id = 1 or id = 2;
104+
ERROR: DELETE
105+
DROP SCHEMA views CASCADE;
106+
NOTICE: drop cascades to 13 other objects
107+
DROP EXTENSION pg_pathman;

‎expected/pathman_views_1.out

Lines changed: 143 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,143 @@
1+
/*
2+
* -------------------------------------------
3+
* NOTE: This test behaves differenly on 9.5
4+
* -------------------------------------------
5+
*/
6+
\set VERBOSITY terse
7+
SET search_path = 'public';
8+
CREATE EXTENSION pg_pathman;
9+
CREATE SCHEMA views;
10+
/* create a partitioned table */
11+
create table views._abc(id int4 not null);
12+
select create_hash_partitions('views._abc', 'id', 10);
13+
create_hash_partitions
14+
------------------------
15+
10
16+
(1 row)
17+
18+
insert into views._abc select generate_series(1, 100);
19+
/* create a facade view */
20+
create view views.abc as select * from views._abc;
21+
create or replace function views.disable_modification()
22+
returns trigger as
23+
$$
24+
BEGIN
25+
RAISE EXCEPTION '%', TG_OP;
26+
RETURN NULL;
27+
END;
28+
$$
29+
language 'plpgsql';
30+
create trigger abc_mod_tr
31+
instead of insert or update or delete
32+
on views.abc for each row
33+
execute procedure views.disable_modification();
34+
/* Test SELECT */
35+
explain (costs off) select * from views.abc;
36+
QUERY PLAN
37+
--------------------------
38+
Append
39+
-> Seq Scan on _abc_0
40+
-> Seq Scan on _abc_1
41+
-> Seq Scan on _abc_2
42+
-> Seq Scan on _abc_3
43+
-> Seq Scan on _abc_4
44+
-> Seq Scan on _abc_5
45+
-> Seq Scan on _abc_6
46+
-> Seq Scan on _abc_7
47+
-> Seq Scan on _abc_8
48+
-> Seq Scan on _abc_9
49+
(11 rows)
50+
51+
explain (costs off) select * from views.abc where id = 1;
52+
QUERY PLAN
53+
--------------------------
54+
Append
55+
-> Seq Scan on _abc_0
56+
Filter: (id = 1)
57+
(3 rows)
58+
59+
select count (*) from views.abc;
60+
count
61+
-------
62+
100
63+
(1 row)
64+
65+
/* Test INSERT */
66+
explain (costs off) insert into views.abc values (1);
67+
QUERY PLAN
68+
---------------
69+
Insert on abc
70+
-> Result
71+
(2 rows)
72+
73+
insert into views.abc values (1);
74+
ERROR: INSERT
75+
/* Test UPDATE */
76+
explain (costs off) update views.abc set id = 2 where id = 1 or id = 2;
77+
QUERY PLAN
78+
----------------------------------------------------
79+
Update on abc
80+
-> Result
81+
-> Append
82+
-> Seq Scan on _abc
83+
Filter: ((id = 1) OR (id = 2))
84+
-> Seq Scan on _abc_0
85+
Filter: ((id = 1) OR (id = 2))
86+
-> Seq Scan on _abc_1
87+
Filter: ((id = 1) OR (id = 2))
88+
-> Seq Scan on _abc_2
89+
Filter: ((id = 1) OR (id = 2))
90+
-> Seq Scan on _abc_3
91+
Filter: ((id = 1) OR (id = 2))
92+
-> Seq Scan on _abc_4
93+
Filter: ((id = 1) OR (id = 2))
94+
-> Seq Scan on _abc_5
95+
Filter: ((id = 1) OR (id = 2))
96+
-> Seq Scan on _abc_6
97+
Filter: ((id = 1) OR (id = 2))
98+
-> Seq Scan on _abc_7
99+
Filter: ((id = 1) OR (id = 2))
100+
-> Seq Scan on _abc_8
101+
Filter: ((id = 1) OR (id = 2))
102+
-> Seq Scan on _abc_9
103+
Filter: ((id = 1) OR (id = 2))
104+
(25 rows)
105+
106+
update views.abc set id = 2 where id = 1 or id = 2;
107+
ERROR: UPDATE
108+
/* Test DELETE */
109+
explain (costs off) delete from views.abc where id = 1 or id = 2;
110+
QUERY PLAN
111+
----------------------------------------------------
112+
Delete on abc
113+
-> Result
114+
-> Append
115+
-> Seq Scan on _abc
116+
Filter: ((id = 1) OR (id = 2))
117+
-> Seq Scan on _abc_0
118+
Filter: ((id = 1) OR (id = 2))
119+
-> Seq Scan on _abc_1
120+
Filter: ((id = 1) OR (id = 2))
121+
-> Seq Scan on _abc_2
122+
Filter: ((id = 1) OR (id = 2))
123+
-> Seq Scan on _abc_3
124+
Filter: ((id = 1) OR (id = 2))
125+
-> Seq Scan on _abc_4
126+
Filter: ((id = 1) OR (id = 2))
127+
-> Seq Scan on _abc_5
128+
Filter: ((id = 1) OR (id = 2))
129+
-> Seq Scan on _abc_6
130+
Filter: ((id = 1) OR (id = 2))
131+
-> Seq Scan on _abc_7
132+
Filter: ((id = 1) OR (id = 2))
133+
-> Seq Scan on _abc_8
134+
Filter: ((id = 1) OR (id = 2))
135+
-> Seq Scan on _abc_9
136+
Filter: ((id = 1) OR (id = 2))
137+
(25 rows)
138+
139+
delete from views.abc where id = 1 or id = 2;
140+
ERROR: DELETE
141+
DROP SCHEMA views CASCADE;
142+
NOTICE: drop cascades to 13 other objects
143+
DROP EXTENSION pg_pathman;

‎sql/pathman_views.sql

Lines changed: 63 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,63 @@
1+
/*
2+
* -------------------------------------------
3+
* NOTE: This test behaves differenly on 9.5
4+
* -------------------------------------------
5+
*/
6+
7+
\set VERBOSITY terse
8+
9+
SET search_path='public';
10+
CREATE EXTENSION pg_pathman;
11+
CREATESCHEMAviews;
12+
13+
14+
15+
/* create a partitioned table*/
16+
createtableviews._abc(id int4not null);
17+
select create_hash_partitions('views._abc','id',10);
18+
insert intoviews._abcselect generate_series(1,100);
19+
20+
21+
/* create a facade view*/
22+
createviewviews.abcasselect*fromviews._abc;
23+
24+
create or replacefunctionviews.disable_modification()
25+
returns triggeras
26+
$$
27+
BEGIN
28+
RAISE EXCEPTION'%', TG_OP;
29+
RETURNNULL;
30+
END;
31+
$$
32+
language'plpgsql';
33+
34+
createtriggerabc_mod_tr
35+
instead of insertorupdateordelete
36+
onviews.abc for each row
37+
execute procedureviews.disable_modification();
38+
39+
40+
/* Test SELECT*/
41+
explain (costs off)select*fromviews.abc;
42+
explain (costs off)select*fromviews.abcwhere id=1;
43+
selectcount (*)fromviews.abc;
44+
45+
46+
/* Test INSERT*/
47+
explain (costs off)insert intoviews.abcvalues (1);
48+
insert intoviews.abcvalues (1);
49+
50+
51+
/* Test UPDATE*/
52+
explain (costs off)updateviews.abcset id=2where id=1or id=2;
53+
updateviews.abcset id=2where id=1or id=2;
54+
55+
56+
/* Test DELETE*/
57+
explain (costs off)deletefromviews.abcwhere id=1or id=2;
58+
deletefromviews.abcwhere id=1or id=2;
59+
60+
61+
62+
DROPSCHEMA views CASCADE;
63+
DROP EXTENSION pg_pathman;

0 commit comments

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