Skip to content

Commit f0cd787

Browse files
schmiddyschmiddy
committed
Merge in Pull Request #20 from Steeve Lennmark.
Adds support for repacking only the tables in a specified schema. This doesn't support --only-indexes mode, but that seems alright for now. Fix merge conflicts, and make a few tweaks along the way: * bump version to 1.3-dev0 * add Beena to list of maintainers * documentation wordsmithing * fix up the INFO message printed for each index in --index or --only-indexes mode, so that it is only printed once per index, and prints the name of the original index, not that of the transient index_%u name.
2 parents f097982 + f094795 commit f0cd787

File tree

7 files changed

+105
-13
lines changed

7 files changed

+105
-13
lines changed

‎META.json

Lines changed: 5 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -2,18 +2,19 @@
22
"name": "pg_repack",
33
"abstract": "PostgreSQL module for data reorganization",
44
"description": "Reorganize tables in PostgreSQL databases with minimal locks",
5-
"version": "1.2.1",
5+
"version": "1.3-dev0",
66
"maintainer": [
77
"Josh Kupershmidt <schmiddy@gmail.com>",
8-
"Daniele Varrazzo <daniele.varrazzo@gmail.com>"
8+
"Daniele Varrazzo <daniele.varrazzo@gmail.com>",
9+
"Beena Emerson <memissemerson@gmail.com>"
910
],
1011
"tags": [ "bloat", "maintenance", "vacuum", "cluster" ],
11-
"release_status": "stable",
12+
"release_status": "testing",
1213
"license": "bsd",
1314
"provides": {
1415
"pg_repack": {
1516
"file": "lib/pg_repack.sql",
16-
"version": "1.2.1",
17+
"version": "1.3-dev0",
1718
"abstract": "Reorganize tables in PostgreSQL databases with minimal locks"
1819
}
1920
},

‎bin/pg_repack.c

Lines changed: 35 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -219,6 +219,7 @@ static bool analyze = true;
219219
static bool alldb = false;
220220
static bool noorder = false;
221221
static SimpleStringList table_list = {NULL, NULL};
222+
static SimpleStringList schema_list = {NULL, NULL};
222223
static char *orderby = NULL;
223224
static char *tablespace = NULL;
224225
static bool moveidx = false;
@@ -240,6 +241,7 @@ static pgut_option options[] =
240241
{
241242
{ 'b', 'a', "all", &alldb },
242243
{ 'l', 't', "table", &table_list },
244+
{ 'l', 'c', "schema", &schema_list },
243245
{ 'b', 'n', "no-order", &noorder },
244246
{ 'b', 'N', "dry-run", &dryrun },
245247
{ 's', 'o', "order-by", &orderby },
@@ -308,6 +310,11 @@ main(int argc, char *argv[])
308310
}
309311
else
310312
{
313+
if (schema_list.head && table_list.head)
314+
ereport(ERROR,
315+
(errcode(EINVAL),
316+
errmsg("cannot repack specific table(s) in schema, use schema.table notation instead")));
317+
311318
if (noorder)
312319
orderby = "";
313320

@@ -317,6 +324,10 @@ main(int argc, char *argv[])
317324
ereport(ERROR,
318325
(errcode(EINVAL),
319326
errmsg("cannot repack specific table(s) in all databases")));
327+
if (schema_list.head)
328+
ereport(ERROR,
329+
(errcode(EINVAL),
330+
errmsg("cannot repack specific schema(s) in all databases")));
320331
repack_all_databases(orderby);
321332
}
322333
else
@@ -563,12 +574,14 @@ repack_one_database(const char *orderby, char *errbuf, size_t errsize)
563574
const char **params = NULL;
564575
int iparam = 0;
565576
size_t num_tables;
577+
size_t num_schemas;
566578
size_t num_params;
567579

568580
num_tables = simple_string_list_size(table_list);
581+
num_schemas = simple_string_list_size(schema_list);
569582

570583
/* 1st param is the user-specified tablespace */
571-
num_params = num_tables + 1;
584+
num_params = num_tables + num_schemas + 1;
572585
params = pgut_malloc(num_params * sizeof(char *));
573586

