<div dir="ltr"><div><div><div>Hello, <br><br></div>We are currently migrating from db2 to postgres. Our setup is quite simple: we have one tool that reads (reporting service), and only 2 tools that write.  This means that we'll never get in the situation where we have >20 connections to the db at the same time. <br>
But because our sql statements are sometimes very complicated we have set up postgres with streaming replication, and placed pgpool in front. <br><br></div>pgpool really works good for read-only cursors: our reporting system works great, and connections are send to both server.<br>
However we have a problem with our ETL system.<br>From the moment it starts writing data it fails. <br><br></div>I have the same problem when I try it with python: (this shows better what I mean)<br><pre style class=""><code><span class="">>>></span><span class=""> import pyodbc
</span><span class="">>>></span><span class=""> conn </span><span class="">=</span><span class=""> pyodbc</span><span class="">.</span><span class="">connect</span><span class="">(</span><span class="">'DSN=PostgreSQL'</span><span class="">)</span><span class="">
</span><span class="">>>></span><span class=""> </span><span class="">cursor</span><span class="">=</span><span class="">conn</span><span class="">.</span><span class="">cursor</span><span class="">()</span><span class="">
</span><span class="">>>></span><span class=""> query </span><span class="">=</span><span class=""> </span><span class="">"INSERT INTO table foo () values (), ()"</span><span class="">
</span><span class="">>>></span><span class=""> </span><span class="">cursor</span><span class="">.</span><span class="">execute</span><span class="">(</span><span class="">query</span><span class="">)</span><span class="">
</span><span class=""><</span><span class="">pyodbc</span><span class="">.</span><span class="">Cursor</span><span class=""> object at </span><span class="">0x7f52858e2f30</span><span class="">></span><span class="">
</span><span class="">>>></span><span class=""> conn</span><span class="">.</span><span class="">commit</span><span class="">()</span><span class="">
Traceback </span><span class="">(</span><span class="">most recent call last</span><span class="">):</span><span class="">
  </span><span class="">File</span><span class=""> </span><span class="">"<stdin>"</span><span class="">,</span><span class=""> line </span><span class="">1</span><span class="">,</span><span class=""> </span><span class="">in</span><span class=""> </span><span class=""><</span><span class="">module</span><span class="">></span><span class="">
pyodbc</span><span class="">.</span><span class="">Error</span><span class="">:</span><span class=""> </span><span class="">(</span><span class="">'HY000'</span><span class="">,</span><span class=""> </span><span class="">'The driver did not supply an error!'</span><span class="">)</span><span class="">
</span><span class="">>>></span><span class=""> </span><span class="">cursor</span><span class="">.</span><span class="">execute</span><span class="">(</span><span class="">query</span><span class="">)</span><span class="">
Traceback </span><span class="">(</span><span class="">most recent call last</span><span class="">):</span><span class="">
  </span><span class="">File</span><span class=""> </span><span class="">"<stdin>"</span><span class="">,</span><span class=""> line </span><span class="">1</span><span class="">,</span><span class=""> </span><span class="">in</span><span class=""> </span><span class=""><</span><span class="">module</span><span class="">></span><span class="">
pyodbc</span><span class="">.</span><span class="">Error</span><span class="">:</span><span class=""> </span><span class="">(</span><span class="">'08S01'</span><span class="">,</span><span class=""> </span><span class="">'[08S01] Could not send Query(connection dead);\nCould not send Query(connection dead) (26) (SQLExecDirectW)'</span><span class="">)<br>
<br></span></code></pre><br><div><div>Is there a way we can get around that? <br><br></div><div>This script works great when we connect to postgres without going through pgpool.<br><br>wkr,<br>Bert<br></div></div></div>