From eea9d3470af45c59c551a658b15b6c47ce6da103 Mon Sep 17 00:00:00 2001 From: Paul Ramsey Date: Wed, 16 Mar 2011 13:02:43 +0000 Subject: [PATCH] Jeff Adams patch to support tablespaces (#67) git-svn-id: http://svn.osgeo.org/postgis/trunk@6905 b70326c6-7e19-0410-871a-916f4a2858ee --- loader/shp2pgsql-cli.c | 16 ++++++++++- loader/shp2pgsql-core.c | 60 ++++++++++++++++++++++++++++++++++++----- loader/shp2pgsql-core.h | 6 +++++ 3 files changed, 74 insertions(+), 8 deletions(-) diff --git a/loader/shp2pgsql-cli.c b/loader/shp2pgsql-cli.c index 7174fa1dc..a61078dc1 100644 --- a/loader/shp2pgsql-cli.c +++ b/loader/shp2pgsql-cli.c @@ -43,6 +43,12 @@ usage() " attribute column. (default: \"UTF-8\")\n" )); printf(_( " -N NULL geometries handling policy (insert*,skip,abort).\n" )); printf(_( " -n Only import DBF file.\n" )); + printf(_( " -T Specify the tablespace for the new table.\n" + " Note that indexes will still use the default tablespace unless the" + " -X flag is also used.")); + printf(_( " -X Specify the tablespace for the table's indexes.\n" + " This applies to the primary key, and the spatial index if" + " the -I flag is used." )); printf(_( " -? Display this help screen.\n" )); } @@ -73,7 +79,7 @@ main (int argc, char **argv) config = malloc(sizeof(SHPLOADERCONFIG)); set_config_defaults(config); - while ((c = pgis_getopt(argc, argv, "kcdapGDs:Sg:iW:wIN:n")) != EOF) + while ((c = pgis_getopt(argc, argv, "kcdapGDs:Sg:iW:wIN:nT:X:")) != EOF) { switch (c) { @@ -155,6 +161,14 @@ main (int argc, char **argv) } break; + case 'T': + config->tablespace = pgis_optarg; + break; + + case 'X': + config->idxtablespace = pgis_optarg; + break; + case '?': usage(); exit(0); diff --git a/loader/shp2pgsql-core.c b/loader/shp2pgsql-core.c index 48043b1e2..957ef89ee 100644 --- a/loader/shp2pgsql-core.c +++ b/loader/shp2pgsql-core.c @@ -797,6 +797,8 @@ set_config_defaults(SHPLOADERCONFIG *config) config->null_policy = POLICY_NULL_INSERT; config->sr_id = -1; config->hwgeom = 0; + config->tablespace = NULL; + config->idxtablespace = NULL; } /* Create a new shapefile state object */ @@ -1249,12 +1251,12 @@ ShpLoaderGetSQLHeader(SHPLOADERSTATE *state, char **strheader) */ if (state->config->schema) { - stringbuffer_aprintf(sb, "CREATE TABLE \"%s\".\"%s\" (gid serial PRIMARY KEY", + stringbuffer_aprintf(sb, "CREATE TABLE \"%s\".\"%s\" (gid serial", state->config->schema, state->config->table); } else { - stringbuffer_aprintf(sb, "CREATE TABLE \"%s\" (gid serial PRIMARY KEY", state->config->table); + stringbuffer_aprintf(sb, "CREATE TABLE \"%s\" (gid serial", state->config->table); } /* Generate the field types based upon the shapefile information */ @@ -1334,7 +1336,47 @@ ShpLoaderGetSQLHeader(SHPLOADERSTATE *state, char **strheader) stringbuffer_aprintf(sb, ",\n\"%s\" geography(%s%s,%d)", state->config->geom, state->pgtype, dimschar, 4326); } - stringbuffer_aprintf(sb, ");\n"); + stringbuffer_aprintf(sb, ")"); + /* Tablespace is optional. */ + if (state->config->tablespace != NULL) + { + stringbuffer_aprintf(sb, " TABLESPACE \"%s\"", state->config->tablespace); + } + stringbuffer_aprintf(sb, ";\n"); + + /* Create the primary key. This is done separately because the index for the PK needs + * to be in the correct tablespace. */ + + /* TODO: Currently PostgreSQL does not allow specifying an index to use for a PK (so you get + * a default one called table_pkey) and it does not provide a way to create a PK index + * in a specific tablespace. So as a hacky solution we create the PK, then move the + * index to the correct tablespace. Eventually this should be: + * CREATE INDEX table_pkey on table(gid) TABLESPACE tblspc; + * ALTER TABLE table ADD PRIMARY KEY (gid) USING INDEX table_pkey; + * A patch has apparently been submitted to PostgreSQL to enable this syntax, see this thread: + * http://archives.postgresql.org/pgsql-hackers/2011-01/msg01405.php */ + stringbuffer_aprintf(sb, "ALTER TABLE "); + /* Schema is optional, include if present. */ + if (state->config->schema) + { + stringbuffer_aprintf(sb, "\"%s\".",state->config->schema); + } + stringbuffer_aprintf(sb, "\"%s\" ADD PRIMARY KEY (gid);\n", state->config->table); + /* Tablespace is optional for the index. */ + if (state->config->idxtablespace != NULL) + { + stringbuffer_aprintf(sb, "ALTER INDEX "); + if (state->config->schema) + { + stringbuffer_aprintf(sb, "\"%s\".",state->config->schema); + } + /* WARNING: We're assuming the default "table_pkey" name for the primary + * key index. PostgreSQL may use "table_pkey1" or similar in the + * case of a name conflict, so you may need to edit the produced + * SQL in this rare case. */ + stringbuffer_aprintf(sb, "\"%s_pkey\" SET TABLESPACE \"%s\";\n", + state->config->table, state->config->idxtablespace); + } /* Create the geometry column with an addgeometry call */ if (state->config->readshape == 1 && (!state->config->geography)) @@ -1754,15 +1796,19 @@ ShpLoaderGetSQLFooter(SHPLOADERSTATE *state, char **strfooter) /* Create gist index if specified and not in "prepare" mode */ if (state->config->createindex) { + stringbuffer_aprintf(sb, "CREATE INDEX \"%s_%s_gist\" ON ", state->config->table, state->config->geom); + /* Schema is optional, include if present. */ if (state->config->schema) { - stringbuffer_aprintf(sb, "CREATE INDEX \"%s_%s_gist\" ON \"%s\".\"%s\" using gist (\"%s\" %s);\n", state->config->table, state->config->geom, - state->config->schema, state->config->table, state->config->geom, ops); + stringbuffer_aprintf(sb, "\"%s\".",state->config->schema); } - else + stringbuffer_aprintf(sb, "\"%s\" USING GIST (\"%s\" %s)", state->config->table, state->config->geom, ops); + /* Tablespace is also optional. */ + if (state->config->idxtablespace != NULL) { - stringbuffer_aprintf(sb, "CREATE INDEX \"%s_%s_gist\" ON \"%s\" using gist (\"%s\" %s);\n", state->config->table, state->config->geom, state->config->table, state->config->geom, ops); + stringbuffer_aprintf(sb, " TABLESPACE \"%s\"", state->config->idxtablespace); } + stringbuffer_aprintf(sb, ";\n"); } /* End the transaction */ diff --git a/loader/shp2pgsql-core.h b/loader/shp2pgsql-core.h index 849b7916b..8c93ebacc 100644 --- a/loader/shp2pgsql-core.h +++ b/loader/shp2pgsql-core.h @@ -123,6 +123,12 @@ typedef struct shp_loader_config /* iconv encoding name */ char *encoding; + /* tablespace name for the table */ + char *tablespace; + + /* tablespace name for the indexes */ + char *idxtablespace; + /* how to handle nulls */ int null_policy; -- 2.50.1