1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
|
SET client_min_messages = warning;
--
-- Tablespace features tests
--
-- Note: in order to pass this test you must create a tablespace called 'testts'
--
SELECT spcname FROM pg_tablespace WHERE spcname = 'testts';
-- If the query above failed you must create the 'testts' tablespace;
CREATE TABLE testts1 (id serial primary key, data text);
CREATE INDEX testts1_partial_idx on testts1 (id) where (id > 0);
CREATE INDEX testts1_with_idx on testts1 (id) with (fillfactor=80);
INSERT INTO testts1 (data) values ('a');
INSERT INTO testts1 (data) values ('b');
INSERT INTO testts1 (data) values ('c');
-- check the indexes definitions
SELECT regexp_replace(
repack.repack_indexdef(indexrelid, 'testts1'::regclass, NULL, false),
'_[0-9]+', '_OID', 'g')
FROM pg_index i join pg_class c ON c.oid = indexrelid
WHERE indrelid = 'testts1'::regclass ORDER BY relname;
SELECT regexp_replace(
repack.repack_indexdef(indexrelid, 'testts1'::regclass, 'foo', false),
'_[0-9]+', '_OID', 'g')
FROM pg_index i join pg_class c ON c.oid = indexrelid
WHERE indrelid = 'testts1'::regclass ORDER BY relname;
SELECT regexp_replace(
repack.repack_indexdef(indexrelid, 'testts1'::regclass, NULL, true),
'_[0-9]+', '_OID', 'g')
FROM pg_index i join pg_class c ON c.oid = indexrelid
WHERE indrelid = 'testts1'::regclass ORDER BY relname;
SELECT regexp_replace(
repack.repack_indexdef(indexrelid, 'testts1'::regclass, 'foo', true),
'_[0-9]+', '_OID', 'g')
FROM pg_index i join pg_class c ON c.oid = indexrelid
WHERE indrelid = 'testts1'::regclass ORDER BY relname;
-- Test that a tablespace is quoted as an identifier
SELECT regexp_replace(
repack.repack_indexdef(indexrelid, 'testts1'::regclass, 'foo bar', false),
'_[0-9]+', '_OID', 'g')
FROM pg_index i join pg_class c ON c.oid = indexrelid
WHERE indrelid = 'testts1'::regclass ORDER BY relname;
-- can move the tablespace from default
\! pg_repack --dbname=contrib_regression --no-order --table=testts1 --tablespace testts
SELECT relname, spcname
FROM pg_class JOIN pg_tablespace ts ON ts.oid = reltablespace
WHERE relname ~ '^testts1'
ORDER BY relname;
SELECT * from testts1 order by id;
-- tablespace stays where it is
\! pg_repack --dbname=contrib_regression --no-order --table=testts1
SELECT relname, spcname
FROM pg_class JOIN pg_tablespace ts ON ts.oid = reltablespace
WHERE relname ~ '^testts1'
ORDER BY relname;
-- can move the ts back to default
\! pg_repack --dbname=contrib_regression --no-order --table=testts1 -s pg_default
SELECT relname, spcname
FROM pg_class JOIN pg_tablespace ts ON ts.oid = reltablespace
WHERE relname ~ '^testts1'
ORDER BY relname;
-- can move the table together with the indexes
\! pg_repack --dbname=contrib_regression --no-order --table=testts1 --tablespace testts --moveidx
SELECT relname, spcname
FROM pg_class JOIN pg_tablespace ts ON ts.oid = reltablespace
WHERE relname ~ '^testts1'
ORDER BY relname;
-- can't specify --moveidx without --tablespace
\! pg_repack --dbname=contrib_regression --no-order --table=testts1 --moveidx
\! pg_repack --dbname=contrib_regression --no-order --table=testts1 -S
-- not broken with order
\! pg_repack --dbname=contrib_regression -o id --table=testts1 --tablespace pg_default --moveidx
--move all indexes of the table to a tablespace
\! pg_repack --dbname=contrib_regression --table=testts1 --only-indexes --tablespace=testts
SELECT relname, spcname
FROM pg_class JOIN pg_tablespace ts ON ts.oid = reltablespace
WHERE relname ~ '^testts1'
ORDER BY relname;
--all indexes of tablespace remain in same tablespace
\! pg_repack --dbname=contrib_regression --table=testts1 --only-indexes
SELECT relname, spcname
FROM pg_class JOIN pg_tablespace ts ON ts.oid = reltablespace
WHERE relname ~ '^testts1'
ORDER BY relname;
--move all indexes of the table to pg_default
\! pg_repack --dbname=contrib_regression --table=testts1 --only-indexes --tablespace=pg_default
SELECT relname, spcname
FROM pg_class JOIN pg_tablespace ts ON ts.oid = reltablespace
WHERE relname ~ '^testts1'
ORDER BY relname;
--move one index to a tablespace
\! pg_repack --dbname=contrib_regression --index=testts1_pkey --tablespace=testts
SELECT relname, spcname
FROM pg_class JOIN pg_tablespace ts ON ts.oid = reltablespace
WHERE relname ~ '^testts1'
ORDER BY relname;
--index tablespace stays as is
\! pg_repack --dbname=contrib_regression --index=testts1_pkey
SELECT relname, spcname
FROM pg_class JOIN pg_tablespace ts ON ts.oid = reltablespace
WHERE relname ~ '^testts1'
ORDER BY relname;
--move index to pg_default
\! pg_repack --dbname=contrib_regression --index=testts1_pkey --tablespace=pg_default
SELECT relname, spcname
FROM pg_class JOIN pg_tablespace ts ON ts.oid = reltablespace
WHERE relname ~ '^testts1'
ORDER BY relname;
--using multiple --index option
\! pg_repack --dbname=contrib_regression --index=testts1_pkey --index=testts1_with_idx --tablespace=testts
SELECT relname, spcname
FROM pg_class JOIN pg_tablespace ts ON ts.oid = reltablespace
WHERE relname ~ '^testts1'
ORDER BY relname;
--using --indexes-only and --index option together
\! pg_repack --dbname=contrib_regression --table=testts1 --only-indexes --index=testts1_pkey
--check quote_ident() with 1testts tablespace
\! pg_repack --dbname=contrib_regression --table=testts1 --tablespace=1testts --moveidx
SELECT relname, spcname
FROM pg_class JOIN pg_tablespace ts ON ts.oid = reltablespace
WHERE relname ~ '^testts1'
ORDER BY relname;
--check quote_ident() with "test ts" tablespace
\! pg_repack --dbname=contrib_regression --table=testts1 --tablespace="test ts" --moveidx
SELECT relname, spcname
FROM pg_class JOIN pg_tablespace ts ON ts.oid = reltablespace
WHERE relname ~ '^testts1'
ORDER BY relname;
--check quote_ident() with "test""ts" tablespace
\! pg_repack --dbname=contrib_regression --table=testts1 --tablespace="test\"ts" --moveidx
SELECT relname, spcname
FROM pg_class JOIN pg_tablespace ts ON ts.oid = reltablespace
WHERE relname ~ '^testts1'
ORDER BY relname;
|
