Skip to content

Commit ee23ec8

Browse files
schmiddyschmiddy
committed
Initial import of indexes-only building patch.
Patch from Beena Emerson.
1 parent 5b6a162 commit ee23ec8

File tree

3 files changed

+319
-18
lines changed

3 files changed

+319
-18
lines changed

‎bin/pg_repack.c

Lines changed: 275 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -177,6 +177,8 @@ static bool preliminary_checks(char *errbuf, size_t errsize);
177177
static void repack_all_databases(const char *order_by);
178178
static bool repack_one_database(const char *order_by, char *errbuf, size_t errsize);
179179
static void repack_one_table(const repack_table *table, const char *order_by);
180+
static bool repack_one_index(Oid table, const char *table_name, Oid index, const char *schema_name);
181+
static bool repack_all_indexes(char *errbuf, size_t errsize);
180182
static void repack_cleanup(bool fatal, const repack_table *table);
181183
static bool rebuild_indexes(const repack_table *table);
182184

@@ -202,6 +204,8 @@ static SimpleStringList table_list = {NULL, NULL};
202204
static char *orderby = NULL;
203205
static char *tablespace = NULL;
204206
static bool moveidx = false;
207+
static char *r_index = NULL;
208+
static bool only_indexes = false;
205209
static int wait_timeout = 60; /* in seconds */
206210
static int jobs = 0; /* number of concurrent worker conns. */
207211

@@ -221,6 +225,8 @@ static pgut_option options[] =
221225
{ 's', 'o', "order-by", &orderby },
222226
{ 's', 's', "tablespace", &tablespace },
223227
{ 'b', 'S', "moveidx", &moveidx },
228+
{ 's', 'i', "index", &r_index },
229+
{ 'b', 'x', "only-index", &only_indexes },
224230
{ 'i', 'T', "wait-timeout", &wait_timeout },
225231
{ 'B', 'Z', "no-analyze", &analyze },
226232
{ 'i', 'j', "jobs", &jobs },
@@ -231,6 +237,7 @@ int
231237
main(int argc, char *argv[])
232238
{
233239
int i;
240+
char errbuf[256];
234241

235242
i = pgut_getopt(argc, argv, options);
236243

@@ -243,24 +250,58 @@ main(int argc, char *argv[])
243250

244251
check_tablespace();
245252

246-
if (noorder)
247-
orderby = "";
248-
249-
if (alldb)
253+
if (r_index || only_indexes)
250254
{
251-
if (table_list.head)
252-
ereport(ERROR,
253-
(errcode(EINVAL),
254-
errmsg("cannot repack specific table(s) in all databases")));
255-
repack_all_databases(orderby);
255+
if (r_index && table_list.head)
256+
ereport(ERROR, (errcode(EINVAL),
257+
errmsg("cannot specify --index (-i) and --table (-t)")));
258+
else if (r_index && only_indexes)
259+
ereport(ERROR, (errcode(EINVAL),
260+
errmsg("cannot specify --index (-i) and --indexes_only (-x)")));
261+
else if (only_indexes && !table_list.head)
262+
ereport(ERROR, (errcode(EINVAL),
263+
errmsg("cannot repack all indexes of database, specify the table with -t option")));
264+
else if (alldb)
265+
ereport(ERROR, (errcode(EINVAL),
266+
errmsg("cannot repack specific index(es) in all databases")));
267+
else
268+
{
269+
if (orderby)
270+
ereport(WARNING, (errcode(EINVAL),
271+
errmsg("option -o (--order-by) has no effect while repacking indexes")));
272+
else if (noorder)
273+
ereport(WARNING, (errcode(EINVAL),
274+
errmsg("option -n (--no-order) has no effect while repacking indexes")));
275+
else if (!analyze)
276+
ereport(WARNING, (errcode(EINVAL),
277+
errmsg("ANALYZE is not performed after repacking indexes, -z (--no-analyze) has no effect")));
278+
else if (jobs)
279+
ereport(WARNING, (errcode(EINVAL),
280+
errmsg("option -j (--jobs) has no effect, repacking indexes doesnot use parallel jobs")));
281+
if (!repack_all_indexes(errbuf, sizeof(errbuf)))
282+
ereport(ERROR,
283+
(errcode(ERROR), errmsg("%s", errbuf)));
284+
}
256285
}
257286
else
258287
{
259-
char errbuf[256];
260-
if (!repack_one_database(orderby, errbuf, sizeof(errbuf)))
261-
ereport(ERROR,
262-
(errcode(ERROR),
263-
errmsg("%s", errbuf)));
288+
if (noorder)
289+
orderby = "";
290+
291+
if (alldb)
292+
{
293+
if (table_list.head)
294+
ereport(ERROR,
295+
(errcode(EINVAL),
296+
errmsg("cannot repack specific table(s) in all databases")));
297+
repack_all_databases(orderby);
298+
}
299+
else
300+
{
301+
if (!repack_one_database(orderby, errbuf, sizeof(errbuf)))
302+
ereport(ERROR,
303+
(errcode(ERROR), errmsg("%s", errbuf)));
304+
}
264305
}
265306

266307
return 0;
@@ -710,7 +751,7 @@ rebuild_indexes(const repack_table *table)
710751
}
711752

