aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--doc/src/sgml/ref/allfiles.sgml3
-rw-r--r--doc/src/sgml/ref/clusterdb.sgml280
-rw-r--r--doc/src/sgml/reference.sgml3
-rw-r--r--src/backend/commands/cluster.c32
-rw-r--r--src/bin/scripts/Makefile5
-rw-r--r--src/bin/scripts/clusterdb176
-rw-r--r--src/test/regress/expected/cluster.out11
-rw-r--r--src/test/regress/sql/cluster.sql7
8 files changed, 509 insertions, 8 deletions
diff --git a/doc/src/sgml/ref/allfiles.sgml b/doc/src/sgml/ref/allfiles.sgml
index c81cded1178..79221044c21 100644
--- a/doc/src/sgml/ref/allfiles.sgml
+++ b/doc/src/sgml/ref/allfiles.sgml
@@ -1,5 +1,5 @@
<!--
-$Header: /cvsroot/pgsql/doc/src/sgml/ref/allfiles.sgml,v 1.46 2002/08/17 03:38:43 tgl Exp $
+$Header: /cvsroot/pgsql/doc/src/sgml/ref/allfiles.sgml,v 1.47 2002/08/27 03:38:27 momjian Exp $
PostgreSQL documentation
Complete list of usable sgml source files in this directory.
-->
@@ -120,6 +120,7 @@ Complete list of usable sgml source files in this directory.
<!entity vacuum system "vacuum.sgml">
<!-- applications and utilities -->
+<!entity clusterdb system "clusterdb.sgml">
<!entity createdb system "createdb.sgml">
<!entity createlang system "createlang.sgml">
<!entity createuser system "createuser.sgml">
diff --git a/doc/src/sgml/ref/clusterdb.sgml b/doc/src/sgml/ref/clusterdb.sgml
new file mode 100644
index 00000000000..b650ce24e45
--- /dev/null
+++ b/doc/src/sgml/ref/clusterdb.sgml
@@ -0,0 +1,280 @@
+<!--
+$Header: /cvsroot/pgsql/doc/src/sgml/ref/clusterdb.sgml,v 1.1 2002/08/27 03:38:27 momjian Exp $
+PostgreSQL documentation
+-->
+
+<refentry id="APP-CLUSTERDB">
+ <refmeta>
+ <refentrytitle id="APP-CLUSTERDB-TITLE"><application>clusterdb</application></refentrytitle>
+ <manvolnum>1</manvolnum>
+ <refmiscinfo>Application</refmiscinfo>
+ </refmeta>
+
+ <refnamediv>
+ <refname id="clusterdb">clusterdb</refname>
+ <refpurpose>cluster a <productname>PostgreSQL</productname> database</refpurpose>
+ </refnamediv>
+
+ <refsynopsisdiv>
+ <cmdsynopsis>
+ <command>clusterdb</command>
+ <arg rep="repeat"><replaceable>connection-options</replaceable></arg>
+ <arg>--table | -t '<replaceable>table</replaceable> </arg>
+ <arg><replaceable>dbname</replaceable></arg>
+ <sbr>
+ <command>clusterdb</command>
+ <arg rep="repeat"><replaceable>connection-options</replaceable></arg>
+ <group><arg>--all</arg><arg>-a</arg></group>
+ </cmdsynopsis>
+ </refsynopsisdiv>
+
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <application>clusterdb</application> is a utility for clustering tables inside a
+ <productname>PostgreSQL</productname> database.
+ </para>
+
+ <para>
+ <application>clusterdb</application> is a shell script wrapper around the
+ backend command
+ <xref linkend="SQL-CLUSTER" endterm="SQL-CLUSTER-title"> via
+ the <productname>PostgreSQL</productname> interactive terminal
+ <xref linkend="APP-PSQL">. There is no effective
+ difference between clustering databases via this or other methods.
+ <application>psql</application> must be found by the script and
+ a database server must be running at the targeted host. Also, any default
+ settings and environment variables available to <application>psql</application>
+ and the <application>libpq</application> front-end library do apply.
+ </para>
+
+ <para>
+ <application>clusterdb</application> will need to connect several times to the
+ <productname>PostgreSQL</productname> server, asking for the password each
+ time. It will probably be very convenient to have a PGPASSWORDFILE in that case.
+ </para>
+
+ </refsect1>
+
+
+ <refsect1>
+ <title>Options</title>
+
+ <para>
+ <application>clusterdb</application> accepts the following command line arguments:
+
+ <variablelist>
+ <varlistentry>
+ <term>-d <replaceable class="parameter">dbname</replaceable></term>
+ <term>--dbname <replaceable class="parameter">dbname</replaceable></term>
+ <listitem>
+ <para>
+ Specifies the name of the database to be clustered.
+ If this is not specified and <option>-a</option> (or
+ <option>--all</option>) is not used, the database name is read
+ from the environment variable <envar>PGDATABASE</envar>. If
+ that is not set, the user name specified for the connection is
+ used.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>-a</term>
+ <term>--all</term>
+ <listitem>
+ <para>
+ Cluster all databases.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>-t <replaceable class="parameter">table</replaceable></term>
+ <term>--table <replaceable class="parameter">table</replaceable></term>
+ <listitem>
+ <para>
+ Clusters <replaceable class="parameter">table</replaceable> only.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ </variablelist>
+ </para>
+
+ <para>
+ <application>clusterdb</application> also accepts
+ the following command line arguments for connection parameters:
+
+ <variablelist>
+ <varlistentry>
+ <term>-h <replaceable class="parameter">host</replaceable></term>
+ <term>--host <replaceable class="parameter">host</replaceable></term>
+ <listitem>
+ <para>
+ Specifies the host name of the machine on which the
+ server
+ is running. If host begins with a slash, it is used
+ as the directory for the Unix domain socket.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>-p <replaceable class="parameter">port</replaceable></term>
+ <term>--port <replaceable class="parameter">port</replaceable></term>
+ <listitem>
+ <para>
+ Specifies the Internet TCP/IP port or local Unix domain socket file
+ extension on which the server
+ is listening for connections.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>-U <replaceable class="parameter">username</replaceable></term>
+ <term>--username <replaceable class="parameter">username</replaceable></term>
+ <listitem>
+ <para>
+ User name to connect as
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>-W</term>
+ <term>--password</term>
+ <listitem>
+ <para>
+ Force password prompt.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>-e</term>
+ <term>--echo</term>
+ <listitem>
+ <para>
+ Echo the commands that <application>clusterdb</application> generates
+ and sends to the server.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>-q</term>
+ <term>--quiet</term>
+ <listitem>
+ <para>
+ Do not display a response.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </para>
+ </refsect1>
+
+
+ <refsect1>
+ <title>Diagnostics</title>
+
+ <para>
+ <variablelist>
+ <varlistentry>
+ <term><computeroutput>CLUSTER</computeroutput></term>
+ <listitem>
+ <para>
+ Everything went well.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><computeroutput>clusterdb: Cluster failed.</computeroutput></term>
+ <listitem>
+ <para>
+ Something went wrong. <application>clusterdb</application> is only a wrapper
+ script. See <xref linkend="SQL-CLUSTER" endterm="SQL-CLUSTER-title">
+ and <xref linkend="APP-PSQL"> for a detailed
+ discussion of error messages and potential problems. Note that this message
+ may appear once per table to be clustered.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ </variablelist>
+ </para>
+ </refsect1>
+
+
+ <refsect1>
+ <title>Environment</title>
+
+ <variablelist>
+ <varlistentry>
+ <term><envar>PGDATABASE</envar></term>
+ <term><envar>PGHOST</envar></term>
+ <term><envar>PGPORT</envar></term>
+ <term><envar>PGUSER</envar></term>
+
+ <listitem>
+ <para>
+ Default connection parameters.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </refsect1>
+
+
+ <refsect1>
+ <title>Examples</title>
+
+ <para>
+ To cluster the database <literal>test</literal>:
+<screen>
+<prompt>$ </prompt><userinput>clusterdb test</userinput>
+</screen>
+ </para>
+
+ <para>
+ To cluster a single table
+ <literal>foo</literal> in a database named
+ <literal>xyzzy</literal>:
+<screen>
+<prompt>$ </prompt><userinput>clusterdb --verbose --table foo xyzzy</userinput>
+</screen>
+ </para>
+
+ </refsect1>
+
+ <refsect1>
+ <title>See Also</title>
+
+ <simplelist type="inline">
+ <member><xref linkend="sql-cluster" endterm="sql-cluster-title"></member>
+ </simplelist>
+ </refsect1>
+
+</refentry>
+
+<!-- Keep this comment at the end of the file
+Local variables:
+mode: sgml
+sgml-omittag:nil
+sgml-shorttag:t
+sgml-minimize-attributes:nil
+sgml-always-quote-attributes:t
+sgml-indent-step:1
+sgml-indent-data:t
+sgml-parent-document:nil
+sgml-default-dtd-file:"../reference.ced"
+sgml-exposed-tags:nil
+sgml-local-catalogs:"/usr/lib/sgml/catalog"
+sgml-local-ecat-files:nil
+End:
+-->
diff --git a/doc/src/sgml/reference.sgml b/doc/src/sgml/reference.sgml
index 70af1fc6c9f..effe495f1da 100644
--- a/doc/src/sgml/reference.sgml
+++ b/doc/src/sgml/reference.sgml
@@ -1,5 +1,5 @@
<!-- reference.sgml
-$Header: /cvsroot/pgsql/doc/src/sgml/reference.sgml,v 1.35 2002/08/17 03:38:43 tgl Exp $
+$Header: /cvsroot/pgsql/doc/src/sgml/reference.sgml,v 1.36 2002/08/27 03:38:27 momjian Exp $
PostgreSQL Reference Manual
-->
@@ -165,6 +165,7 @@ Disable this chapter until we have more functions documented.
</para>
</partintro>
+ &clusterdb;
&createdb;
&createlang;
&createuser;
diff --git a/src/backend/commands/cluster.c b/src/backend/commands/cluster.c
index 7ca8e1dd329..e8d2aa7e7e3 100644
--- a/src/backend/commands/cluster.c
+++ b/src/backend/commands/cluster.c
@@ -11,7 +11,7 @@
*
*
* IDENTIFICATION
- * $Header: /cvsroot/pgsql/src/backend/commands/cluster.c,v 1.86 2002/08/11 21:17:34 tgl Exp $
+ * $Header: /cvsroot/pgsql/src/backend/commands/cluster.c,v 1.87 2002/08/27 03:38:27 momjian Exp $
*
*-------------------------------------------------------------------------
*/
@@ -45,11 +45,12 @@ typedef struct
IndexInfo *indexInfo;
Oid accessMethodOID;
Oid *classOID;
+ bool isclustered;
} IndexAttrs;
static Oid make_new_heap(Oid OIDOldHeap, const char *NewName);
static void copy_heap_data(Oid OIDNewHeap, Oid OIDOldHeap, Oid OIDOldIndex);
-static List *get_indexattr_list(Relation OldHeap);
+static List *get_indexattr_list(Relation OldHeap, Oid OldIndex);
static void recreate_indexattr(Oid OIDOldHeap, List *indexes);
static void swap_relfilenodes(Oid r1, Oid r2);
@@ -121,7 +122,7 @@ cluster(RangeVar *oldrelation, char *oldindexname)
RelationGetRelationName(OldHeap));
/* Save the information of all indexes on the relation. */
- indexes = get_indexattr_list(OldHeap);
+ indexes = get_indexattr_list(OldHeap, OIDOldIndex);
/* Drop relcache refcnts, but do NOT give up the locks */
index_close(OldIndex);
@@ -274,7 +275,7 @@ copy_heap_data(Oid OIDNewHeap, Oid OIDOldHeap, Oid OIDOldIndex)
* return a list of IndexAttrs structures.
*/
static List *
-get_indexattr_list(Relation OldHeap)
+get_indexattr_list(Relation OldHeap, Oid OldIndex)
{
List *indexes = NIL;
List *indlist;
@@ -305,6 +306,12 @@ get_indexattr_list(Relation OldHeap)
memcpy(attrs->classOID, indexForm->indclass,
sizeof(Oid) * attrs->indexInfo->ii_NumIndexAttrs);
+ /* We'll set indisclustered at index creation time on the
+ * index we are currently clustering, and reset it on other
+ * indexes.
+ */
+ attrs->isclustered = (OldIndex == indexOID ? true : false);
+
/* Name and access method of each index come from pg_class */
classTuple = SearchSysCache(RELOID,
ObjectIdGetDatum(indexOID),
@@ -343,6 +350,9 @@ recreate_indexattr(Oid OIDOldHeap, List *indexes)
Oid newIndexOID;
char newIndexName[NAMEDATALEN];
ObjectAddress object;
+ Form_pg_index index;
+ HeapTuple tuple;
+ Relation pg_index;
/* Create the new index under a temporary name */
snprintf(newIndexName, NAMEDATALEN, "pg_temp_%u", attrs->indexOID);
@@ -364,6 +374,20 @@ recreate_indexattr(Oid OIDOldHeap, List *indexes)
CommandCounterIncrement();
+ /* Set indisclustered to the correct value. Only one index is
+ * allowed to be clustered.
+ */
+ pg_index = heap_openr(IndexRelationName, RowExclusiveLock);
+ tuple = SearchSysCacheCopy(INDEXRELID,
+ ObjectIdGetDatum(attrs->indexOID),
+ 0, 0, 0);
+ index = (Form_pg_index) GETSTRUCT(tuple);
+ index->indisclustered = attrs->isclustered;
+ simple_heap_update(pg_index, &tuple->t_self, tuple);
+ CatalogUpdateIndexes(pg_index, tuple);
+ heap_freetuple(tuple);
+ heap_close(pg_index, NoLock);
+
/* Destroy new index with old filenode */
object.classId = RelOid_pg_class;
object.objectId = newIndexOID;
diff --git a/src/bin/scripts/Makefile b/src/bin/scripts/Makefile
index 4a574b464aa..db87a9efc01 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 00000000000..58430e2d6a9
--- /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
diff --git a/src/test/regress/expected/cluster.out b/src/test/regress/expected/cluster.out
index a76536ac8d4..6a2ba61e832 100644
--- a/src/test/regress/expected/cluster.out
+++ b/src/test/regress/expected/cluster.out
@@ -274,3 +274,14 @@ FROM pg_class c WHERE relname LIKE 'clstr_tst%' ORDER BY relname;
clstr_tst_s_rf_a_seq | S | f
(11 rows)
+-- Verify that indisclustered is correctly set
+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
+-------------
+ clstr_tst_c
+(1 row)
+
diff --git a/src/test/regress/sql/cluster.sql b/src/test/regress/sql/cluster.sql
index 599f6ebd82c..384a185d09e 100644
--- a/src/test/regress/sql/cluster.sql
+++ b/src/test/regress/sql/cluster.sql
@@ -79,3 +79,10 @@ SELECT conname FROM pg_constraint WHERE conrelid = 'clstr_tst'::regclass;
SELECT relname, relkind,
EXISTS(SELECT 1 FROM pg_class WHERE oid = c.reltoastrelid) AS hastoast
FROM pg_class c WHERE relname LIKE 'clstr_tst%' ORDER BY relname;
+
+-- Verify that indisclustered is correctly set
+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;
generated by cgit v1.2.3 (git 2.25.1) at 2025-05-28 17:23:00 +0000