Skip to content

Commit 03015f8

Browse files
schmiddyschmiddy
committed
Regression tests for the new indexes-repacking features.
Patch from Beena Emerson.
1 parent 3f7a051 commit 03015f8

File tree

2 files changed

+186
-0
lines changed

2 files changed

+186
-0
lines changed

‎regress/expected/tablespace.out

Lines changed: 115 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -42,6 +42,30 @@ WHERE indrelid = 'testts1'::regclass ORDER BY relname;
4242
CREATE INDEX index_OID ON repack.table_OID USING btree (id) WITH (fillfactor=80) TABLESPACE foo
4343
(3 rows)
4444

45+
SELECT regexp_replace(
46+
repack.repack_indexdef(indexrelid, 'testts1'::regclass, NULL, true),
47+
'_[0-9]+', '_OID', 'g')
48+
FROM pg_index i join pg_class c ON c.oid = indexrelid
49+
WHERE indrelid = 'testts1'::regclass ORDER BY relname;
50+
regexp_replace
51+
--------------------------------------------------------------------------------------
52+
CREATE INDEX CONCURRENTLY index_OID ON testts1 USING btree (id) WHERE (id > 0)
53+
CREATE UNIQUE INDEX CONCURRENTLY index_OID ON testts1 USING btree (id)
54+
CREATE INDEX CONCURRENTLY index_OID ON testts1 USING btree (id) WITH (fillfactor=80)
55+
(3 rows)
56+
57+
SELECT regexp_replace(
58+
repack.repack_indexdef(indexrelid, 'testts1'::regclass, 'foo', true),
59+
'_[0-9]+', '_OID', 'g')
60+
FROM pg_index i join pg_class c ON c.oid = indexrelid
61+
WHERE indrelid = 'testts1'::regclass ORDER BY relname;
62+
regexp_replace
63+
-----------------------------------------------------------------------------------------------------
64+
CREATE INDEX CONCURRENTLY index_OID ON testts1 USING btree (id) TABLESPACE foo WHERE (id > 0)
65+
CREATE UNIQUE INDEX CONCURRENTLY index_OID ON testts1 USING btree (id) TABLESPACE foo
66+
CREATE INDEX CONCURRENTLY index_OID ON testts1 USING btree (id) WITH (fillfactor=80) TABLESPACE foo
67+
(3 rows)
68+
4569
-- can move the tablespace from default
4670
\! pg_repack --dbname=contrib_regression --no-order --table=testts1 --tablespace testts
4771
INFO: repacking table "testts1"
@@ -108,3 +132,94 @@ ERROR: cannot specify --moveidx (-S) without --tablespace (-s)
108132
-- not broken with order
109133
\! pg_repack --dbname=contrib_regression -o id --table=testts1 --tablespace pg_default --moveidx
110134
INFO: repacking table "testts1"
135+
--move all indexes of the table to a tablespace
136+
\! pg_repack --dbname=contrib_regression --table=testts1 --only-indexes --tablespace=testts
137+
INFO: repacking indexes of "testts1"
138+
SELECT relname, spcname
139+
FROM pg_class JOIN pg_tablespace ts ON ts.oid = reltablespace
140+
WHERE relname ~ '^testts1'
141+
ORDER BY relname;
142+
relname | spcname
143+
---------------------+---------
144+
testts1_partial_idx | testts
145+
testts1_pkey | testts
146+
testts1_with_idx | testts
147+
(3 rows)
148+
149+
--all indexes of tablespace remain in same tablespace
150+
\! pg_repack --dbname=contrib_regression --table=testts1 --only-indexes
151+
INFO: repacking indexes of "testts1"
152+
SELECT relname, spcname
153+
FROM pg_class JOIN pg_tablespace ts ON ts.oid = reltablespace
154+
WHERE relname ~ '^testts1'
155+
ORDER BY relname;
156+
relname | spcname
157+
---------------------+---------
158+
testts1_partial_idx | testts
159+
testts1_pkey | testts
160+
testts1_with_idx | testts
161+
(3 rows)
162+
163+
--move all indexes of the table to pg_default
164+
\! pg_repack --dbname=contrib_regression --table=testts1 --only-indexes --tablespace=pg_default
165+
INFO: repacking indexes of "testts1"
166+
SELECT relname, spcname
167+
FROM pg_class JOIN pg_tablespace ts ON ts.oid = reltablespace
168+
WHERE relname ~ '^testts1'
169+
ORDER BY relname;
170+
relname | spcname
171+
---------+---------
172+
(0 rows)
173+
174+
--move one index to a tablespace
175+
\! pg_repack --dbname=contrib_regression --index=testts1_pkey --tablespace=testts
176+
INFO: repacking "testts1_pkey"
177+
SELECT relname, spcname
178+
FROM pg_class JOIN pg_tablespace ts ON ts.oid = reltablespace
179+
WHERE relname ~ '^testts1'
180+
ORDER BY relname;
181+
relname | spcname
182+
--------------+---------
183+
testts1_pkey | testts
184+
(1 row)
185+
186+
--index tablespace stays as is
187+
\! pg_repack --dbname=contrib_regression --index=testts1_pkey
188+
INFO: repacking "testts1_pkey"
189+
SELECT relname, spcname
190+
FROM pg_class JOIN pg_tablespace ts ON ts.oid = reltablespace
191+
WHERE relname ~ '^testts1'
192+
ORDER BY relname;
193+
relname | spcname
194+
--------------+---------
195+
testts1_pkey | testts
196+
(1 row)
197+
198+
--move index to pg_default
199+
\! pg_repack --dbname=contrib_regression --index=testts1_pkey --tablespace=pg_default
200+
INFO: repacking "testts1_pkey"
201+
SELECT relname, spcname
202+
FROM pg_class JOIN pg_tablespace ts ON ts.oid = reltablespace
203+
WHERE relname ~ '^testts1'
204+
ORDER BY relname;
205+
relname | spcname
206+
---------+---------
207+
(0 rows)
208+
209+
--using multiple --index option
210+
\! pg_repack --dbname=contrib_regression --index=testts1_pkey --index=testts1_with_idx --tablespace=testts
211+
INFO: repacking "testts1_pkey"
212+
INFO: repacking "testts1_with_idx"
213+
SELECT relname, spcname
214+
FROM pg_class JOIN pg_tablespace ts ON ts.oid = reltablespace
215+
WHERE relname ~ '^testts1'
216+
ORDER BY relname;
217+
relname | spcname
218+
------------------+---------
219+
testts1_pkey | testts
220+
testts1_with_idx | testts
221+
(2 rows)
222+
223+
--using --indexes-only and --index option together
224+
\! pg_repack --dbname=contrib_regression --table=testts1 --only-indexes --index=testts1_pkey
225+
ERROR: cannot specify --index (-i) and --table (-t)

