<div dir="ltr">Hi<br><br>I have applied your purposed patchs after commenting out CloseStatement on line 1490 in postgresql/driver/pq3.py as suggested by you but still gets the same error <font face="courier new, monospace">ValueError: need more than 0 values to unpack,</font> when I execute your test case with pgpool-II.<br><br>I think the real problem is py-postgresql is not properly handling the close cursor scenario. <i>It wrongly issues the ClosePortal packet to the server for closing the opened cursor</i>. which is the cause of real problem. The fact that the test case works fine with bare PostgreSQL server without involvement of pgpool-II is because of a reason that PostgreSQL ignores the Close Portal packets for non existing portals. and when py-postgresql issues the close portal command with cursor name, that command gets silently ignored by postgres server and test case reports success.<div><br>But pgpool-II does not ignores the close portal for non existing portals and throws an error. Same reason we need to comment out CloseStatement in postgresql/driver/pq3.py to get pass the connecting phase with pgpool-II.<br><br></div><div>So apparently the solution for the problem is to fix the close cursor message in py-postgresql. I have tried this by commenting out the code used by py-postgresql to close cursor (<b>postgresql/protocol/client3.py line 364</b>) and the test case works fine after that.<br><br>Regards<br>Usama<br></div></div><div class="gmail_extra"><br><div class="gmail_quote">On Mon, Sep 22, 2014 at 10:27 AM, Сергей Мелехин <span dir="ltr"><<a href="mailto:cpro29a@gmail.com" target="_blank">cpro29a@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr"><div>Hello!</div><div><br></div><div>We are experiencing random failures when calling Connection.proc() with set returning functions through pgpool2.<br>I use py-postgresql-1.1.0 with commented out CloseStatement in line 1490 in postgresql/driver/pq3.py, to use it with pgpool2 (3.1.3 and 3.3.2) and postgresql 9.3.5.<br>And it looks like, that there are protocol errors.<br><br>Here is code that fails:<br><br><font face="courier new, monospace">import postgresql<br> <br>URL = "pq://hr:hr@localhost:5434/hr" //using pgpool2 (3.3.2)<br> <br>def main():<br> db = postgresql.open(URL)<br> db.execute("""<br> CREATE OR REPLACE FUNCTION foo()<br> RETURNS SETOF boolean AS<br> $BODY$<br> select true;<br> $BODY$<br> LANGUAGE sql VOLATILE<br> COST 100<br> ROWS 1000;<br> ALTER FUNCTION foo() OWNER TO hr;<br> <br> """)<br> # db.query("select foo()") # works<br> # v = db.proc("foo()")() #works<br> db.proc("foo()")() # does not work<br> <br> for i in range(200):<br> db.query("select 1") //error comes here<br> <br> <br>if __name__ == "__main__":<br> main()</font><br><br> <br>And I get one of two errors:<br><br><font face="courier new, monospace"> Traceback (most recent call last):<br> File "/home/sergey/projects/emply/sitelib/sitelib/tests/error.py", line 28, in <module><br> main()<br> File "/home/sergey/projects/emply/sitelib/sitelib/tests/error.py", line 24, in main<br> db.query("select 1")<br> File "/home/sergey/projects/emply/.venv/sitelib/lib/python3.4/site-packages/py_postgresql-1.1.0-py3.4-linux-x86_64.egg/postgresql/driver/pq3.py", line 1233, in __call__<br> return self._prepare(query)(*parameters)<br> File "/home/sergey/projects/emply/.venv/sitelib/lib/python3.4/site-packages/py_postgresql-1.1.0-py3.4-linux-x86_64.egg/postgresql/driver/pq3.py", line 2329, in prepare<br> ps._fini()<br> File "/home/sergey/projects/emply/.venv/sitelib/lib/python3.4/site-packages/py_postgresql-1.1.0-py3.4-linux-x86_64.egg/postgresql/driver/pq3.py", line 1521, in _fini<br> (*head, argtypes, tupdesc, last) = self._xact.messages_received()<br>ValueError: need more than 0 values to unpack</font><br> <br>Or:<br><br><font face="courier new, monospace">Traceback (most recent call last):<br> File "/home/sergey/projects/emply/sitelib/sitelib/tests/error.py", line 28, in <module><br> main()<br> File "/home/sergey/projects/emply/sitelib/sitelib/tests/error.py", line 24, in main<br> db.query("select 1")<br> File "/home/sergey/projects/emply/.venv/sitelib/lib/python3.4/site-packages/py_postgresql-1.1.0-py3.4-linux-x86_64.egg/postgresql/driver/pq3.py", line 1233, in __call__<br> return self._prepare(query)(*parameters)<br> File "/home/sergey/projects/emply/.venv/sitelib/lib/python3.4/site-packages/py_postgresql-1.1.0-py3.4-linux-x86_64.egg/postgresql/driver/pq3.py", line 1573, in __call__<br> c = SingleXactFetch(self, parameters)<br> File "/home/sergey/projects/emply/.venv/sitelib/lib/python3.4/site-packages/py_postgresql-1.1.0-py3.4-linux-x86_64.egg/postgresql/driver/pq3.py", line 868, in __init__<br> Output.__init__(self, '')<br> File "/home/sergey/projects/emply/.venv/sitelib/lib/python3.4/site-packages/py_postgresql-1.1.0-py3.4-linux-x86_64.egg/postgresql/driver/pq3.py", line 655, in __init__<br> self._init()<br> File "/home/sergey/projects/emply/.venv/sitelib/lib/python3.4/site-packages/py_postgresql-1.1.0-py3.4-linux-x86_64.egg/postgresql/driver/pq3.py", line 885, in _init<br> STEP()<br> File "/home/sergey/projects/emply/.venv/sitelib/lib/python3.4/site-packages/py_postgresql-1.1.0-py3.4-linux-x86_64.egg/postgresql/driver/pq3.py", line 2615, in _pq_step<br> pq.step()<br> File "/home/sergey/projects/emply/.venv/sitelib/lib/python3.4/site-packages/py_postgresql-1.1.0-py3.4-linux-x86_64.egg/postgresql/protocol/client3.py", line 420, in step<br> "unexpected PQ transaction state: " + repr(dir)<br>RuntimeError: unexpected PQ transaction state: None</font><div style="color:rgb(80,0,80);font-family:arial,sans-serif;font-size:12.7272720336914px"><font face="courier new, monospace"><br></font></div>We got rid of errors, by making some minor changes in pq3.py:<div style="color:rgb(80,0,80);font-family:arial,sans-serif;font-size:12.7272720336914px"><br></div><div style="color:rgb(80,0,80);font-family:arial,sans-serif;font-size:12.7272720336914px"><a href="https://github.com/C-Pro/fe/commit/26a360be216975229215d458b27c644e1bd1893b" target="_blank">https://github.com/C-Pro/fe/commit/26a360be216975229215d458b27c644e1bd1893b</a><br></div><div style="color:rgb(80,0,80);font-family:arial,sans-serif;font-size:12.7272720336914px"><a href="https://github.com/C-Pro/fe/commit/a69da2b05048e7d801cece6a75e4c80a53cc27d4" target="_blank">https://github.com/C-Pro/fe/commit/a69da2b05048e7d801cece6a75e4c80a53cc27d4</a><br></div><div style="color:rgb(80,0,80);font-family:arial,sans-serif;font-size:12.7272720336914px"><br></div>and<div style="color:rgb(80,0,80);font-family:arial,sans-serif;font-size:12.7272720336914px"><br></div><div style="color:rgb(80,0,80);font-family:arial,sans-serif;font-size:12.7272720336914px"><a href="https://github.com/C-Pro/fe/commit/adb4d98240eeaf250543d2350a5d3c71fb58c5f8" target="_blank">https://github.com/C-Pro/fe/commit/adb4d98240eeaf250543d2350a5d3c71fb58c5f8</a><br></div>to return list, and changed version to automatically pull new version as an app dependency on deployment.<br><br>All existing tests pass.<br><br>It would be perfect if you could review changes, because I think tere is misunderstanding of extended protocol between pgpool2 and py-postgresql.<br><br>With best regards, Sergey Melekhin</div><div><br></div>
</div>
<br>_______________________________________________<br>
pgpool-general mailing list<br>
<a href="mailto:pgpool-general@pgpool.net">pgpool-general@pgpool.net</a><br>
<a href="http://www.pgpool.net/mailman/listinfo/pgpool-general" target="_blank">http://www.pgpool.net/mailman/listinfo/pgpool-general</a><br>
<br></blockquote></div><br></div>