Date: Sun, 2 Feb 2020 10:17:18 -0600
From: Justin Pryzby <pryzby@telsasoft.com>
To: pgsql-hackers@postgresql.org
Subject: ALTER tbl rewrite loses CLUSTER ON index
Message-ID: <20200202161718.GI13621@telsasoft.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="2+IDmuKgu0wSQZlt"
Content-Disposition: inline
User-Agent: Mutt/1.5.24 (2015-08-30)
Precedence: bulk


--2+IDmuKgu0wSQZlt
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

Other options are preserved by ALTER (and CLUSTER ON is and most obviously
should be preserved by CLUSTER's rewrite), so I think (SET) CLUSTER should be
preserved by ALTER, too.

As far as I can see, this should be the responsibility of something in the
vicinity of ATPostAlterTypeParse/RememberIndexForRebuilding.

Attach patch sketches a fix.

ts=# SET client_min_messages=debug; DROP TABLE t; CREATE TABLE t(i int); CREATE INDEX ON t(i)WITH(fillfactor=11, vacuum_cleanup_index_scale_factor=12); CLUSTER t USING t_i_key; ALTER TABLE t ALTER i TYPE bigint; \d t
SET
DEBUG:  drop auto-cascades to type t
DEBUG:  drop auto-cascades to type t[]
DEBUG:  drop auto-cascades to index t_i_idx
DROP TABLE
CREATE TABLE
DEBUG:  building index "t_i_idx" on table "t" serially
CREATE INDEX
ERROR:  index "t_i_key" for table "t" does not exist
DEBUG:  rewriting table "t"
DEBUG:  building index "t_i_idx" on table "t" serially
DEBUG:  drop auto-cascades to type pg_temp_3091172777
DEBUG:  drop auto-cascades to type pg_temp_3091172777[]
ALTER TABLE
                 Table "public.t"
 Column |  Type  | Collation | Nullable | Default 
--------+--------+-----------+----------+---------
 i      | bigint |           |          | 
Indexes:
    "t_i_idx" btree (i) WITH (fillfactor='11', vacuum_cleanup_index_scale_factor='12')

--2+IDmuKgu0wSQZlt
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
 filename="v1-0001-preserve-CLUSTER-ON-during-ALTER-TABLE.patch"

From f235a691722a464059358cd6b1d744f75d7bf92f Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryzbyj@telsasoft.com>
Date: Sun, 2 Feb 2020 09:49:57 -0600
Subject: [PATCH v1] preserve CLUSTER ON during ALTER TABLE

---
 src/backend/commands/tablecmds.c | 18 ++++++++++++++++++
 1 file changed, 18 insertions(+)

diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index f599393..c4e6cbd 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -11616,6 +11616,7 @@ RememberIndexForRebuilding(Oid indoid, AlteredTableInfo *tab)
 		}
 		else
 		{
+			Relation indrel;
 			/* OK, capture the index's existing definition string */
 			char	   *defstring = pg_get_indexdef_string(indoid);
 
@@ -11623,6 +11624,18 @@ RememberIndexForRebuilding(Oid indoid, AlteredTableInfo *tab)
 												indoid);
 			tab->changedIndexDefs = lappend(tab->changedIndexDefs,
 											defstring);
+			/* Preserve CLUSTER ON if set */
+			indrel = index_open(indoid, AccessShareLock);
+			if (indrel->rd_index->indisclustered) {
+				char buf[3*NAMEDATALEN + 24];
+				sprintf(buf, "ALTER TABLE %s CLUSTER ON %s",
+						get_rel_name(tab->relid), get_rel_name(indoid)); // XXX: schema
+				tab->changedIndexOids = lappend_oid(tab->changedIndexOids,
+													indoid);
+				tab->changedIndexDefs = lappend(tab->changedIndexDefs,
+												pstrdup(buf));
+			}
+			index_close(indrel, NoLock);
 		}
 	}
 }
@@ -11901,6 +11914,11 @@ ATPostAlterTypeParse(Oid oldId, Oid oldRelId, Oid refRelId, char *cmd,
 					 * the new table definition.
 					 */
 				}
+				else if (cmd->subtype == AT_ClusterOn)
+				{
+					tab->subcmds[AT_PASS_OLD_INDEX] =
+						lappend(tab->subcmds[AT_PASS_OLD_INDEX], cmd);
+				}
 				else
 					elog(ERROR, "unexpected statement subtype: %d",
 						 (int) cmd->subtype);
-- 
2.7.4


--2+IDmuKgu0wSQZlt--