<p>I have a question about the search_path based approach used in <a class="issue-link js-issue-link" data-error-text="Failed to load issue title" data-id="57140433" data-permission-text="Issue title is private" data-url="https://github.com/openstreetmap/osm2pgsql/issues/279" data-hovercard-type="pull_request" data-hovercard-url="/openstreetmap/osm2pgsql/pull/279/hovercard" href="https://github.com/openstreetmap/osm2pgsql/pull/279">#279</a>: this seems to assume that we need to set the search_path to <code>SET search_path TO %1%,public;</code> 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).</p>
<p>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.</p>
<details>
<div class="highlight highlight-source-c++"><pre>diff --git a/db-copy.cpp b/db-copy.cpp
index a4f156e..47e27ab <span class="pl-c1">100644</span>
--- a/db-copy.cpp
+++ b/db-copy.cpp
@@ -<span class="pl-c1">104</span>,<span class="pl-c1">6</span> +<span class="pl-c1">104</span>,<span class="pl-c1">7</span> @@ <span class="pl-k">void</span> <span class="pl-en">db_copy_thread_t::connect</span>()
<span class="pl-c"><span class="pl-c">//</span> Let commits happen faster by delaying when they actually occur.</span>
pgsql_exec_simple(m_conn, PGRES_COMMAND_OK,
<span class="pl-s"><span class="pl-pds">"</span>SET synchronous_commit TO off;<span class="pl-pds">"</span></span>);
+ pgsql_exec_simple(m_conn, PGRES_COMMAND_OK, <span class="pl-s"><span class="pl-pds">"</span>SET search_path TO demo3;<span class="pl-pds">"</span></span>);
}
<span class="pl-k">void</span> <span class="pl-en">db_copy_thread_t::disconnect</span>()
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, <span class="pl-s"><span class="pl-pds">"</span>SET search_path TO demo3,public;<span class="pl-pds">"</span></span>);
+
<span class="pl-k">if</span> (append) {
<span class="pl-c"><span class="pl-c">//</span> Prepare queries for updating dependent objects</span>
<span class="pl-k">for</span> (<span class="pl-k">auto</span> &table : tables) {
@@ -<span class="pl-c1">861</span>,<span class="pl-c1">6</span> +<span class="pl-c1">863</span>,<span class="pl-c1">8</span> @@ <span class="pl-c1">middle_pgsql_t::get_query_instance</span>(std::shared_ptr<<span class="pl-c1">middle_t</span>> <span class="pl-k">const</span> &from) <span class="pl-k">const</span>
src-><span class="pl-smi">out_options</span>-><span class="pl-smi">database_options</span>.<span class="pl-c1">conninfo</span>().<span class="pl-c1">c_str</span>(), src-><span class="pl-smi">cache</span>,
src-><span class="pl-smi">persistent_cache</span>));
+ mid-><span class="pl-c1">exec_sql</span>(<span class="pl-s"><span class="pl-pds">"</span>SET search_path TO demo3;<span class="pl-pds">"</span></span>);
+
<span class="pl-c"><span class="pl-c">//</span> We use a connection per table to enable the use of COPY</span>
<span class="pl-k">for</span> (<span class="pl-k">int</span> i = <span class="pl-c1">0</span>; i < NUM_TABLES; i++) {
mid-><span class="pl-c1">exec_sql</span>(src-><span class="pl-smi">tables</span>[i].<span class="pl-smi">m_prepare_query</span>);
diff --git a/output-gazetteer.<span class="pl-smi">cpp</span> b/output-gazetteer.<span class="pl-smi">cpp</span>
<span class="pl-c1">index</span> fcc7b6f..16807b5 <span class="pl-c1">100644</span>
--- a/output-gazetteer.<span class="pl-smi">cpp</span>
+++ b/output-gazetteer.<span class="pl-smi">cpp</span>
@@ -<span class="pl-c1">69</span>,<span class="pl-c1">6</span> +<span class="pl-c1">69</span>,<span class="pl-c1">8</span> @@ <span class="pl-k">void</span> <span class="pl-smi">output_gazetteer_t::connect</span>()
<span class="pl-c1">PQerrorMessage</span>(m_conn));
<span class="pl-k">throw</span> <span class="pl-smi">std::runtime_error</span>(<span class="pl-s"><span class="pl-pds">"</span>Connecting to database<span class="pl-pds">"</span></span>);
}
+
+ <span class="pl-c1">pgsql_exec_simple</span>(m_conn, PGRES_COMMAND_OK, <span class="pl-s"><span class="pl-pds">"</span>SET search_path TO demo3;<span class="pl-pds">"</span></span>);
}
<span class="pl-k">int</span> <span class="pl-smi">output_gazetteer_t::start</span>()
diff --git a/table.<span class="pl-smi">cpp</span> b/table.<span class="pl-smi">cpp</span>
<span class="pl-c1">index</span> d077367..<span class="pl-smi">afeb3fe</span> <span class="pl-c1">100644</span>
--- a/table.<span class="pl-smi">cpp</span>
+++ b/table.<span class="pl-smi">cpp</span>
@@ -<span class="pl-c1">78</span>,<span class="pl-c1">6</span> +<span class="pl-c1">78</span>,<span class="pl-c1">8</span> @@ <span class="pl-k">void</span> <span class="pl-smi">table_t::connect</span>()
sql_conn = _conn;
<span class="pl-c"><span class="pl-c">//</span>let commits happen faster by delaying when they actually occur</span>
<span class="pl-c1">pgsql_exec_simple</span>(sql_conn, PGRES_COMMAND_OK, <span class="pl-s"><span class="pl-pds">"</span>SET synchronous_commit TO off;<span class="pl-pds">"</span></span>);
+
+ <span class="pl-c1">pgsql_exec_simple</span>(sql_conn, PGRES_COMMAND_OK, <span class="pl-s"><span class="pl-pds">"</span>SET search_path TO demo3;<span class="pl-pds">"</span></span>);
}
<span class="pl-k">void</span> <span class="pl-en">table_t::start</span>(std::string <span class="pl-k">const</span> &conninfo,
@@ -<span class="pl-c1">108</span>,<span class="pl-c1">6</span> +<span class="pl-c1">110</span>,<span class="pl-c1">9</span> @@ <span class="pl-k">void</span> <span class="pl-en">table_t::start</span>(std::string <span class="pl-k">const</span> &conninfo,
<span class="pl-en">pgsql_exec_simple</span>(sql_conn, PGRES_COMMAND_OK, <span class="pl-s"><span class="pl-pds">"</span>RESET client_min_messages<span class="pl-pds">"</span></span>);
+
+ pgsql_exec_simple(sql_conn, PGRES_COMMAND_OK, <span class="pl-s"><span class="pl-pds">"</span>SET search_path TO demo3,public;<span class="pl-pds">"</span></span>);
+
<span class="pl-c"><span class="pl-c">//</span>making a new table</span>
<span class="pl-k">if</span> (!append)
{
</pre></div>
</details>
<p style="font-size:small;-webkit-text-size-adjust:none;color:#666;">—<br />You are receiving this because you are subscribed to this thread.<br />Reply to this email directly, <a href="https://github.com/openstreetmap/osm2pgsql/issues/768?email_source=notifications&email_token=AA6353U7DTXPHZD2L435FATQT7RL5A5CNFSM4DQHUB2KYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEEHQLFY#issuecomment-554632599">view it on GitHub</a>, or <a href="https://github.com/notifications/unsubscribe-auth/AA6353RZXECZKXLR2G3JPO3QT7RL5ANCNFSM4DQHUB2A">unsubscribe</a>.<img src="https://github.com/notifications/beacon/AA6353VGA3RTMPKVTYIQUO3QT7RL5A5CNFSM4DQHUB2KYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEEHQLFY.gif" height="1" width="1" alt="" /></p>
<script type="application/ld+json">[
{
"@context": "http://schema.org",
"@type": "EmailMessage",
"potentialAction": {
"@type": "ViewAction",
"target": "https://github.com/openstreetmap/osm2pgsql/issues/768?email_source=notifications\u0026email_token=AA6353U7DTXPHZD2L435FATQT7RL5A5CNFSM4DQHUB2KYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEEHQLFY#issuecomment-554632599",
"url": "https://github.com/openstreetmap/osm2pgsql/issues/768?email_source=notifications\u0026email_token=AA6353U7DTXPHZD2L435FATQT7RL5A5CNFSM4DQHUB2KYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEEHQLFY#issuecomment-554632599",
"name": "View Issue"
},
"description": "View this Issue on GitHub",
"publisher": {
"@type": "Organization",
"name": "GitHub",
"url": "https://github.com"
}
}
]</script>