Adding upstream version 14.5.upstream/14.5upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 601 insertions, 0 deletions

diff --git a/src/test/regress/expected/cluster.out b/src/test/regress/expected/cluster.out
new file mode 100644
index 0000000..7293c0f
--- /dev/null
+++ b/src/test/regress/expected/cluster.out
@@ -0,0 +1,601 @@
+--
+--  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_con
+ clstr_tst_pkey
+(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 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;
+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)
+
+-- 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 cannot be clustered
+CREATE TABLE clstrpart (a int) PARTITION BY RANGE (a);
+CREATE INDEX clstrpart_idx ON clstrpart (a);
+ALTER TABLE clstrpart CLUSTER ON clstrpart_idx;
+ERROR:  cannot mark index clustered in partitioned table
+CLUSTER clstrpart USING clstrpart_idx;
+ERROR:  cannot cluster a partitioned table
+DROP TABLE clstrpart;
+-- 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;
+-- 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 USER regress_clstr_user;