src/test/regress/expected/cluster.out - master - githubmirror/postgres

src/test/regress/expected/cluster.out
840 строк37 KB
Álvaro Herrera
Cover additional errors and corner conditions in repack.c
29 май 2026, 12:54
29 май 2026, 12:542670cc2
--
--  CLUSTER
--
CREATE TABLE clstr_tst_s (rf_a SERIAL PRIMARY KEY,
	b INT);
CREATE TABLE clstr_tst (a SERIAL PRIMARY KEY,
	b INT,
	c TEXT,
	d TEXT,
	CONSTRAINT clstr_tst_con FOREIGN KEY (b) REFERENCES clstr_tst_s);
CREATE INDEX clstr_tst_b ON clstr_tst (b);
CREATE INDEX clstr_tst_c ON clstr_tst (c);
CREATE INDEX clstr_tst_c_b ON clstr_tst (c,b);
CREATE INDEX clstr_tst_b_c ON clstr_tst (b,c);
INSERT INTO clstr_tst_s (b) VALUES (0);
INSERT INTO clstr_tst_s (b) SELECT b FROM clstr_tst_s;
INSERT INTO clstr_tst_s (b) SELECT b FROM clstr_tst_s;
INSERT INTO clstr_tst_s (b) SELECT b FROM clstr_tst_s;
INSERT INTO clstr_tst_s (b) SELECT b FROM clstr_tst_s;
INSERT INTO clstr_tst_s (b) SELECT b FROM clstr_tst_s;
CREATE TABLE clstr_tst_inh () INHERITS (clstr_tst);
INSERT INTO clstr_tst (b, c) VALUES (11, 'once');
INSERT INTO clstr_tst (b, c) VALUES (10, 'diez');
INSERT INTO clstr_tst (b, c) VALUES (31, 'treinta y uno');
INSERT INTO clstr_tst (b, c) VALUES (22, 'veintidos');
INSERT INTO clstr_tst (b, c) VALUES (3, 'tres');
INSERT INTO clstr_tst (b, c) VALUES (20, 'veinte');
INSERT INTO clstr_tst (b, c) VALUES (23, 'veintitres');
INSERT INTO clstr_tst (b, c) VALUES (21, 'veintiuno');
INSERT INTO clstr_tst (b, c) VALUES (4, 'cuatro');
INSERT INTO clstr_tst (b, c) VALUES (14, 'catorce');
INSERT INTO clstr_tst (b, c) VALUES (2, 'dos');
INSERT INTO clstr_tst (b, c) VALUES (18, 'dieciocho');
INSERT INTO clstr_tst (b, c) VALUES (27, 'veintisiete');
INSERT INTO clstr_tst (b, c) VALUES (25, 'veinticinco');
INSERT INTO clstr_tst (b, c) VALUES (13, 'trece');
INSERT INTO clstr_tst (b, c) VALUES (28, 'veintiocho');
INSERT INTO clstr_tst (b, c) VALUES (32, 'treinta y dos');
INSERT INTO clstr_tst (b, c) VALUES (5, 'cinco');
INSERT INTO clstr_tst (b, c) VALUES (29, 'veintinueve');
INSERT INTO clstr_tst (b, c) VALUES (1, 'uno');
INSERT INTO clstr_tst (b, c) VALUES (24, 'veinticuatro');
INSERT INTO clstr_tst (b, c) VALUES (30, 'treinta');
INSERT INTO clstr_tst (b, c) VALUES (12, 'doce');
INSERT INTO clstr_tst (b, c) VALUES (17, 'diecisiete');
INSERT INTO clstr_tst (b, c) VALUES (9, 'nueve');
INSERT INTO clstr_tst (b, c) VALUES (19, 'diecinueve');
INSERT INTO clstr_tst (b, c) VALUES (26, 'veintiseis');
INSERT INTO clstr_tst (b, c) VALUES (15, 'quince');
INSERT INTO clstr_tst (b, c) VALUES (7, 'siete');
INSERT INTO clstr_tst (b, c) VALUES (16, 'dieciseis');
INSERT INTO clstr_tst (b, c) VALUES (8, 'ocho');
-- This entry is needed to test that TOASTED values are copied correctly.
INSERT INTO clstr_tst (b, c, d) VALUES (6, 'seis', repeat('xyzzy', 100000));
CLUSTER clstr_tst_c ON clstr_tst;
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     |                                |       
(32 rows)

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

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

