From: Justin Pryzby <pryzby@telsasoft.com>
To: Michael Paquier <michael@paquier.xyz>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Alvaro Herrera <alvherre@2ndquadrant.com>
Cc: Amit Langote <amitlangote09@gmail.com>
Cc: pgsql-hackers@postgresql.org
Subject: Re: ALTER tbl rewrite loses CLUSTER ON index
Date: Mon, 2 Mar 2020 19:36:25 -0600
Message-ID: <20200303013625.GA21225@telsasoft.com> (raw)
In-Reply-To: <20200303013132.GA684@telsasoft.com>
References: <20200206144426.GH403@telsasoft.com>
<20200206172447.GA28041@alvherre.pgsql>
<20200207143935.GP403@telsasoft.com>
<17937.1582932364@sss.pgh.pa.us>
<20200229024202.GH29456@telsasoft.com>
<20200229165258.GJ29456@telsasoft.com>
<20200302032818.GB32059@paquier.xyz>
<20200303013132.GA684@telsasoft.com>
@cfbot: resending with only Amit's 0001, since Michael pushed a variation on
0002.
--
Justin
Attachments:
[text/x-diff] v5-0001-ALTER-tbl-rewrite-loses-CLUSTER-ON-index.patch (5.8K, ../20200303013625.GA21225@telsasoft.com/2-v5-0001-ALTER-tbl-rewrite-loses-CLUSTER-ON-index.patch)
download | inline diff:
From 865fc2713ad29d0f8c0f63609a7c15c83cfa5cfe Mon Sep 17 00:00:00 2001
From: Amit Langote <amitlangote09@gmail.com>
Date: Thu, 6 Feb 2020 18:14:16 +0900
Subject: [PATCH v5] ALTER tbl rewrite loses CLUSTER ON index
---
src/backend/commands/tablecmds.c | 39 +++++++++++++++++++++++
src/test/regress/expected/alter_table.out | 34 ++++++++++++++++++++
src/test/regress/sql/alter_table.sql | 16 +++++++++-
3 files changed, 88 insertions(+), 1 deletion(-)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.cindex 02a7c04fdb..6b2469bd09 100644--- a/src/backend/commands/tablecmds.c+++ b/src/backend/commands/tablecmds.c@@ -490,6 +490,7 @@ static void ATPostAlterTypeParse(Oid oldId, Oid oldRelId, Oid refRelId,
static void RebuildConstraintComment(AlteredTableInfo *tab, int pass,
Oid objid, Relation rel, List *domname,
const char *conname);
+static void PreserveClusterOn(AlteredTableInfo *tab, int pass, Oid indoid);
static void TryReuseIndex(Oid oldId, IndexStmt *stmt);
static void TryReuseForeignKey(Oid oldId, Constraint *con);
static ObjectAddress ATExecAlterColumnGenericOptions(Relation rel, const char *colName,
@@ -11838,6 +11839,9 @@ ATPostAlterTypeParse(Oid oldId, Oid oldRelId, Oid refRelId, char *cmd,
newcmd->def = (Node *) stmt;
tab->subcmds[AT_PASS_OLD_INDEX] =
lappend(tab->subcmds[AT_PASS_OLD_INDEX], newcmd);
++ /* Preserve index's indisclustered property, if set. */+ PreserveClusterOn(tab, AT_PASS_OLD_INDEX, oldId);
}
else if (IsA(stm, AlterTableStmt))
{
@@ -11874,6 +11878,9 @@ ATPostAlterTypeParse(Oid oldId, Oid oldRelId, Oid refRelId, char *cmd,
rel,
NIL,
indstmt->idxname);
++ /* Preserve index's indisclustered property, if set. */+ PreserveClusterOn(tab, AT_PASS_OLD_INDEX, indoid);
}
else if (cmd->subtype == AT_AddConstraint)
{
@@ -11996,6 +12003,38 @@ RebuildConstraintComment(AlteredTableInfo *tab, int pass, Oid objid,
tab->subcmds[pass] = lappend(tab->subcmds[pass], newcmd);
}
+/*+ * For a table's index that is to be recreated due to PostAlterType+ * processing, preserve its indisclustered property by issuing ALTER TABLE+ * CLUSTER ON command on the table that will run after the command to recreate+ * the index.+ */+static void+PreserveClusterOn(AlteredTableInfo *tab, int pass, Oid indoid)+{+ HeapTuple indexTuple;+ Form_pg_index indexForm;++ Assert(OidIsValid(indoid));+ Assert(pass == AT_PASS_OLD_INDEX);++ indexTuple = SearchSysCache1(INDEXRELID, ObjectIdGetDatum(indoid));+ if (!HeapTupleIsValid(indexTuple))+ elog(ERROR, "cache lookup failed for index %u", indoid);+ indexForm = (Form_pg_index) GETSTRUCT(indexTuple);++ if (indexForm->indisclustered)+ {+ AlterTableCmd *newcmd = makeNode(AlterTableCmd);++ newcmd->subtype = AT_ClusterOn;+ newcmd->name = get_rel_name(indoid);+ tab->subcmds[pass] = lappend(tab->subcmds[pass], newcmd);+ }++ ReleaseSysCache(indexTuple);+}+
/*
* Subroutine for ATPostAlterTypeParse(). Calls out to CheckIndexCompatible()
* for the real analysis, then mutates the IndexStmt based on that verdict.
diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.outindex fb6d86a269..a01c6d6ec5 100644--- a/src/test/regress/expected/alter_table.out+++ b/src/test/regress/expected/alter_table.out@@ -4296,3 +4296,37 @@ create trigger xtrig
update bar1 set a = a + 1;
INFO: a=1, b=1
/* End test case for bug #16242 */
+-- alter type rewrite/rebuild should preserve cluster marking on index+create table alttype_cluster (a int);+create index alttype_cluster_a on alttype_cluster (a);+alter table alttype_cluster cluster on alttype_cluster_a;+select indisclustered from pg_index where indrelid = 'alttype_cluster'::regclass;+ indisclustered +----------------+ t+(1 row)++alter table alttype_cluster alter a type bigint;+select indisclustered from pg_index where indrelid = 'alttype_cluster'::regclass;+ indisclustered +----------------+ t+(1 row)++drop index alttype_cluster_a;+alter table alttype_cluster add primary key (a);+alter table alttype_cluster cluster on alttype_cluster_pkey;+select indisclustered from pg_index where indrelid = 'alttype_cluster'::regclass;+ indisclustered +----------------+ t+(1 row)++alter table alttype_cluster alter a type int;+select indisclustered from pg_index where indrelid = 'alttype_cluster'::regclass;+ indisclustered +----------------+ t+(1 row)++drop table alttype_cluster;diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sqlindex 3801f19c58..6e9048bbec 100644--- a/src/test/regress/sql/alter_table.sql+++ b/src/test/regress/sql/alter_table.sql@@ -2802,7 +2802,6 @@ drop table at_test_sql_partop;
drop operator class at_test_sql_partop using btree;
drop function at_test_sql_partop;
-
/* Test case for bug #16242 */
-- We create a parent and child where the child has missing
@@ -2840,3 +2839,18 @@ create trigger xtrig
update bar1 set a = a + 1;
/* End test case for bug #16242 */
++-- alter type rewrite/rebuild should preserve cluster marking on index+create table alttype_cluster (a int);+create index alttype_cluster_a on alttype_cluster (a);+alter table alttype_cluster cluster on alttype_cluster_a;+select indisclustered from pg_index where indrelid = 'alttype_cluster'::regclass;+alter table alttype_cluster alter a type bigint;+select indisclustered from pg_index where indrelid = 'alttype_cluster'::regclass;+drop index alttype_cluster_a;+alter table alttype_cluster add primary key (a);+alter table alttype_cluster cluster on alttype_cluster_pkey;+select indisclustered from pg_index where indrelid = 'alttype_cluster'::regclass;+alter table alttype_cluster alter a type int;+select indisclustered from pg_index where indrelid = 'alttype_cluster'::regclass;+drop table alttype_cluster;--
2.17.0
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: pryzby@telsasoft.com, michael@paquier.xyz, tgl@sss.pgh.pa.us, alvherre@2ndquadrant.com, amitlangote09@gmail.com
Subject: Re: ALTER tbl rewrite loses CLUSTER ON index
In-Reply-To: <20200303013625.GA21225@telsasoft.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox