From c0ad595386b6027449ada778162c599e4b09354a Mon Sep 17 00:00:00 2001 From: Bruce Momjian Date: Tue, 27 Aug 2002 03:38:28 +0000 Subject: [PATCH] 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 --- doc/src/sgml/ref/allfiles.sgml | 3 +- doc/src/sgml/ref/clusterdb.sgml | 280 ++++++++++++++++++++++++++ doc/src/sgml/reference.sgml | 3 +- src/backend/commands/cluster.c | 32 ++- src/bin/scripts/Makefile | 5 +- src/bin/scripts/clusterdb | 176 ++++++++++++++++ src/test/regress/expected/cluster.out | 11 + src/test/regress/sql/cluster.sql | 7 + 8 files changed, 509 insertions(+), 8 deletions(-) create mode 100644 doc/src/sgml/ref/clusterdb.sgml create mode 100644 src/bin/scripts/clusterdb diff --git a/doc/src/sgml/ref/allfiles.sgml b/doc/src/sgml/ref/allfiles.sgml index c81cded117..79221044c2 100644 --- a/doc/src/sgml/ref/allfiles.sgml +++ b/doc/src/sgml/ref/allfiles.sgml @@ -1,5 +1,5 @@ @@ -120,6 +120,7 @@ Complete list of usable sgml source files in this directory. + diff --git a/doc/src/sgml/ref/clusterdb.sgml b/doc/src/sgml/ref/clusterdb.sgml new file mode 100644 index 0000000000..b650ce24e4 --- /dev/null +++ b/doc/src/sgml/ref/clusterdb.sgml @@ -0,0 +1,280 @@ + + + + + clusterdb + 1 + Application + + + + clusterdb + cluster a PostgreSQL database + + + + + clusterdb + connection-options + --table | -t 'table + dbname + + clusterdb + connection-options + --all-a + + + + + + Description + + + clusterdb is a utility for clustering tables inside a + PostgreSQL database. + + + + clusterdb is a shell script wrapper around the + backend command + via + the PostgreSQL interactive terminal + . There is no effective + difference between clustering databases via this or other methods. + psql 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 psql + and the libpq front-end library do apply. + + + + clusterdb will need to connect several times to the + PostgreSQL server, asking for the password each + time. It will probably be very convenient to have a PGPASSWORDFILE in that case. + + + + + + + Options + + + clusterdb accepts the following command line arguments: + + + + -d dbname + --dbname dbname + + + Specifies the name of the database to be clustered. + If this is not specified and (or + ) is not used, the database name is read + from the environment variable PGDATABASE. If + that is not set, the user name specified for the connection is + used. + + + + + + -a + --all + + + Cluster all databases. + + + + + + -t table + --table table + + + Clusters table only. + + + + + + + + + clusterdb also accepts + the following command line arguments for connection parameters: + + + + -h host + --host host + + + 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. + + + + + + -p port + --port port + + + Specifies the Internet TCP/IP port or local Unix domain socket file + extension on which the server + is listening for connections. + + + + + + -U username + --username username + + + User name to connect as + + + + + + -W + --password + + + Force password prompt. + + + + + + -e + --echo + + + Echo the commands that clusterdb generates + and sends to the server. + + + + + + -q + --quiet + + + Do not display a response. + + + + + + + + + + Diagnostics + + + + + CLUSTER + + + Everything went well. + + + + + + clusterdb: Cluster failed. + + + Something went wrong. clusterdb is only a wrapper + script. See + and for a detailed + discussion of error messages and potential problems. Note that this message + may appear once per table to be clustered. + + + + + + + + + + + Environment + + + + PGDATABASE + PGHOST + PGPORT + PGUSER + + + + Default connection parameters. + + + + + + + + + Examples + + + To cluster the database test: + +$ clusterdb test + + + + + To cluster a single table + foo in a database named + xyzzy: + +$ clusterdb --verbose --table foo xyzzy + + + + + + + See Also + + + + + + + + + diff --git a/doc/src/sgml/reference.sgml b/doc/src/sgml/reference.sgml index 70af1fc6c9..effe495f1d 100644 --- a/doc/src/sgml/reference.sgml +++ b/doc/src/sgml/reference.sgml @@ -1,5 +1,5 @@ @@ -165,6 +165,7 @@ Disable this chapter until we have more functions documented. + &clusterdb; &createdb; &createlang; &createuser; diff --git a/src/backend/commands/cluster.c b/src/backend/commands/cluster.c index 7ca8e1dd32..e8d2aa7e7e 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 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 ." + 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 a76536ac8d..6a2ba61e83 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 599f6ebd82..384a185d09 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; -- 2.40.0