Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly
Appearance settings

Commit fe263d1

Browse filesBrowse 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
Copy full SHA for fe263d1

File tree

Expand file treeCollapse file tree

12 files changed

+253
-61
lines changed
Filter options
Expand file treeCollapse file tree

12 files changed

+253
-61
lines changed

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

Copy file name to clipboardExpand all lines: doc/src/sgml/ref/reindex.sgml
+14-1Lines 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

Copy file name to clipboardExpand all lines: doc/src/sgml/ref/reindexdb.sgml
+22Lines 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

Copy file name to clipboardExpand all lines: src/backend/commands/indexcmds.c
+80-30Lines 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

Copy file name to clipboardExpand all lines: src/backend/parser/gram.y
+21-14Lines 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

Copy file name to clipboardExpand all lines: src/backend/tcop/utility.c
+8-7Lines 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

Copy file name to clipboardExpand all lines: src/bin/psql/tab-complete.c
+3-1Lines 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 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.