712753
res = execute("SELECT indexrelid,"
713-
" repack.repack_indexdef(indexrelid, indrelid, $2) "
754+
" repack.repack_indexdef(indexrelid, indrelid, $2, FALSE) "
714755
" FROM pg_index WHERE indrelid = $1 AND indisvalid",
715756
2, params);
716757

@@ -1533,6 +1574,223 @@ repack_cleanup(bool fatal, const repack_table *table)
15331574
}
15341575
}
15351576

1577+
/*
1578+
* repack one index
1579+
*/
1580+
static bool
1581+
repack_one_index(Oid table, const char *table_name, Oid index, const char *schema_name){
1582+
bool ret = false;
1583+
PGresult *res = NULL;
1584+
StringInfoData sql, temp_index;
1585+
char buffer[2][12];
1586+
char *create_idx;
1587+
const char *params[3];
1588+
1589+
params[0] = utoa(index, buffer[0]);
1590+
params[1] = utoa(table, buffer[1]);
1591+
params[2] = tablespace;
1592+
res = execute("SELECT repack.repack_indexdef($1, $2, $3, true)", 3, params);
1593+
if (PQntuples(res) < 1)
1594+
{
1595+
ereport(ERROR, (errcode(EINVAL),
1596+
errmsg("unable to generate SQL to CREATE new index")));
1597+
goto cleanup;
1598+
}
1599+
create_idx = getstr(res, 0, 0);
1600+
CLEARPGRES(res);
1601+
res = execute_elevel(create_idx, 0, NULL, DEBUG2);
1602+
1603+
initStringInfo(&temp_index);
1604+
if (schema_name)
1605+
appendStringInfo(&temp_index, "%s.index_%u", schema_name, index);
1606+
else
1607+
appendStringInfo(&temp_index, "index_%u", index);
1608+
1609+
if (PQresultStatus(res) != PGRES_COMMAND_OK)
1610+
{
1611+
ereport(ERROR,
1612+
(errcode(E_PG_COMMAND),
1613+
errmsg("%s", PQerrorMessage(connection)),
1614+
errdetail("The temporary index may be left behind "
1615+
" by a pg_repack command on the table which"
1616+
" was interrupted and failed to clean up"
1617+
" the temporary objects. Please use the \"DROP INDEX %s\""
1618+
" to remove the temporary index.", temp_index.data)));
1619+
goto cleanup;
1620+
}
1621+
CLEARPGRES(res);
1622+
1623+
/* take exclusive lock on table before calling repack_index_swap() */
1624+
initStringInfo(&sql);
1625+
if (schema_name)
1626+
appendStringInfo(&sql, "LOCK TABLE %s.%s IN ACCESS EXCLUSIVE MODE", schema_name, table_name);
1627+
else
1628+
appendStringInfo(&sql, "LOCK TABLE %s IN ACCESS EXCLUSIVE MODE", table_name);
1629+
if (!(lock_exclusive(connection, params[1], sql.data, TRUE)))
1630+
{
1631+
elog(WARNING, "lock_exclusive() failed in connection for %s",
1632+
table_name);
1633+
goto drop_idx;
1634+
}
1635+
pgut_command(connection, "SELECT repack.repack_index_swap($1)", 1, params);
1636+
pgut_command(connection, "COMMIT", 0, NULL);
1637+
1638+
drop_idx:
1639+
initStringInfo(&sql);
1640+
#if PG_VERSION_NUM < 90200
1641+
appendStringInfoString(&sql, "DROP INDEX ");
1642+
#else
1643+
appendStringInfoString(&sql, "DROP INDEX CONCURRENTLY ");
1644+
#endif
1645+
appendStringInfo(&sql, "%s", temp_index.data);
1646+
command(sql.data, 0, NULL);
1647+
ret = true;
1648+
cleanup:
1649+
CLEARPGRES(res);
1650+
termStringInfo(&sql);
1651+
return ret;
1652+
}
1653+
1654+
/*
1655+
* Call repack_one_index for each of the indexes
1656+
*/
1657+
static bool
1658+
repack_all_indexes(char *errbuf, size_t errsize){
1659+
bool ret = false;
1660+
PGresult *res = NULL, *res2 = NULL;
1661+
int i;
1662+
int num;
1663+
StringInfoData sql;
1664+
const char *params[1];
1665+
const char *table_name = NULL;
1666+
const char *schema_name = NULL;
1667+
char *pos;
1668+
1669+
initStringInfo(&sql);
1670+
reconnect(ERROR);
1671+
1672+
if (!preliminary_checks(errbuf, errsize))
1673+
goto cleanup;
1674+
1675+
/* If only one index is specified, append the appropriate data to the sql and check if the index exists */
1676+
if (r_index)
1677+
{
1678+
appendStringInfoString(&sql, "SELECT i.relname, idx.indexrelid, idx.indisvalid, tbl.oid, tbl.relname"
1679+
" FROM pg_class tbl JOIN pg_index idx ON tbl.oid = idx.indrelid"
1680+
" JOIN pg_class i ON i.oid = idx.indexrelid"
1681+
" WHERE idx.indexrelid = $1::regclass");
1682+
params[0] = r_index;
1683+
1684+
res = execute_elevel(sql.data, 1, params, DEBUG2);
1685+
1686+
if (PQresultStatus(res) != PGRES_TUPLES_OK)
1687+
{
1688+
snprintf(errbuf, errsize, "%s", PQerrorMessage(connection));
1689+
goto cleanup;
1690+
}
1691+
else
1692+
{
1693+
num = PQntuples(res);
1694+
if (num == 0)
1695+
{
1696+
ereport(ERROR,
1697+
(errcode(EINVAL),
1698+
errmsg("index \"%s\" doesnot exist.\n", r_index)));
1699+
goto cleanup;
1700+
}
1701+
}
1702+
1703+
// seperate schema name and index name
1704+
pos = strchr(params[0], '.');
1705+
if (pos)
1706+
{
1707+
pos[0] = '\0';
1708+
schema_name = params[0];
1709+
r_index = pos + 1;
1710+
}
1711+
table_name = getstr(res, 0, 4);
1712+
}
1713+
/* To repack all indexes, append appropriate data to the sql and run the query */
1714+
else {
1715+
params[0] = table_list.head->val;
1716+
1717+
appendStringInfoString(&sql, "SELECT i.relname, idx.indexrelid, idx.indisvalid, idx.indrelid"
1718+
" FROM pg_index idx JOIN pg_class i ON i.oid = idx.indexrelid"
1719+
" WHERE idx.indrelid = $1::regclass");
1720+
1721+
res = execute_elevel(sql.data, 1, params, DEBUG2);
1722+
1723+
if (PQresultStatus(res) != PGRES_TUPLES_OK)
1724+
{
1725+
snprintf(errbuf, errsize, "%s", PQerrorMessage(connection));
1726+
goto cleanup;
1727+
}
1728+
else
1729+
{
1730+
num = PQntuples(res);
1731+
if (num == 0)
1732+
{
1733+
elog(WARNING, "\"%s\" doesnot have any indexes", table_list.head->val);
1734+
ret = true;
1735+
goto cleanup;
1736+
}
1737+
}
1738+
1739+
// seperate schema name and table name
1740+
pos = strchr(params[0], '.');
1741+
if (pos)
1742+
{
1743+
pos[0] = '\0';
1744+
schema_name = params[0];
1745+
table_name = pos + 1;
1746+
}
1747+
else
1748+
table_name = params[0];
1749+
}
1750+
1751+
/* Check if any concurrent pg_repack command is being run on the same table */
1752+
initStringInfo(&sql);
1753+
appendStringInfo(&sql, "SELECT pg_try_advisory_lock(%u)", getoid(res, 0, 3));
1754+
1755+
res2 = execute_elevel(sql.data, 0, NULL, DEBUG2);
1756+
if (PQresultStatus(res2) != PGRES_TUPLES_OK)
1757+
{
1758+
elog(ERROR, "%s", PQerrorMessage(connection));
1759+
goto cleanup;
1760+
}
1761+
else if (strcmp(getstr(res2, 0, 0), "f") == 0)
1762+
{
1763+
snprintf(errbuf, errsize, "Another pg_repack command may be running on the table. Please try again later.");
1764+
goto cleanup;
1765+
}
1766+
1767+
for (i = 0; i < num; i++)
1768+
{
1769+
char *isvalid = getstr(res, i, 2);
1770+
if (isvalid[0] == 't')
1771+
{
1772+
if (schema_name)
1773+
elog(INFO, "repacking index \"%s.%s\"", schema_name, getstr(res, i, 0));
1774+
else
1775+
elog(INFO, "repacking index \"%s\"", getstr(res, i, 0));
1776+
1777+
if (!(repack_one_index(getoid(res, i, 3), table_name, getoid(res, i, 1), schema_name)))
1778+
goto cleanup;
1779+
}
1780+
else
1781+
if (schema_name)
1782+
elog(WARNING, "skipping invalid index: %s.%s", schema_name, getstr(res, i, 0));
1783+
else
1784+
elog(WARNING, "skipping invalid index: %s", getstr(res, i, 0));
1785+
}
1786+
ret = true;
1787+
cleanup:
1788+
CLEARPGRES(res);
1789+
disconnect();
1790+
termStringInfo(&sql);
1791+
return ret;
1792+
}
1793+
15361794
void
15371795
pgut_help(bool details)
15381796
{
@@ -1551,6 +1809,8 @@ pgut_help(bool details)
15511809
printf(" -o, --order-by=COLUMNS order by columns instead of cluster keys\n");
15521810
printf(" -n, --no-order do vacuum full instead of cluster\n");
15531811
printf(" -j, --jobs=NUM Use this many parallel jobs for each table\n");
1812+
printf(" -i, --index=INDEX move only the specified index\n");
1813+
printf(" -x, --only-index move only indexes of the specified table\n");
15541814
printf(" -T, --wait-timeout=SECS timeout to cancel other backends on conflict\n");
15551815
printf(" -Z, --no-analyze don't analyze at end\n");
15561816
}

‎lib/pg_repack.sql.in

Lines changed: 5 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -207,7 +207,7 @@ CREATE VIEW repack.tables AS
207207
AND N.nspname NOT IN ('pg_catalog', 'information_schema')
208208
AND N.nspname NOT LIKE E'pg\\_temp\\_%';
209209

210-
CREATE FUNCTION repack.repack_indexdef(oid, oid, name) RETURNS text AS
210+
CREATE FUNCTION repack.repack_indexdef(oid, oid, name, bool) RETURNS text AS
211211
'MODULE_PATHNAME', 'repack_indexdef'
212212
LANGUAGE C STABLE;
213213

@@ -246,3 +246,7 @@ LANGUAGE C VOLATILE STRICT;
246246
CREATE FUNCTION repack.repack_drop(oid) RETURNS void AS
247247
'MODULE_PATHNAME', 'repack_drop'
248248
LANGUAGE C VOLATILE STRICT;
249+
250+
CREATE FUNCTION repack.repack_index_swap(oid) RETURNS void AS
251+
'MODULE_PATHNAME', 'repack_index_swap'
252+
LANGUAGE C STABLE STRICT;

0 commit comments

Comments
 (0)