Skip to content

Commit fe263d1

Browse files
simonat2ndQuadrantsimonat2ndQuadrant
committed
REINDEX SCHEMA
Add new SCHEMA option to REINDEX and reindexdb. Sawada Masahiko Reviewed by Michael Paquier and Fabrízio de Royes Mello
1 parent 8001fe6 commit fe263d1

File tree

12 files changed

+253
-61
lines changed

12 files changed

+253
-61
lines changed

‎doc/src/sgml/ref/reindex.sgml

Lines changed: 14 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -21,7 +21,7 @@ PostgreSQL documentation
2121

2222
<refsynopsisdiv>
2323
<synopsis>
24-
REINDEX { INDEX | TABLE | DATABASE | SYSTEM } <replaceable class="PARAMETER">name</replaceable> [ FORCE ]
24+
REINDEX { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } <replaceable class="PARAMETER">name</replaceable> [ FORCE ]
2525
</synopsis>
2626
</refsynopsisdiv>
2727

@@ -100,6 +100,19 @@ REINDEX { INDEX | TABLE | DATABASE | SYSTEM } <replaceable class="PARAMETER">nam
100100
</listitem>
101101
</varlistentry>
102102

103+
<varlistentry>
104+
<term><literal>SCHEMA</literal></term>
105+
<listitem>
106+
<para>
107+
Recreate all indexes of the specified schema. If a table of this
108+
schema has a secondary <quote>TOAST</> table, that is reindexed as
109+
well. Indexes on shared system catalogs are also processed.
110+
This form of <command>REINDEX</command> cannot be executed inside a
111+
transaction block.
112+
</para>
113+
</listitem>
114+
</varlistentry>
115+
103116
<varlistentry>
104117
<term><literal>DATABASE</literal></term>
105118
<listitem>

‎doc/src/sgml/ref/reindexdb.sgml

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -24,6 +24,16 @@ PostgreSQL documentation
2424
<command>reindexdb</command>
2525
<arg rep="repeat"><replaceable>connection-option</replaceable></arg>
2626

27+
<arg choice="plain" rep="repeat">
28+
<arg choice="opt">
29+
<group choice="plain">
30+
<arg choice="plain"><option>--schema</option></arg>
31+
<arg choice="plain"><option>-S</option></arg>
32+
</group>
33+
<replaceable>table</replaceable>
34+
</arg>
35+
</arg>
36+
2737
<arg choice="plain" rep="repeat">
2838
<arg choice="opt">
2939
<group choice="plain">
@@ -161,6 +171,18 @@ PostgreSQL documentation
161171
</listitem>
162172
</varlistentry>
163173

174+
<varlistentry>
175+
<term><option>-S <replaceable class="parameter">schema</replaceable></></term>
176+
<term><option>--schema=<replaceable class="parameter">schema</replaceable></></term>
177+
<listitem>
178+
<para>
179+
Reindex <replaceable class="parameter">schema</replaceable> only.
180+
Multiple schemas can be reindexed by writing multiple
181+
<option>-S</> switches.
182+
</para>
183+
</listitem>
184+
</varlistentry>
185+
164186
<varlistentry>
165187
<term><option>-t <replaceable class="parameter">table</replaceable></></term>
166188
<term><option>--table=<replaceable class="parameter">table</replaceable></></term>

‎src/backend/commands/indexcmds.c

Lines changed: 80 additions & 30 deletions
Original file line numberDiff line numberDiff line change
@@ -1777,34 +1777,58 @@ ReindexTable(RangeVar *relation)
17771777
}
17781778

