Re: SET WITHOUT CLUSTER

Patch applied. Thanks.

I modified your docs slightly:

This form removes the most recently used
<xref linkend="SQL-CLUSTER" endterm="sql-cluster-title">
index specification from the table. This affects
future cluster operations that don't specify an index.

---------------------------------------------------------------------------

Christopher Kings-Lynne wrote:
> OK, here's the final version of SET WITHOUT CLUSTER. Has docs +
> regression test.
>
> Chris
>

> Index: doc/src/sgml/ref/alter_table.sgml
> ===================================================================
> RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/ref/alter_table.sgml,v
> retrieving revision 1.69
> diff -c -r1.69 alter_table.sgml
> *** doc/src/sgml/ref/alter_table.sgml 5 May 2004 04:48:45 -0000 1.69
> --- doc/src/sgml/ref/alter_table.sgml 24 May 2004 05:55:47 -0000
> ***************
> *** 42,47 ****
> --- 42,48 ----
> SET WITHOUT OIDS
> OWNER TO <replaceable class="PARAMETER">new_owner</replaceable>
> CLUSTER ON <replaceable class="PARAMETER">index_name</replaceable>
> + SET WITHOUT CLUSTER
> </synopsis>
> </refsynopsisdiv>
>
> ***************
> *** 219,224 ****
> --- 220,235 ----
> </listitem>
> </varlistentry>
>
> + <varlistentry>
> + <term><literal>SET WITHOUT CLUSTER</literal></term>
> + <listitem>
> + <para>
> + This form disables future <xref linkend="SQL-CLUSTER" endterm="sql-cluster-title"> on
> + any indexes on a table.
> + </para>
> + </listitem>
> + </varlistentry>
> +
> <varlistentry>
> <term><literal>RENAME</literal></term>
> <listitem>
> Index: src/backend/commands/tablecmds.c
> ===================================================================
> RCS file: /projects/cvsroot/pgsql-server/src/backend/commands/tablecmds.c,v
> retrieving revision 1.107
> diff -c -r1.107 tablecmds.c
> *** src/backend/commands/tablecmds.c 8 May 2004 22:46:29 -0000 1.107
> --- src/backend/commands/tablecmds.c 24 May 2004 05:55:50 -0000
> ***************
> *** 233,238 ****
> --- 233,239 ----
> static void ATPostAlterTypeParse(char *cmd, List **wqueue);
> static void ATExecChangeOwner(Oid relationOid, int32 newOwnerSysId);
> static void ATExecClusterOn(Relation rel, const char *indexName);
> + static void ATExecDropCluster(Relation rel);
> static int ri_trigger_type(Oid tgfoid);
> static void update_ri_trigger_args(Oid relid,
> const char *oldname,
> ***************
> *** 1921,1928 ****
> pass = AT_PASS_MISC;
> break;
> case AT_ClusterOn: /* CLUSTER ON */
> ATSimplePermissions(rel, false);
> ! /* This command never recurses */
> /* No command-specific prep needed */
> pass = AT_PASS_MISC;
> break;
> --- 1922,1930 ----
> pass = AT_PASS_MISC;
> break;
> case AT_ClusterOn: /* CLUSTER ON */
> + case AT_DropCluster: /* SET WITHOUT CLUSTER */
> ATSimplePermissions(rel, false);
> ! /* These commands never recurse */
> /* No command-specific prep needed */
> pass = AT_PASS_MISC;
> break;
> ***************
> *** 2082,2087 ****
> --- 2084,2092 ----
> case AT_ClusterOn: /* CLUSTER ON */
> ATExecClusterOn(rel, cmd->name);
> break;
> + case AT_DropCluster: /* SET WITHOUT CLUSTER */
> + ATExecDropCluster(rel);
> + break;
> case AT_DropOids: /* SET WITHOUT OIDS */
> /*
> * Nothing to do here; we'll have generated a DropColumn subcommand
> ***************
> *** 5051,5056 ****
> --- 5056,5074 ----
> /* And do the work */
> mark_index_clustered(rel, indexOid);
> }
> +
> + /*
> + * ALTER TABLE SET WITHOUT CLUSTER
> + *
> + * We have to find any indexes on the table that have indisclustered bit
> + * set and turn it off.
> + */
> + static void
> + ATExecDropCluster(Relation rel)
> + {
> + mark_index_clustered(rel, InvalidOid);
> + }
> +
>
> /*
> * ALTER TABLE CREATE TOAST TABLE
> Index: src/backend/parser/gram.y
> ===================================================================
> RCS file: /projects/cvsroot/pgsql-server/src/backend/parser/gram.y,v
> retrieving revision 2.454
> diff -c -r2.454 gram.y
> *** src/backend/parser/gram.y 10 May 2004 22:44:45 -0000 2.454
> --- src/backend/parser/gram.y 24 May 2004 05:55:52 -0000
> ***************
> *** 1263,1268 ****
> --- 1263,1276 ----
> n->name = $3;
> $$ = (Node *)n;
> }
> + /* ALTER TABLE <name> SET WITHOUT CLUSTER */
> + | SET WITHOUT CLUSTER
> + {
> + AlterTableCmd *n = makeNode(AlterTableCmd);
> + n->subtype = AT_DropCluster;
> + n->name = NULL;
> + $$ = (Node *)n;
> + }
> ;
>
> alter_column_default:
> Index: src/include/nodes/parsenodes.h
> ===================================================================
> RCS file: /projects/cvsroot/pgsql-server/src/include/nodes/parsenodes.h,v
> retrieving revision 1.255
> diff -c -r1.255 parsenodes.h
> *** src/include/nodes/parsenodes.h 5 May 2004 04:48:47 -0000 1.255
> --- src/include/nodes/parsenodes.h 24 May 2004 05:55:53 -0000
> ***************
> *** 793,798 ****
> --- 793,799 ----
> AT_ToastTable, /* create toast table */
> AT_ChangeOwner, /* change owner */
> AT_ClusterOn, /* CLUSTER ON */
> + AT_DropCluster, /* SET WITHOUT CLUSTER */
> AT_DropOids /* SET WITHOUT OIDS */
> } AlterTableType;
>
> Index: src/test/regress/expected/cluster.out
> ===================================================================
> RCS file: /projects/cvsroot/pgsql-server/src/test/regress/expected/cluster.out,v
> retrieving revision 1.14
> diff -c -r1.14 cluster.out
> *** src/test/regress/expected/cluster.out 2 Oct 2003 06:32:46 -0000 1.14
> --- src/test/regress/expected/cluster.out 24 May 2004 05:55:53 -0000
> ***************
> *** 297,302 ****
> --- 297,313 ----
> 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);
> Index: src/test/regress/sql/cluster.sql
> ===================================================================
> RCS file: /projects/cvsroot/pgsql-server/src/test/regress/sql/cluster.sql,v
> retrieving revision 1.7
> diff -c -r1.7 cluster.sql
> *** src/test/regress/sql/cluster.sql 20 Mar 2003 18:52:48 -0000 1.7
> --- src/test/regress/sql/cluster.sql 24 May 2004 05:55:53 -0000
> ***************
> *** 95,100 ****
> --- 95,108 ----
> AND pg_class_2.relname = 'clstr_tst'
> AND indisclustered;
>
> + -- 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;
> +
> -- Verify that clustering all tables does in fact cluster the right ones
> CREATE USER clstr_user;
> CREATE TABLE clstr_1 (a INT PRIMARY KEY);

>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073