1-- 2-- CLUSTER 3-- 4 5CREATE TABLE clstr_tst_s (rf_a SERIAL PRIMARY KEY, 6 b INT); 7 8CREATE 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 14CREATE INDEX clstr_tst_b ON clstr_tst (b); 15CREATE INDEX clstr_tst_c ON clstr_tst (c); 16CREATE INDEX clstr_tst_c_b ON clstr_tst (c,b); 17CREATE INDEX clstr_tst_b_c ON clstr_tst (b,c); 18 19INSERT INTO clstr_tst_s (b) VALUES (0); 20INSERT INTO clstr_tst_s (b) SELECT b FROM clstr_tst_s; 21INSERT INTO clstr_tst_s (b) SELECT b FROM clstr_tst_s; 22INSERT INTO clstr_tst_s (b) SELECT b FROM clstr_tst_s; 23INSERT INTO clstr_tst_s (b) SELECT b FROM clstr_tst_s; 24INSERT INTO clstr_tst_s (b) SELECT b FROM clstr_tst_s; 25 26CREATE TABLE clstr_tst_inh () INHERITS (clstr_tst); 27 28INSERT INTO clstr_tst (b, c) VALUES (11, 'once'); 29INSERT INTO clstr_tst (b, c) VALUES (10, 'diez'); 30INSERT INTO clstr_tst (b, c) VALUES (31, 'treinta y uno'); 31INSERT INTO clstr_tst (b, c) VALUES (22, 'veintidos'); 32INSERT INTO clstr_tst (b, c) VALUES (3, 'tres'); 33INSERT INTO clstr_tst (b, c) VALUES (20, 'veinte'); 34INSERT INTO clstr_tst (b, c) VALUES (23, 'veintitres'); 35INSERT INTO clstr_tst (b, c) VALUES (21, 'veintiuno'); 36INSERT INTO clstr_tst (b, c) VALUES (4, 'cuatro'); 37INSERT INTO clstr_tst (b, c) VALUES (14, 'catorce'); 38INSERT INTO clstr_tst (b, c) VALUES (2, 'dos'); 39INSERT INTO clstr_tst (b, c) VALUES (18, 'dieciocho'); 40INSERT INTO clstr_tst (b, c) VALUES (27, 'veintisiete'); 41INSERT INTO clstr_tst (b, c) VALUES (25, 'veinticinco'); 42INSERT INTO clstr_tst (b, c) VALUES (13, 'trece'); 43INSERT INTO clstr_tst (b, c) VALUES (28, 'veintiocho'); 44INSERT INTO clstr_tst (b, c) VALUES (32, 'treinta y dos'); 45INSERT INTO clstr_tst (b, c) VALUES (5, 'cinco'); 46INSERT INTO clstr_tst (b, c) VALUES (29, 'veintinueve'); 47INSERT INTO clstr_tst (b, c) VALUES (1, 'uno'); 48INSERT INTO clstr_tst (b, c) VALUES (24, 'veinticuatro'); 49INSERT INTO clstr_tst (b, c) VALUES (30, 'treinta'); 50INSERT INTO clstr_tst (b, c) VALUES (12, 'doce'); 51INSERT INTO clstr_tst (b, c) VALUES (17, 'diecisiete'); 52INSERT INTO clstr_tst (b, c) VALUES (9, 'nueve'); 53INSERT INTO clstr_tst (b, c) VALUES (19, 'diecinueve'); 54INSERT INTO clstr_tst (b, c) VALUES (26, 'veintiseis'); 55INSERT INTO clstr_tst (b, c) VALUES (15, 'quince'); 56INSERT INTO clstr_tst (b, c) VALUES (7, 'siete'); 57INSERT INTO clstr_tst (b, c) VALUES (16, 'dieciseis'); 58INSERT INTO clstr_tst (b, c) VALUES (8, 'ocho'); 59-- This entry is needed to test that TOASTED values are copied correctly. 60INSERT INTO clstr_tst (b, c, d) VALUES (6, 'seis', repeat('xyzzy', 100000)); 61 62CLUSTER clstr_tst_c ON clstr_tst; 63 64SELECT a,b,c,substring(d for 30), length(d) from clstr_tst; 65SELECT a,b,c,substring(d for 30), length(d) from clstr_tst ORDER BY a; 66SELECT a,b,c,substring(d for 30), length(d) from clstr_tst ORDER BY b; 67SELECT a,b,c,substring(d for 30), length(d) from clstr_tst ORDER BY c; 68 69-- Verify that inheritance link still works 70INSERT INTO clstr_tst_inh VALUES (0, 100, 'in child table'); 71SELECT a,b,c,substring(d for 30), length(d) from clstr_tst; 72 73-- Verify that foreign key link still works 74INSERT INTO clstr_tst (b, c) VALUES (1111, 'this should fail'); 75 76SELECT conname FROM pg_constraint WHERE conrelid = 'clstr_tst'::regclass 77ORDER BY 1; 78 79 80SELECT relname, relkind, 81 EXISTS(SELECT 1 FROM pg_class WHERE oid = c.reltoastrelid) AS hastoast 82FROM pg_class c WHERE relname LIKE 'clstr_tst%' ORDER BY relname; 83 84-- Verify that indisclustered is correctly set 85SELECT pg_class.relname FROM pg_index, pg_class, pg_class AS pg_class_2 86WHERE pg_class.oid=indexrelid 87 AND indrelid=pg_class_2.oid 88 AND pg_class_2.relname = 'clstr_tst' 89 AND indisclustered; 90 91-- Try changing indisclustered 92ALTER TABLE clstr_tst CLUSTER ON clstr_tst_b_c; 93SELECT pg_class.relname FROM pg_index, pg_class, pg_class AS pg_class_2 94WHERE pg_class.oid=indexrelid 95 AND indrelid=pg_class_2.oid 96 AND pg_class_2.relname = 'clstr_tst' 97 AND indisclustered; 98 99-- Try turning off all clustering 100ALTER TABLE clstr_tst SET WITHOUT CLUSTER; 101SELECT pg_class.relname FROM pg_index, pg_class, pg_class AS pg_class_2 102WHERE pg_class.oid=indexrelid 103 AND indrelid=pg_class_2.oid 104 AND pg_class_2.relname = 'clstr_tst' 105 AND indisclustered; 106 107-- Verify that clustering all tables does in fact cluster the right ones 108CREATE USER regress_clstr_user; 109CREATE TABLE clstr_1 (a INT PRIMARY KEY); 110CREATE TABLE clstr_2 (a INT PRIMARY KEY); 111CREATE TABLE clstr_3 (a INT PRIMARY KEY); 112ALTER TABLE clstr_1 OWNER TO regress_clstr_user; 113ALTER TABLE clstr_3 OWNER TO regress_clstr_user; 114GRANT SELECT ON clstr_2 TO regress_clstr_user; 115INSERT INTO clstr_1 VALUES (2); 116INSERT INTO clstr_1 VALUES (1); 117INSERT INTO clstr_2 VALUES (2); 118INSERT INTO clstr_2 VALUES (1); 119INSERT INTO clstr_3 VALUES (2); 120INSERT INTO clstr_3 VALUES (1); 121 122-- "CLUSTER <tablename>" on a table that hasn't been clustered 123CLUSTER clstr_2; 124 125CLUSTER clstr_1_pkey ON clstr_1; 126CLUSTER clstr_2 USING clstr_2_pkey; 127SELECT * FROM clstr_1 UNION ALL 128 SELECT * FROM clstr_2 UNION ALL 129 SELECT * FROM clstr_3; 130 131-- revert to the original state 132DELETE FROM clstr_1; 133DELETE FROM clstr_2; 134DELETE FROM clstr_3; 135INSERT INTO clstr_1 VALUES (2); 136INSERT INTO clstr_1 VALUES (1); 137INSERT INTO clstr_2 VALUES (2); 138INSERT INTO clstr_2 VALUES (1); 139INSERT INTO clstr_3 VALUES (2); 140INSERT INTO clstr_3 VALUES (1); 141 142-- this user can only cluster clstr_1 and clstr_3, but the latter 143-- has not been clustered 144SET SESSION AUTHORIZATION regress_clstr_user; 145CLUSTER; 146SELECT * FROM clstr_1 UNION ALL 147 SELECT * FROM clstr_2 UNION ALL 148 SELECT * FROM clstr_3; 149 150-- cluster a single table using the indisclustered bit previously set 151DELETE FROM clstr_1; 152INSERT INTO clstr_1 VALUES (2); 153INSERT INTO clstr_1 VALUES (1); 154CLUSTER clstr_1; 155SELECT * FROM clstr_1; 156 157-- Test MVCC-safety of cluster. There isn't much we can do to verify the 158-- results with a single backend... 159 160CREATE TABLE clustertest (key int PRIMARY KEY); 161 162INSERT INTO clustertest VALUES (10); 163INSERT INTO clustertest VALUES (20); 164INSERT INTO clustertest VALUES (30); 165INSERT INTO clustertest VALUES (40); 166INSERT INTO clustertest VALUES (50); 167 168-- Use a transaction so that updates are not committed when CLUSTER sees 'em 169BEGIN; 170 171-- Test update where the old row version is found first in the scan 172UPDATE clustertest SET key = 100 WHERE key = 10; 173 174-- Test update where the new row version is found first in the scan 175UPDATE clustertest SET key = 35 WHERE key = 40; 176 177-- Test longer update chain 178UPDATE clustertest SET key = 60 WHERE key = 50; 179UPDATE clustertest SET key = 70 WHERE key = 60; 180UPDATE clustertest SET key = 80 WHERE key = 70; 181 182SELECT * FROM clustertest; 183CLUSTER clustertest_pkey ON clustertest; 184SELECT * FROM clustertest; 185 186COMMIT; 187 188SELECT * FROM clustertest; 189 190-- check that temp tables can be clustered 191create temp table clstr_temp (col1 int primary key, col2 text); 192insert into clstr_temp values (2, 'two'), (1, 'one'); 193cluster clstr_temp using clstr_temp_pkey; 194select * from clstr_temp; 195drop table clstr_temp; 196 197RESET SESSION AUTHORIZATION; 198 199-- Test CLUSTER with external tuplesorting 200 201create table clstr_4 as select * from tenk1; 202create index cluster_sort on clstr_4 (hundred, thousand, tenthous); 203-- ensure we don't use the index in CLUSTER nor the checking SELECTs 204set enable_indexscan = off; 205 206-- Use external sort that only ever uses quicksort to sort runs: 207set maintenance_work_mem = '1MB'; 208set replacement_sort_tuples = 0; 209cluster clstr_4 using cluster_sort; 210select * from 211(select hundred, lag(hundred) over () as lhundred, 212 thousand, lag(thousand) over () as lthousand, 213 tenthous, lag(tenthous) over () as ltenthous from clstr_4) ss 214where row(hundred, thousand, tenthous) <= row(lhundred, lthousand, ltenthous); 215 216-- Replacement selection will now be forced. It should only produce a single 217-- run, due to the fact that input is found to be presorted: 218set replacement_sort_tuples = 150000; 219cluster clstr_4 using cluster_sort; 220select * from 221(select hundred, lag(hundred) over () as lhundred, 222 thousand, lag(thousand) over () as lthousand, 223 tenthous, lag(tenthous) over () as ltenthous from clstr_4) ss 224where row(hundred, thousand, tenthous) <= row(lhundred, lthousand, ltenthous); 225 226reset enable_indexscan; 227reset maintenance_work_mem; 228reset replacement_sort_tuples; 229 230-- clean up 231DROP TABLE clustertest; 232DROP TABLE clstr_1; 233DROP TABLE clstr_2; 234DROP TABLE clstr_3; 235DROP TABLE clstr_4; 236DROP USER regress_clstr_user; 237