cluster.out

PostgreSQL 8.1.4的源码 适用于Linux下的开源数据库系统

 14 | 25 | veinticinco   |                                |       
 21 | 24 | veinticuatro  |                                |       
  4 | 22 | veintidos     |                                |       
 19 | 29 | veintinueve   |                                |       
 16 | 28 | veintiocho    |                                |       
 27 | 26 | veintiseis    |                                |       
 13 | 27 | veintisiete   |                                |       
  7 | 23 | veintitres    |                                |       
  8 | 21 | veintiuno     |                                |       
(32 rows)

-- Verify that inheritance link still works
INSERT INTO clstr_tst_inh VALUES (0, 100, 'in child table');
SELECT a,b,c,substring(d for 30), length(d) from clstr_tst;
 a  |  b  |       c        |           substring            | length 
----+-----+----------------+--------------------------------+--------
 10 |  14 | catorce        |                                |       
 18 |   5 | cinco          |                                |       
  9 |   4 | cuatro         |                                |       
 26 |  19 | diecinueve     |                                |       
 12 |  18 | dieciocho      |                                |       
 30 |  16 | dieciseis      |                                |       
 24 |  17 | diecisiete     |                                |       
  2 |  10 | diez           |                                |       
 23 |  12 | doce           |                                |       
 11 |   2 | dos            |                                |       
 25 |   9 | nueve          |                                |       
 31 |   8 | ocho           |                                |       
  1 |  11 | once           |                                |       
 28 |  15 | quince         |                                |       
 32 |   6 | seis           | xyzzyxyzzyxyzzyxyzzyxyzzyxyzzy | 500000
 29 |   7 | siete          |                                |       
 15 |  13 | trece          |                                |       
 22 |  30 | treinta        |                                |       
 17 |  32 | treinta y dos  |                                |       
  3 |  31 | treinta y uno  |                                |       
  5 |   3 | tres           |                                |       
 20 |   1 | uno            |                                |       
  6 |  20 | veinte         |                                |       
 14 |  25 | veinticinco    |                                |       
 21 |  24 | veinticuatro   |                                |       
  4 |  22 | veintidos      |                                |       
 19 |  29 | veintinueve    |                                |       
 16 |  28 | veintiocho     |                                |       
 27 |  26 | veintiseis     |                                |       
 13 |  27 | veintisiete    |                                |       
  7 |  23 | veintitres     |                                |       
  8 |  21 | veintiuno      |                                |       
  0 | 100 | in child table |                                |       
(33 rows)

-- Verify that foreign key link still works
INSERT INTO clstr_tst (b, c) VALUES (1111, 'this should fail');
ERROR:  insert or update on table "clstr_tst" violates foreign key constraint "clstr_tst_con"
DETAIL:  Key (b)=(1111) is not present in table "clstr_tst_s".
SELECT conname FROM pg_constraint WHERE conrelid = 'clstr_tst'::regclass;
    conname     
----------------
 clstr_tst_pkey
 clstr_tst_con
(2 rows)

SELECT relname, relkind,
    EXISTS(SELECT 1 FROM pg_class WHERE oid = c.reltoastrelid) AS hastoast
FROM pg_class c WHERE relname LIKE 'clstr_tst%' ORDER BY relname;
       relname        | relkind | hastoast 
----------------------+---------+----------
 clstr_tst            | r       | t
 clstr_tst_a_seq      | S       | f
 clstr_tst_b          | i       | f
 clstr_tst_b_c        | i       | f
 clstr_tst_c          | i       | f
 clstr_tst_c_b        | i       | f
 clstr_tst_inh        | r       | t
 clstr_tst_pkey       | i       | f
 clstr_tst_s          | r       | f
 clstr_tst_s_pkey     | i       | f
 clstr_tst_s_rf_a_seq | S       | f
(11 rows)

