5
CREATE TABLE clstr_tst_s (rf_a SERIAL PRIMARY KEY,
8
CREATE TABLE clstr_tst (a SERIAL PRIMARY KEY,
12
CONSTRAINT clstr_tst_con FOREIGN KEY (b) REFERENCES clstr_tst_s);
14
CREATE INDEX clstr_tst_b ON clstr_tst (b);
15
CREATE INDEX clstr_tst_c ON clstr_tst (c);
16
CREATE INDEX clstr_tst_c_b ON clstr_tst (c,b);
17
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;
26
CREATE TABLE clstr_tst_inh () INHERITS (clstr_tst);
28
INSERT INTO clstr_tst (b, c) VALUES (11, 'once');
29
INSERT INTO clstr_tst (b, c) VALUES (10, 'diez');
30
INSERT INTO clstr_tst (b, c) VALUES (31, 'treinta y uno');
31
INSERT INTO clstr_tst (b, c) VALUES (22, 'veintidos');
32
INSERT INTO clstr_tst (b, c) VALUES (3, 'tres');
33
INSERT INTO clstr_tst (b, c) VALUES (20, 'veinte');
34
INSERT INTO clstr_tst (b, c) VALUES (23, 'veintitres');
35
INSERT INTO clstr_tst (b, c) VALUES (21, 'veintiuno');
36
INSERT INTO clstr_tst (b, c) VALUES (4, 'cuatro');
37
INSERT INTO clstr_tst (b, c) VALUES (14, 'catorce');
38
INSERT INTO clstr_tst (b, c) VALUES (2, 'dos');
39
INSERT INTO clstr_tst (b, c) VALUES (18, 'dieciocho');
40
INSERT INTO clstr_tst (b, c) VALUES (27, 'veintisiete');
41
INSERT INTO clstr_tst (b, c) VALUES (25, 'veinticinco');
42
INSERT INTO clstr_tst (b, c) VALUES (13, 'trece');
43
INSERT INTO clstr_tst (b, c) VALUES (28, 'veintiocho');
44
INSERT INTO clstr_tst (b, c) VALUES (32, 'treinta y dos');
45
INSERT INTO clstr_tst (b, c) VALUES (5, 'cinco');
46
INSERT INTO clstr_tst (b, c) VALUES (29, 'veintinueve');
47
INSERT INTO clstr_tst (b, c) VALUES (1, 'uno');
48
INSERT INTO clstr_tst (b, c) VALUES (24, 'veinticuatro');
49
INSERT INTO clstr_tst (b, c) VALUES (30, 'treinta');
50
INSERT INTO clstr_tst (b, c) VALUES (12, 'doce');
51
INSERT INTO clstr_tst (b, c) VALUES (17, 'diecisiete');
52
INSERT INTO clstr_tst (b, c) VALUES (9, 'nueve');
53
INSERT INTO clstr_tst (b, c) VALUES (19, 'diecinueve');
54
INSERT INTO clstr_tst (b, c) VALUES (26, 'veintiseis');
55
INSERT INTO clstr_tst (b, c) VALUES (15, 'quince');
56
INSERT INTO clstr_tst (b, c) VALUES (7, 'siete');
57
INSERT INTO clstr_tst (b, c) VALUES (16, 'dieciseis');
58
INSERT INTO clstr_tst (b, c) VALUES (8, 'ocho');
59
-- This entry is needed to test that TOASTED values are copied correctly.
60
INSERT INTO clstr_tst (b, c, d) VALUES (6, 'seis', repeat('xyzzy', 100000));
62
CLUSTER clstr_tst_c ON clstr_tst;
64
SELECT a,b,c,substring(d for 30), length(d) from clstr_tst;
65
SELECT a,b,c,substring(d for 30), length(d) from clstr_tst ORDER BY a;
66
SELECT a,b,c,substring(d for 30), length(d) from clstr_tst ORDER BY b;
67
SELECT a,b,c,substring(d for 30), length(d) from clstr_tst ORDER BY c;
69
-- Verify that inheritance link still works
70
INSERT INTO clstr_tst_inh VALUES (0, 100, 'in child table');
71
SELECT a,b,c,substring(d for 30), length(d) from clstr_tst;
73
-- Verify that foreign key link still works
74
INSERT INTO clstr_tst (b, c) VALUES (1111, 'this should fail');
76
SELECT conname FROM pg_constraint WHERE conrelid = 'clstr_tst'::regclass;
79
SELECT relname, relkind,
80
EXISTS(SELECT 1 FROM pg_class WHERE oid = c.reltoastrelid) AS hastoast
81
FROM pg_class c WHERE relname LIKE 'clstr_tst%' ORDER BY relname;
83
-- Verify that indisclustered is correctly set
84
SELECT pg_class.relname FROM pg_index, pg_class, pg_class AS pg_class_2
85
WHERE pg_class.oid=indexrelid
86
AND indrelid=pg_class_2.oid
87
AND pg_class_2.relname = 'clstr_tst'
90
-- Try changing indisclustered
91
ALTER TABLE clstr_tst CLUSTER ON clstr_tst_b_c;
92
SELECT pg_class.relname FROM pg_index, pg_class, pg_class AS pg_class_2
93
WHERE pg_class.oid=indexrelid
94
AND indrelid=pg_class_2.oid
95
AND pg_class_2.relname = 'clstr_tst'
98
-- Try turning off all clustering
99
ALTER TABLE clstr_tst SET WITHOUT CLUSTER;
100
SELECT pg_class.relname FROM pg_index, pg_class, pg_class AS pg_class_2
101
WHERE pg_class.oid=indexrelid
102
AND indrelid=pg_class_2.oid
103
AND pg_class_2.relname = 'clstr_tst'
106
-- Verify that clustering all tables does in fact cluster the right ones
107
CREATE USER clstr_user;
108
CREATE TABLE clstr_1 (a INT PRIMARY KEY);
109
CREATE TABLE clstr_2 (a INT PRIMARY KEY);
110
CREATE TABLE clstr_3 (a INT PRIMARY KEY);
111
ALTER TABLE clstr_1 OWNER TO clstr_user;
112
ALTER TABLE clstr_3 OWNER TO clstr_user;
113
GRANT SELECT ON clstr_2 TO clstr_user;
114
INSERT INTO clstr_1 VALUES (2);
115
INSERT INTO clstr_1 VALUES (1);
116
INSERT INTO clstr_2 VALUES (2);
117
INSERT INTO clstr_2 VALUES (1);
118
INSERT INTO clstr_3 VALUES (2);
119
INSERT INTO clstr_3 VALUES (1);
121
-- "CLUSTER <tablename>" on a table that hasn't been clustered
124
CLUSTER clstr_1_pkey ON clstr_1;
125
CLUSTER clstr_2_pkey ON clstr_2;
126
SELECT * FROM clstr_1 UNION ALL
127
SELECT * FROM clstr_2 UNION ALL
128
SELECT * FROM clstr_3;
130
-- revert to the original state
134
INSERT INTO clstr_1 VALUES (2);
135
INSERT INTO clstr_1 VALUES (1);
136
INSERT INTO clstr_2 VALUES (2);
137
INSERT INTO clstr_2 VALUES (1);
138
INSERT INTO clstr_3 VALUES (2);
139
INSERT INTO clstr_3 VALUES (1);
141
-- this user can only cluster clstr_1 and clstr_3, but the latter
142
-- has not been clustered
143
SET SESSION AUTHORIZATION clstr_user;
145
SELECT * FROM clstr_1 UNION ALL
146
SELECT * FROM clstr_2 UNION ALL
147
SELECT * FROM clstr_3;
149
-- cluster a single table using the indisclustered bit previously set
151
INSERT INTO clstr_1 VALUES (2);
152
INSERT INTO clstr_1 VALUES (1);
154
SELECT * FROM clstr_1;
160
DROP USER clstr_user;