Skip to content

Commit a561c92

Browse files
dvarrazzodvarrazzo
committed
Parse tablespace/where in parse_indexdef
Makes injecting the target tablespace much easier and fixes interaction between tablespace and WITH/WHERE clauses. Added tests to check the correct indexes definition.
1 parent 2b7e1b2 commit a561c92

File tree

3 files changed

+83
-40
lines changed

3 files changed

+83
-40
lines changed

‎bin/expected/tablespace.out

Lines changed: 25 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -17,6 +17,31 @@ CREATE INDEX testts1_with_idx on testts1 (id) with (fillfactor=80);
1717
INSERT INTO testts1 (data) values ('a');
1818
INSERT INTO testts1 (data) values ('b');
1919
INSERT INTO testts1 (data) values ('c');
20+
-- check the indexes definitions
21+
SELECT regexp_replace(
22+
repack.repack_indexdef(indexrelid, 'testts1'::regclass, NULL),
23+
'_[0-9]+', '_OID', 'g')
24+
FROM pg_index i join pg_class c ON c.oid = indexrelid
25+
WHERE indrelid = 'testts1'::regclass ORDER BY relname;
26+
regexp_replace
27+
----------------------------------------------------------------------------------
28+
CREATE INDEX index_OID ON repack.table_OID USING btree (id) WHERE (id > 0)
29+
CREATE UNIQUE INDEX index_OID ON repack.table_OID USING btree (id)
30+
CREATE INDEX index_OID ON repack.table_OID USING btree (id) WITH (fillfactor=80)
31+
(3 rows)
32+
33+
SELECT regexp_replace(
34+
repack.repack_indexdef(indexrelid, 'testts1'::regclass, 'foo'),
35+
'_[0-9]+', '_OID', 'g')
36+
FROM pg_index i join pg_class c ON c.oid = indexrelid
37+
WHERE indrelid = 'testts1'::regclass ORDER BY relname;
38+
regexp_replace
39+
-------------------------------------------------------------------------------------------------
40+
CREATE INDEX index_OID ON repack.table_OID USING btree (id) TABLESPACE foo WHERE (id > 0)
41+
CREATE UNIQUE INDEX index_OID ON repack.table_OID USING btree (id) TABLESPACE foo
42+
CREATE INDEX index_OID ON repack.table_OID USING btree (id) WITH (fillfactor=80) TABLESPACE foo
43+
(3 rows)
44+
2045
-- can move the tablespace from default
2146
\! pg_repack --dbname=contrib_regression --no-order --table=testts1 --tablespace testts
2247
INFO: repacking table "testts1"

‎bin/sql/tablespace.sql

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -16,6 +16,19 @@ INSERT INTO testts1 (data) values ('a');
1616
INSERT INTO testts1 (data) values ('b');
1717
INSERT INTO testts1 (data) values ('c');
1818

19+
-- check the indexes definitions
20+
SELECT regexp_replace(
21+
repack.repack_indexdef(indexrelid, 'testts1'::regclass, NULL),
22+
'_[0-9]+', '_OID', 'g')
23+
FROM pg_index i join pg_class c ON c.oid = indexrelid
24+
WHERE indrelid = 'testts1'::regclass ORDER BY relname;
25+
26+
SELECT regexp_replace(
27+
repack.repack_indexdef(indexrelid, 'testts1'::regclass, 'foo'),
28+
'_[0-9]+', '_OID', 'g')
29+
FROM pg_index i join pg_class c ON c.oid = indexrelid
30+
WHERE indrelid = 'testts1'::regclass ORDER BY relname;
31+
1932
-- can move the tablespace from default
2033
\! pg_repack --dbname=contrib_regression --no-order --table=testts1 --tablespace testts
2134

‎lib/repack.c

Lines changed: 45 additions & 40 deletions
Original file line numberDiff line numberDiff line change
@@ -305,7 +305,9 @@ typedef struct IndexDef
305305
char *table; /* table name including schema */
306306
char *type; /* btree, hash, gist or gin */
307307
char *columns; /* column definition */
308-
char *options; /* options after columns. WITH, TABLESPACE and WHERE */
308+
char *options; /* options after columns, before TABLESPACE (e.g. COLLATE) */
309+
char *tablespace; /* tablespace if specified */
310+
char *where; /* WHERE content if specified */
309311
} IndexDef;
310312

