Skip to content

Commit 6710e51

Browse files
dvarrazzodvarrazzo
committed
Added --namespace option to set the namespace of repacked tables
Bumped version number to enforce extension re-creation as the SQL has been modified. Current limitations: - Check for namespace existence: on error temp objects are left around - What happens to the indexes? - Tests needed. - Should the default be the GUC default_tablespace instead of pg_default? This is actually an original pg_repack shortcoming, not a regression.
1 parent 89031f9 commit 6710e51

File tree

3 files changed

+62
-26
lines changed

3 files changed

+62
-26
lines changed

‎META.json

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -2,7 +2,7 @@
22
"name": "pg_repack",
33
"abstract": "PostgreSQL module for data reorganization",
44
"description": "Reorganize tables in PostgreSQL databases with minimal locks",
5-
"version": "1.2dev0",
5+
"version": "1.2dev1",
66
"maintainer": [
77
"Josh Kupershmidt <schmiddy@gmail.com>",
88
"Daniele Varrazzo <daniele.varrazzo@gmail.com>"
@@ -13,7 +13,7 @@
1313
"provides": {
1414
"pg_repack": {
1515
"file": "lib/pg_repack.sql",
16-
"version": "1.2dev0",
16+
"version": "1.2dev1",
1717
"abstract": "Reorganize tables in PostgreSQL databases with minimal locks"
1818
}
1919
},

‎bin/pg_repack.c

Lines changed: 57 additions & 23 deletions
Original file line numberDiff line numberDiff line change
@@ -197,6 +197,7 @@ static bool alldb = false;
197197
static bool noorder = false;
198198
static SimpleStringList table_list = {NULL, NULL};
199199
static char *orderby = NULL;
200+
static char *tablespace = NULL;
200201
static int wait_timeout = 60; /* in seconds */
201202
static int jobs = 0; /* number of concurrent worker conns. */
202203

@@ -214,6 +215,7 @@ static pgut_option options[] =
214215
{ 'l', 't', "table", &table_list },
215216
{ 'b', 'n', "no-order", &noorder },
216217
{ 's', 'o', "order-by", &orderby },
218+
{ 's', 's', "tablespace", &tablespace },
217219
{ 'i', 'T', "wait-timeout", &wait_timeout },
218220
{ 'B', 'Z', "no-analyze", &analyze },
219221
{ 'i', 'j', "jobs", &jobs },
@@ -360,10 +362,15 @@ repack_one_database(const char *orderby, char *errbuf, size_t errsize)
360362
StringInfoData sql;
361363
SimpleStringListCell *cell;
362364
const char **params = NULL;
363-
size_t num_params = simple_string_list_size(table_list);
365+
int iparam = 0;
366+
size_t num_tables;
367+
size_t num_params;
364368

365-
if (num_params)
366-
params = pgut_malloc(num_params * sizeof(char *));
369+
num_tables = simple_string_list_size(table_list);
370+
371+
/* 1st param is the user-specified tablespace */
372+
num_params = num_tables + 1;
373+
params = pgut_malloc(num_params * sizeof(char *));
367374

368375
initStringInfo(&sql);
369376

@@ -442,29 +449,46 @@ repack_one_database(const char *orderby, char *errbuf, size_t errsize)
442449
command("SET client_min_messages = warning", 0, NULL);
443450

444451
/* acquire target tables */
445-
appendStringInfoString(&sql, "SELECT * FROM repack.tables WHERE ");
446-
if (num_params)
452+
appendStringInfoString(&sql,
453+
"SELECT t.*,"
454+
" coalesce(v.tablespace, t.tablespace_orig) as tablespace_dest"
455+
" FROM repack.tables t, "
456+
" (VALUES (quote_ident($1::text))) as v (tablespace)"
457+
" WHERE ");
458+
459+
params[iparam++] = tablespace;
460+
if (num_tables)
447461
{
448462
appendStringInfoString(&sql, "(");
449-
for (i = 0, cell = table_list.head; cell; cell = cell->next, i++)
463+
for (cell = table_list.head; cell; cell = cell->next)
450464
{
451465
/* Construct table name placeholders to be used by PQexecParams */
452-
appendStringInfo(&sql, "relid = $%d::regclass", i + 1);
453-
params[i] = cell->val;
466+
appendStringInfo(&sql, "relid = $%d::regclass", iparam + 1);
467+
params[iparam++] = cell->val;
454468
if (cell->next)
455469
appendStringInfoString(&sql, " OR ");
456470
}
457471
appendStringInfoString(&sql, ")");
458-
res = execute_elevel(sql.data, (int) num_params, params, DEBUG2);
459472
}
460473
else
461474
{
462475
appendStringInfoString(&sql, "pkid IS NOT NULL");
463476
if (!orderby)
464477
appendStringInfoString(&sql, " AND ckid IS NOT NULL");
465-
res = execute_elevel(sql.data, 0, NULL, DEBUG2);
466478
}
467479

480+
/* double check the parameters array is sane */
481+
if (iparam != num_params)
482+
{
483+
if (errbuf)
484+
snprintf(errbuf, errsize,
485+
"internal error: bad parameters count: %i instead of %zi",
486+
iparam, num_params);
487+
goto cleanup;
488+
}
489+
490+
res = execute_elevel(sql.data, (int) num_params, params, DEBUG2);
491+
468492
/* on error skip the database */
469493
if (PQresultStatus(res) != PGRES_TUPLES_OK)
470494
{
@@ -489,7 +513,9 @@ repack_one_database(const char *orderby, char *errbuf, size_t errsize)
489513
for (i = 0; i < num; i++)
490514
{
491515
repack_table table;
492-
const char *create_table;
516+
const char *create_table_1;
517+
const char *create_table_2;
518+
const char *tablespace;
493519
const char *ckey;
494520
int c = 0;
495521

@@ -512,13 +538,24 @@ repack_one_database(const char *orderby, char *errbuf, size_t errsize)
512538
table.create_trigger = getstr(res, i, c++);
513539
table.enable_trigger = getstr(res, i, c++);
514540

515-
create_table = getstr(res, i, c++);
541+
create_table_1 = getstr(res, i, c++);
542+
tablespace = getstr(res, i, c++); /* to be clobbered */
543+
create_table_2 = getstr(res, i, c++);
516544
table.drop_columns = getstr(res, i, c++);
517545
table.delete_log = getstr(res, i, c++);
518546
table.lock_table = getstr(res, i, c++);
519547
ckey = getstr(res, i, c++);
548+
table.sql_peek = getstr(res, i, c++);
549+
table.sql_insert = getstr(res, i, c++);
550+
table.sql_delete = getstr(res, i, c++);
551+
table.sql_update = getstr(res, i, c++);
552+
table.sql_pop = getstr(res, i, c++);
553+
tablespace = getstr(res, i, c++);
520554

521555
resetStringInfo(&sql);
556+
appendStringInfoString(&sql, create_table_1);
557+
appendStringInfoString(&sql, tablespace);
558+
appendStringInfoString(&sql, create_table_2);
522559
if (!orderby)
523560
{
524561
/* CLUSTER mode */
@@ -529,27 +566,23 @@ repack_one_database(const char *orderby, char *errbuf, size_t errsize)
529566
errmsg("relation \"%s\" has no cluster key", table.target_name)));
530567
continue;
531568
}
532-
appendStringInfo(&sql, "%s ORDER BY %s", create_table, ckey);
533-
table.create_table = sql.data;
569+
appendStringInfoString(&sql, " ORDER BY ");
570+
appendStringInfoString(&sql, ckey);
571+
table.create_table = sql.data;
534572
}
535573
else if (!orderby[0])
536574
{
537575
/* VACUUM FULL mode */
538-
table.create_table = create_table;
576+
table.create_table = sql.data;
539577
}
540578
else
541579
{
542580
/* User specified ORDER BY */
543-
appendStringInfo(&sql, "%s ORDER BY %s", create_table, orderby);
544-
table.create_table = sql.data;
581+
appendStringInfoString(&sql, " ORDER BY ");
582+
appendStringInfoString(&sql, orderby);
583+
table.create_table = sql.data;
545584
}
546585

