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

Muhammad Usama m.usama at gmail.com
Tue Sep 30 20:29:39 JST 2014 

Hi

After commenting out xm.append(element.ClosePortal(x)) I can successfully
execute the test case.
can you share the pgpool-II log when this this error is thrown.

--Usama

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

> Hello!
> Thank you for replying!
> I tried to comment out xm.append(element.ClosePortal(x))
> in  (postgresql/protocol/client3.py line 364) but still getting random
> errors
>
> [Tue Sep 30 04:57:31 2014] [error] 2014-09-30 04:57:31,067 ERROR
> [root][MainThread]
> \xd0\x98\xd1\x81\xd0\xba\xd0\xbb\xd1\x8e\xd1\x87\xd0\xb5\xd0\xbd\xd0\xb8\xd0\xb5
> \xd0\xb2 \xd1\x84\xd1\x83\xd0\xbd\xd0\xba\xd1\x86\xd0\xb8\xd0\xb8
> kbsite.users.getUser(<kbsite.users.Users object at 0x7f35516e0160>,5):
> [Tue Sep 30 04:57:31 2014] [error] Traceback (most recent call last):
> [Tue Sep 30 04:57:31 2014] [error]   File
> "/var/www/.ve/lib/python3.4/site-packages/sitelib-0.3.33-py3.4.egg/sitelib/tools/log.py",
> line 43, in inner
> [Tue Sep 30 04:57:31 2014] [error]     return f( *args, **kwargs )
> [Tue Sep 30 04:57:31 2014] [error]   File
> "/var/www/.ve/lib/python3.4/site-packages/kbsite-0.2-py3.4.egg/kbsite/users.py",
> line 78, in getUser
> [Tue Sep 30 04:57:31 2014] [error]     res = self.db.get_list(sql,
> [userId], limit=1)
> [Tue Sep 30 04:57:31 2014] [error]   File
> "/var/www/.ve/lib/python3.4/site-packages/sitelib-0.3.33-py3.4.egg/sitelib/tools/db/db.py",
> line 204, in get_list
> [Tue Sep 30 04:57:31 2014] [error]     return self.query(sql, *params,
> limit=None if limit == 'all' else int(limit), offset=offset)
> [Tue Sep 30 04:57:31 2014] [error]   File
> "/var/www/.ve/lib/python3.4/site-packages/sitelib-0.3.33-py3.4.egg/sitelib/tools/db/db.py",
> line 160, in query
> [Tue Sep 30 04:57:31 2014] [error]     result =
> self._connection.query(sql, *args)
> [Tue Sep 30 04:57:31 2014] [error]   File
> "/var/www/.ve/lib/python3.4/site-packages/py_postgresql-1.1.2.pgpool-py3.4-linux-x86_64.egg/postgresql/driver/pq3.py",
> line 1231, in __call__
> [Tue Sep 30 04:57:31 2014] [error]     return
> self._prepare(query)(*parameters)
> [Tue Sep 30 04:57:31 2014] [error]   File
> "/var/www/.ve/lib/python3.4/site-packages/py_postgresql-1.1.2.pgpool-py3.4-linux-x86_64.egg/postgresql/driver/pq3.py",
> line 2326, in prepare
> [Tue Sep 30 04:57:31 2014] [error]     ps._fini()
> [Tue Sep 30 04:57:31 2014] [error]   File
> "/var/www/.ve/lib/python3.4/site-packages/py_postgresql-1.1.2.pgpool-py3.4-linux-x86_64.egg/postgresql/driver/pq3.py",
> line 1518, in _fini
> [Tue Sep 30 04:57:31 2014] [error]     (*head, argtypes, tupdesc, last) =
> self._xact.messages_received()
> [Tue Sep 30 04:57:31 2014] [error] ValueError: need more than 0 values to
> unpack
>
>
>
> С Уважением, Сергей Мелехин.
>
> 2014-09-30 5:55 GMT+11:00 Muhammad Usama <m.usama at gmail.com>:
>
>> 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/20140930/be838eb3/attachment-0001.html>

More information about the pgpool-general mailing list