[Tile-serving] [openstreetmap/osm2pgsql] Creating new Schema for OSM data (#768)

[mmd]

I have a question about the search_path based approach used in #279: this seems to assume that we need to set the search_path to `SET search_path TO %1%,public;` everywhere, even though we need the public schema only in case of CREATE TABLE (e.g.due to the depency of hstore which presumably lives in the public schema).

I tried the patch below with a hardcoded schema name demo3. It seems to work here, even if there are no planet* tables in the public schema. 

<details>

```c++
diff --git a/db-copy.cpp b/db-copy.cpp
index a4f156e..47e27ab 100644
--- a/db-copy.cpp
+++ b/db-copy.cpp
@@ -104,6 +104,7 @@ void db_copy_thread_t::connect()
     // Let commits happen faster by delaying when they actually occur.
     pgsql_exec_simple(m_conn, PGRES_COMMAND_OK,
                       "SET synchronous_commit TO off;");
+    pgsql_exec_simple(m_conn, PGRES_COMMAND_OK, "SET search_path TO demo3;");
 }
 
 void db_copy_thread_t::disconnect()
diff --git a/middle-pgsql.cpp b/middle-pgsql.cpp
index b0f5ba1..0c76838 100644
--- a/middle-pgsql.cpp
+++ b/middle-pgsql.cpp
@@ -736,6 +736,8 @@ void middle_pgsql_t::start()
         util::exit_nicely();
     }
 
+    pgsql_exec_simple(m_query_conn, PGRES_COMMAND_OK, "SET search_path TO demo3,public;");
+
     if (append) {
         // Prepare queries for updating dependent objects
         for (auto &table : tables) {
@@ -861,6 +863,8 @@ middle_pgsql_t::get_query_instance(std::shared_ptr<middle_t> const &from) const
         src->out_options->database_options.conninfo().c_str(), src->cache,
         src->persistent_cache));
 
+    mid->exec_sql("SET search_path TO demo3;");
+
     // We use a connection per table to enable the use of COPY
     for (int i = 0; i < NUM_TABLES; i++) {
         mid->exec_sql(src->tables[i].m_prepare_query);
diff --git a/output-gazetteer.cpp b/output-gazetteer.cpp
index fcc7b6f..16807b5 100644
--- a/output-gazetteer.cpp
+++ b/output-gazetteer.cpp
@@ -69,6 +69,8 @@ void output_gazetteer_t::connect()
                 PQerrorMessage(m_conn));
         throw std::runtime_error("Connecting to database");
     }
+
+    pgsql_exec_simple(m_conn, PGRES_COMMAND_OK, "SET search_path TO demo3;");
 }
 
 int output_gazetteer_t::start()
diff --git a/table.cpp b/table.cpp
index d077367..afeb3fe 100644
--- a/table.cpp
+++ b/table.cpp
@@ -78,6 +78,8 @@ void table_t::connect()
     sql_conn = _conn;
     //let commits happen faster by delaying when they actually occur
     pgsql_exec_simple(sql_conn, PGRES_COMMAND_OK, "SET synchronous_commit TO off;");
+
+    pgsql_exec_simple(sql_conn, PGRES_COMMAND_OK, "SET search_path TO demo3;");
 }
 
 void table_t::start(std::string const &conninfo,
@@ -108,6 +110,9 @@ void table_t::start(std::string const &conninfo,
 
     pgsql_exec_simple(sql_conn, PGRES_COMMAND_OK, "RESET client_min_messages");
 
+
+    pgsql_exec_simple(sql_conn, PGRES_COMMAND_OK, "SET search_path TO demo3,public;");
+
     //making a new table
     if (!append)
     {

```
</details>

-- 
You are receiving this because you are subscribed to this thread.
Reply to this email directly or view it on GitHub:
https://github.com/openstreetmap/osm2pgsql/issues/768#issuecomment-554632599
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/tile-serving/attachments/20191116/19ab5513/attachment.html>