[pgpool-general: 3205] Re: Setof returning function errors with pgpool2

Muhammad Usama m.usama at gmail.com
Tue Sep 30 03:55:27 JST 2014


Hi

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 ValueError: need more than 0 values to unpack, when I
execute your test case with pgpool-II.

I think the real problem is py-postgresql is not properly handling the
close cursor scenario. *It wrongly issues the ClosePortal packet to the
server for closing the opened cursor*. 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.

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.

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 (*postgresql/protocol/client3.py line 364*)
and the test case works fine after that.

Regards
Usama

On Mon, Sep 22, 2014 at 10:27 AM, Сергей Мелехин <cpro29a at gmail.com> wrote:

> Hello!
>
> We are experiencing random failures when calling Connection.proc() with
> set returning functions through pgpool2.
> 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.
> And it looks like, that there are protocol errors.
>
> Here is code that fails:
>
> import postgresql
>
> URL = "pq://hr:hr@localhost:5434/hr" //using pgpool2 (3.3.2)
>
> def main():
>     db = postgresql.open(URL)
>     db.execute("""
>         CREATE OR REPLACE FUNCTION foo()
>           RETURNS SETOF boolean AS
>         $BODY$
>            select true;
>         $BODY$
>           LANGUAGE sql VOLATILE
>           COST 100
>           ROWS 1000;
>         ALTER FUNCTION foo() OWNER TO hr;
>
>     """)
>     # db.query("select foo()") # works
>     # v = db.proc("foo()")() #works
>     db.proc("foo()")() # does not work
>
>     for i in range(200):
>         db.query("select 1") //error comes here
>
>
> if __name__ == "__main__":
>     main()
>
>
> And I get one of two errors:
>
>  Traceback (most recent call last):
>   File "/home/sergey/projects/emply/sitelib/sitelib/tests/error.py", line
> 28, in <module>
>     main()
>   File "/home/sergey/projects/emply/sitelib/sitelib/tests/error.py", line
> 24, in main
>     db.query("select 1")
>   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__
>     return self._prepare(query)(*parameters)
>   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
>     ps._fini()
>   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
>     (*head, argtypes, tupdesc, last) = self._xact.messages_received()
> ValueError: need more than 0 values to unpack
>
> Or:
>
> Traceback (most recent call last):
>   File "/home/sergey/projects/emply/sitelib/sitelib/tests/error.py", line
> 28, in <module>
>     main()
>   File "/home/sergey/projects/emply/sitelib/sitelib/tests/error.py", line
> 24, in main
>     db.query("select 1")
>   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__
>     return self._prepare(query)(*parameters)
>   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__
>     c = SingleXactFetch(self, parameters)
>   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__
>     Output.__init__(self, '')
>   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__
>     self._init()
>   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
>     STEP()
>   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
>     pq.step()
>   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
>     "unexpected PQ transaction state: " + repr(dir)
> RuntimeError: unexpected PQ transaction state: None
>
> We got rid of errors, by making some minor changes in pq3.py:
>
> https://github.com/C-Pro/fe/commit/26a360be216975229215d458b27c644e1bd1893b
> https://github.com/C-Pro/fe/commit/a69da2b05048e7d801cece6a75e4c80a53cc27d4
>
> and
>
> https://github.com/C-Pro/fe/commit/adb4d98240eeaf250543d2350a5d3c71fb58c5f8
> to return list, and changed version to automatically pull new version as
> an app dependency on deployment.
>
> All existing tests pass.
>
> It would be perfect if you could review changes, because I think tere is
> misunderstanding of extended protocol between pgpool2 and py-postgresql.
>
> With best regards, Sergey Melekhin
>
>
> _______________________________________________
> pgpool-general mailing list
> pgpool-general at pgpool.net
> http://www.pgpool.net/mailman/listinfo/pgpool-general
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20140929/fcfda60b/attachment.html>


More information about the pgpool-general mailing list