‎regress/sql/tablespace.sql

Lines changed: 71 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -29,6 +29,18 @@ SELECT regexp_replace(
2929
FROM pg_index i join pg_class c ON c.oid = indexrelid
3030
WHERE indrelid = 'testts1'::regclass ORDER BY relname;
3131

32+
SELECT regexp_replace(
33+
repack.repack_indexdef(indexrelid, 'testts1'::regclass, NULL, true),
34+
'_[0-9]+', '_OID', 'g')
35+
FROM pg_index i join pg_class c ON c.oid = indexrelid
36+
WHERE indrelid = 'testts1'::regclass ORDER BY relname;
37+
38+
SELECT regexp_replace(
39+
repack.repack_indexdef(indexrelid, 'testts1'::regclass, 'foo', true),
40+
'_[0-9]+', '_OID', 'g')
41+
FROM pg_index i join pg_class c ON c.oid = indexrelid
42+
WHERE indrelid = 'testts1'::regclass ORDER BY relname;
43+
3244
-- can move the tablespace from default
3345
\! pg_repack --dbname=contrib_regression --no-order --table=testts1 --tablespace testts
3446

@@ -69,3 +81,62 @@ ORDER BY relname;
6981

7082
-- not broken with order
7183
\! pg_repack --dbname=contrib_regression -o id --table=testts1 --tablespace pg_default --moveidx
84+
85+
--move all indexes of the table to a tablespace
86+
\! pg_repack --dbname=contrib_regression --table=testts1 --only-indexes --tablespace=testts
87+
88+
SELECT relname, spcname
89+
FROM pg_class JOIN pg_tablespace ts ON ts.oid = reltablespace
90+
WHERE relname ~ '^testts1'
91+
ORDER BY relname;
92+
93+
--all indexes of tablespace remain in same tablespace
94+
\! pg_repack --dbname=contrib_regression --table=testts1 --only-indexes
95+
96+
SELECT relname, spcname
97+
FROM pg_class JOIN pg_tablespace ts ON ts.oid = reltablespace
98+
WHERE relname ~ '^testts1'
99+
ORDER BY relname;
100+
101+
--move all indexes of the table to pg_default
102+
\! pg_repack --dbname=contrib_regression --table=testts1 --only-indexes --tablespace=pg_default
103+
104+
SELECT relname, spcname
105+
FROM pg_class JOIN pg_tablespace ts ON ts.oid = reltablespace
106+
WHERE relname ~ '^testts1'
107+
ORDER BY relname;
108+
109+
--move one index to a tablespace
110+
\! pg_repack --dbname=contrib_regression --index=testts1_pkey --tablespace=testts
111+
112+
SELECT relname, spcname
113+
FROM pg_class JOIN pg_tablespace ts ON ts.oid = reltablespace
114+
WHERE relname ~ '^testts1'
115+
ORDER BY relname;
116+
117+
--index tablespace stays as is
118+
\! pg_repack --dbname=contrib_regression --index=testts1_pkey
119+
120+
SELECT relname, spcname
121+
FROM pg_class JOIN pg_tablespace ts ON ts.oid = reltablespace
122+
WHERE relname ~ '^testts1'
123+
ORDER BY relname;
124+
125+
--move index to pg_default
126+
\! pg_repack --dbname=contrib_regression --index=testts1_pkey --tablespace=pg_default
127+
128+
SELECT relname, spcname
129+
FROM pg_class JOIN pg_tablespace ts ON ts.oid = reltablespace
130+
WHERE relname ~ '^testts1'
131+
ORDER BY relname;
132+
133+
--using multiple --index option
134+
\! pg_repack --dbname=contrib_regression --index=testts1_pkey --index=testts1_with_idx --tablespace=testts
135+
136+
SELECT relname, spcname
137+
FROM pg_class JOIN pg_tablespace ts ON ts.oid = reltablespace
138+
WHERE relname ~ '^testts1'
139+
ORDER BY relname;
140+
141+
--using --indexes-only and --index option together
142+
\! pg_repack --dbname=contrib_regression --table=testts1 --only-indexes --index=testts1_pkey

0 commit comments

Comments
 (0)