311313
static char *
@@ -349,14 +351,14 @@ skip_const(Oid index, char *sql, const char *arg1, const char *arg2)
349351
static char *
350352
skip_until_const(Oid index, char *sql, const char *what)
351353
{
352-
char *pos;
354+
char *pos;
353355

354356
if ((pos = strstr(sql, what)))
355357
{
356-
size_t len;
358+
size_t len;
357359

358-
len = strlen(what);
359-
pos[len] = '\0';
360+
len = strlen(what);
361+
pos[-1] = '\0';
360362
return pos + len + 1;
361363
}
362364

@@ -462,6 +464,7 @@ parse_indexdef(IndexDef *stmt, Oid index, Oid table)
462464
char *sql = pg_get_indexdef_string(index);
463465
const char *idxname = get_quoted_relname(index);
464466
const char *tblname = get_relation_name(table);
467+
const char *limit = strchr(sql, '\0');
465468

466469
/* CREATE [UNIQUE] INDEX */
467470
stmt->create = sql;
@@ -486,8 +489,36 @@ parse_indexdef(IndexDef *stmt, Oid index, Oid table)
486489
stmt->columns = sql;
487490
if ((sql = skip_until(index, sql, ')')) == NULL)
488491
parse_error(index);
492+
489493
/* options */
490494
stmt->options = sql;
495+
stmt->tablespace = NULL;
496+
stmt->where = NULL;
497+
498+
/* Is there a tablespace? Note that apparently there is never, but
499+
* if there was one it would appear here. */
500+
if (sql < limit && strstr(sql, "TABLESPACE"))
501+
{
502+
sql = skip_until_const(index, sql, "TABLESPACE");
503+
stmt->tablespace = sql;
504+
sql = skip_ident(index, sql);
505+
}
506+
507+
/* Note: assuming WHERE is the only clause allowed after TABLESPACE */
508+
if (sql < limit && strstr(sql, "WHERE"))
509+
{
510+
sql = skip_until_const(index, sql, "WHERE");
511+
stmt->where = sql;
512+
}
513+
514+
elog(DEBUG2, "indexdef.create = %s", stmt->create);
515+
elog(DEBUG2, "indexdef.index = %s", stmt->index);
516+
elog(DEBUG2, "indexdef.table = %s", stmt->table);
517+
elog(DEBUG2, "indexdef.type = %s", stmt->type);
518+
elog(DEBUG2, "indexdef.columns = %s", stmt->columns);
519+
elog(DEBUG2, "indexdef.options = %s", stmt->options);
520+
elog(DEBUG2, "indexdef.tspace = %s", stmt->tablespace);
521+
elog(DEBUG2, "indexdef.where = %s", stmt->where);
491522
}
492523

493524
/*
@@ -546,12 +577,6 @@ repack_get_order_by(PG_FUNCTION_ARGS)
546577
int nattr;
547578

548579
parse_indexdef(&stmt, index, table);
549-
elog(DEBUG2, "indexdef.create = %s", stmt.create);
550-
elog(DEBUG2, "indexdef.index = %s", stmt.index);
551-
elog(DEBUG2, "indexdef.table = %s", stmt.table);
552-
elog(DEBUG2, "indexdef.type = %s", stmt.type);
553-
elog(DEBUG2, "indexdef.columns = %s", stmt.columns);
554-
elog(DEBUG2, "indexdef.options = %s", stmt.options);
555580

556581
/*
557582
* FIXME: this is very unreliable implementation but I don't want to
@@ -660,36 +685,16 @@ repack_indexdef(PG_FUNCTION_ARGS)
660685
parse_indexdef(&stmt, index, table);
661686

662687
initStringInfo(&str);
663-
appendStringInfo(&str, "%s index_%u ON repack.table_%u USING %s (%s)",
664-
stmt.create, index, table, stmt.type, stmt.columns);
688+
appendStringInfo(&str, "%s index_%u ON repack.table_%u USING %s (%s)%s",
689+
stmt.create, index, table, stmt.type, stmt.columns, stmt.options);
665690

666-
/* Replace the tablespace in the index options */
667-
if (tablespace == NULL)
668-
{
669-
/* tablespace is just fine */
670-
appendStringInfoString(&str, stmt.options);
671-
}
672-
else
673-
{
674-
if (NULL == strstr(stmt.options, "TABLESPACE"))
675-
{
676-
/* tablespace is to append */
677-
appendStringInfoString(&str, " TABLESPACE ");
678-
appendStringInfoString(&str, NameStr(*tablespace));
679-
}
680-
else
681-
{
682-
/* tablespace is to replace */
683-
char *tmp, *limit;
684-
limit = strchr(stmt.options, '\0');
685-
tmp = skip_until_const(index, stmt.options, " TABLESPACE");
686-
appendStringInfoString(&str, stmt.options);
687-
appendStringInfo(&str, " %s", NameStr(*tablespace));
688-
tmp = skip_ident(index, tmp);
689-
if (tmp < limit)
690-
appendStringInfo(&str, " %s", tmp);
691-
}
692-
}
691+
/* specify the new tablespace or the original one if any */
692+
if (tablespace || stmt.tablespace)
693+
appendStringInfo(&str, " TABLESPACE %s",
694+
(tablespace ? NameStr(*tablespace) : stmt.tablespace));
695+
696+
if (stmt.where)
697+
appendStringInfo(&str, " WHERE %s", stmt.where);
693698

694699
PG_RETURN_TEXT_P(cstring_to_text(str.data));
695700
}

0 commit comments

Comments
 (0)