[PATCH] add CLUSTER table USING index (take 3)

Your patch has been added to the PostgreSQL unapplied patches list at:

http://momjian.postgresql.org/cgi-bin/pgpatches

It will be applied as soon as one of the PostgreSQL committers reviews
and approves it.

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

Holger Schurig wrote:

SGML ref text (swapped parameter list, changed example text)

Also, I noticed that the text of the example spoke about a
table "employees", but the example used the table "emp". I
fixed this inconsistency.

Index: src/doc/src/sgml/ref/cluster.sgml
===================================================================
*** src.orig/doc/src/sgml/ref/cluster.sgml	2007-03-28 23:03:20.000000000 +0200
--- src/doc/src/sgml/ref/cluster.sgml	2007-03-29 21:32:26.000000000 +0200
***************
*** 20,27 ****

<refsynopsisdiv>
<synopsis>
! CLUSTER <replaceable class="PARAMETER">indexname</replaceable> ON <replaceable class="PARAMETER">tablename</replaceable>
! CLUSTER <replaceable class="PARAMETER">tablename</replaceable>
CLUSTER
</synopsis>
</refsynopsisdiv>
--- 20,26 ----

<refsynopsisdiv>
<synopsis>
! CLUSTER <replaceable class="PARAMETER">tablename</replaceable> [ USING <replaceable class="PARAMETER">indexname</replaceable> ]
CLUSTER
</synopsis>
</refsynopsisdiv>
***************
*** 77,95 ****

<variablelist>
<varlistentry>
! <term><replaceable class="PARAMETER">indexname</replaceable></term>
<listitem>
<para>
! The name of an index.
</para>
</listitem>
</varlistentry>

<varlistentry>
!     <term><replaceable class="PARAMETER">tablename</replaceable></term>
<listitem>
<para>
!       The name (possibly schema-qualified) of a table.
</para>
</listitem>
</varlistentry>
--- 76,94 ----

<variablelist>
<varlistentry>
! <term><replaceable class="PARAMETER">tablename</replaceable></term>
<listitem>
<para>
! The name (possibly schema-qualified) of a table.
</para>
</listitem>
</varlistentry>

<varlistentry>
! <term><replaceable class="PARAMETER">indexname</replaceable></term>
<listitem>
<para>
! The name of an index.
</para>
</listitem>
</varlistentry>
***************
*** 172,180 ****

<para>
Cluster the table <literal>employees</literal> on the basis of
! its index <literal>emp_ind</literal>:
<programlisting>
! CLUSTER emp_ind ON emp;
</programlisting>
</para>

--- 171,179 ----

<para>
Cluster the table <literal>employees</literal> on the basis of
! its index <literal>employees_ind</literal>:
<programlisting>
! CLUSTER employees USING employees_ind;
</programlisting>
</para>

***************
*** 182,188 ****
Cluster the <literal>employees</literal> table using the same
index that was used before:
<programlisting>
! CLUSTER emp;
</programlisting>
</para>

--- 181,187 ----
Cluster the <literal>employees</literal> table using the same
index that was used before:
<programlisting>
! CLUSTER employees;
</programlisting>
</para>

Index: src/src/backend/parser/gram.y
===================================================================
*** src.orig/src/backend/parser/gram.y	2007-03-28 23:03:20.000000000 +0200
--- src/src/backend/parser/gram.y	2007-03-28 23:03:35.000000000 +0200
***************
*** 209,215 ****

%type <str> relation_name copy_file_name
database_name access_method_clause access_method attr_name
! index_name name file_name

%type <list>	func_name handler_name qual_Op qual_all_Op subquery_Op
opt_class opt_validator
--- 209,215 ----

%type <str> relation_name copy_file_name
database_name access_method_clause access_method attr_name
! index_name name file_name opt_cluster_using

%type <list>	func_name handler_name qual_Op qual_all_Op subquery_Op
opt_class opt_validator
***************
*** 5327,5332 ****
--- 5327,5333 ----
*
*		QUERY:
*				cluster <index_name> on <qualified_name>
+  *				cluster <qualified_name> USING <index_name>
*				cluster <qualified_name>
*				cluster
*
***************
*** 5340,5350 ****
n->indexname = $2;
$$ = (Node*)n;
}
! 			| CLUSTER qualified_name
{
ClusterStmt *n = makeNode(ClusterStmt);
n->relation = $2;
! 				   n->indexname = NULL;
$$ = (Node*)n;
}
| CLUSTER
--- 5341,5351 ----
n->indexname = $2;
$$ = (Node*)n;
}
! 			| CLUSTER qualified_name opt_cluster_using
{
ClusterStmt *n = makeNode(ClusterStmt);
n->relation = $2;
! 				   n->indexname = $3;
$$ = (Node*)n;
}
| CLUSTER
***************
*** 5356,5361 ****
--- 5357,5368 ----
}
;

