Skip to content

Commit c634444

Browse files
woodpeckwoodpeck
committed
fix tablespaces AGAIN. problem was that with no tablespaces specified,
osm2pgsql would now always use pg_default even if a different default tablespace has been created for the database.
1 parent 6c174cc commit c634444

4 files changed

Lines changed: 124 additions & 45 deletions

File tree

‎middle-pgsql.c‎

Lines changed: 62 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -63,10 +63,10 @@ static struct table_desc tables [] = {
6363
.name = "%p_nodes",
6464
.start = "BEGIN;\n",
6565
#ifdef FIXED_POINT
66-
.create = "CREATE TABLE %p_nodes (id int4 PRIMARY KEY USING INDEX TABLESPACE %i, lat int4 not null, lon int4 not null, tags text[]) TABLESPACE %t;\n",
66+
.create = "CREATE TABLE %p_nodes (id int4 PRIMARY KEY {USING INDEX TABLESPACE %i}, lat int4 not null, lon int4 not null, tags text[]) {TABLESPACE %t};\n",
6767
.prepare = "PREPARE insert_node (int4, int4, int4, text[]) AS INSERT INTO %p_nodes VALUES ($1,$2,$3,$4);\n"
6868
#else
69-
.create = "CREATE TABLE %p_nodes (id int4 PRIMARY KEY USING INDEX TABLESPACE %i, lat double precision not null, lon double precision not null, tags text[]) TABLESPACE %t;\n",
69+
.create = "CREATE TABLE %p_nodes (id int4 PRIMARY KEY {USING INDEX TABLESPACE %i}, lat double precision not null, lon double precision not null, tags text[]) {TABLESPACE %t};\n",
7070
.prepare = "PREPARE insert_node (int4, double precision, double precision, text[]) AS INSERT INTO %p_nodes VALUES ($1,$2,$3,$4);\n"
7171
#endif
7272
"PREPARE get_node (int4) AS SELECT lat,lon,tags FROM %p_nodes WHERE id = $1 LIMIT 1;\n"
@@ -84,9 +84,9 @@ static struct table_desc tables [] = {
8484
//table = t_way,
8585
.name = "%p_ways",
8686
.start = "BEGIN;\n",
87-
.create = "CREATE TABLE %p_ways (id int4 PRIMARY KEY USING INDEX TABLESPACE %i, nodes int4[] not null, tags text[], pending boolean not null) TABLESPACE %t;\n",
88-
.create_index = "CREATE INDEX %p_ways_idx ON %p_ways (id) TABLESPACE %i WHERE pending;\n",
89-
.array_indexes = "CREATE INDEX %p_ways_nodes ON %p_ways USING gin (nodes gin__int_ops) TABLESPACE %i;\n",
87+
.create = "CREATE TABLE %p_ways (id int4 PRIMARY KEY {USING INDEX TABLESPACE %i}, nodes int4[] not null, tags text[], pending boolean not null) {TABLESPACE %t};\n",
88+
.create_index = "CREATE INDEX %p_ways_idx ON %p_ways (id) {TABLESPACE %i} WHERE pending;\n",
89+
.array_indexes = "CREATE INDEX %p_ways_nodes ON %p_ways USING gin (nodes gin__int_ops) {TABLESPACE %i};\n",
9090
.prepare = "PREPARE insert_way (int4, int4[], text[], boolean) AS INSERT INTO %p_ways VALUES ($1,$2,$3,$4);\n"
9191
"PREPARE get_way (int4) AS SELECT nodes, tags, array_upper(nodes,1) FROM %p_ways WHERE id = $1;\n"
9292
"PREPARE way_done(int4) AS UPDATE %p_ways SET pending = false WHERE id = $1;\n"
@@ -101,9 +101,9 @@ static struct table_desc tables [] = {
101101
//table = t_rel,
102102
.name = "%p_rels",
103103
.start = "BEGIN;\n",
104-
.create = "CREATE TABLE %p_rels(id int4 PRIMARY KEY USING INDEX TABLESPACE %i, way_off int2, rel_off int2, parts int4[], members text[], tags text[], pending boolean not null) TABLESPACE %t;\n",
105-
.create_index = "CREATE INDEX %p_rels_idx ON %p_rels (id) TABLESPACE %i WHERE pending;\n",
106-
.array_indexes = "CREATE INDEX %p_rels_parts ON %p_rels USING gin (parts gin__int_ops) TABLESPACE %i;\n",
104+
.create = "CREATE TABLE %p_rels(id int4 PRIMARY KEY {USING INDEX TABLESPACE %i}, way_off int2, rel_off int2, parts int4[], members text[], tags text[], pending boolean not null) {TABLESPACE %t};\n",
105+
.create_index = "CREATE INDEX %p_rels_idx ON %p_rels (id) {TABLESPACE %i} WHERE pending;\n",
106+
.array_indexes = "CREATE INDEX %p_rels_parts ON %p_rels USING gin (parts gin__int_ops) {TABLESPACE %i};\n",
107107
.prepare = "PREPARE insert_rel (int4, int2, int2, int[], text[], text[]) AS INSERT INTO %p_rels VALUES ($1,$2,$3,$4,$5,$6,false);\n"
108108
"PREPARE get_rel (int4) AS SELECT members, tags, array_upper(members,1)/2 FROM %p_rels WHERE id = $1;\n"
109109
"PREPARE rel_done(int4) AS UPDATE %p_rels SET pending = false WHERE id = $1;\n"
@@ -1085,28 +1085,74 @@ static void pgsql_end(void)
10851085
}
10861086
}
10871087

1088-
/* Replace %p with prefix, %t with data tablespace, %i with index tablespace */
1088+
/**
1089+
* Helper to create SQL queries.
1090+
*
1091+
* Takes four arguments:
1092+
* prefix, t (tablespace) and i (index tablespace) - these may be NULL
1093+
* a pointer to an input string which, on success, is changed to point to the
1094+
* result (caller takes ownership).
1095+
*
1096+
* The input string is mangled as follows:
1097+
* %p replaced by the given prefix,
1098+
* %i replaced by the given index tablespace
1099+
* %t replaced by the given tablespace
1100+
* other occurrences of the "%" char are treated normally.
1101+
* any occurrence of { or } will be ignored (not copied to output string);
1102+
* anything inside {} is only copied if it contained at least one of
1103+
* %p, %i, %t that was not NULL.
1104+
*
1105+
* So, the input string
1106+
* Hello{ dear %i}!
1107+
* will, if i is set to "John", translate to
1108+
* Hello dear John!
1109+
* but if i is unset, translate to
1110+
* Hello!
1111+
*
1112+
* This is used for constructing SQL queries with proper tablespace settings.
1113+
*/
10891114
static inline void set_prefix_and_tbls(const char *prefix, const char *t, const char *i, const char **string)
10901115
{
10911116
char buffer[1024];
10921117
if (*string == NULL) return;
10931118
const char *source = *string;
10941119
char *dest = buffer;
1120+
char *openbrace = NULL;
1121+
int copied = 0;
1122+
10951123
while (*source) {
1096-
if (*source == '%') {
1124+
if (*source == '{') {
1125+
openbrace = dest;
1126+
copied = 0;
1127+
source++;
1128+
continue;
1129+
} else if (*source == '}') {
1130+
if (!copied && openbrace) dest = openbrace;
1131+
source++;
1132+
continue;
1133+
} else if (*source == '%') {
10971134
if (*(source+1) == 'p') {
1098-
strcpy(dest, prefix);
1099-
dest += strlen(prefix);
1135+
if (prefix) {
1136+
strcpy(dest, prefix);
1137+
dest += strlen(prefix);
1138+
copied = 1;
1139+
}
11001140
source+=2;
11011141
continue;
11021142
} else if (*(source+1) == 't') {
1103-
strcpy(dest, t);
1104-
dest += strlen(t);
1143+
if (t) {
1144+
strcpy(dest, t);
1145+
dest += strlen(t);
1146+
copied = 1;
1147+
}
11051148
source+=2;
11061149
continue;
11071150
} else if (*(source+1) == 'i') {
1108-
strcpy(dest, i);
1109-
dest += strlen(i);
1151+
if (i) {
1152+
strcpy(dest, i);
1153+
dest += strlen(i);
1154+
copied = 1;
1155+
}
11101156
source+=2;
11111157
continue;
11121158
}

‎osm2pgsql.c‎

Lines changed: 6 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -135,13 +135,13 @@ static void long_usage(char *arg0)
135135
printf(" -d|--database\tThe name of the PostgreSQL database to connect\n");
136136
printf(" \t\tto (default: gis).\n");
137137
printf(" -i|--tablespace-index\tThe name of the PostgreSQL tablespace where\n");
138-
printf(" \t\tall indexes will be created (default: pg_default).\n");
138+
printf(" \t\tall indexes will be created.\n");
139139
printf(" \t\tThe following options allow more fine-grained control:\n");
140140
printf(" --tablespace-main-data \ttablespace for main tables\n");
141141
printf(" --tablespace-main-index\ttablespace for main table indexes\n");
142142
printf(" --tablespace-slim-data \ttablespace for slim mode tables\n");
143143
printf(" --tablespace-slim-index\ttablespace for slim mode indexes\n");
144-
printf(" \t\t(all default to pg_default; -i is equivalent to setting\n");
144+
printf(" \t\t(if unset, use db's default; -i is equivalent to setting\n");
145145
printf(" \t\t--tablespace-main-index and --tablespace-slim-index)\n");
146146
printf(" -l|--latlong\t\tStore data in degrees of latitude & longitude.\n");
147147
printf(" -m|--merc\t\tStore data in proper spherical mercator (default)\n");
@@ -315,10 +315,10 @@ int main(int argc, char *argv[])
315315
const char *host=NULL;
316316
const char *password=NULL;
317317
const char *port = "5432";
318-
const char *tblsmain_index = "pg_default"; // default TABLESPACE for index on main tables
319-
const char *tblsmain_data = "pg_default"; // default TABLESPACE for main tables
320-
const char *tblsslim_index = "pg_default"; // default TABLESPACE for index on slim mode tables
321-
const char *tblsslim_data = "pg_default"; // default TABLESPACE for slim mode tables
318+
const char *tblsmain_index = NULL; // no default TABLESPACE for index on main tables
319+
const char *tblsmain_data = NULL; // no default TABLESPACE for main tables
320+
const char *tblsslim_index = NULL; // no default TABLESPACE for index on slim mode tables
321+
const char *tblsslim_data = NULL; // no default TABLESPACE for slim mode tables
322322
const char *conninfo = NULL;
323323
const char *prefix = "planet_osm";
324324
const char *style = OSM2PGSQL_DATADIR "/default.style";

‎output-gazetteer.c‎

Lines changed: 21 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -46,7 +46,7 @@
4646
" rank_address INTEGER," \
4747
" rank_search INTEGER," \
4848
" indexed BOOLEAN" \
49-
") TABLESPACE %s"
49+
") %s %s"
5050

5151
#define V2_CREATE_PLACE_TABLE \
5252
"CREATE TABLE place (" \
@@ -62,12 +62,12 @@
6262
" postcode TEXT," \
6363
" country_code VARCHAR(2)," \
6464
" extratags HSTORE" \
65-
") TABLESPACE %s"
65+
") %s %s"
6666

6767
#define ADMINLEVEL_NONE 100
6868

6969
#define CREATE_PLACE_ID_INDEX \
70-
"CREATE INDEX place_id_idx ON place USING BTREE (osm_type, osm_id) TABLESPACE %s"
70+
"CREATE INDEX place_id_idx ON place USING BTREE (osm_type, osm_id) %s %s"
7171

7272
#define TAGINFO_NODE 0x1u
7373
#define TAGINFO_WAY 0x2u
@@ -978,16 +978,29 @@ static int gazetteer_out_start(const struct output_options *options)
978978
/* Create types and functions */
979979
if (!Options->enable_hstore)
980980
{
981-
pgsql_exec(Connection, PGRES_COMMAND_OK, CREATE_KEYVALUETYPE_TYPE, Options->tblsmain_data);
981+
pgsql_exec(Connection, PGRES_COMMAND_OK, CREATE_KEYVALUETYPE_TYPE, "", "");
982982
}
983983
pgsql_exec(Connection, PGRES_COMMAND_OK, CREATE_WORDSCORE_TYPE, Options->tblsmain_data);
984984

985985
/* Create the new table */
986-
if (Options->enable_hstore)
987-
pgsql_exec(Connection, PGRES_COMMAND_OK, V2_CREATE_PLACE_TABLE, Options->tblsmain_data);
986+
if (Options->tblsmain_data)
987+
{
988+
pgsql_exec(Connection, PGRES_COMMAND_OK, Options->enable_hstore ?
989+
V2_CREATE_PLACE_TABLE : CREATE_PLACE_TABLE, "TABLESPACE", Options->tblsmain_data);
990+
}
991+
else
992+
{
993+
pgsql_exec(Connection, PGRES_COMMAND_OK, Options->enable_hstore ? V2_CREATE_PLACE_TABLE : CREATE_PLACE_TABLE, "", "");
994+
}
995+
if (Options->tblsmain_index)
996+
{
997+
pgsql_exec(Connection, PGRES_COMMAND_OK, CREATE_PLACE_ID_INDEX, "TABLESPACE", Options->tblsmain_index);
998+
}
988999
else
989-
pgsql_exec(Connection, PGRES_COMMAND_OK, CREATE_PLACE_TABLE, Options->tblsmain_data);
990-
pgsql_exec(Connection, PGRES_COMMAND_OK, CREATE_PLACE_ID_INDEX, Options->tblsmain_index);
1000+
{
1001+
pgsql_exec(Connection, PGRES_COMMAND_OK, CREATE_PLACE_ID_INDEX, "", "");
1002+
}
1003+
9911004
pgsql_exec(Connection, PGRES_TUPLES_OK, "SELECT AddGeometryColumn('place', 'geometry', %d, 'GEOMETRY', 2)", SRID);
9921005
pgsql_exec(Connection, PGRES_COMMAND_OK, "ALTER TABLE place ALTER COLUMN geometry SET NOT NULL");
9931006
}

‎output-pgsql.c‎

Lines changed: 35 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -1211,15 +1211,24 @@ static int pgsql_out_start(const struct output_options *options)
12111211
if (Options->enable_hstore) {
12121212
strcat(sql, ",tags hstore");
12131213
}
1214-
sprintf(sql + strlen(sql), ") TABLESPACE %s\n", Options->tblsmain_data);
1214+
strcat(sql, ")");
1215+
if (Options->tblsmain_data) {
1216+
sprintf(sql + strlen(sql), " TABLESPACE %s", Options->tblsmain_data);
1217+
}
1218+
strcat(sql, "\n");
12151219

12161220
pgsql_exec(sql_conn, PGRES_COMMAND_OK, "%s", sql);
12171221
pgsql_exec(sql_conn, PGRES_TUPLES_OK, "SELECT AddGeometryColumn('%s', 'way', %d, '%s', 2 );\n",
12181222
tables[i].name, SRID, tables[i].type );
12191223
pgsql_exec(sql_conn, PGRES_COMMAND_OK, "ALTER TABLE %s ALTER COLUMN way SET NOT NULL;\n", tables[i].name);
12201224
/* slim mode needs this to be able to apply diffs */
1221-
if (Options->slim)
1222-
pgsql_exec(sql_conn, PGRES_COMMAND_OK, "CREATE INDEX %s_pkey ON %s USING BTREE (osm_id) TABLESPACE %s;\n", tables[i].name, tables[i].name, Options->tblsmain_index);
1225+
if (Options->slim) {
1226+
sprintf(sql, "CREATE INDEX %s_pkey ON %s USING BTREE (osm_id)", tables[i].name, tables[i].name);
1227+
if (Options->tblsmain_index) {
1228+
sprintf(sql + strlen(sql), " TABLESPACE %s\n", Options->tblsmain_index);
1229+
}
1230+
pgsql_exec(sql_conn, PGRES_COMMAND_OK, "%s", sql);
1231+
}
12231232
} else {
12241233
/* Add any new columns referenced in the default.style */
12251234
PGresult *res;
@@ -1334,19 +1343,30 @@ static void *pgsql_out_stop_one(void *arg)
13341343
// Commit transaction
13351344
fprintf(stderr, "Committing transaction for %s\n", table->name);
13361345
pgsql_exec(sql_conn, PGRES_COMMAND_OK, "COMMIT");
1337-
if( !Options->append )
1346+
if (!Options->append)
13381347
{
1339-
fprintf(stderr, "Sorting data and creating indexes for %s\n", table->name);
1340-
pgsql_exec(sql_conn, PGRES_COMMAND_OK, "ANALYZE %s;\n", table->name);
1341-
pgsql_exec(sql_conn, PGRES_COMMAND_OK, "CREATE TABLE %s_tmp AS SELECT * FROM %s ORDER BY way;\n", table->name, table->name);
1342-
pgsql_exec(sql_conn, PGRES_COMMAND_OK, "DROP TABLE %s;\n", table->name);
1343-
pgsql_exec(sql_conn, PGRES_COMMAND_OK, "ALTER TABLE %s_tmp RENAME TO %s;\n", table->name, table->name);
1344-
pgsql_exec(sql_conn, PGRES_COMMAND_OK, "CREATE INDEX %s_index ON %s USING GIST (way GIST_GEOMETRY_OPS) TABLESPACE %s;\n", table->name, table->name, Options->tblsmain_index);
1345-
/* slim mode needs this to be able to apply diffs */
1346-
if( Options->slim )
1347-
pgsql_exec(sql_conn, PGRES_COMMAND_OK, "CREATE INDEX %s_pkey ON %s USING BTREE (osm_id) TABLESPACE %s;\n", table->name, table->name, Options->tblsmain_index);
1348-
pgsql_exec(sql_conn, PGRES_COMMAND_OK, "GRANT SELECT ON %s TO PUBLIC;\n", table->name);
1349-
pgsql_exec(sql_conn, PGRES_COMMAND_OK, "ANALYZE %s;\n", table->name);
1348+
fprintf(stderr, "Sorting data and creating indexes for %s\n", table->name);
1349+
pgsql_exec(sql_conn, PGRES_COMMAND_OK, "ANALYZE %s;\n", table->name);
1350+
pgsql_exec(sql_conn, PGRES_COMMAND_OK, "CREATE TABLE %s_tmp AS SELECT * FROM %s ORDER BY way;\n", table->name, table->name);
1351+
pgsql_exec(sql_conn, PGRES_COMMAND_OK, "DROP TABLE %s;\n", table->name);
1352+
pgsql_exec(sql_conn, PGRES_COMMAND_OK, "ALTER TABLE %s_tmp RENAME TO %s;\n", table->name, table->name);
1353+
if (Options->tblsmain_index) {
1354+
pgsql_exec(sql_conn, PGRES_COMMAND_OK, "CREATE INDEX %s_index ON %s USING GIST (way GIST_GEOMETRY_OPS) TABLESPACE %s;\n", table->name, table->name, Options->tblsmain_index);
1355+
} else {
1356+
pgsql_exec(sql_conn, PGRES_COMMAND_OK, "CREATE INDEX %s_index ON %s USING GIST (way GIST_GEOMETRY_OPS);\n", table->name, table->name);
1357+
}
1358+
1359+
/* slim mode needs this to be able to apply diffs */
1360+
if (Options->slim)
1361+
{
1362+
if (Options->tblsmain_index) {
1363+
pgsql_exec(sql_conn, PGRES_COMMAND_OK, "CREATE INDEX %s_pkey ON %s USING BTREE (osm_id) TABLESPACE %s;\n", table->name, table->name, Options->tblsmain_index);
1364+
} else {
1365+
pgsql_exec(sql_conn, PGRES_COMMAND_OK, "CREATE INDEX %s_pkey ON %s USING BTREE (osm_id);\n", table->name, table->name);
1366+
}
1367+
}
1368+
pgsql_exec(sql_conn, PGRES_COMMAND_OK, "GRANT SELECT ON %s TO PUBLIC;\n", table->name);
1369+
pgsql_exec(sql_conn, PGRES_COMMAND_OK, "ANALYZE %s;\n", table->name);
13501370
}
13511371
PQfinish(sql_conn);
13521372
table->sql_conn = NULL;

0 commit comments

Comments
 (0)