SELECT a,b,c,substring(d for 30), length(d) from clstr_tst ORDER BY c;
 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     |                                |       
(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
ORDER BY 1;
       conname        
----------------------
 clstr_tst_a_not_null
 clstr_tst_con
 clstr_tst_pkey
(3 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 toast tables are clusterable
CLUSTER pg_toast.pg_toast_826 USING pg_toast_826_index;
-- Verify that clustering all tables does in fact cluster the right ones
CREATE USER regress_clstr_user;
CREATE TABLE clstr_1 (a INT PRIMARY KEY);
CREATE TABLE clstr_2 (a INT PRIMARY KEY);
CREATE TABLE clstr_3 (a INT PRIMARY KEY);
ALTER TABLE clstr_1 OWNER TO regress_clstr_user;
ALTER TABLE clstr_3 OWNER TO regress_clstr_user;
GRANT SELECT ON clstr_2 TO regress_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 USING clstr_2_pkey;
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 regress_clstr_user;
SET client_min_messages = ERROR;  -- order of "skipping" warnings may vary
CLUSTER;
RESET client_min_messages;
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)

-- Test MVCC-safety of cluster. There isn't much we can do to verify the
-- results with a single backend...
CREATE TABLE clustertest (key int PRIMARY KEY);
INSERT INTO clustertest VALUES (10);
INSERT INTO clustertest VALUES (20);
INSERT INTO clustertest VALUES (30);
INSERT INTO clustertest VALUES (40);
INSERT INTO clustertest VALUES (50);
-- Use a transaction so that updates are not committed when CLUSTER sees 'em
BEGIN;
-- Test update where the old row version is found first in the scan
UPDATE clustertest SET key = 100 WHERE key = 10;
-- Test update where the new row version is found first in the scan
UPDATE clustertest SET key = 35 WHERE key = 40;
-- Test longer update chain
UPDATE clustertest SET key = 60 WHERE key = 50;
UPDATE clustertest SET key = 70 WHERE key = 60;
UPDATE clustertest SET key = 80 WHERE key = 70;
SELECT * FROM clustertest;
 key 
-----
  20
  30
 100
  35
  80
(5 rows)

CLUSTER clustertest_pkey ON clustertest;
SELECT * FROM clustertest;
 key 
-----
  20
  30
  35
  80
 100
(5 rows)

COMMIT;
SELECT * FROM clustertest;
 key 
-----
  20
  30
  35
  80
 100
(5 rows)

-- check that temp tables can be clustered
create temp table clstr_temp (col1 int primary key, col2 text);
insert into clstr_temp values (2, 'two'), (1, 'one');
cluster clstr_temp using clstr_temp_pkey;
select * from clstr_temp;
 col1 | col2 
------+------
    1 | one
    2 | two
(2 rows)

drop table clstr_temp;
RESET SESSION AUTHORIZATION;
-- check clustering an empty table
DROP TABLE clustertest;
CREATE TABLE clustertest (f1 int PRIMARY KEY);
CLUSTER clustertest USING clustertest_pkey;
CLUSTER clustertest;
-- Check that partitioned tables can be clustered
CREATE TABLE clstrpart (a int) PARTITION BY RANGE (a);
CREATE TABLE clstrpart1 PARTITION OF clstrpart FOR VALUES FROM (1) TO (10) PARTITION BY RANGE (a);
CREATE TABLE clstrpart11 PARTITION OF clstrpart1 FOR VALUES FROM (1) TO (5);
CREATE TABLE clstrpart12 PARTITION OF clstrpart1 FOR VALUES FROM (5) TO (10) PARTITION BY RANGE (a);
CREATE TABLE clstrpart2 PARTITION OF clstrpart FOR VALUES FROM (10) TO (20);
CREATE TABLE clstrpart3 PARTITION OF clstrpart DEFAULT PARTITION BY RANGE (a);
CREATE TABLE clstrpart33 PARTITION OF clstrpart3 DEFAULT;
CREATE INDEX clstrpart_only_idx ON ONLY clstrpart (a);
CLUSTER clstrpart USING clstrpart_only_idx; -- fails
ERROR:  cannot cluster on invalid index "clstrpart_only_idx"
DROP INDEX clstrpart_only_idx;
CREATE INDEX clstrpart_idx ON clstrpart (a);
-- Check that clustering sets new relfilenodes:
CREATE TEMP TABLE old_cluster_info AS SELECT relname, level, relfilenode, relkind FROM pg_partition_tree('clstrpart'::regclass) AS tree JOIN pg_class c ON c.oid=tree.relid ;
CLUSTER clstrpart USING clstrpart_idx;
CREATE TEMP TABLE new_cluster_info AS SELECT relname, level, relfilenode, relkind FROM pg_partition_tree('clstrpart'::regclass) AS tree JOIN pg_class c ON c.oid=tree.relid ;
SELECT relname, old.level, old.relkind, old.relfilenode = new.relfilenode FROM old_cluster_info AS old JOIN new_cluster_info AS new USING (relname) ORDER BY relname COLLATE "C";
   relname   | level | relkind | ?column? 
-------------+-------+---------+----------
 clstrpart   |     0 | p       | t
 clstrpart1  |     1 | p       | t
 clstrpart11 |     2 | r       | f
 clstrpart12 |     2 | p       | t
 clstrpart2  |     1 | r       | f
 clstrpart3  |     1 | p       | t
 clstrpart33 |     2 | r       | f
(7 rows)

-- Partitioned indexes aren't and can't be marked un/clustered:
\d clstrpart
       Partitioned table "public.clstrpart"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           |          | 
Partition key: RANGE (a)
Indexes:
    "clstrpart_idx" btree (a)
Number of partitions: 3 (Use \d+ to list them.)

CLUSTER clstrpart;
ERROR:  there is no previously clustered index for table "clstrpart"
ALTER TABLE clstrpart SET WITHOUT CLUSTER;
ERROR:  ALTER action SET WITHOUT CLUSTER cannot be performed on relation "clstrpart"
DETAIL:  This operation is not supported for partitioned tables.
ALTER TABLE clstrpart CLUSTER ON clstrpart_idx;
ERROR:  ALTER action CLUSTER ON cannot be performed on relation "clstrpart"
DETAIL:  This operation is not supported for partitioned tables.
-- and they cannot get an index-ordered REPACK without an explicit index name
REPACK clstrpart USING INDEX;
ERROR:  cannot execute REPACK on partitioned table "clstrpart" USING INDEX with no index name
-- Check that REPACK sets new relfilenodes: it should process exactly the same
-- tables as CLUSTER did.
DROP TABLE old_cluster_info;
DROP TABLE new_cluster_info;
CREATE TEMP TABLE old_cluster_info AS SELECT relname, level, relfilenode, relkind FROM pg_partition_tree('clstrpart'::regclass) AS tree JOIN pg_class c ON c.oid=tree.relid ;
REPACK clstrpart USING INDEX clstrpart_idx;
CREATE TEMP TABLE new_cluster_info AS SELECT relname, level, relfilenode, relkind FROM pg_partition_tree('clstrpart'::regclass) AS tree JOIN pg_class c ON c.oid=tree.relid ;
SELECT relname, old.level, old.relkind, old.relfilenode = new.relfilenode FROM old_cluster_info AS old JOIN new_cluster_info AS new USING (relname) ORDER BY relname COLLATE "C";
   relname   | level | relkind | ?column? 
-------------+-------+---------+----------
 clstrpart   |     0 | p       | t
 clstrpart1  |     1 | p       | t
 clstrpart11 |     2 | r       | f
 clstrpart12 |     2 | p       | t
 clstrpart2  |     1 | r       | f
 clstrpart3  |     1 | p       | t
 clstrpart33 |     2 | r       | f
(7 rows)

-- And finally the same for REPACK w/o index.
DROP TABLE old_cluster_info;
DROP TABLE new_cluster_info;
CREATE TEMP TABLE old_cluster_info AS SELECT relname, level, relfilenode, relkind FROM pg_partition_tree('clstrpart'::regclass) AS tree JOIN pg_class c ON c.oid=tree.relid ;
REPACK clstrpart;
CREATE TEMP TABLE new_cluster_info AS SELECT relname, level, relfilenode, relkind FROM pg_partition_tree('clstrpart'::regclass) AS tree JOIN pg_class c ON c.oid=tree.relid ;
SELECT relname, old.level, old.relkind, old.relfilenode = new.relfilenode FROM old_cluster_info AS old JOIN new_cluster_info AS new USING (relname) ORDER BY relname COLLATE "C";
   relname   | level | relkind | ?column? 
-------------+-------+---------+----------
 clstrpart   |     0 | p       | t
 clstrpart1  |     1 | p       | t
 clstrpart11 |     2 | r       | f
 clstrpart12 |     2 | p       | t
 clstrpart2  |     1 | r       | f
 clstrpart3  |     1 | p       | t
 clstrpart33 |     2 | r       | f
(7 rows)

-- Ownership of partitions is checked
CREATE TABLE ptnowner(i int unique not null) PARTITION BY LIST (i);
CREATE INDEX ptnowner_i_idx ON ptnowner(i);
CREATE TABLE ptnowner1 PARTITION OF ptnowner FOR VALUES IN (1);
CREATE ROLE regress_ptnowner;
CREATE TABLE ptnowner2 PARTITION OF ptnowner FOR VALUES IN (2);
ALTER TABLE ptnowner1 OWNER TO regress_ptnowner;
SET SESSION AUTHORIZATION regress_ptnowner;
CLUSTER ptnowner USING ptnowner_i_idx;
ERROR:  permission denied for table ptnowner
RESET SESSION AUTHORIZATION;
ALTER TABLE ptnowner OWNER TO regress_ptnowner;
CREATE TEMP TABLE ptnowner_oldnodes AS
  SELECT oid, relname, relfilenode FROM pg_partition_tree('ptnowner') AS tree
  JOIN pg_class AS c ON c.oid=tree.relid;
SET SESSION AUTHORIZATION regress_ptnowner;
CLUSTER ptnowner USING ptnowner_i_idx;
WARNING:  permission denied to execute CLUSTER on "ptnowner2", skipping it
RESET SESSION AUTHORIZATION;
DROP TABLE ptnowner;
DROP ROLE regress_ptnowner;
-- Test CLUSTER with external tuplesorting
create table clstr_4 as select * from tenk1;
create index cluster_sort on clstr_4 (hundred, thousand, tenthous);
-- ensure we don't use the index in CLUSTER nor the checking SELECTs
set enable_indexscan = off;
-- Use external sort:
set maintenance_work_mem = '1MB';
cluster clstr_4 using cluster_sort;
select * from
(select hundred, lag(hundred) over () as lhundred,
        thousand, lag(thousand) over () as lthousand,
        tenthous, lag(tenthous) over () as ltenthous from clstr_4) ss
where row(hundred, thousand, tenthous) <= row(lhundred, lthousand, ltenthous);
 hundred | lhundred | thousand | lthousand | tenthous | ltenthous 
---------+----------+----------+-----------+----------+-----------
(0 rows)

reset enable_indexscan;
reset maintenance_work_mem;
-- test CLUSTER on expression index
CREATE TABLE clstr_expression(id serial primary key, a int, b text COLLATE "C");
INSERT INTO clstr_expression(a, b) SELECT g.i % 42, 'prefix'||g.i FROM generate_series(1, 133) g(i);
CREATE INDEX clstr_expression_minus_a ON clstr_expression ((-a), b);
CREATE INDEX clstr_expression_upper_b ON clstr_expression ((upper(b)));
-- verify indexes work before cluster
BEGIN;
SET LOCAL enable_seqscan = false;
EXPLAIN (COSTS OFF) SELECT * FROM clstr_expression WHERE upper(b) = 'PREFIX3';
                          QUERY PLAN                           
---------------------------------------------------------------
 Index Scan using clstr_expression_upper_b on clstr_expression
   Index Cond: (upper(b) = 'PREFIX3'::text)
(2 rows)

SELECT * FROM clstr_expression WHERE upper(b) = 'PREFIX3';
 id | a |    b    
----+---+---------
  3 | 3 | prefix3
(1 row)

EXPLAIN (COSTS OFF) SELECT * FROM clstr_expression WHERE -a = -3 ORDER BY -a, b;
                          QUERY PLAN                           
---------------------------------------------------------------
 Index Scan using clstr_expression_minus_a on clstr_expression
   Index Cond: ((- a) = '-3'::integer)
(2 rows)

SELECT * FROM clstr_expression WHERE -a = -3 ORDER BY -a, b;
 id  | a |     b     
-----+---+-----------
 129 | 3 | prefix129
   3 | 3 | prefix3
  45 | 3 | prefix45
  87 | 3 | prefix87
(4 rows)

COMMIT;
-- and after clustering on clstr_expression_minus_a
CLUSTER clstr_expression USING clstr_expression_minus_a;
WITH rows AS
  (SELECT ctid, lag(a) OVER (ORDER BY ctid) AS la, a FROM clstr_expression)
SELECT * FROM rows WHERE la < a;
 ctid | la | a 
------+----+---
(0 rows)

BEGIN;
SET LOCAL enable_seqscan = false;
EXPLAIN (COSTS OFF) SELECT * FROM clstr_expression WHERE upper(b) = 'PREFIX3';
                          QUERY PLAN                           
---------------------------------------------------------------
 Index Scan using clstr_expression_upper_b on clstr_expression
   Index Cond: (upper(b) = 'PREFIX3'::text)
(2 rows)

SELECT * FROM clstr_expression WHERE upper(b) = 'PREFIX3';
 id | a |    b    
----+---+---------
  3 | 3 | prefix3
(1 row)

EXPLAIN (COSTS OFF) SELECT * FROM clstr_expression WHERE -a = -3 ORDER BY -a, b;
                          QUERY PLAN                           
---------------------------------------------------------------
 Index Scan using clstr_expression_minus_a on clstr_expression
   Index Cond: ((- a) = '-3'::integer)
(2 rows)

SELECT * FROM clstr_expression WHERE -a = -3 ORDER BY -a, b;
 id  | a |     b     
-----+---+-----------
 129 | 3 | prefix129
   3 | 3 | prefix3
  45 | 3 | prefix45
  87 | 3 | prefix87
(4 rows)

COMMIT;
-- and after clustering on clstr_expression_upper_b
CLUSTER clstr_expression USING clstr_expression_upper_b;
WITH rows AS
  (SELECT ctid, lag(b) OVER (ORDER BY ctid) AS lb, b FROM clstr_expression)
SELECT * FROM rows WHERE upper(lb) > upper(b);
 ctid | lb | b 
------+----+---
(0 rows)

BEGIN;
SET LOCAL enable_seqscan = false;
EXPLAIN (COSTS OFF) SELECT * FROM clstr_expression WHERE upper(b) = 'PREFIX3';
                          QUERY PLAN                           
---------------------------------------------------------------
 Index Scan using clstr_expression_upper_b on clstr_expression
   Index Cond: (upper(b) = 'PREFIX3'::text)
(2 rows)

SELECT * FROM clstr_expression WHERE upper(b) = 'PREFIX3';
 id | a |    b    
----+---+---------
  3 | 3 | prefix3
(1 row)

EXPLAIN (COSTS OFF) SELECT * FROM clstr_expression WHERE -a = -3 ORDER BY -a, b;
                          QUERY PLAN                           
---------------------------------------------------------------
 Index Scan using clstr_expression_minus_a on clstr_expression
   Index Cond: ((- a) = '-3'::integer)
(2 rows)

SELECT * FROM clstr_expression WHERE -a = -3 ORDER BY -a, b;
 id  | a |     b     
-----+---+-----------
 129 | 3 | prefix129
   3 | 3 | prefix3
  45 | 3 | prefix45
  87 | 3 | prefix87
(4 rows)

COMMIT;
-- verify some error cases
CREATE TABLE clstr_table_one (id int, val text);
CREATE TABLE clstr_table_two (id int, val text);
CREATE INDEX clstr_idx_b ON clstr_table_two (id);
CLUSTER clstr_table_one USING clstr_idx_b;
ERROR:  "clstr_idx_b" is not an index for table "clstr_table_one"
CLUSTER clstr_table_one USING nonexistant;
ERROR:  index "nonexistant" for table "clstr_table_one" does not exist
CREATE INDEX clstr_hash_idx ON clstr_table_one USING hash (id);
CLUSTER clstr_table_one USING clstr_hash_idx;
ERROR:  cannot cluster on index "clstr_hash_idx" because access method does not support clustering
CREATE INDEX clstr_partial_idx ON clstr_table_one (id) WHERE id > 0;
CLUSTER clstr_table_one USING clstr_partial_idx;
ERROR:  cannot cluster on partial index "clstr_partial_idx"
REPACK pg_class USING INDEX pg_class_oid_index;
ERROR:  permission denied: "pg_class" is a system catalog
DETAIL:  System catalogs can only be clustered by the index they're already clustered on, if any, unless "allow_system_table_mods" is enabled.
DROP TABLE clstr_table_one, clstr_table_two;
-- verify that CLUSTER/REPACK don't touch a NO DATA matview
CREATE MATERIALIZED VIEW clstr_matview AS
    SELECT i FROM generate_series(1, 5) i
    WITH NO DATA;
CREATE INDEX clstr_matview_idx ON clstr_matview (i);
SELECT relfilenode FROM pg_class WHERE oid = 'clstr_matview'::regclass \gset
CLUSTER clstr_matview USING clstr_matview_idx;
REPACK clstr_matview USING INDEX clstr_matview_idx;
SELECT relfilenode = :relfilenode FROM pg_class WHERE oid = 'clstr_matview'::regclass;
 ?column? 
----------
 t
(1 row)

DROP MATERIALIZED VIEW clstr_matview;
----------------------------------------------------------------------
--
-- REPACK
--
-- Note we cannot test working REPACK (CONCURRENTLY) here, because the
-- tests are sometimes run with wal_level=minimal.  Tests for that appear
-- elsewhere.
--
----------------------------------------------------------------------
-- REPACK handles individual tables identically to CLUSTER, but it's worth
-- checking if it handles table hierarchies identically as well.
REPACK clstr_tst USING INDEX clstr_tst_c;
-- Verify that inheritance link still works
INSERT INTO clstr_tst_inh VALUES (0, 100, 'in child table 2');
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   |                                |       
  0 | 100 | in child table 2 |                                |       
(34 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
ORDER BY 1;
       conname        
----------------------
 clstr_tst_a_not_null
 clstr_tst_con
 clstr_tst_pkey
(3 rows)

-- Verify partial analyze works
REPACK (ANALYZE) clstr_tst (a);
REPACK (ANALYZE) clstr_tst;
REPACK (VERBOSE) clstr_tst (a);
ERROR:  ANALYZE option must be specified when a column list is provided
-- REPACK w/o argument performs no ordering, so we can only check which tables
-- have the relfilenode changed.
RESET SESSION AUTHORIZATION;
CREATE TEMP TABLE relnodes_old AS
(SELECT relname, relfilenode
FROM pg_class
WHERE relname IN ('clstr_1', 'clstr_2', 'clstr_3'));
SET SESSION AUTHORIZATION regress_clstr_user;
SET client_min_messages = ERROR;  -- order of "skipping" warnings may vary
REPACK;
RESET client_min_messages;
RESET SESSION AUTHORIZATION;
CREATE TEMP TABLE relnodes_new AS
(SELECT relname, relfilenode
FROM pg_class
WHERE relname IN ('clstr_1', 'clstr_2', 'clstr_3'));
-- Do the actual comparison. Unlike CLUSTER, clstr_3 should have been
-- processed because there is nothing like clustering index here.
SELECT o.relname FROM relnodes_old o
JOIN relnodes_new n ON o.relname = n.relname
WHERE o.relfilenode <> n.relfilenode
ORDER BY o.relname;
 relname 
---------
 clstr_1
 clstr_3
(2 rows)

-- clean up
DROP TABLE clustertest;
DROP TABLE clstr_1;
DROP TABLE clstr_2;
DROP TABLE clstr_3;
DROP TABLE clstr_4;
DROP TABLE clstr_expression;
DROP TABLE clstrpart;
DROP USER regress_clstr_user;