+ opt_cluster_using:
+ 			USING index_name			{ $$ = $2; }
+ 			| /*EMPTY*/				{ $$ = NULL; }
+ 		;
+ 
+ 
/*****************************************************************************
*
*		QUERY:
Index: src/src/bin/psql/tab-complete.c
===================================================================
*** src.orig/src/bin/psql/tab-complete.c	2007-03-28 23:03:20.000000000 +0200
--- src/src/bin/psql/tab-complete.c	2007-03-28 23:03:35.000000000 +0200
***************
*** 822,832 ****

COMPLETE_WITH_LIST(list_COLUMNALTER);
}
! 	else if (pg_strcasecmp(prev3_wd, "TABLE") == 0 &&
! 			 pg_strcasecmp(prev_wd, "CLUSTER") == 0)
COMPLETE_WITH_CONST("ON");
else if (pg_strcasecmp(prev4_wd, "TABLE") == 0 &&
- 			 pg_strcasecmp(prev2_wd, "CLUSTER") == 0 &&
pg_strcasecmp(prev_wd, "ON") == 0)
{
completion_info_charp = prev3_wd;
--- 822,830 ----

COMPLETE_WITH_LIST(list_COLUMNALTER);
}
! else if (pg_strcasecmp(prev3_wd, "TABLE") == 0)
COMPLETE_WITH_CONST("ON");
else if (pg_strcasecmp(prev4_wd, "TABLE") == 0 &&
pg_strcasecmp(prev_wd, "ON") == 0)
{
completion_info_charp = prev3_wd;
***************
*** 929,952 ****

/*
* If the previous word is CLUSTER and not without produce list of
! * indexes.
*/
else if (pg_strcasecmp(prev_wd, "CLUSTER") == 0 &&
pg_strcasecmp(prev2_wd, "WITHOUT") != 0)
! COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_indexes, NULL);
! /* If we have CLUSTER <sth>, then add "ON" */
else if (pg_strcasecmp(prev2_wd, "CLUSTER") == 0 &&
! pg_strcasecmp(prev_wd, "ON") != 0)
! COMPLETE_WITH_CONST("ON");

/*
! * If we have CLUSTER <sth> ON, then add the correct tablename as well.
*/
else if (pg_strcasecmp(prev3_wd, "CLUSTER") == 0 &&
! pg_strcasecmp(prev_wd, "ON") == 0)
{
completion_info_charp = prev2_wd;
! COMPLETE_WITH_QUERY(Query_for_table_owning_index);
}

/* COMMENT */
--- 927,951 ----

/*
* If the previous word is CLUSTER and not without produce list of
! * tables
*/
else if (pg_strcasecmp(prev_wd, "CLUSTER") == 0 &&
pg_strcasecmp(prev2_wd, "WITHOUT") != 0)
! COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables, NULL);
! /* If we have CLUSTER <sth>, then add "USING" */
else if (pg_strcasecmp(prev2_wd, "CLUSTER") == 0 &&
! pg_strcasecmp(prev_wd, "ON") != 0) {
! COMPLETE_WITH_CONST("USING");
! }

/*
! * If we have CLUSTER <sth> ORDER BY, then add the index as well.
*/
else if (pg_strcasecmp(prev3_wd, "CLUSTER") == 0 &&
! pg_strcasecmp(prev_wd, "USING") == 0)
{
completion_info_charp = prev2_wd;
! COMPLETE_WITH_QUERY(Query_for_index_of_table);
}

/* COMMENT */
Index: src/src/test/regress/expected/cluster.out
===================================================================
*** src.orig/src/test/regress/expected/cluster.out	2007-03-28 23:03:20.000000000 +0200
--- src/src/test/regress/expected/cluster.out	2007-03-28 23:03:35.000000000 +0200
***************
*** 329,335 ****
CLUSTER clstr_2;
ERROR:  there is no previously clustered index for table "clstr_2"
CLUSTER clstr_1_pkey ON clstr_1;
! CLUSTER clstr_2_pkey ON clstr_2;
SELECT * FROM clstr_1 UNION ALL
SELECT * FROM clstr_2 UNION ALL
SELECT * FROM clstr_3;
--- 329,335 ----
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;
Index: src/src/test/regress/sql/cluster.sql
===================================================================
*** src.orig/src/test/regress/sql/cluster.sql	2007-03-28 23:03:20.000000000 +0200
--- src/src/test/regress/sql/cluster.sql	2007-03-28 23:03:35.000000000 +0200
***************
*** 122,128 ****
CLUSTER clstr_2;

CLUSTER clstr_1_pkey ON clstr_1;
! CLUSTER clstr_2_pkey ON clstr_2;
SELECT * FROM clstr_1 UNION ALL
SELECT * FROM clstr_2 UNION ALL
SELECT * FROM clstr_3;
--- 122,128 ----
CLUSTER 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;

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Updated patch applied. Thanks.

I added a mention of the old syntax at the bottom of the CLUSTER manual
page, and cleaned up the grammar a little. Also did a little comment
cleaning in gram.y.

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

Holger Schurig wrote:

SGML ref text (swapped parameter list, changed example text)

Also, I noticed that the text of the example spoke about a
table "employees", but the example used the table "emp". I
fixed this inconsistency.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +