Skip to content

Commit 42c65d1

Browse files
dvarrazzodvarrazzo
committed
Merge branch 'change-tablespace'
Conflicts: bin/pg_repack.c doc/pg_repack.rst
2 parents 7617e07 + b6bd078 commit 42c65d1

File tree

8 files changed

+311
-36
lines changed

8 files changed

+311
-36
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/Makefile

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
SRCS = pg_repack.c pgut/pgut.c pgut/pgut-fe.c
99
OBJS = $(SRCS:.c=.o)
1010
PROGRAM = pg_repack
11-
REGRESS = init repack
11+
REGRESS = init repack tablespace
1212

1313
EXTRA_CLEAN = sql/init-$(MAJORVERSION).sql sql/init.sql
1414

‎bin/expected/tablespace.out

Lines changed: 76 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,76 @@
1+
SET client_min_messages = warning;
2+
--
3+
-- Tablespace features tests
4+
--
5+
-- Note: in order to pass this test you must create a tablespace called 'testts'
6+
--
7+
SELECT spcname FROM pg_tablespace WHERE spcname = 'testts';
8+
spcname
9+
---------
10+
testts
11+
(1 row)
12+
13+
-- If the query above failed you must create the 'testts' tablespace;
14+
CREATE TABLE testts1 (id serial primary key, data text);
15+
CREATE INDEX testts1_partial_idx on testts1 (id) where (id > 0);
16+
INSERT INTO testts1 (data) values ('a');
17+
INSERT INTO testts1 (data) values ('b');
18+
INSERT INTO testts1 (data) values ('c');
19+
-- can move the tablespace from default
20+
\! pg_repack --dbname=contrib_regression --no-order --table=testts1 --tablespace testts
21+
SELECT relname, spcname
22+
FROM pg_class JOIN pg_tablespace ts ON ts.oid = reltablespace
23+
WHERE relname ~ '^testts1'
24+
ORDER BY relname;
25+
relname | spcname
26+
---------+---------
27+
testts1 | testts
28+
(1 row)
29+
30+
SELECT * from testts1 order by id;
31+
id | data
32+
----+------
33+
1 | a
34+
2 | b
35+
3 | c
36+
(3 rows)
37+
38+
-- tablespace stays where it is
39+
\! pg_repack --dbname=contrib_regression --no-order --table=testts1
40+
SELECT relname, spcname
41+
FROM pg_class JOIN pg_tablespace ts ON ts.oid = reltablespace
42+
WHERE relname ~ '^testts1'
43+
ORDER BY relname;
44+
relname | spcname
45+
---------+---------
46+
testts1 | testts
47+
(1 row)
48+
49+
-- can move the ts back to default
50+
\! pg_repack --dbname=contrib_regression --no-order --table=testts1 -s pg_default
51+
SELECT relname, spcname
52+
FROM pg_class JOIN pg_tablespace ts ON ts.oid = reltablespace
53+
WHERE relname ~ '^testts1'
54+
ORDER BY relname;
55+
relname | spcname
56+
---------+---------
57+
(0 rows)
58+
59+
-- can move the table together with the indexes
60+
\! pg_repack --dbname=contrib_regression --no-order --table=testts1 --tablespace testts --moveidx
61+
SELECT relname, spcname
62+
FROM pg_class JOIN pg_tablespace ts ON ts.oid = reltablespace
63+
WHERE relname ~ '^testts1'
64+
ORDER BY relname;
65+
relname | spcname
66+
---------------------+---------
67+
testts1 | testts
68+
testts1_partial_idx | testts
69+
testts1_pkey | testts
70+
(3 rows)
71+
72+
-- can't specify --moveidx without --tablespace
73+
\! pg_repack --dbname=contrib_regression --no-order --table=testts1 --moveidx
74+
ERROR: cannot specify --moveidx (-S) without --tablespace (-s)
75+
\! pg_repack --dbname=contrib_regression --no-order --table=testts1 -S
76+
ERROR: cannot specify --moveidx (-S) without --tablespace (-s)

‎bin/pg_repack.c

Lines changed: 117 additions & 26 deletions
Original file line numberDiff line numberDiff line change
@@ -172,6 +172,7 @@ typedef struct repack_index
172172
} repack_index;
173173

174174
static bool is_superuser(void);
175+
static void check_tablespace(void);
175176
static void repack_all_databases(const char *order_by);
176177
static bool repack_one_database(const char *order_by, char *errbuf, size_t errsize);
177178
static void repack_one_table(const repack_table *table, const char *order_by);
@@ -197,6 +198,8 @@ static bool alldb = false;
197198
static bool noorder = false;
198199
static SimpleStringList table_list = {NULL, NULL};
199200
static char *orderby = NULL;
201+
static char *tablespace = NULL;
202+
static bool moveidx = false;
200203
static int wait_timeout = 60; /* in seconds */
201204
static int jobs = 0; /* number of concurrent worker conns. */
202205

