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

Сергей Мелехин cpro29a at gmail.com
Mon Sep 22 14:27:14 JST 2014


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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20140922/ea967928/attachment.html>


More information about the pgpool-general mailing list