547-
table.sql_peek = getstr(res, i, c++);
548-
table.sql_insert = getstr(res, i, c++);
549-
table.sql_delete = getstr(res, i, c++);
550-
table.sql_update = getstr(res, i, c++);
551-
table.sql_pop = getstr(res, i, c++);
552-
553586
repack_one_table(&table, orderby);
554587
}
555588
ret = true;
@@ -1430,6 +1463,7 @@ pgut_help(bool details)
14301463
printf(" -n, --no-order do vacuum full instead of cluster\n");
14311464
printf(" -o, --order-by=COLUMNS order by columns instead of cluster keys\n");
14321465
printf(" -t, --table=TABLE repack specific table only\n");
1466+
printf(" -s, --tablespace=TABLESPC move repacked tables to a new tablespace\n");
14331467
printf(" -T, --wait-timeout=SECS timeout to cancel other backends on conflict\n");
14341468
printf(" -Z, --no-analyze don't analyze at end\n");
14351469
}

‎lib/pg_repack.sql.in

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -179,7 +179,9 @@ CREATE VIEW repack.tables AS
179179
'CREATE TABLE repack.log_' || R.oid || ' (id bigserial PRIMARY KEY, pk repack.pk_' || R.oid || ', row ' || repack.oid2text(R.oid) || ')' AS create_log,
180180
repack.get_create_trigger(R.oid, PK.indexrelid) AS create_trigger,
181181
repack.get_enable_trigger(R.oid) as enable_trigger,
182-
'CREATE TABLE repack.table_' || R.oid || ' WITH (' || array_to_string(array_append(R.reloptions, 'oids=' || CASE WHEN R.relhasoids THEN 'true' ELSE 'false' END), ',') || ') TABLESPACE ' || coalesce(quote_ident(S.spcname), 'pg_default') || ' AS SELECT ' || repack.get_columns_for_create_as(R.oid) || ' FROM ONLY ' || repack.oid2text(R.oid) AS create_table,
182+
'CREATE TABLE repack.table_' || R.oid || ' WITH (' || array_to_string(array_append(R.reloptions, 'oids=' || CASE WHEN R.relhasoids THEN 'true' ELSE 'false' END), ',') || ') TABLESPACE ' AS create_table_1,
183+
coalesce(quote_ident(S.spcname), 'pg_default') tablespace_orig,
184+
' AS SELECT ' || repack.get_columns_for_create_as(R.oid) || ' FROM ONLY ' || repack.oid2text(R.oid) AS create_table_2,
183185
repack.get_drop_columns(R.oid, 'repack.table_' || R.oid) AS drop_columns,
184186
'DELETE FROM repack.log_' || R.oid AS delete_log,
185187
'LOCK TABLE ' || repack.oid2text(R.oid) || ' IN ACCESS EXCLUSIVE MODE' AS lock_table,

0 commit comments

Comments
 (0)