Index: doc/src/sgml/ref/cluster.sgml =================================================================== RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/ref/cluster.sgml,v retrieving revision 1.19 diff -c -r1.19 cluster.sgml *** doc/src/sgml/ref/cluster.sgml 2002/08/11 02:43:57 1.19 --- doc/src/sgml/ref/cluster.sgml 2002/09/01 05:12:12 *************** *** 21,27 **** 1999-07-20 ! CLUSTER indexname ON tablename --- 21,27 ---- 1999-07-20 ! CLUSTER [ indexname ON tablename ] *************** *** 104,109 **** --- 104,117 ---- periodically re-cluster by issuing the command again. + + When a table is clustered, PostgreSQL + remembers on which index it was clustered. In calls to + CLUSTER without parameters, all the tables + that have been previously clustered will be clustered again on + the same indexes, using the saved information. + + 1998-09-08 *************** *** 140,148 **** sizes. ! CLUSTER preserves GRANT, inheritance, index, foreign key, and other ! ancillary information about the table. --- 148,163 ---- sizes. + + CLUSTER preserves GRANT, inheritance, index, foreign + key, and other ancillary information about the table. + + ! Because CLUSTER remembers the clustering information, ! one can cluster the tables one wants clustered the first time manually, and ! setup a timed event similar to VACUUM so that the tables ! are periodically and automatically clustered. Index: src/backend/commands/cluster.c =================================================================== RCS file: /projects/cvsroot/pgsql-server/src/backend/commands/cluster.c,v retrieving revision 1.87 diff -c -r1.87 cluster.c *** src/backend/commands/cluster.c 2002/08/27 03:38:27 1.87 --- src/backend/commands/cluster.c 2002/09/01 05:12:14 *************** *** 25,33 **** --- 25,35 ---- #include "catalog/index.h" #include "catalog/indexing.h" #include "catalog/catname.h" + #include "catalog/namespace.h" #include "commands/cluster.h" #include "commands/tablecmds.h" #include "miscadmin.h" + #include "utils/acl.h" #include "utils/fmgroids.h" #include "utils/lsyscache.h" #include "utils/syscache.h" *************** *** 48,60 **** 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, Oid OldIndex); static void recreate_indexattr(Oid OIDOldHeap, List *indexes); static void swap_relfilenodes(Oid r1, Oid r2); - /* * cluster * --- 50,74 ---- bool isclustered; } IndexAttrs; + /* This struct is used to pass around the information on tables to be + * clustered. We need this so we can make a list of them when invoked without + * a specific table/index pair. + */ + typedef struct + { + Oid tableOid; + Oid indexOid; + } relToCluster; + 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, Oid OldIndex); static void recreate_indexattr(Oid OIDOldHeap, List *indexes); static void swap_relfilenodes(Oid r1, Oid r2); + static void clusterRel(relToCluster *rv); + static bool checkClusterOwnership(Oid relOid); + static List *getTablesToCluster(Oid owner); /* * cluster * *************** *** 72,82 **** * Permissions checks were done already. */ void ! cluster(RangeVar *oldrelation, char *oldindexname) { ! Oid OIDOldHeap, ! OIDOldIndex, ! OIDNewHeap; Relation OldHeap, OldIndex; char NewHeapName[NAMEDATALEN]; --- 86,94 ---- * Permissions checks were done already. */ void ! clusterRel(relToCluster *rvtc) { ! Oid OIDNewHeap; Relation OldHeap, OldIndex; char NewHeapName[NAMEDATALEN]; *************** *** 87,111 **** * We grab exclusive access to the target rel and index for the * duration of the transaction. */ ! OldHeap = heap_openrv(oldrelation, AccessExclusiveLock); ! OIDOldHeap = RelationGetRelid(OldHeap); ! /* ! * The index is expected to be in the same namespace as the relation. ! */ ! OIDOldIndex = get_relname_relid(oldindexname, ! RelationGetNamespace(OldHeap)); ! if (!OidIsValid(OIDOldIndex)) ! elog(ERROR, "CLUSTER: cannot find index \"%s\" for table \"%s\"", ! oldindexname, RelationGetRelationName(OldHeap)); ! OldIndex = index_open(OIDOldIndex); LockRelation(OldIndex, AccessExclusiveLock); /* * Check that index is in fact an index on the given relation */ if (OldIndex->rd_index == NULL || ! OldIndex->rd_index->indrelid != OIDOldHeap) elog(ERROR, "CLUSTER: \"%s\" is not an index for table \"%s\"", RelationGetRelationName(OldIndex), RelationGetRelationName(OldHeap)); --- 99,114 ---- * We grab exclusive access to the target rel and index for the * duration of the transaction. */ ! OldHeap = heap_open(rvtc->tableOid, AccessExclusiveLock); ! OldIndex = index_open(rvtc->indexOid); LockRelation(OldIndex, AccessExclusiveLock); /* * Check that index is in fact an index on the given relation */ if (OldIndex->rd_index == NULL || ! OldIndex->rd_index->indrelid != rvtc->tableOid) elog(ERROR, "CLUSTER: \"%s\" is not an index for table \"%s\"", RelationGetRelationName(OldIndex), RelationGetRelationName(OldHeap)); *************** *** 122,128 **** RelationGetRelationName(OldHeap)); /* Save the information of all indexes on the relation. */ ! indexes = get_indexattr_list(OldHeap, OIDOldIndex); /* Drop relcache refcnts, but do NOT give up the locks */ index_close(OldIndex); --- 125,131 ---- RelationGetRelationName(OldHeap)); /* Save the information of all indexes on the relation. */ ! indexes = get_indexattr_list(OldHeap, rvtc->indexOid); /* Drop relcache refcnts, but do NOT give up the locks */ index_close(OldIndex); *************** *** 135,156 **** * particular, we can't create the new heap in a different namespace from * the old, or we will have problems with the TEMP status of temp tables. */ ! snprintf(NewHeapName, NAMEDATALEN, "pg_temp_%u", OIDOldHeap); ! OIDNewHeap = make_new_heap(OIDOldHeap, NewHeapName); /* We don't need CommandCounterIncrement() because make_new_heap did it. */ /* * Copy the heap data into the new table in the desired order. */ ! copy_heap_data(OIDNewHeap, OIDOldHeap, OIDOldIndex); /* To make the new heap's data visible (probably not needed?). */ CommandCounterIncrement(); /* Swap the relfilenodes of the old and new heaps. */ ! swap_relfilenodes(OIDOldHeap, OIDNewHeap); CommandCounterIncrement(); --- 138,159 ---- * particular, we can't create the new heap in a different namespace from * the old, or we will have problems with the TEMP status of temp tables. */ ! snprintf(NewHeapName, NAMEDATALEN, "pg_temp_%u", rvtc->tableOid); ! OIDNewHeap = make_new_heap(rvtc->tableOid, NewHeapName); /* We don't need CommandCounterIncrement() because make_new_heap did it. */ /* * Copy the heap data into the new table in the desired order. */ ! copy_heap_data(OIDNewHeap, rvtc->tableOid, rvtc->indexOid); /* To make the new heap's data visible (probably not needed?). */ CommandCounterIncrement(); /* Swap the relfilenodes of the old and new heaps. */ ! swap_relfilenodes(rvtc->tableOid, OIDNewHeap); CommandCounterIncrement(); *************** *** 171,177 **** * Recreate each index on the relation. We do not need * CommandCounterIncrement() because recreate_indexattr does it. */ ! recreate_indexattr(OIDOldHeap, indexes); } /* --- 174,180 ---- * Recreate each index on the relation. We do not need * CommandCounterIncrement() because recreate_indexattr does it. */ ! recreate_indexattr(rvtc->tableOid, indexes); } /* *************** *** 549,552 **** --- 552,655 ---- heap_freetuple(reltup2); heap_close(relRelation, RowExclusiveLock); + } + + void + cluster(ClusterStmt *stmt) + { + if (stmt->relation != NULL) + { + Oid indexOid, + tableOid; + relToCluster rvtc; + HeapTuple tuple; + + tableOid = RangeVarGetRelid(stmt->relation, false); + if (!checkClusterOwnership(tableOid)) + elog(ERROR, "CLUSTER: You do not own relation %s", + stmt->relation->relname); + + /* The index is expected to be in the same namespace as the + * relation. */ + tuple = SearchSysCache(RELOID, + ObjectIdGetDatum(tableOid), + 0, 0, 0); + if (!HeapTupleIsValid(tuple)) + elog(ERROR, "Cache lookup failed for relation %u", tableOid); + indexOid = get_relname_relid(stmt->indexname, + ((Form_pg_class) GETSTRUCT(tuple))->relnamespace); + ReleaseSysCache(tuple); + + /* XXX The namespace should be reported as well */ + if (!OidIsValid(indexOid)) + elog(ERROR, "CLUSTER: cannot find index \"%s\" for table \"%s\"", + stmt->indexname, stmt->relation->relname); + rvtc.tableOid = tableOid; + rvtc.indexOid = indexOid; + clusterRel(&rvtc); + } + else + { + relToCluster *rvtc; + List *rv; + + /* Ok, now that we've got them all, cluster them one by one */ + foreach (rv, getTablesToCluster(GetUserId())) + { + rvtc = (relToCluster *)lfirst(rv); + clusterRel(rvtc); + /* We don't need this entry anymore, so free it. */ + pfree(rvtc); + } + } + } + + /* Checks if the user owns the relation. Superusers + * are allowed to cluster any table. + */ + bool + checkClusterOwnership(Oid relOid) + { + /* Superusers bypass this check */ + return pg_class_ownercheck(relOid, GetUserId()); + } + + /* Get a list of tables that the current user owns and + * have indisclustered set. Return the list in a List * of rvsToCluster + * with the tableOid and the indexOid on which the table is already + * clustered. + */ + List * + getTablesToCluster(Oid owner) + { + Relation indRelation; + HeapScanDesc scan; + ScanKeyData entry; + HeapTuple indexTuple; + Form_pg_index index; + relToCluster *rvtc; + List *rvs = NIL; + + /* Get all indexes that have indisclustered set. + * System relations or nailed relations cannot ever have indisclustered + * set, as CLUSTER will refuse to set it when called with one of them + * as argument. + */ + indRelation = relation_openr(IndexRelationName, AccessShareLock); + ScanKeyEntryInitialize(&entry, 0, Anum_pg_index_indisclustered, + F_BOOLEQ, true); + scan = heap_beginscan(indRelation, SnapshotNow, 1, &entry); + while ((indexTuple = heap_getnext(scan, ForwardScanDirection)) != NULL) + { + index = (Form_pg_index) GETSTRUCT(indexTuple); + if (!checkClusterOwnership(index->indrelid)) + continue; + rvtc = (relToCluster *)palloc(sizeof(relToCluster)); + rvtc->indexOid = index->indexrelid; + rvtc->tableOid = index->indrelid; + rvs = lcons((void *)rvtc, rvs); + } + heap_endscan(scan); + relation_close(indRelation, AccessShareLock); + return rvs; } Index: src/backend/parser/gram.y =================================================================== RCS file: /projects/cvsroot/pgsql-server/src/backend/parser/gram.y,v retrieving revision 2.364 diff -c -r2.364 gram.y *** src/backend/parser/gram.y 2002/08/29 00:17:04 2.364 --- src/backend/parser/gram.y 2002/09/01 05:12:26 *************** *** 3740,3746 **** /***************************************************************************** * * QUERY: ! * cluster on * *****************************************************************************/ --- 3740,3746 ---- /***************************************************************************** * * QUERY: ! * cluster [ on ] * *****************************************************************************/ *************** *** 3750,3755 **** --- 3750,3762 ---- ClusterStmt *n = makeNode(ClusterStmt); n->relation = $4; n->indexname = $2; + $$ = (Node*)n; + } + | CLUSTER + { + ClusterStmt *n = makeNode(ClusterStmt); + n->relation = NULL; + n->indexname = NULL; $$ = (Node*)n; } ; Index: src/backend/tcop/utility.c =================================================================== RCS file: /projects/cvsroot/pgsql-server/src/backend/tcop/utility.c,v retrieving revision 1.175 diff -c -r1.175 utility.c *** src/backend/tcop/utility.c 2002/08/30 19:23:20 1.175 --- src/backend/tcop/utility.c 2002/09/01 05:12:28 *************** *** 695,703 **** { ClusterStmt *stmt = (ClusterStmt *) parsetree; ! CheckOwnership(stmt->relation, true); ! ! cluster(stmt->relation, stmt->indexname); } break; --- 695,701 ---- { ClusterStmt *stmt = (ClusterStmt *) parsetree; ! cluster(stmt); } break; Index: src/include/commands/cluster.h =================================================================== RCS file: /projects/cvsroot/pgsql-server/src/include/commands/cluster.h,v retrieving revision 1.15 diff -c -r1.15 cluster.h *** src/include/commands/cluster.h 2002/08/10 21:00:34 1.15 --- src/include/commands/cluster.h 2002/09/01 05:12:30 *************** *** 13,21 **** #ifndef CLUSTER_H #define CLUSTER_H /* * functions */ ! extern void cluster(RangeVar *oldrelation, char *oldindexname); #endif /* CLUSTER_H */ --- 13,22 ---- #ifndef CLUSTER_H #define CLUSTER_H + #include /* * functions */ ! extern void cluster(ClusterStmt *stmt); #endif /* CLUSTER_H */ Index: src/include/nodes/parsenodes.h =================================================================== RCS file: /projects/cvsroot/pgsql-server/src/include/nodes/parsenodes.h,v retrieving revision 1.203 diff -c -r1.203 parsenodes.h *** src/include/nodes/parsenodes.h 2002/08/30 19:23:20 1.203 --- src/include/nodes/parsenodes.h 2002/09/01 05:12:34 *************** *** 1466,1472 **** typedef struct ClusterStmt { NodeTag type; ! RangeVar *relation; /* relation being indexed */ char *indexname; /* original index defined */ } ClusterStmt; --- 1466,1472 ---- typedef struct ClusterStmt { NodeTag type; ! RangeVar *relation; /* relation being indexed, or NULL if all */ char *indexname; /* original index defined */ } ClusterStmt; Index: src/test/regress/expected/cluster.out =================================================================== RCS file: /projects/cvsroot/pgsql-server/src/test/regress/expected/cluster.out,v retrieving revision 1.3 diff -c -r1.3 cluster.out *** src/test/regress/expected/cluster.out 2002/08/27 03:38:27 1.3 --- src/test/regress/expected/cluster.out 2002/09/01 05:12:38 *************** *** 285,287 **** --- 285,351 ---- clstr_tst_c (1 row) + -- Verify that clustering all tables does in fact cluster the right ones + CREATE USER clstr_user; + CREATE TABLE clstr_1 (a INT PRIMARY KEY); + NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'clstr_1_pkey' for table 'clstr_1' + CREATE TABLE clstr_2 (a INT PRIMARY KEY); + NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'clstr_2_pkey' for table 'clstr_2' + CREATE TABLE clstr_3 (a INT PRIMARY KEY); + NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'clstr_3_pkey' for table 'clstr_3' + ALTER TABLE clstr_1 OWNER TO clstr_user; + ALTER TABLE clstr_3 OWNER TO clstr_user; + GRANT SELECT ON clstr_2 TO clstr_user; + INSERT INTO clstr_1 VALUES (2); + INSERT INTO clstr_1 VALUES (1); + INSERT INTO clstr_2 VALUES (2); + INSERT INTO clstr_2 VALUES (1); + INSERT INTO clstr_3 VALUES (2); + INSERT INTO clstr_3 VALUES (1); + 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; + a + --- + 1 + 2 + 1 + 2 + 2 + 1 + (6 rows) + + -- revert to the original state + DELETE FROM clstr_1; + DELETE FROM clstr_2; + DELETE FROM clstr_3; + INSERT INTO clstr_1 VALUES (2); + INSERT INTO clstr_1 VALUES (1); + INSERT INTO clstr_2 VALUES (2); + INSERT INTO clstr_2 VALUES (1); + INSERT INTO clstr_3 VALUES (2); + INSERT INTO clstr_3 VALUES (1); + -- this user can only cluster clstr_1 and clstr_3, but the latter + -- has not been clustered + SET SESSION AUTHORIZATION clstr_user; + CLUSTER; + SELECT * FROM clstr_1 UNION ALL + SELECT * FROM clstr_2 UNION ALL + SELECT * FROM clstr_3; + a + --- + 1 + 2 + 2 + 1 + 2 + 1 + (6 rows) + + -- clean up + \c - + DROP TABLE clstr_1; + DROP TABLE clstr_3; + DROP USER clstr_user; Index: src/test/regress/sql/cluster.sql =================================================================== RCS file: /projects/cvsroot/pgsql-server/src/test/regress/sql/cluster.sql,v retrieving revision 1.4 diff -c -r1.4 cluster.sql *** src/test/regress/sql/cluster.sql 2002/08/27 03:38:28 1.4 --- src/test/regress/sql/cluster.sql 2002/09/01 05:12:39 *************** *** 86,88 **** --- 86,132 ---- 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); + CREATE TABLE clstr_2 (a INT PRIMARY KEY); + CREATE TABLE clstr_3 (a INT PRIMARY KEY); + ALTER TABLE clstr_1 OWNER TO clstr_user; + ALTER TABLE clstr_3 OWNER TO clstr_user; + GRANT SELECT ON clstr_2 TO clstr_user; + INSERT INTO clstr_1 VALUES (2); + INSERT INTO clstr_1 VALUES (1); + INSERT INTO clstr_2 VALUES (2); + INSERT INTO clstr_2 VALUES (1); + INSERT INTO clstr_3 VALUES (2); + INSERT INTO clstr_3 VALUES (1); + 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; + + -- revert to the original state + DELETE FROM clstr_1; + DELETE FROM clstr_2; + DELETE FROM clstr_3; + INSERT INTO clstr_1 VALUES (2); + INSERT INTO clstr_1 VALUES (1); + INSERT INTO clstr_2 VALUES (2); + INSERT INTO clstr_2 VALUES (1); + INSERT INTO clstr_3 VALUES (2); + INSERT INTO clstr_3 VALUES (1); + + -- this user can only cluster clstr_1 and clstr_3, but the latter + -- has not been clustered + SET SESSION AUTHORIZATION clstr_user; + CLUSTER; + SELECT * FROM clstr_1 UNION ALL + SELECT * FROM clstr_2 UNION ALL + SELECT * FROM clstr_3; + -- clean up + \c - + DROP TABLE clstr_1; + DROP TABLE clstr_3; + DROP USER clstr_user;