17791779
/*
1780-
* ReindexDatabase
1781-
* Recreate indexes of a database.
1780+
* ReindexObject
1781+
* Recreate indexes of object whose type is defined by objectKind.
17821782
*
17831783
* To reduce the probability of deadlocks, each table is reindexed in a
17841784
* separate transaction, so we can release the lock on it right away.
17851785
* That means this must not be called within a user transaction block!
17861786
*/
17871787
Oid
1788-
ReindexDatabase(const char *databaseName, bool do_system, bool do_user)
1788+
ReindexObject(const char *objectName, ReindexObjectType objectKind)
17891789
{
1790+
Oid objectOid;
17901791
Relation relationRelation;
17911792
HeapScanDesc scan;
1793+
ScanKeyData *scan_keys = NULL;
17921794
HeapTuple tuple;
17931795
MemoryContext private_context;
17941796
MemoryContext old;
17951797
List *relids = NIL;
17961798
ListCell *l;
1799+
int num_keys;
17971800

1798-
AssertArg(databaseName);
1801+
AssertArg(objectName);
1802+
Assert(objectKind == REINDEX_OBJECT_SCHEMA ||
1803+
objectKind == REINDEX_OBJECT_SYSTEM ||
1804+
objectKind == REINDEX_OBJECT_DATABASE);
17991805

1800-
if (strcmp(databaseName, get_database_name(MyDatabaseId)) != 0)
1801-
ereport(ERROR,
1802-
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
1803-
errmsg("can only reindex the currently open database")));
1806+
/*
1807+
* Get OID of object to reindex, being the database currently being
1808+
* used by session for a database or for system catalogs, or the schema
1809+
* defined by caller. At the same time do permission checks that need
1810+
* different processing depending on the object type.
1811+
*/
1812+
if (objectKind == REINDEX_OBJECT_SCHEMA)
1813+
{
1814+
objectOid = get_namespace_oid(objectName, false);
18041815

1805-
if (!pg_database_ownercheck(MyDatabaseId, GetUserId()))
1806-
aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_DATABASE,
1807-
databaseName);
1816+
if (!pg_namespace_ownercheck(objectOid, GetUserId()))
1817+
aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_NAMESPACE,
1818+
objectName);
1819+
}
1820+
else
1821+
{
1822+
objectOid = MyDatabaseId;
1823+
1824+
if (strcmp(objectName, get_database_name(MyDatabaseId)) != 0)
1825+
ereport(ERROR,
1826+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
1827+
errmsg("can only reindex the currently open database")));
1828+
if (!pg_database_ownercheck(MyDatabaseId, GetUserId()))
1829+
aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_DATABASE,
1830+
objectName);
1831+
}
18081832

18091833
/*
18101834
* Create a memory context that will survive forced transaction commits we
@@ -1813,32 +1837,58 @@ ReindexDatabase(const char *databaseName, bool do_system, bool do_user)
18131837
* abort cleanup logic.
18141838
*/
18151839
private_context = AllocSetContextCreate(PortalContext,
1816-
"ReindexDatabase",
1840+
(objectKind == REINDEX_OBJECT_SCHEMA) ?
1841+
"ReindexSchema" : "ReindexDatabase",
18171842
ALLOCSET_DEFAULT_MINSIZE,
18181843
ALLOCSET_DEFAULT_INITSIZE,
18191844
ALLOCSET_DEFAULT_MAXSIZE);
18201845

18211846
/*
1822-
* We always want to reindex pg_class first. This ensures that if there
1823-
* is any corruption in pg_class' indexes, they will be fixed before we
1824-
* process any other tables. This is critical because reindexing itself
1825-
* will try to update pg_class.
1847+
* We always want to reindex pg_class first when reindexing system
1848+
* catalogs or a database. This ensures that if there is any corruption
1849+
* in pg_class' indexes, they will be fixed before we process any other
1850+
* tables. This is critical because reindexing itself will try to
1851+
* update pg_class.
18261852
*/
1827-
if (do_system)
1853+
if (objectKind == REINDEX_OBJECT_DATABASE ||
1854+
objectKind == REINDEX_OBJECT_SYSTEM ||
1855+
(objectKind == REINDEX_OBJECT_SCHEMA &&
1856+
IsSystemNamespace(objectOid)))
18281857
{
18291858
old = MemoryContextSwitchTo(private_context);
18301859
relids = lappend_oid(relids, RelationRelationId);
18311860
MemoryContextSwitchTo(old);
18321861
}
18331862

