4
CREATE TABLE clstr_tst_s (rf_a SERIAL PRIMARY KEY,
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,
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
----+----+---------------+--------------------------------+--------
66
26 | 19 | diecinueve | |
67
12 | 18 | dieciocho | |
68
30 | 16 | dieciseis | |
69
24 | 17 | diecisiete | |
77
32 | 6 | seis | xyzzyxyzzyxyzzyxyzzyxyzzyxyzzy | 500000
81
17 | 32 | treinta y dos | |
82
3 | 31 | treinta y uno | |
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 | |
97
SELECT a,b,c,substring(d for 30), length(d) from clstr_tst ORDER BY a;
98
a | b | c | substring | length
99
----+----+---------------+--------------------------------+--------
102
3 | 31 | treinta y uno | |
103
4 | 22 | veintidos | |
106
7 | 23 | veintitres | |
107
8 | 21 | veintiuno | |
109
10 | 14 | catorce | |
111
12 | 18 | dieciocho | |
112
13 | 27 | veintisiete | |
113
14 | 25 | veinticinco | |
115
16 | 28 | veintiocho | |
116
17 | 32 | treinta y dos | |
118
19 | 29 | veintinueve | |
120
21 | 24 | veinticuatro | |
121
22 | 30 | treinta | |
123
24 | 17 | diecisiete | |
125
26 | 19 | diecinueve | |
126
27 | 26 | veintiseis | |
129
30 | 16 | dieciseis | |
131
32 | 6 | seis | xyzzyxyzzyxyzzyxyzzyxyzzyxyzzy | 500000
134
SELECT a,b,c,substring(d for 30), length(d) from clstr_tst ORDER BY b;
135
a | b | c | substring | length
136
----+----+---------------+--------------------------------+--------
142
32 | 6 | seis | xyzzyxyzzyxyzzyxyzzyxyzzyxyzzy | 500000
150
10 | 14 | catorce | |
152
30 | 16 | dieciseis | |
153
24 | 17 | diecisiete | |
154
12 | 18 | dieciocho | |
155
26 | 19 | diecinueve | |
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 | |
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 | |
177
26 | 19 | diecinueve | |
178
12 | 18 | dieciocho | |
179
30 | 16 | dieciseis | |
180
24 | 17 | diecisiete | |
188
32 | 6 | seis | xyzzyxyzzyxyzzyxyzzyxyzzyxyzzy | 500000
191
22 | 30 | treinta | |
192
17 | 32 | treinta y dos | |
193
3 | 31 | treinta y uno | |
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 | |
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 | |
216
26 | 19 | diecinueve | |
217
12 | 18 | dieciocho | |
218
30 | 16 | dieciseis | |
219
24 | 17 | diecisiete | |
227
32 | 6 | seis | xyzzyxyzzyxyzzyxyzzyxyzzyxyzzy | 500000
230
22 | 30 | treinta | |
231
17 | 32 | treinta y dos | |
232
3 | 31 | treinta y uno | |
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 | |
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;
259
SELECT relname, relkind,
260
EXISTS(SELECT 1 FROM pg_class WHERE oid = c.reltoastrelid) AS hastoast
261
FROM pg_class c WHERE relname LIKE 'clstr_tst%' ORDER BY relname;
262
relname | relkind | hastoast
263
----------------------+---------+----------
265
clstr_tst_a_seq | S | f
267
clstr_tst_b_c | i | f
269
clstr_tst_c_b | i | f
270
clstr_tst_inh | r | t
271
clstr_tst_pkey | i | f
273
clstr_tst_s_pkey | i | f
274
clstr_tst_s_rf_a_seq | S | f
277
-- Verify that indisclustered is correctly set
278
SELECT pg_class.relname FROM pg_index, pg_class, pg_class AS pg_class_2
279
WHERE pg_class.oid=indexrelid
280
AND indrelid=pg_class_2.oid
281
AND pg_class_2.relname = 'clstr_tst'
288
-- Try changing indisclustered
289
ALTER TABLE clstr_tst CLUSTER ON clstr_tst_b_c;
290
SELECT pg_class.relname FROM pg_index, pg_class, pg_class AS pg_class_2
291
WHERE pg_class.oid=indexrelid
292
AND indrelid=pg_class_2.oid
293
AND pg_class_2.relname = 'clstr_tst'
300
-- Try turning off all clustering
301
ALTER TABLE clstr_tst SET WITHOUT CLUSTER;
302
SELECT pg_class.relname FROM pg_index, pg_class, pg_class AS pg_class_2
303
WHERE pg_class.oid=indexrelid
304
AND indrelid=pg_class_2.oid
305
AND pg_class_2.relname = 'clstr_tst'
311
-- Verify that clustering all tables does in fact cluster the right ones
312
CREATE USER clstr_user;
313
CREATE TABLE clstr_1 (a INT PRIMARY KEY);
314
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "clstr_1_pkey" for table "clstr_1"
315
CREATE TABLE clstr_2 (a INT PRIMARY KEY);
316
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "clstr_2_pkey" for table "clstr_2"
317
CREATE TABLE clstr_3 (a INT PRIMARY KEY);
318
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "clstr_3_pkey" for table "clstr_3"
319
ALTER TABLE clstr_1 OWNER TO clstr_user;
320
ALTER TABLE clstr_3 OWNER TO clstr_user;
321
GRANT SELECT ON clstr_2 TO clstr_user;
322
INSERT INTO clstr_1 VALUES (2);
323
INSERT INTO clstr_1 VALUES (1);
324
INSERT INTO clstr_2 VALUES (2);
325
INSERT INTO clstr_2 VALUES (1);
326
INSERT INTO clstr_3 VALUES (2);
327
INSERT INTO clstr_3 VALUES (1);
328
-- "CLUSTER <tablename>" on a table that hasn't been clustered
330
ERROR: there is no previously clustered index for table "clstr_2"
331
CLUSTER clstr_1_pkey ON clstr_1;
332
CLUSTER clstr_2_pkey ON clstr_2;
333
SELECT * FROM clstr_1 UNION ALL
334
SELECT * FROM clstr_2 UNION ALL
335
SELECT * FROM clstr_3;
346
-- revert to the original state
350
INSERT INTO clstr_1 VALUES (2);
351
INSERT INTO clstr_1 VALUES (1);
352
INSERT INTO clstr_2 VALUES (2);
353
INSERT INTO clstr_2 VALUES (1);
354
INSERT INTO clstr_3 VALUES (2);
355
INSERT INTO clstr_3 VALUES (1);
356
-- this user can only cluster clstr_1 and clstr_3, but the latter
357
-- has not been clustered
358
SET SESSION AUTHORIZATION clstr_user;
360
SELECT * FROM clstr_1 UNION ALL
361
SELECT * FROM clstr_2 UNION ALL
362
SELECT * FROM clstr_3;
373
-- cluster a single table using the indisclustered bit previously set
375
INSERT INTO clstr_1 VALUES (2);
376
INSERT INTO clstr_1 VALUES (1);
378
SELECT * FROM clstr_1;
389
DROP USER clstr_user;