@@ -214,6 +217,8 @@ static pgut_option options[] =
214217
{ 'l', 't', "table", &table_list },
215218
{ 'b', 'n', "no-order", &noorder },
216219
{ 's', 'o', "order-by", &orderby },
220+
{ 's', 's', "tablespace", &tablespace },
221+
{ 'b', 'S', "moveidx", &moveidx },
217222
{ 'i', 'T', "wait-timeout", &wait_timeout },
218223
{ 'B', 'Z', "no-analyze", &analyze },
219224
{ 'i', 'j', "jobs", &jobs },
@@ -234,6 +239,8 @@ main(int argc, char *argv[])
234239
(errcode(EINVAL),
235240
errmsg("too many arguments")));
236241

242+
check_tablespace();
243+
237244
if (noorder)
238245
orderby = "";
239246

@@ -281,6 +288,56 @@ is_superuser(void)
281288
return false;
282289
}
283290

291+
/*
292+
* Check if the tablespace requested exists.
293+
*
294+
* Raise an exception on error.
295+
*/
296+
void
297+
check_tablespace()
298+
{
299+
PGresult *res = NULL;
300+
const char *params[1];
301+
302+
if (tablespace == NULL)
303+
{
304+
/* nothing to check, but let's see the options */
305+
if (moveidx)
306+
{
307+
ereport(ERROR,
308+
(errcode(EINVAL),
309+
errmsg("cannot specify --moveidx (-S) without --tablespace (-s)")));
310+
}
311+
return;
312+
}
313+
314+
/* check if the tablespace exists */
315+
reconnect(ERROR);
316+
params[0] = tablespace;
317+
res = execute_elevel(
318+
"select spcname from pg_tablespace where spcname = $1",
319+
1, params, DEBUG2);
320+
321+
if (PQresultStatus(res) == PGRES_TUPLES_OK)
322+
{
323+
if (PQntuples(res) == 0)
324+
{
325+
ereport(ERROR,
326+
(errcode(EINVAL),
327+
errmsg("the tablespace \"%s\" doesn't exist", tablespace)));
328+
}
329+
}
330+
else
331+
{
332+
ereport(ERROR,
333+
(errcode(EINVAL),
334+
errmsg("error checking the namespace: %s",
335+
PQerrorMessage(connection))));
336+
}
337+
338+
CLEARPGRES(res);
339+
}
340+
284341

285342
/*
286343
* Call repack_one_database for each database.
@@ -360,10 +417,15 @@ repack_one_database(const char *orderby, char *errbuf, size_t errsize)
360417
StringInfoData sql;
361418
SimpleStringListCell *cell;
362419
const char **params = NULL;
363-
size_t num_params = simple_string_list_size(table_list);
420+
int iparam = 0;
421+
size_t num_tables;
422+
size_t num_params;
423+
424+
num_tables = simple_string_list_size(table_list);
364425

365-
if (num_params)
366-
params = pgut_malloc(num_params * sizeof(char *));
426+
/* 1st param is the user-specified tablespace */
427+
num_params = num_tables + 1;
428+
params = pgut_malloc(num_params * sizeof(char *));
367429

368430
initStringInfo(&sql);
369431

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

444506
/* acquire target tables */
445-
appendStringInfoString(&sql, "SELECT * FROM repack.tables WHERE ");
446-
if (num_params)
507+
appendStringInfoString(&sql,
508+
"SELECT t.*,"
509+
" coalesce(v.tablespace, t.tablespace_orig) as tablespace_dest"
510+
" FROM repack.tables t, "
511+
" (VALUES (quote_ident($1::text))) as v (tablespace)"
512+
" WHERE ");
513+
514+
params[iparam++] = tablespace;
515+
if (num_tables)
447516
{
448517
appendStringInfoString(&sql, "(");
449-
for (i = 0, cell = table_list.head; cell; cell = cell->next, i++)
518+
for (cell = table_list.head; cell; cell = cell->next)
450519
{
451520
/* Construct table name placeholders to be used by PQexecParams */
452-
appendStringInfo(&sql, "relid = $%d::regclass", i + 1);
453-
params[i] = cell->val;
521+
appendStringInfo(&sql, "relid = $%d::regclass", iparam + 1);
522+
params[iparam++] = cell->val;
454523
if (cell->next)
455524
appendStringInfoString(&sql, " OR ");
456525
}
457526
appendStringInfoString(&sql, ")");
458-
res = execute_elevel(sql.data, (int) num_params, params, DEBUG2);
459527
}
460528
else
461529
{
462530
appendStringInfoString(&sql, "pkid IS NOT NULL");
463531
if (!orderby)
464532
appendStringInfoString(&sql, " AND ckid IS NOT NULL");
465-
res = execute_elevel(sql.data, 0, NULL, DEBUG2);
466533
}
467534

