[pgpool-general: 3206] Re: Setof returning function errors with pgpool2
Сергей Мелехин
cpro29a at gmail.com
Tue Sep 30 14:02:37 JST 2014
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/21a8adb1/attachment.html>
More information about the pgpool-general
mailing list