574587
initStringInfo(&sql);
@@ -604,6 +617,19 @@ repack_one_database(const char *orderby, char *errbuf, size_t errsize)
604617
}
605618
appendStringInfoString(&sql, ")");
606619
}
620+
else if (num_schemas)
621+
{
622+
appendStringInfoString(&sql, "schemaname IN (");
623+
for (cell = schema_list.head; cell; cell = cell->next)
624+
{
625+
/* Construct schema name placeholders to be used by PQexecParams */
626+
appendStringInfo(&sql, "$%d", iparam + 1);
627+
params[iparam++] = cell->val;
628+
if (cell->next)
629+
appendStringInfoString(&sql, ", ");
630+
}
631+
appendStringInfoString(&sql, ")");
632+
}
607633
else
608634
{
609635
appendStringInfoString(&sql, "pkid IS NOT NULL");
@@ -645,6 +671,7 @@ repack_one_database(const char *orderby, char *errbuf, size_t errsize)
645671
table.target_oid = getoid(res, i, c++);
646672
table.target_toast = getoid(res, i, c++);
647673
table.target_tidx = getoid(res, i, c++);
674+
c++; // Skip schemaname
648675
table.pkid = getoid(res, i, c++);
649676
table.ckid = getoid(res, i, c++);
650677

@@ -1671,6 +1698,7 @@ repack_table_indexes(PGresult *index_details)
16711698
for (i = 0; i < num; i++)
16721699
{
16731700
char *isvalid = getstr(index_details, i, 2);
1701+
char *idx_name = getstr(index_details, i, 0);
16741702

16751703
if (isvalid[0] == 't')
16761704
{
@@ -1683,7 +1711,7 @@ repack_table_indexes(PGresult *index_details)
16831711
"WHERE pgc.relname = 'index_%u' "
16841712
"AND nsp.nspname = $1", index);
16851713
params[0] = schema_name;
1686-
elog(INFO, "repacking index \"%s\".\"index_%u\"", schema_name, index);
1714+
elog(INFO, "repacking index \"%s\".\"%s\"", schema_name, idx_name);
16871715
res = execute(sql.data, 1, params);
16881716
if (PQresultStatus(res) != PGRES_TUPLES_OK)
16891717
{
@@ -1837,6 +1865,10 @@ repack_all_indexes(char *errbuf, size_t errsize)
18371865
if (!preliminary_checks(errbuf, errsize))
18381866
goto cleanup;
18391867

1868+
/* XXX: tighten these ORDER BYs to avoid intermittent installcheck
1869+
* failures due to differently-ordered results for some of the
1870+
* --only-indexes tests.
1871+
*/
18401872
if (r_index.head)
18411873
{
18421874
appendStringInfoString(&sql,
@@ -1883,8 +1915,6 @@ repack_all_indexes(char *errbuf, size_t errsize)
18831915

18841916
if(table_list.head)
18851917
elog(INFO, "repacking indexes of \"%s\"", cell->val);
1886-
else
1887-
elog(INFO, "repacking \"%s\"", cell->val);
18881918

18891919
if (!repack_table_indexes(res))
18901920
elog(WARNING, "repack failed for \"%s\"", cell->val);
@@ -1913,6 +1943,7 @@ pgut_help(bool details)
19131943
printf("Options:\n");
19141944
printf(" -a, --all repack all databases\n");
19151945
printf(" -t, --table=TABLE repack specific table only\n");
1946+
printf(" -c, --schema=SCHEMA repack tables in specific schema only\n");
19161947
printf(" -s, --tablespace=TBLSPC move repacked tables to a new tablespace\n");
19171948
printf(" -S, --moveidx move repacked indexes to TBLSPC too\n");
19181949
printf(" -o, --order-by=COLUMNS order by columns instead of cluster keys\n");

‎doc/pg_repack.rst

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -117,6 +117,7 @@ The following options can be specified in ``OPTIONS``.
117117
Options:
118118
-a, --all repack all databases
119119
-t, --table=TABLE repack specific table only
120+
-c, --schema=SCHEMA repack tables in specific schema only
120121
-s, --tablespace=TBLSPC move repacked tables to a new tablespace
121122
-S, --moveidx move repacked indexes to *TBLSPC* too
122123
-o, --order-by=COLUMNS order by columns instead of cluster keys
@@ -155,6 +156,11 @@ Reorg Options
155156
reorganized by writing multiple ``-t`` switches. By default, all eligible
156157
tables in the target databases are reorganized.
157158

159+
``-c``, ``--schema``
160+
Repack the tables in the specified schema(s) only. Multiple schemas may
161+
be repacked by writing multiple ``-c`` switches. May be used in
162+
conjunction with ``--tablespace`` to move tables to a different tablespace.
163+
158164
``-o COLUMNS [,...]``, ``--order-by=COLUMNS [,...]``
159165
Perform an online CLUSTER ordered by the specified columns.
160166

@@ -438,6 +444,7 @@ Releases
438444
* pg_repack 1.3
439445

440446
* Added ``--dry-run`` to do a dry run.
447+
* Added ``--schema`` to repack only the specified schema.
441448

442449
* pg_repack 1.2
443450

‎lib/pg_repack.sql.in

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -173,6 +173,7 @@ CREATE VIEW repack.tables AS
173173
R.oid AS relid,
174174
R.reltoastrelid AS reltoastrelid,
175175
CASE WHEN R.reltoastrelid = 0 THEN 0 ELSE (SELECT reltoastidxid FROM pg_class WHERE oid = R.reltoastrelid) END AS reltoastidxid,
176+
N.nspname AS schemaname,
176177
PK.indexrelid AS pkid,
177178
CK.indexrelid AS ckid,
178179
repack.get_create_index_type(PK.indexrelid, 'repack.pk_' || R.oid) AS create_pktype,

‎regress/expected/repack.out

Lines changed: 26 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -325,6 +325,8 @@ INFO: repacking table "tbl_pk_uk"
325325
-- => OK
326326
\! pg_repack --dbname=contrib_regression --table=tbl_pk_uk --only-indexes
327327
INFO: repacking indexes of "tbl_pk_uk"
328+
INFO: repacking index "public"."tbl_pk_uk_pkey"
329+
INFO: repacking index "public"."tbl_pk_uk_col2_col1_key"
328330
-- => OK
329331
\! pg_repack --dbname=contrib_regression --table=tbl_nn_puk
330332
WARNING: relation "tbl_nn_puk" must have a primary key or not-null unique keys
@@ -361,3 +363,27 @@ INFO: repacking table "trg4"
361363
\! pg_repack --dbname=contrib_regression --table=tbl_cluster --dry-run
362364
INFO: Dry run enabled, not executing repack
363365
INFO: repacking table "tbl_cluster"
366+
-- Test --schema
367+
--
368+
CREATE SCHEMA test_schema1;
369+
CREATE TABLE test_schema1.tbl1 (id INTEGER PRIMARY KEY);
370+
CREATE TABLE test_schema1.tbl2 (id INTEGER PRIMARY KEY);
371+
CREATE SCHEMA test_schema2;
372+
CREATE TABLE test_schema2.tbl1 (id INTEGER PRIMARY KEY);
373+
CREATE TABLE test_schema2.tbl2 (id INTEGER PRIMARY KEY);
374+
-- => OK
375+
\! pg_repack --dbname=contrib_regression --schema=test_schema1
376+
INFO: repacking table "test_schema1.tbl1"
377+
INFO: repacking table "test_schema1.tbl2"
378+
-- => OK
379+
\! pg_repack --dbname=contrib_regression --schema=test_schema1 --schema=test_schema2
380+
INFO: repacking table "test_schema2.tbl1"
381+
INFO: repacking table "test_schema2.tbl2"
382+
INFO: repacking table "test_schema1.tbl2"
383+
INFO: repacking table "test_schema1.tbl1"
384+
-- => ERROR
385+
\! pg_repack --dbname=contrib_regression --schema=test_schema1 --table=tbl1
386+
ERROR: cannot repack specific table(s) in schema, use schema.table notation instead
387+
-- => ERROR
388+
\! pg_repack --dbname=contrib_regression --all --schema=test_schema1
389+
ERROR: cannot repack specific schema(s) in all databases

‎regress/expected/tablespace.out

Lines changed: 14 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -135,6 +135,9 @@ INFO: repacking table "testts1"
135135
--move all indexes of the table to a tablespace
136136
\! pg_repack --dbname=contrib_regression --table=testts1 --only-indexes --tablespace=testts
137137
INFO: repacking indexes of "testts1"
138+
INFO: repacking index "public"."testts1_pkey"
139+
INFO: repacking index "public"."testts1_partial_idx"
140+
INFO: repacking index "public"."testts1_with_idx"
138141
SELECT relname, spcname
139142
FROM pg_class JOIN pg_tablespace ts ON ts.oid = reltablespace
140143
WHERE relname ~ '^testts1'
@@ -149,6 +152,9 @@ ORDER BY relname;
149152
--all indexes of tablespace remain in same tablespace
150153
\! pg_repack --dbname=contrib_regression --table=testts1 --only-indexes
151154
INFO: repacking indexes of "testts1"
155+
INFO: repacking index "public"."testts1_pkey"
156+
INFO: repacking index "public"."testts1_partial_idx"
157+
INFO: repacking index "public"."testts1_with_idx"
152158
SELECT relname, spcname
153159
FROM pg_class JOIN pg_tablespace ts ON ts.oid = reltablespace
154160
WHERE relname ~ '^testts1'
@@ -163,6 +169,9 @@ ORDER BY relname;
163169
--move all indexes of the table to pg_default
164170
\! pg_repack --dbname=contrib_regression --table=testts1 --only-indexes --tablespace=pg_default
165171
INFO: repacking indexes of "testts1"
172+
INFO: repacking index "public"."testts1_pkey"
173+
INFO: repacking index "public"."testts1_partial_idx"
174+
INFO: repacking index "public"."testts1_with_idx"
166175
SELECT relname, spcname
167176
FROM pg_class JOIN pg_tablespace ts ON ts.oid = reltablespace
168177
WHERE relname ~ '^testts1'
@@ -173,7 +182,7 @@ ORDER BY relname;
173182

174183
--move one index to a tablespace
175184
\! pg_repack --dbname=contrib_regression --index=testts1_pkey --tablespace=testts
176-
INFO: repacking "testts1_pkey"
185+
INFO: repacking index "public"."testts1_pkey"
177186
SELECT relname, spcname
178187
FROM pg_class JOIN pg_tablespace ts ON ts.oid = reltablespace
179188
WHERE relname ~ '^testts1'
@@ -185,7 +194,7 @@ ORDER BY relname;
185194

186195
--index tablespace stays as is
187196
\! pg_repack --dbname=contrib_regression --index=testts1_pkey
188-
INFO: repacking "testts1_pkey"
197+
INFO: repacking index "public"."testts1_pkey"
189198
SELECT relname, spcname
190199
FROM pg_class JOIN pg_tablespace ts ON ts.oid = reltablespace
191200
WHERE relname ~ '^testts1'
@@ -197,7 +206,7 @@ ORDER BY relname;
197206

198207
--move index to pg_default
199208
\! pg_repack --dbname=contrib_regression --index=testts1_pkey --tablespace=pg_default
200-
INFO: repacking "testts1_pkey"
209+
INFO: repacking index "public"."testts1_pkey"
201210
SELECT relname, spcname
202211
FROM pg_class JOIN pg_tablespace ts ON ts.oid = reltablespace
203212
WHERE relname ~ '^testts1'
@@ -208,8 +217,8 @@ ORDER BY relname;
208217

209218
--using multiple --index option
210219
\! pg_repack --dbname=contrib_regression --index=testts1_pkey --index=testts1_with_idx --tablespace=testts
211-
INFO: repacking "testts1_pkey"
212-
INFO: repacking "testts1_with_idx"
220+
INFO: repacking index "public"."testts1_pkey"
221+
INFO: repacking index "public"."testts1_with_idx"
213222
SELECT relname, spcname
214223
FROM pg_class JOIN pg_tablespace ts ON ts.oid = reltablespace
215224
WHERE relname ~ '^testts1'

‎regress/sql/repack.sql

Lines changed: 17 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -213,3 +213,20 @@ CREATE TRIGGER zzzzzz AFTER UPDATE ON trg4 FOR EACH ROW EXECUTE PROCEDURE trgtes
213213
-- Dry run
214214
--
215215
\! pg_repack --dbname=contrib_regression --table=tbl_cluster --dry-run
216+
217+
-- Test --schema
218+
--
219+
CREATE SCHEMA test_schema1;
220+
CREATE TABLE test_schema1.tbl1 (id INTEGER PRIMARY KEY);
221+
CREATE TABLE test_schema1.tbl2 (id INTEGER PRIMARY KEY);
222+
CREATE SCHEMA test_schema2;
223+
CREATE TABLE test_schema2.tbl1 (id INTEGER PRIMARY KEY);
224+
CREATE TABLE test_schema2.tbl2 (id INTEGER PRIMARY KEY);
225+
-- => OK
226+
\! pg_repack --dbname=contrib_regression --schema=test_schema1
227+
-- => OK
228+
\! pg_repack --dbname=contrib_regression --schema=test_schema1 --schema=test_schema2
229+
-- => ERROR
230+
\! pg_repack --dbname=contrib_regression --schema=test_schema1 --table=tbl1
231+
-- => ERROR
232+
\! pg_repack --dbname=contrib_regression --all --schema=test_schema1

0 commit comments

Comments
 (0)