1863+
/*
1864+
* Define the search keys to find the objects to reindex. For a schema,
1865+
* we search target relations using relnamespace and relkind, something
1866+
* not necessary for a database-wide operation.
1867+
*/
1868+
if (objectKind == REINDEX_OBJECT_SCHEMA)
1869+
{
1870+
scan_keys = palloc(sizeof(ScanKeyData) * 2);
1871+
ScanKeyInit(&scan_keys[0],
1872+
Anum_pg_class_relnamespace,
1873+
BTEqualStrategyNumber, F_OIDEQ,
1874+
ObjectIdGetDatum(objectOid));
1875+
ScanKeyInit(&scan_keys[1],
1876+
Anum_pg_class_relkind,
1877+
BTEqualStrategyNumber, F_CHAREQ,
1878+
'r');
1879+
num_keys = 2;
1880+
}
1881+
else
1882+
num_keys = 0;
1883+
18341884
/*
18351885
* Scan pg_class to build a list of the relations we need to reindex.
18361886
*
18371887
* We only consider plain relations and materialized views here (toast
18381888
* rels will be processed indirectly by reindex_relation).
18391889
*/
18401890
relationRelation = heap_open(RelationRelationId, AccessShareLock);
1841-
scan = heap_beginscan_catalog(relationRelation, 0, NULL);
1891+
scan = heap_beginscan_catalog(relationRelation, num_keys, scan_keys);
18421892
while ((tuple = heap_getnext(scan, ForwardScanDirection)) != NULL)
18431893
{
18441894
Form_pg_class classtuple = (Form_pg_class) GETSTRUCT(tuple);
@@ -1854,19 +1904,17 @@ ReindexDatabase(const char *databaseName, bool do_system, bool do_user)
18541904
continue;
18551905

18561906
/* Check user/system classification, and optionally skip */
1857-
if (IsSystemClass(relid, classtuple))
1858-
{
1859-
if (!do_system)
1860-
continue;
1861-
}
1862-
else
1863-
{
1864-
if (!do_user)
1865-
continue;
1866-
}
1907+
if (!IsSystemClass(relid, classtuple) &&
1908+
objectKind == REINDEX_OBJECT_SYSTEM)
1909+
continue;
18671910

1911+
/*
1912+
* Already have it in the case of system catalogs being all
1913+
* reindexed, of a database or of a system catalog being reindexed
1914+
* as a schema.
1915+
*/
18681916
if (HeapTupleGetOid(tuple) == RelationRelationId)
1869-
continue; /* got it already */
1917+
continue;
18701918

18711919
old = MemoryContextSwitchTo(private_context);
18721920
relids = lappend_oid(relids, relid);
@@ -1898,6 +1946,8 @@ ReindexDatabase(const char *databaseName, bool do_system, bool do_user)
18981946
StartTransactionCommand();
18991947

19001948
MemoryContextDelete(private_context);
1949+
if (scan_keys)
1950+
pfree(scan_keys);
19011951

1902-
return MyDatabaseId;
1952+
return objectOid;
19031953
}

‎src/backend/parser/gram.y

Lines changed: 21 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -404,7 +404,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
404404
%type <boolean> copy_from opt_program
405405

406406
%type <ival> opt_column event cursor_options opt_hold opt_set_data
407-
%type <objtype> reindex_type drop_type comment_type security_label_type
407+
%type <objtype> drop_type comment_type security_label_type
408408

409409
%type <node> fetch_args limit_clause select_limit_value
410410
offset_clause select_offset_value
@@ -7198,41 +7198,48 @@ opt_if_exists: IF_P EXISTS { $$ = TRUE; }
71987198
*****************************************************************************/
71997199

