I attach a little patch to make CLUSTER set and reset the indisclustered

bit on the indexes.

I also attach clusterdb and clusterdb.sgml; both of them are blatant
rips of vacuumdb and vacuumdb.sgml, but get the job done.  Please review
them, as I'm probably making a lot of mistakes with SGML and I can't
compile it here.

vacuumdb itself is not very comfortable to use when the databases have
passwords, because it has to connect once for each table (I can probably
make it connect only once for each database; should I?).  Because of
this I added a mention of PGPASSWORDFILE in the documentation, but I
don't know if that is the correct place for that.

Alvaro Herrera

2 files changed, 179 insertions, 2 deletions

diff --git a/src/bin/scripts/Makefile b/src/bin/scripts/Makefile
index 4a574b464a..db87a9efc0 100644
--- a/src/bin/scripts/Makefile
+++ b/src/bin/scripts/Makefile
@@ -5,7 +5,7 @@
 # Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group
 # Portions Copyright (c) 1994, Regents of the University of California
 #
-# $Header: /cvsroot/pgsql/src/bin/scripts/Makefile,v 1.15 2002/06/20 20:29:42 momjian Exp $
+# $Header: /cvsroot/pgsql/src/bin/scripts/Makefile,v 1.16 2002/08/27 03:38:27 momjian Exp $
 #
 #-------------------------------------------------------------------------
 
@@ -13,7 +13,8 @@ subdir = src/bin/scripts
 top_builddir = ../../..
 include $(top_builddir)/src/Makefile.global
 
-SCRIPTS := createdb dropdb createuser dropuser createlang droplang vacuumdb
+SCRIPTS := createdb dropdb createuser dropuser createlang droplang vacuumdb \
+           clusterdb
 
 all: $(SCRIPTS)
 
