On Wed, Oct 22, 2014 at 9:21 PM, Alvaro Herrera <alvhe...@2ndquadrant.com>
wrote:
>
> Sawada Masahiko wrote:
>
> > Thank you for reviewing.
> > I agree 2) - 5).
> > Attached patch is latest version patch I modified above.
> > Also, I noticed I had forgotten to add the patch regarding document of
> > reindexdb.
>
> Please don't use pg_catalog in the regression test. That way we will
> need to update the expected file whenever a new catalog is added, which
> seems pointless. Maybe create a schema with a couple of tables
> specifically for this, instead.
>
Attached new regression test.
Isn't better join the two patches in just one?
Regards,
--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
>> Timbira: http://www.timbira.com.br
>> Blog: http://fabriziomello.github.io
>> Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello
>> Github: http://github.com/fabriziomello
diff --git a/doc/src/sgml/ref/reindex.sgml b/doc/src/sgml/ref/reindex.sgml
index cabae19..e47604c 100644
--- a/doc/src/sgml/ref/reindex.sgml
+++ b/doc/src/sgml/ref/reindex.sgml
@@ -21,7 +21,7 @@ PostgreSQL documentation
<refsynopsisdiv>
<synopsis>
-REINDEX { INDEX | TABLE | DATABASE | SYSTEM } <replaceable class="PARAMETER">name</replaceable> [ FORCE ]
+REINDEX { INDEX | TABLE | ALL IN SCHEMA | DATABASE | SYSTEM } <replaceable class="PARAMETER">name</replaceable> [ FORCE ]
</synopsis>
</refsynopsisdiv>
@@ -101,6 +101,19 @@ REINDEX { INDEX | TABLE | DATABASE | SYSTEM } <replaceable class="PARAMETER">nam
</varlistentry>
<varlistentry>
+ <term><literal>ALL IN SCHEMA</literal></term>
+ <listitem>
+ <para>
+ Recreate all indexes of the specified schema. If the table has a
+ secondary <quote>TOAST</> table, that is reindexed as well.
+ Indexes on shared system catalogs are also processed.
+ This form of <command>REINDEX</command> cannot be executed inside a
+ transaction block.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><literal>DATABASE</literal></term>
<listitem>
<para>
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index 3c1e90e..7c25e02 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -1770,34 +1770,52 @@ ReindexTable(RangeVar *relation)
}
/*
- * ReindexDatabase
- * Recreate indexes of a database.
+ * ReindexDatabaseOrSchema
+ * Recreate indexes of a database or schema.
*
+ * kind means the type of object, database or schema.
* To reduce the probability of deadlocks, each table is reindexed in a
* separate transaction, so we can release the lock on it right away.
* That means this must not be called within a user transaction block!
*/
Oid
-ReindexDatabase(const char *databaseName, bool do_system, bool do_user)
+ReindexDatabaseOrSchema(const char *objectName, bool do_system, bool do_user, ObjectType kind)
{
+ Oid objectOid;
Relation relationRelation;
HeapScanDesc scan;
+ ScanKeyData *key = NULL;
HeapTuple tuple;
MemoryContext private_context;
MemoryContext old;
List *relids = NIL;
ListCell *l;
+ bool do_database = (kind == OBJECT_DATABASE);
+ int nkeys;
- AssertArg(databaseName);
+ AssertArg(objectName);
+ Assert(kind == OBJECT_DATABASE || kind == OBJECT_SCHEMA);
- if (strcmp(databaseName, get_database_name(MyDatabaseId)) != 0)
+ /* Get OID of object for result */
+ if (do_database)
+ objectOid = MyDatabaseId;
+ else
+ objectOid = get_namespace_oid(objectName, false);
+
+ if (!do_database)
+ {
+ do_system = IsSystemNamespace(objectOid);
+ do_user = !do_system;
+ }
+
+ if (do_database && strcmp(objectName, get_database_name(MyDatabaseId)) != 0)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("can only reindex the currently open database")));
- if (!pg_database_ownercheck(MyDatabaseId, GetUserId()))
+ if (do_database && !pg_database_ownercheck(MyDatabaseId, GetUserId()))
aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_DATABASE,
- databaseName);
+ objectName);
/*
* Create a memory context that will survive forced transaction commits we
@@ -1806,7 +1824,8 @@ ReindexDatabase(const char *databaseName, bool do_system, bool do_user)
* abort cleanup logic.
*/
private_context = AllocSetContextCreate(PortalContext,
- "ReindexDatabase",
+ (do_database) ?
+ "ReindexDatabase" : "ReindexSchema",
ALLOCSET_DEFAULT_MINSIZE,
ALLOCSET_DEFAULT_INITSIZE,
ALLOCSET_DEFAULT_MAXSIZE);
@@ -1824,6 +1843,23 @@ ReindexDatabase(const char *databaseName, bool do_system, bool do_user)
MemoryContextSwitchTo(old);
}
+ /* For schema, we search target relations by relnamespce and relkind */
+ if (!do_database)
+ {
+ key = palloc(sizeof(ScanKeyData) * 2);
+ ScanKeyInit(&key[0],
+ Anum_pg_class_relnamespace,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(objectOid));
+ ScanKeyInit(&key[1],
+ Anum_pg_class_relkind,
+ BTEqualStrategyNumber, F_CHAREQ,
+ 'r');
+ nkeys = 2;
+ }
+ else
+ nkeys = 0;
+
/*
* Scan pg_class to build a list of the relations we need to reindex.
*
@@ -1831,7 +1867,7 @@ ReindexDatabase(const char *databaseName, bool do_system, bool do_user)
* rels will be processed indirectly by reindex_relation).
*/
relationRelation = heap_open(RelationRelationId, AccessShareLock);
- scan = heap_beginscan_catalog(relationRelation, 0, NULL);
+ scan = heap_beginscan_catalog(relationRelation, nkeys, key);
while ((tuple = heap_getnext(scan, ForwardScanDirection)) != NULL)
{
Form_pg_class classtuple = (Form_pg_class) GETSTRUCT(tuple);
@@ -1892,5 +1928,5 @@ ReindexDatabase(const char *databaseName, bool do_system, bool do_user)
MemoryContextDelete(private_context);
- return MyDatabaseId;
+ return objectOid;
}
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 0de9584..f59f880 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -7181,6 +7181,14 @@ ReindexStmt:
n->do_user = false;
$$ = (Node *)n;
}
+ | REINDEX ALL IN_P SCHEMA name opt_force
+ {
+ ReindexStmt *n = makeNode(ReindexStmt);
+ n->kind = OBJECT_SCHEMA;
+ n->name = $5;
+ n->relation = NULL;
+ $$ = (Node *)n;
+ }
| REINDEX DATABASE name opt_force
{
ReindexStmt *n = makeNode(ReindexStmt);
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index 4a2a339..b5bda55 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -755,6 +755,7 @@ standard_ProcessUtility(Node *parsetree,
case OBJECT_MATVIEW:
ReindexTable(stmt->relation);
break;
+ case OBJECT_SCHEMA:
case OBJECT_DATABASE:
/*
@@ -764,9 +765,10 @@ standard_ProcessUtility(Node *parsetree,
* intended effect!
*/
PreventTransactionChain(isTopLevel,
- "REINDEX DATABASE");
- ReindexDatabase(stmt->name,
- stmt->do_system, stmt->do_user);
+ (stmt->kind == OBJECT_SCHEMA) ?
+ "REINDEX ALL IN SCHEMA" : "REINDEX DATABASE");
+ ReindexDatabaseOrSchema(stmt->name,
+ stmt->do_system, stmt->do_user, stmt->kind);
break;
default:
elog(ERROR, "unrecognized object type: %d",
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 886188c..106ec8a 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -3330,7 +3330,7 @@ psql_completion(const char *text, int start, int end)
else if (pg_strcasecmp(prev_wd, "REINDEX") == 0)
{
static const char *const list_REINDEX[] =
- {"TABLE", "INDEX", "SYSTEM", "DATABASE", NULL};
+ {"TABLE", "INDEX", "SYSTEM", "ALL IN SCHEMA", "DATABASE", NULL};
COMPLETE_WITH_LIST(list_REINDEX);
}
@@ -3340,6 +3340,8 @@ psql_completion(const char *text, int start, int end)
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tm, NULL);
else if (pg_strcasecmp(prev_wd, "INDEX") == 0)
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_indexes, NULL);
+ else if (pg_strcasecmp(prev_wd, "ALL IN SCHEMA") == 0 )
+ COMPLETE_WITH_QUERY(Query_for_list_of_schemas);
else if (pg_strcasecmp(prev_wd, "SYSTEM") == 0 ||
pg_strcasecmp(prev_wd, "DATABASE") == 0)
COMPLETE_WITH_QUERY(Query_for_list_of_databases);
diff --git a/src/include/commands/defrem.h b/src/include/commands/defrem.h
index 0ebdbc1..330d05c 100644
--- a/src/include/commands/defrem.h
+++ b/src/include/commands/defrem.h
@@ -30,8 +30,8 @@ extern Oid DefineIndex(Oid relationId,
bool quiet);
extern Oid ReindexIndex(RangeVar *indexRelation);
extern Oid ReindexTable(RangeVar *relation);
-extern Oid ReindexDatabase(const char *databaseName,
- bool do_system, bool do_user);
+extern Oid ReindexDatabaseOrSchema(const char *databaseName,
+ bool do_system, bool do_user, ObjectType kind);
extern char *makeObjectName(const char *name1, const char *name2,
const char *label);
extern char *ChooseRelationName(const char *name1, const char *name2,
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index 8326e94..07bd854 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -2786,3 +2786,22 @@ explain (costs off)
Index Cond: ((thousand = 1) AND (tenthous = 1001))
(2 rows)
+--
+-- Reindex All In Schema
+--
+REINDEX ALL IN SCHEMA tobereindexed; -- fail because schema doesn't exists
+ERROR: schema "tobereindexed" does not exist
+CREATE SCHEMA tobereindexed;
+CREATE TABLE tobereindexed.table1(col1 SERIAL PRIMARY KEY);
+CREATE TABLE tobereindexed.table2(col1 SERIAL PRIMARY KEY, col2 VARCHAR(100) NOT NULL);
+CREATE INDEX ON tobereindexed.table2(col2);
+REINDEX ALL IN SCHEMA tobereindexed;
+NOTICE: table "tobereindexed.table1" was reindexed
+NOTICE: table "tobereindexed.table2" was reindexed
+BEGIN;
+REINDEX ALL IN SCHEMA tobereindexed;
+ERROR: REINDEX ALL IN SCHEMA cannot run inside a transaction block
+END;
+DROP TABLE tobereindexed.table2;
+DROP TABLE tobereindexed.table1;
+DROP SCHEMA tobereindexed;
diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql
index d4d24ef..5d6bcc0 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -938,3 +938,19 @@ ORDER BY thousand;
explain (costs off)
select * from tenk1 where (thousand, tenthous) in ((1,1001), (null,null));
+
+--
+-- Reindex All In Schema
+--
+REINDEX ALL IN SCHEMA tobereindexed; -- fail because schema doesn't exists
+CREATE SCHEMA tobereindexed;
+CREATE TABLE tobereindexed.table1(col1 SERIAL PRIMARY KEY);
+CREATE TABLE tobereindexed.table2(col1 SERIAL PRIMARY KEY, col2 VARCHAR(100) NOT NULL);
+CREATE INDEX ON tobereindexed.table2(col2);
+REINDEX ALL IN SCHEMA tobereindexed;
+BEGIN;
+REINDEX ALL IN SCHEMA tobereindexed;
+END;
+DROP TABLE tobereindexed.table2;
+DROP TABLE tobereindexed.table1;
+DROP SCHEMA tobereindexed;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
