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

Commit1817d26

Browse files
committed
Add more tests on dropped columns (issue#174)
1 parent431b316 commit1817d26

File tree

2 files changed

+187
-0
lines changed

2 files changed

+187
-0
lines changed

‎expected/pathman_dropped_cols.out‎

Lines changed: 126 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -80,5 +80,131 @@ select pg_get_constraintdef(oid, true) from pg_constraint where conname = 'pathm
8080

8181
drop table test_hash cascade;
8282
NOTICE: drop cascades to 3 other objects
83+
-- Yury Smirnov case
84+
CREATE TABLE root_dict (
85+
id BIGSERIAL PRIMARY KEY NOT NULL,
86+
root_id BIGINT NOT NULL,
87+
start_date DATE,
88+
num TEXT,
89+
main TEXT,
90+
dict_code TEXT,
91+
dict_name TEXT,
92+
edit_num TEXT,
93+
edit_date DATE,
94+
sign CHAR(4)
95+
);
96+
CREATE INDEX "root_dict_root_id_idx" ON "root_dict" ("root_id");
97+
DO
98+
$$
99+
DECLARE
100+
r RECORD;
101+
BEGIN
102+
FOR r IN SELECT * FROM generate_series(1, 3) r
103+
LOOP
104+
FOR d IN 1..2 LOOP
105+
INSERT INTO root_dict (root_id, start_date, num, main, dict_code, dict_name, edit_num, edit_date, sign) VALUES
106+
(r.r, now(), 'num_' || d, (d % 2) + 1, 'code_' || d, 'name_' || d, NULL, NULL, '2014');
107+
END LOOP;
108+
END LOOP;
109+
END
110+
$$;
111+
ALTER TABLE root_dict ADD COLUMN dict_id BIGINT DEFAULT 3;
112+
ALTER TABLE root_dict DROP COLUMN dict_code,
113+
DROP COLUMN dict_name,
114+
DROP COLUMN sign;
115+
CREATE EXTENSION pg_pathman;
116+
ERROR: extension "pg_pathman" already exists
117+
SELECT create_hash_partitions('root_dict' :: REGCLASS,
118+
'root_id',
119+
3,
120+
true);
121+
create_hash_partitions
122+
------------------------
123+
3
124+
(1 row)
125+
126+
VACUUM FULL ANALYZE "root_dict";
127+
SELECT set_enable_parent('root_dict' :: REGCLASS, FALSE);
128+
set_enable_parent
129+
-------------------
130+
131+
(1 row)
132+
133+
PREPARE getbyroot AS
134+
SELECT
135+
id, root_id, start_date, num, main, edit_num, edit_date, dict_id
136+
FROM root_dict
137+
WHERE root_id = $1;
138+
EXECUTE getbyroot(2);
139+
id | root_id | start_date | num | main | edit_num | edit_date | dict_id
140+
----+---------+------------+-------+------+----------+-----------+---------
141+
3 | 2 | 09-12-2018 | num_1 | 2 | | | 3
142+
4 | 2 | 09-12-2018 | num_2 | 1 | | | 3
143+
(2 rows)
144+
145+
EXECUTE getbyroot(2);
146+
id | root_id | start_date | num | main | edit_num | edit_date | dict_id
147+
----+---------+------------+-------+------+----------+-----------+---------
148+
3 | 2 | 09-12-2018 | num_1 | 2 | | | 3
149+
4 | 2 | 09-12-2018 | num_2 | 1 | | | 3
150+
(2 rows)
151+
152+
EXECUTE getbyroot(2);
153+
id | root_id | start_date | num | main | edit_num | edit_date | dict_id
154+
----+---------+------------+-------+------+----------+-----------+---------
155+
3 | 2 | 09-12-2018 | num_1 | 2 | | | 3
156+
4 | 2 | 09-12-2018 | num_2 | 1 | | | 3
157+
(2 rows)
158+
159+
EXECUTE getbyroot(2);
160+
id | root_id | start_date | num | main | edit_num | edit_date | dict_id
161+
----+---------+------------+-------+------+----------+-----------+---------
162+
3 | 2 | 09-12-2018 | num_1 | 2 | | | 3
163+
4 | 2 | 09-12-2018 | num_2 | 1 | | | 3
164+
(2 rows)
165+
166+
EXECUTE getbyroot(2);
167+
id | root_id | start_date | num | main | edit_num | edit_date | dict_id
168+
----+---------+------------+-------+------+----------+-----------+---------
169+
3 | 2 | 09-12-2018 | num_1 | 2 | | | 3
170+
4 | 2 | 09-12-2018 | num_2 | 1 | | | 3
171+
(2 rows)
172+
173+
-- errors usually start here
174+
EXECUTE getbyroot(2);
175+
id | root_id | start_date | num | main | edit_num | edit_date | dict_id
176+
----+---------+------------+-------+------+----------+-----------+---------
177+
3 | 2 | 09-12-2018 | num_1 | 2 | | | 3
178+
4 | 2 | 09-12-2018 | num_2 | 1 | | | 3
179+
(2 rows)
180+
181+
EXECUTE getbyroot(2);
182+
id | root_id | start_date | num | main | edit_num | edit_date | dict_id
183+
----+---------+------------+-------+------+----------+-----------+---------
184+
3 | 2 | 09-12-2018 | num_1 | 2 | | | 3
185+
4 | 2 | 09-12-2018 | num_2 | 1 | | | 3
186+
(2 rows)
187+
188+
EXPLAIN EXECUTE getbyroot(2);
189+
QUERY PLAN
190+
--------------------------------------------------------------------------------------------
191+
Custom Scan (RuntimeAppend) (cost=4.17..11.28 rows=3 width=128)
192+
Prune by: (root_dict.root_id = $1)
193+
-> Bitmap Heap Scan on root_dict_0 root_dict (cost=4.17..11.28 rows=3 width=128)
194+
Recheck Cond: (root_id = $1)
195+
-> Bitmap Index Scan on root_dict_0_root_id_idx (cost=0.00..4.17 rows=3 width=0)
196+
Index Cond: (root_id = $1)
197+
-> Bitmap Heap Scan on root_dict_1 root_dict (cost=4.17..11.28 rows=3 width=128)
198+
Recheck Cond: (root_id = $1)
199+
-> Bitmap Index Scan on root_dict_1_root_id_idx (cost=0.00..4.17 rows=3 width=0)
200+
Index Cond: (root_id = $1)
201+
-> Bitmap Heap Scan on root_dict_2 root_dict (cost=4.17..11.28 rows=3 width=128)
202+
Recheck Cond: (root_id = $1)
203+
-> Bitmap Index Scan on root_dict_2_root_id_idx (cost=0.00..4.17 rows=3 width=0)
204+
Index Cond: (root_id = $1)
205+
(14 rows)
206+
207+
DROP TABLE root_dict CASCADE;
208+
NOTICE: drop cascades to 3 other objects
83209
DROP SCHEMA dropped_cols CASCADE;
84210
DROP EXTENSION pg_pathman;

‎sql/pathman_dropped_cols.sql‎

Lines changed: 61 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -38,6 +38,67 @@ select pg_get_constraintdef(oid, true) from pg_constraint where conname = 'pathm
3838
select pg_get_constraintdef(oid, true)from pg_constraintwhere conname='pathman_test_dummy_check';
3939
droptable test_hash cascade;
4040

41+
-- Yury Smirnov case
42+
CREATETABLEroot_dict (
43+
idBIGSERIALPRIMARY KEYNOT NULL,
44+
root_idBIGINTNOT NULL,
45+
start_dateDATE,
46+
numTEXT,
47+
mainTEXT,
48+
dict_codeTEXT,
49+
dict_nameTEXT,
50+
edit_numTEXT,
51+
edit_dateDATE,
52+
signCHAR(4)
53+
);
4154

55+
CREATEINDEX "root_dict_root_id_idx"ON"root_dict" ("root_id");
56+
57+
DO
58+
$$
59+
DECLARE
60+
r RECORD;
61+
BEGIN
62+
FOR rINSELECT*FROM generate_series(1,3) r
63+
LOOP
64+
FOR dIN1..2 LOOP
65+
INSERT INTO root_dict (root_id, start_date, num, main, dict_code, dict_name, edit_num, edit_date, sign)VALUES
66+
(r.r, now(),'num_'|| d, (d %2)+1,'code_'|| d,'name_'|| d,NULL,NULL,'2014');
67+
END LOOP;
68+
END LOOP;
69+
END
70+
$$;
71+
72+
ALTERTABLE root_dict ADD COLUMN dict_idBIGINT DEFAULT3;
73+
ALTERTABLE root_dict DROP COLUMN dict_code,
74+
DROP COLUMN dict_name,
75+
DROP COLUMN sign;
76+
77+
CREATE EXTENSION pg_pathman;
78+
SELECT create_hash_partitions('root_dict' :: REGCLASS,
79+
'root_id',
80+
3,
81+
true);
82+
VACUUM FULL ANALYZE"root_dict";
83+
SELECT set_enable_parent('root_dict' :: REGCLASS, FALSE);
84+
85+
PREPARE getbyrootAS
86+
SELECT
87+
id, root_id, start_date, num, main, edit_num, edit_date, dict_id
88+
FROM root_dict
89+
WHERE root_id= $1;
90+
91+
EXECUTE getbyroot(2);
92+
EXECUTE getbyroot(2);
93+
EXECUTE getbyroot(2);
94+
EXECUTE getbyroot(2);
95+
EXECUTE getbyroot(2);
96+
97+
-- errors usually start here
98+
EXECUTE getbyroot(2);
99+
EXECUTE getbyroot(2);
100+
EXPLAIN EXECUTE getbyroot(2);
101+
102+
DROPTABLE root_dict CASCADE;
42103
DROPSCHEMA dropped_cols CASCADE;
43104
DROP EXTENSION pg_pathman;

0 commit comments

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