| blob | 9bf4764b689f8c2b3d4da03e09d02bb4f8327a53 |
1 --
2 -- CLUSTER
3 --
4 CREATE TABLE clstr_tst_s (rf_a SERIAL PRIMARY KEY,
5 b INT);
6 NOTICE: CREATE TABLE will create implicit sequence "clstr_tst_s_rf_a_seq" for serial column "clstr_tst_s.rf_a"
7 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "clstr_tst_s_pkey" for table "clstr_tst_s"
8 CREATE TABLE clstr_tst (a SERIAL PRIMARY KEY,
9 b INT,
10 c TEXT,
11 d TEXT,
12 CONSTRAINT clstr_tst_con FOREIGN KEY (b) REFERENCES clstr_tst_s);
13 NOTICE: CREATE TABLE will create implicit sequence "clstr_tst_a_seq" for serial column "clstr_tst.a"
14 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "clstr_tst_pkey" for table "clstr_tst"
15 CREATE INDEX clstr_tst_b ON clstr_tst (b);
16 CREATE INDEX clstr_tst_c ON clstr_tst (c);
17 CREATE INDEX clstr_tst_c_b ON clstr_tst (c,b);
18 CREATE INDEX clstr_tst_b_c ON clstr_tst (b,c);
19 INSERT INTO clstr_tst_s (b) VALUES (0);
20 INSERT INTO clstr_tst_s (b) SELECT b FROM clstr_tst_s;
21 INSERT INTO clstr_tst_s (b) SELECT b FROM clstr_tst_s;
22 INSERT INTO clstr_tst_s (b) SELECT b FROM clstr_tst_s;
23 INSERT INTO clstr_tst_s (b) SELECT b FROM clstr_tst_s;
24 INSERT INTO clstr_tst_s (b) SELECT b FROM clstr_tst_s;
25 CREATE TABLE clstr_tst_inh () INHERITS (clstr_tst);
26 INSERT INTO clstr_tst (b, c) VALUES (11, 'once');
27 INSERT INTO clstr_tst (b, c) VALUES (10, 'diez');
28 INSERT INTO clstr_tst (b, c) VALUES (31, 'treinta y uno');
29 INSERT INTO clstr_tst (b, c) VALUES (22, 'veintidos');
30 INSERT INTO clstr_tst (b, c) VALUES (3, 'tres');
31 INSERT INTO clstr_tst (b, c) VALUES (20, 'veinte');
32 INSERT INTO clstr_tst (b, c) VALUES (23, 'veintitres');
33 INSERT INTO clstr_tst (b, c) VALUES (21, 'veintiuno');
34 INSERT INTO clstr_tst (b, c) VALUES (4, 'cuatro');
35 INSERT INTO clstr_tst (b, c) VALUES (14, 'catorce');
36 INSERT INTO clstr_tst (b, c) VALUES (2, 'dos');
37 INSERT INTO clstr_tst (b, c) VALUES (18, 'dieciocho');
38 INSERT INTO clstr_tst (b, c) VALUES (27, 'veintisiete');
39 INSERT INTO clstr_tst (b, c) VALUES (25, 'veinticinco');
40 INSERT INTO clstr_tst (b, c) VALUES (13, 'trece');
41 INSERT INTO clstr_tst (b, c) VALUES (28, 'veintiocho');
42 INSERT INTO clstr_tst (b, c) VALUES (32, 'treinta y dos');
43 INSERT INTO clstr_tst (b, c) VALUES (5, 'cinco');
44 INSERT INTO clstr_tst (b, c) VALUES (29, 'veintinueve');
45 INSERT INTO clstr_tst (b, c) VALUES (1, 'uno');
46 INSERT INTO clstr_tst (b, c) VALUES (24, 'veinticuatro');
47 INSERT INTO clstr_tst (b, c) VALUES (30, 'treinta');
48 INSERT INTO clstr_tst (b, c) VALUES (12, 'doce');
49 INSERT INTO clstr_tst (b, c) VALUES (17, 'diecisiete');
50 INSERT INTO clstr_tst (b, c) VALUES (9, 'nueve');
51 INSERT INTO clstr_tst (b, c) VALUES (19, 'diecinueve');
52 INSERT INTO clstr_tst (b, c) VALUES (26, 'veintiseis');
53 INSERT INTO clstr_tst (b, c) VALUES (15, 'quince');
54 INSERT INTO clstr_tst (b, c) VALUES (7, 'siete');
55 INSERT INTO clstr_tst (b, c) VALUES (16, 'dieciseis');
56 INSERT INTO clstr_tst (b, c) VALUES (8, 'ocho');
57 -- This entry is needed to test that TOASTED values are copied correctly.
58 INSERT INTO clstr_tst (b, c, d) VALUES (6, 'seis', repeat('xyzzy', 100000));
59 CLUSTER clstr_tst_c ON clstr_tst;
60 SELECT a,b,c,substring(d for 30), length(d) from clstr_tst;
61 a | b | c | substring | length
62 ----+----+---------------+--------------------------------+--------
63 10 | 14 | catorce | |
64 18 | 5 | cinco | |
65 9 | 4 | cuatro | |
66 26 | 19 | diecinueve | |
67 12 | 18 | dieciocho | |
68 30 | 16 | dieciseis | |
69 24 | 17 | diecisiete | |
70 2 | 10 | diez | |
71 23 | 12 | doce | |
72 11 | 2 | dos | |
73 25 | 9 | nueve | |
74 31 | 8 | ocho | |
75 1 | 11 | once | |
76 28 | 15 | quince | |
77 32 | 6 | seis | xyzzyxyzzyxyzzyxyzzyxyzzyxyzzy | 500000
78 29 | 7 | siete | |
79 15 | 13 | trece | |
80 22 | 30 | treinta | |
81 17 | 32 | treinta y dos | |
82 3 | 31 | treinta y uno | |
83 5 | 3 | tres | |
84 20 | 1 | uno | |
85 6 | 20 | veinte | |
86 14 | 25 | veinticinco | |
87 21 | 24 | veinticuatro | |
88 4 | 22 | veintidos | |
89 19 | 29 | veintinueve | |
90 16 | 28 | veintiocho | |
91 27 | 26 | veintiseis | |
92 13 | 27 | veintisiete | |
93 7 | 23 | veintitres | |
94 8 | 21 | veintiuno | |
95 (32 rows)
97 SELECT a,b,c,substring(d for 30), length(d) from clstr_tst ORDER BY a;
98 a | b | c | substring | length
99 ----+----+---------------+--------------------------------+--------
100 1 | 11 | once | |
101 2 | 10 | diez | |
102 3 | 31 | treinta y uno | |
103 4 | 22 | veintidos | |
104 5 | 3 | tres | |
105 6 | 20 | veinte | |
106 7 | 23 | veintitres | |
107 8 | 21 | veintiuno | |
108 9 | 4 | cuatro | |
109 10 | 14 | catorce | |
110 11 | 2 | dos | |
111 12 | 18 | dieciocho | |
112 13 | 27 | veintisiete | |
113 14 | 25 | veinticinco | |
114 15 | 13 | trece | |
115 16 | 28 | veintiocho | |
116 17 | 32 | treinta y dos | |
117 18 | 5 | cinco | |
118 19 | 29 | veintinueve | |
119 20 | 1 | uno | |
120 21 | 24 | veinticuatro | |
121 22 | 30 | treinta | |
122 23 | 12 | doce | |
123 24 | 17 | diecisiete | |
124 25 | 9 | nueve | |
125 26 | 19 | diecinueve | |
126 27 | 26 | veintiseis | |
127 28 | 15 | quince | |
128 29 | 7 | siete | |
129 30 | 16 | dieciseis | |
130 31 | 8 | ocho | |
131 32 | 6 | seis | xyzzyxyzzyxyzzyxyzzyxyzzyxyzzy | 500000
132 (32 rows)
134 SELECT a,b,c,substring(d for 30), length(d) from clstr_tst ORDER BY b;
135 a | b | c | substring | length
136 ----+----+---------------+--------------------------------+--------
137 20 | 1 | uno | |
138 11 | 2 | dos | |
139 5 | 3 | tres | |
140 9 | 4 | cuatro | |
141 18 | 5 | cinco | |
142 32 | 6 | seis | xyzzyxyzzyxyzzyxyzzyxyzzyxyzzy | 500000
143 29 | 7 | siete | |
144 31 | 8 | ocho | |
145 25 | 9 | nueve | |
146 2 | 10 | diez | |
147 1 | 11 | once | |
148 23 | 12 | doce | |
149 15 | 13 | trece | |
150 10 | 14 | catorce | |
151 28 | 15 | quince | |
152 30 | 16 | dieciseis | |
153 24 | 17 | diecisiete | |
154 12 | 18 | dieciocho | |
155 26 | 19 | diecinueve | |
156 6 | 20 | veinte | |
157 8 | 21 | veintiuno | |
158 4 | 22 | veintidos | |
159 7 | 23 | veintitres | |
160 21 | 24 | veinticuatro | |
161 14 | 25 | veinticinco | |
162 27 | 26 | veintiseis | |
163 13 | 27 | veintisiete | |
164 16 | 28 | veintiocho | |
165 19 | 29 | veintinueve | |
166 22 | 30 | treinta | |
167 3 | 31 | treinta y uno | |
168 17 | 32 | treinta y dos | |
169 (32 rows)
171 SELECT a,b,c,substring(d for 30), length(d) from clstr_tst ORDER BY c;
172 a | b | c | substring | length
173 ----+----+---------------+--------------------------------+--------
174 10 | 14 | catorce | |
175 18 | 5 | cinco | |
176 9 | 4 | cuatro | |
177 26 | 19 | diecinueve | |
178 12 | 18 | dieciocho | |
179 30 | 16 | dieciseis | |
180 24 | 17 | diecisiete | |
181 2 | 10 | diez | |
182 23 | 12 | doce | |
183 11 | 2 | dos | |
184 25 | 9 | nueve | |
185 31 | 8 | ocho | |
186 1 | 11 | once | |
187 28 | 15 | quince | |
188 32 | 6 | seis | xyzzyxyzzyxyzzyxyzzyxyzzyxyzzy | 500000
189 29 | 7 | siete | |
190 15 | 13 | trece | |
191 22 | 30 | treinta | |
192 17 | 32 | treinta y dos | |
193 3 | 31 | treinta y uno | |
194 5 | 3 | tres | |
195 20 | 1 | uno | |
196 6 | 20 | veinte | |
197 14 | 25 | veinticinco | |
198 21 | 24 | veinticuatro | |
199 4 | 22 | veintidos | |
200 19 | 29 | veintinueve | |
201 16 | 28 | veintiocho | |
202 27 | 26 | veintiseis | |
203 13 | 27 | veintisiete | |
204 7 | 23 | veintitres | |
205 8 | 21 | veintiuno | |
206 (32 rows)
208 -- Verify that inheritance link still works
209 INSERT INTO clstr_tst_inh VALUES (0, 100, 'in child table');
210 SELECT a,b,c,substring(d for 30), length(d) from clstr_tst;
211 a | b | c | substring | length
212 ----+-----+----------------+--------------------------------+--------
213 10 | 14 | catorce | |
214 18 | 5 | cinco | |
215 9 | 4 | cuatro | |
216 26 | 19 | diecinueve | |
217 12 | 18 | dieciocho | |
218 30 | 16 | dieciseis | |
219 24 | 17 | diecisiete | |
220 2 | 10 | diez | |
221 23 | 12 | doce | |
222 11 | 2 | dos | |
223 25 | 9 | nueve | |
224 31 | 8 | ocho | |
225 1 | 11 | once | |
226 28 | 15 | quince | |
227 32 | 6 | seis | xyzzyxyzzyxyzzyxyzzyxyzzyxyzzy | 500000
228 29 | 7 | siete | |
229 15 | 13 | trece | |
230 22 | 30 | treinta | |
231 17 | 32 | treinta y dos | |
232 3 | 31 | treinta y uno | |
233 5 | 3 | tres | |
234 20 | 1 | uno | |
235 6 | 20 | veinte | |
236 14 | 25 | veinticinco | |
237 21 | 24 | veinticuatro | |
238 4 | 22 | veintidos | |
239 19 | 29 | veintinueve | |
240 16 | 28 | veintiocho | |
241 27 | 26 | veintiseis | |
242 13 | 27 | veintisiete | |
243 7 | 23 | veintitres | |
244 8 | 21 | veintiuno | |
245 0 | 100 | in child table | |
246 (33 rows)
248 -- Verify that foreign key link still works
249 INSERT INTO clstr_tst (b, c) VALUES (1111, 'this should fail');
250 ERROR: insert or update on table "clstr_tst" violates foreign key constraint "clstr_tst_con"
251 DETAIL: Key (b)=(1111) is not present in table "clstr_tst_s".
252 SELECT conname FROM pg_constraint WHERE conrelid = 'clstr_tst'::regclass
253 ORDER BY 1;
254 conname
255 ----------------
256 clstr_tst_con
257 clstr_tst_pkey
258 (2 rows)
260 SELECT relname, relkind,
261 EXISTS(SELECT 1 FROM pg_class WHERE oid = c.reltoastrelid) AS hastoast
262 FROM pg_class c WHERE relname LIKE 'clstr_tst%' ORDER BY relname;
263 relname | relkind | hastoast
264 ----------------------+---------+----------
265 clstr_tst | r | t
266 clstr_tst_a_seq | S | f
267 clstr_tst_b | i | f
268 clstr_tst_b_c | i | f
269 clstr_tst_c | i | f
270 clstr_tst_c_b | i | f
271 clstr_tst_inh | r | t
272 clstr_tst_pkey | i | f
273 clstr_tst_s | r | f
274 clstr_tst_s_pkey | i | f
275 clstr_tst_s_rf_a_seq | S | f
276 (11 rows)
278 -- Verify that indisclustered is correctly set
279 SELECT pg_class.relname FROM pg_index, pg_class, pg_class AS pg_class_2
280 WHERE pg_class.oid=indexrelid
281 AND indrelid=pg_class_2.oid
282 AND pg_class_2.relname = 'clstr_tst'
283 AND indisclustered;
284 relname
285 -------------
286 clstr_tst_c
287 (1 row)
289 -- Try changing indisclustered
290 ALTER TABLE clstr_tst CLUSTER ON clstr_tst_b_c;
291 SELECT pg_class.relname FROM pg_index, pg_class, pg_class AS pg_class_2
292 WHERE pg_class.oid=indexrelid
293 AND indrelid=pg_class_2.oid
294 AND pg_class_2.relname = 'clstr_tst'
295 AND indisclustered;
296 relname
297 ---------------
298 clstr_tst_b_c
299 (1 row)
301 -- Try turning off all clustering
302 ALTER TABLE clstr_tst SET WITHOUT CLUSTER;
303 SELECT pg_class.relname FROM pg_index, pg_class, pg_class AS pg_class_2
304 WHERE pg_class.oid=indexrelid
305 AND indrelid=pg_class_2.oid
306 AND pg_class_2.relname = 'clstr_tst'
307 AND indisclustered;
308 relname
309 ---------
310 (0 rows)
312 -- Verify that clustering all tables does in fact cluster the right ones
313 CREATE USER clstr_user;
314 CREATE TABLE clstr_1 (a INT PRIMARY KEY);
315 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "clstr_1_pkey" for table "clstr_1"
316 CREATE TABLE clstr_2 (a INT PRIMARY KEY);
317 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "clstr_2_pkey" for table "clstr_2"
318 CREATE TABLE clstr_3 (a INT PRIMARY KEY);
319 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "clstr_3_pkey" for table "clstr_3"
320 ALTER TABLE clstr_1 OWNER TO clstr_user;
321 ALTER TABLE clstr_3 OWNER TO clstr_user;
322 GRANT SELECT ON clstr_2 TO clstr_user;
323 INSERT INTO clstr_1 VALUES (2);
324 INSERT INTO clstr_1 VALUES (1);
325 INSERT INTO clstr_2 VALUES (2);
326 INSERT INTO clstr_2 VALUES (1);
327 INSERT INTO clstr_3 VALUES (2);
328 INSERT INTO clstr_3 VALUES (1);
329 -- "CLUSTER <tablename>" on a table that hasn't been clustered
330 CLUSTER clstr_2;
331 ERROR: there is no previously clustered index for table "clstr_2"
332 CLUSTER clstr_1_pkey ON clstr_1;
333 CLUSTER clstr_2 USING clstr_2_pkey;
334 SELECT * FROM clstr_1 UNION ALL
335 SELECT * FROM clstr_2 UNION ALL
336 SELECT * FROM clstr_3;
337 a
338 ---
339 1
340 2
341 1
342 2
343 2
344 1
345 (6 rows)
347 -- revert to the original state
348 DELETE FROM clstr_1;
349 DELETE FROM clstr_2;
350 DELETE FROM clstr_3;
351 INSERT INTO clstr_1 VALUES (2);
352 INSERT INTO clstr_1 VALUES (1);
353 INSERT INTO clstr_2 VALUES (2);
354 INSERT INTO clstr_2 VALUES (1);
355 INSERT INTO clstr_3 VALUES (2);
356 INSERT INTO clstr_3 VALUES (1);
357 -- this user can only cluster clstr_1 and clstr_3, but the latter
358 -- has not been clustered
359 SET SESSION AUTHORIZATION clstr_user;
360 CLUSTER;
361 SELECT * FROM clstr_1 UNION ALL
362 SELECT * FROM clstr_2 UNION ALL
363 SELECT * FROM clstr_3;
364 a
365 ---
366 1
367 2
368 2
369 1
370 2
371 1
372 (6 rows)
374 -- cluster a single table using the indisclustered bit previously set
375 DELETE FROM clstr_1;
376 INSERT INTO clstr_1 VALUES (2);
377 INSERT INTO clstr_1 VALUES (1);
378 CLUSTER clstr_1;
379 SELECT * FROM clstr_1;
380 a
381 ---
382 1
383 2
384 (2 rows)
386 -- Test MVCC-safety of cluster. There isn't much we can do to verify the
387 -- results with a single backend...
388 CREATE TABLE clustertest (key int PRIMARY KEY);
389 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "clustertest_pkey" for table "clustertest"
390 INSERT INTO clustertest VALUES (10);
391 INSERT INTO clustertest VALUES (20);
392 INSERT INTO clustertest VALUES (30);
393 INSERT INTO clustertest VALUES (40);
394 INSERT INTO clustertest VALUES (50);
395 -- Use a transaction so that updates are not committed when CLUSTER sees 'em
396 BEGIN;
397 -- Test update where the old row version is found first in the scan
398 UPDATE clustertest SET key = 100 WHERE key = 10;
399 -- Test update where the new row version is found first in the scan
400 UPDATE clustertest SET key = 35 WHERE key = 40;
401 -- Test longer update chain
402 UPDATE clustertest SET key = 60 WHERE key = 50;
403 UPDATE clustertest SET key = 70 WHERE key = 60;
404 UPDATE clustertest SET key = 80 WHERE key = 70;
405 SELECT * FROM clustertest;
406 key
407 -----
408 20
409 30
410 100
411 35
412 80
413 (5 rows)
415 CLUSTER clustertest_pkey ON clustertest;
416 SELECT * FROM clustertest;
417 key
418 -----
419 20
420 30
421 35
422 80
423 100
424 (5 rows)
426 COMMIT;
427 SELECT * FROM clustertest;
428 key
429 -----
430 20
431 30
432 35
433 80
434 100
435 (5 rows)
437 -- clean up
438 \c -
439 DROP TABLE clustertest;
440 DROP TABLE clstr_1;
441 DROP TABLE clstr_2;
442 DROP TABLE clstr_3;
443 DROP USER clstr_user;