-- Verify that indisclustered is correctly set
SELECT pg_class.relname FROM pg_index, pg_class, pg_class AS pg_class_2
WHERE pg_class.oid=indexrelid
	AND indrelid=pg_class_2.oid
	AND pg_class_2.relname = 'clstr_tst'
	AND indisclustered;
   relname   
-------------
 clstr_tst_c
(1 row)

-- Try changing indisclustered
ALTER TABLE clstr_tst CLUSTER ON clstr_tst_b_c;
SELECT pg_class.relname FROM pg_index, pg_class, pg_class AS pg_class_2
WHERE pg_class.oid=indexrelid
	AND indrelid=pg_class_2.oid
	AND pg_class_2.relname = 'clstr_tst'
	AND indisclustered;
    relname    
---------------
 clstr_tst_b_c
(1 row)

-- Try turning off all clustering
ALTER TABLE clstr_tst SET WITHOUT CLUSTER;
SELECT pg_class.relname FROM pg_index, pg_class, pg_class AS pg_class_2
WHERE pg_class.oid=indexrelid
	AND indrelid=pg_class_2.oid
	AND pg_class_2.relname = 'clstr_tst'
	AND indisclustered;
 relname 
---------
(0 rows)

-- Verify that clustering all tables does in fact cluster the right ones
CREATE USER clstr_user;
CREATE TABLE clstr_1 (a INT PRIMARY KEY);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "clstr_1_pkey" for table "clstr_1"
CREATE TABLE clstr_2 (a INT PRIMARY KEY);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "clstr_2_pkey" for table "clstr_2"
CREATE TABLE clstr_3 (a INT PRIMARY KEY);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "clstr_3_pkey" for table "clstr_3"
ALTER TABLE clstr_1 OWNER TO clstr_user;
ALTER TABLE clstr_3 OWNER TO clstr_user;
GRANT SELECT ON clstr_2 TO clstr_user;
INSERT INTO clstr_1 VALUES (2);
INSERT INTO clstr_1 VALUES (1);
INSERT INTO clstr_2 VALUES (2);
INSERT INTO clstr_2 VALUES (1);
INSERT INTO clstr_3 VALUES (2);
INSERT INTO clstr_3 VALUES (1);
-- "CLUSTER <tablename>" on a table that hasn't been clustered
CLUSTER clstr_2;
ERROR:  there is no previously clustered index for table "clstr_2"
CLUSTER clstr_1_pkey ON clstr_1;
CLUSTER clstr_2_pkey ON clstr_2;
SELECT * FROM clstr_1 UNION ALL
  SELECT * FROM clstr_2 UNION ALL
  SELECT * FROM clstr_3;
 a 
---
 1
 2
 1
 2
 2
 1
(6 rows)

-- revert to the original state
DELETE FROM clstr_1;
DELETE FROM clstr_2;
DELETE FROM clstr_3;
INSERT INTO clstr_1 VALUES (2);
INSERT INTO clstr_1 VALUES (1);
INSERT INTO clstr_2 VALUES (2);
INSERT INTO clstr_2 VALUES (1);
INSERT INTO clstr_3 VALUES (2);
INSERT INTO clstr_3 VALUES (1);
-- this user can only cluster clstr_1 and clstr_3, but the latter
-- has not been clustered
SET SESSION AUTHORIZATION clstr_user;
CLUSTER;
SELECT * FROM clstr_1 UNION ALL
  SELECT * FROM clstr_2 UNION ALL
  SELECT * FROM clstr_3;
 a 
---
 1
 2
 2
 1
 2
 1
(6 rows)

-- cluster a single table using the indisclustered bit previously set
DELETE FROM clstr_1;
INSERT INTO clstr_1 VALUES (2);
INSERT INTO clstr_1 VALUES (1);
CLUSTER clstr_1;
SELECT * FROM clstr_1;
 a 
---
 1
 2
(2 rows)

-- clean up
\c -
DROP TABLE clstr_1;
DROP TABLE clstr_2;
DROP TABLE clstr_3;
DROP USER clstr_user;