diff --git a/src/bin/scripts/clusterdb b/src/bin/scripts/clusterdb
new file mode 100644
index 0000000000..58430e2d6a
--- /dev/null
+++ b/src/bin/scripts/clusterdb
@@ -0,0 +1,176 @@
+#!/bin/sh
+#-------------------------------------------------------------------------
+#
+# clusterdb--
+#    cluster a postgres database
+#
+#    This script runs psql with the "-c" option to cluster
+#    the requested database.
+#
+# Copyright (c) 2002, PostgreSQL Global Development Group
+#
+#
+# IDENTIFICATION
+#    $Header: /cvsroot/pgsql/src/bin/scripts/Attic/clusterdb,v 1.1 2002/08/27 03:38:27 momjian Exp $
+#
+#-------------------------------------------------------------------------
+
+CMDNAME=`basename "$0"`
+PATHNAME=`echo "$0" | sed "s,$CMDNAME\$,,"`
+
+PSQLOPT=
+table=
+dbname=
+alldb=
+quiet=0
+
+while [ "$#" -gt 0 ]
+do
+	case "$1" in
+	--help|-\?)
+		usage=t
+		break
+		;;
+# options passed on to psql
+	--host|-h)
+		PSQLOPT="$PSQLOPT -h $2"
+		shift;;
+	-h*)
+		PSQLOPT="$PSQLOPT $1"
+		;;
+	--host=*)
+		PSQLOPT="$PSQLOPT -h `echo \"$1\" | sed 's/^--host=//'`"
+		;;
+	--port|-p)
+		PSQLOPT="$PSQLOPT -p $2"
+		shift;;
+	-p*)
+		PSQLOPT="$PSQLOPT $1"
+		;;
+	--port=*)
+		PSQLOPT="$PSQLOPT -p `echo \"$1\" | sed 's/^--port=//'`"
+		;;
+	--username|-U)
+		PSQLOPT="$PSQLOPT -U $2"
+		shift;;
+	-U*)
+		PSQLOPT="$PSQLOPT $1"
+		;;
+	--username=*)
+		PSQLOPT="$PSQLOPT -U `echo \"$1\" | sed 's/^--username=//'`"
+		;;
+	--password|-W)
+		PSQLOPT="$PSQLOPT -W"
+		;;
+	--echo|-e)
+		ECHOOPT="-e"
+		;;
+	--quiet|-q)
+		ECHOOPT="$ECHOOPT -o /dev/null"
+		quiet=1
+		;;
+	--dbname|-d)
+		dbname="$2"
+		shift;;
+	-d*)
+		dbname=`echo $1 | sed 's/^-d//'`
+		;;
+	--dbname=*)
+		dbname=`echo $1 | sed 's/^--dbname=//'`
+		;;
+	-a|--alldb)
+		alldb=1
+		;;
+# options converted into SQL command
+	--table|-t)
+		table="$2"
+		shift;;
+	-t*)
+		table=`echo $1 | sed 's/^-t//'`
+		;;
+	--table=*)
+		table=`echo $1 | sed 's/^--table=//'`
+		;;
+	-*)
+		echo "$CMDNAME: invalid option: $1" 1>&2
+		echo "Try '$CMDNAME --help' for more information." 1>&2
+		exit 1
+		;;
+	*)
+		dbname="$1"
+		if [ "$#" -ne 1 ]; then
+			echo "$CMDNAME: invalid option: $2" 1>&2
+			echo "Try '$CMDNAME --help' for more information." 1>&2
+			exit 1
+		fi
+		;;
+	esac
+	shift
+done
+
+if [ "$usage" ]; then	
+	echo "$CMDNAME cluster all previously clustered tables in a database"
+	echo
+	echo "Usage:"
+	echo "  $CMDNAME [options] [dbname]"
+	echo
+	echo "Options:"
+	echo "  -h, --host=HOSTNAME             Database server host"
+	echo "  -p, --port=PORT                 Database server port"
+	echo "  -U, --username=USERNAME         Username to connect as"
+	echo "  -W, --password                  Prompt for password"
+	echo "  -d, --dbname=DBNAME             Database to cluster"
+	echo "  -a, --all                       Cluster all databases"
+	echo "  -t, --table='TABLE[(columns)]'  Cluster specific table only"
+	echo "  -v, --verbose                   Write a lot of output"
+	echo "  -e, --echo                      Show the command being sent to the backend"
+	echo "  -q, --quiet                     Don't write any output"
+	echo
+	echo "Read the description of the SQL command VACUUM for details."
+	echo
+	echo "Report bugs to <pgsql-bugs@postgresql.org>."
+	exit 0
+fi
+
+if [ "$alldb" ]; then
+	if [ "$dbname" -o "$table" ]; then
+		echo "$CMDNAME: cannot cluster all databases and a specific one at the same time" 1>&2
+		exit 1
+	fi
+	dbname=`${PATHNAME}psql $PSQLOPT -q -t -A -d template1 -c 'SELECT datname FROM pg_database WHERE datallowconn'`
+
+elif [ -z "$dbname" ]; then
+	if [ "$PGDATABASE" ]; then
+		dbname="$PGDATABASE"
+	elif [ "$PGUSER" ]; then
+		dbname="$PGUSER"
+	else
+		dbname=`${PATHNAME}pg_id -u -n`
+	fi
+	[ "$?" -ne 0 ] && exit 1
+fi
+
+for db in $dbname
+do
+	[ "$alldb" -a "$quiet" -ne 1 ] && echo "Clustering $db"
+	query="SELECT pg_class.relname, pg_class_2.relname FROM pg_class, \
+		pg_class AS pg_class_2, pg_index WHERE pg_class.oid=pg_index.indrelid\
+		AND pg_class_2.oid=pg_index.indexrelid AND pg_index.indisclustered"
+	if [ -z "$table" ]; then
+		tables=`${PATHNAME}psql $db $PSQLOPT -F: -P format=unaligned -t -c "$query"`
+	else
+		tables=`${PATHNAME}psql $db $PSQLOPT -F: -P format=unaligned -t -c \
+		"$query AND pg_class.relname='$table'"`
+	fi
+	for tabs in $tables
+	do
+		tab=`echo $tabs | cut -d: -f1`
+		idx=`echo $tabs | cut -d: -f2`
+		${PATHNAME}psql $PSQLOPT $ECHOOPT -c "CLUSTER $idx on $tab" -d $db
+		if [ "$?" -ne 0 ]; then
+			echo "$CMDNAME: cluster $table $db failed" 1>&2
+		fi
+	done
+done
+
+exit 0