535+
/* double check the parameters array is sane */
536+
if (iparam != num_params)
537+
{
538+
if (errbuf)
539+
snprintf(errbuf, errsize,
540+
"internal error: bad parameters count: %i instead of %zi",
541+
iparam, num_params);
542+
goto cleanup;
543+
}
544+
545+
res = execute_elevel(sql.data, (int) num_params, params, DEBUG2);
546+
468547
/* on error skip the database */
469548
if (PQresultStatus(res) != PGRES_TUPLES_OK)
470549
{
@@ -489,7 +568,9 @@ repack_one_database(const char *orderby, char *errbuf, size_t errsize)
489568
for (i = 0; i < num; i++)
490569
{
491570
repack_table table;
492-
const char *create_table;
571+
const char *create_table_1;
572+
const char *create_table_2;
573+
const char *tablespace;
493574
const char *ckey;
494575
int c = 0;
495576

@@ -512,45 +593,51 @@ repack_one_database(const char *orderby, char *errbuf, size_t errsize)
512593
table.create_trigger = getstr(res, i, c++);
513594
table.enable_trigger = getstr(res, i, c++);
514595

515-
create_table = getstr(res, i, c++);
596+
create_table_1 = getstr(res, i, c++);
597+
tablespace = getstr(res, i, c++); /* to be clobbered */
598+
create_table_2 = getstr(res, i, c++);
516599
table.drop_columns = getstr(res, i, c++);
517600
table.delete_log = getstr(res, i, c++);
518601
table.lock_table = getstr(res, i, c++);
519602
ckey = getstr(res, i, c++);
603+
table.sql_peek = getstr(res, i, c++);
604+
table.sql_insert = getstr(res, i, c++);
605+
table.sql_delete = getstr(res, i, c++);
606+
table.sql_update = getstr(res, i, c++);
607+
table.sql_pop = getstr(res, i, c++);
608+
tablespace = getstr(res, i, c++);
520609

521610
resetStringInfo(&sql);
611+
appendStringInfoString(&sql, create_table_1);
612+
appendStringInfoString(&sql, tablespace);
613+
appendStringInfoString(&sql, create_table_2);
522614
if (!orderby)
523615
{
524616
if (ckey != NULL)
525617
{
526618
/* CLUSTER mode */
527-
appendStringInfo(&sql, "%s ORDER BY %s", create_table, ckey);
619+
appendStringInfoString(&sql, " ORDER BY ");
620+
appendStringInfoString(&sql, ckey);
528621
table.create_table = sql.data;
529622
}
530623
else
531624
{
532625
/* VACUUM FULL mode (non-clustered tables) */
533-
table.create_table = create_table;
626+
table.create_table = sql.data;
534627
}
535628
}
536629
else if (!orderby[0])
537630
{
538631
/* VACUUM FULL mode (for clustered tables too) */
539-
table.create_table = create_table;
632+
table.create_table = sql.data;
540633
}
541634
else
542635
{
543636
/* User specified ORDER BY */
544-
appendStringInfo(&sql, "%s ORDER BY %s", create_table, orderby);
545-
table.create_table = sql.data;
637+
appendStringInfoString(&sql, " ORDER BY ");
638+
appendStringInfoString(&sql, orderby);
546639
}
547640

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-
554641
repack_one_table(&table, orderby);
555642
}
556643
ret = true;
@@ -594,7 +681,7 @@ static bool
594681
rebuild_indexes(const repack_table *table)
595682
{
596683
PGresult *res;
597-
const char *params[1];
684+
const char *params[2];
598685
int num_indexes;
599686
int i;
600687
int num_active_workers;
@@ -606,6 +693,7 @@ rebuild_indexes(const repack_table *table)
606693
elog(DEBUG2, "---- create indexes ----");
607694

608695
params[0] = utoa(table->target_oid, buffer);
696+
params[1] = moveidx ? tablespace : NULL;
609697

610698
/* First, just display a warning message for any invalid indexes
611699
* which may be on the table (mostly to match the behavior of 1.1.8).
@@ -621,8 +709,9 @@ rebuild_indexes(const repack_table *table)
621709
}
622710

623711
res = execute("SELECT indexrelid,"
624-
" repack.repack_indexdef(indexrelid, indrelid) "
625-
" FROM pg_index WHERE indrelid = $1 AND indisvalid", 1, params);
712+
" repack.repack_indexdef(indexrelid, indrelid, $2) "
713+
" FROM pg_index WHERE indrelid = $1 AND indisvalid",
714+
2, params);
626715

627716
num_indexes = PQntuples(res);
628717

@@ -1453,6 +1542,8 @@ pgut_help(bool details)
14531542
printf("Options:\n");
14541543
printf(" -a, --all repack all databases\n");
14551544
printf(" -t, --table=TABLE repack specific table only\n");
1545+
printf(" -s, --tablespace=TABLESPC move repacked tables to a new tablespace\n");
1546+
printf(" -S, --moveidx move repacked indexes to TABLESPC too\n");
14561547
printf(" -o, --order-by=COLUMNS order by columns instead of cluster keys\n");
14571548
printf(" -n, --no-order do vacuum full instead of cluster\n");
14581549
printf(" -j --jobs Use this many parallel jobs for each table\n");

0 commit comments

Comments
 (0)