72007200
ReindexStmt:
7201-
REINDEX reindex_type qualified_name opt_force
7201+
REINDEX INDEX qualified_name opt_force
72027202
{
72037203
ReindexStmt *n = makeNode(ReindexStmt);
7204-
n->kind = $2;
7204+
n->kind = REINDEX_OBJECT_INDEX;
72057205
n->relation = $3;
72067206
n->name = NULL;
72077207
$$ = (Node *)n;
72087208
}
7209+
| REINDEX TABLE qualified_name opt_force
7210+
{
7211+
ReindexStmt *n = makeNode(ReindexStmt);
7212+
n->kind = REINDEX_OBJECT_TABLE;
7213+
n->relation = $3;
7214+
n->name = NULL;
7215+
$$ = (Node *)n;
7216+
}
7217+
| REINDEX SCHEMA name opt_force
7218+
{
7219+
ReindexStmt *n = makeNode(ReindexStmt);
7220+
n->kind = REINDEX_OBJECT_SCHEMA;
7221+
n->name = $3;
7222+
n->relation = NULL;
7223+
$$ = (Node *)n;
7224+
}
72097225
| REINDEX SYSTEM_P name opt_force
72107226
{
72117227
ReindexStmt *n = makeNode(ReindexStmt);
7212-
n->kind = OBJECT_DATABASE;
7228+
n->kind = REINDEX_OBJECT_SYSTEM;
72137229
n->name = $3;
72147230
n->relation = NULL;
7215-
n->do_system = true;
7216-
n->do_user = false;
72177231
$$ = (Node *)n;
72187232
}
72197233
| REINDEX DATABASE name opt_force
72207234
{
72217235
ReindexStmt *n = makeNode(ReindexStmt);
7222-
n->kind = OBJECT_DATABASE;
7236+
n->kind = REINDEX_OBJECT_DATABASE;
72237237
n->name = $3;
72247238
n->relation = NULL;
7225-
n->do_system = true;
7226-
n->do_user = true;
72277239
$$ = (Node *)n;
72287240
}
72297241
;
72307242

7231-
reindex_type:
7232-
INDEX { $$ = OBJECT_INDEX; }
7233-
| TABLE { $$ = OBJECT_TABLE; }
7234-
;
7235-
72367243
opt_force: FORCE { $$ = TRUE; }
72377244
| /* EMPTY */ { $$ = FALSE; }
72387245
;

‎src/backend/tcop/utility.c

Lines changed: 8 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -749,14 +749,15 @@ standard_ProcessUtility(Node *parsetree,
749749
PreventCommandDuringRecovery("REINDEX");
750750
switch (stmt->kind)
751751
{
752-
case OBJECT_INDEX:
752+
case REINDEX_OBJECT_INDEX:
753753
ReindexIndex(stmt->relation);
754754
break;
755-
case OBJECT_TABLE:
756-
case OBJECT_MATVIEW:
755+
case REINDEX_OBJECT_TABLE:
757756
ReindexTable(stmt->relation);
758757
break;
759-
case OBJECT_DATABASE:
758+
case REINDEX_OBJECT_SCHEMA:
759+
case REINDEX_OBJECT_SYSTEM:
760+
case REINDEX_OBJECT_DATABASE:
760761

761762
/*
762763
* This cannot run inside a user transaction block; if
@@ -765,9 +766,9 @@ standard_ProcessUtility(Node *parsetree,
765766
* intended effect!
766767
*/
767768
PreventTransactionChain(isTopLevel,
768-
"REINDEX DATABASE");
769-
ReindexDatabase(stmt->name,
770-
stmt->do_system, stmt->do_user);
769+
(stmt->kind == REINDEX_OBJECT_SCHEMA) ?
770+
"REINDEX SCHEMA" : "REINDEX DATABASE");
771+
ReindexObject(stmt->name, stmt->kind);
771772
break;
772773
default:
773774
elog(ERROR, "unrecognized object type: %d",

‎src/bin/psql/tab-complete.c

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -3331,7 +3331,7 @@ psql_completion(const char *text, int start, int end)
33313331
else if (pg_strcasecmp(prev_wd, "REINDEX") == 0)
33323332
{
33333333
static const char *const list_REINDEX[] =
3334-
{"TABLE", "INDEX", "SYSTEM", "DATABASE", NULL};
3334+
{"TABLE", "INDEX", "SYSTEM", "SCHEMA", "DATABASE", NULL};
33353335

33363336
COMPLETE_WITH_LIST(list_REINDEX);
33373337
}
@@ -3341,6 +3341,8 @@ psql_completion(const char *text, int start, int end)
33413341
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tm, NULL);
33423342
else if (pg_strcasecmp(prev_wd, "INDEX") == 0)
33433343
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_indexes, NULL);
3344+
else if (pg_strcasecmp(prev_wd, "SCHEMA") == 0 )
3345+
COMPLETE_WITH_QUERY(Query_for_list_of_schemas);
33443346
else if (pg_strcasecmp(prev_wd, "SYSTEM") == 0 ||
33453347
pg_strcasecmp(prev_wd, "DATABASE") == 0)
33463348
COMPLETE_WITH_QUERY(Query_for_list_of_databases);

0 commit comments

Comments
 (0)