Hi there! Finally I was able to solve the problem uninstalling psycopg2*
and reinstalling psycopg2-binary.
Still, I want to comment what happened, in case it helps others.
A packages update broke the psycopg2 package in the server. Actually, the
server ended up with two instances: psycopg2 and psycopg2-binary, and
trying to import psycopg2 from python would return an error:
>>> import psycopg2
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "/usr/lib64/python2.7/site-packages/psycopg2/__init__.py", line 50,
in <module>
from psycopg2._psycopg import ( # noqa
ImportError: /usr/lib64/python2.7/site-packages/psycopg2/_psycopg.so:
undefined symbol: PQconninfo
>From what I've learned here, web2py comes with several database adapters.
Apparently, for postgresql, web2py tries to use psycopg2, and if it can't,
it will use pg8000.
And here is the weird stuff: the pg8000 driver works good when the
connection is made directly to PostgreSQL server. But if the connection is
made through pgBouncer (a connection pooler for PostgreSQL), then for some
reason the pgBouncer connections are not reused; instead, they start to
pile up very very fast. I'm not sure if this a problem of the pg8000
adapter that comes with web2py or a problem within pgBouncer.
Still, this makes me wonder: should web2py automatically change to pg8000
when psycopg2 fails?
I mean, in this scenario, I would have prefer a 500 error. It would have
been much easier to detect the source of the problem.
As web2py switched to pg8000 without making me notice, and also, as this
new driver produced a problem with pgBouncer, it took me a while to
understand why pgBouncer was failing so spectacularly.
Again, I'm not sure if this is a problem of web2py's pg8000 adapter or a
problem with pgBouncer itself (remember pg8000 works ok connecting directly
to PostgreSQL).
But still, wouldn't be nice to be able to say to DAL: "hey, use only this
adapter, and fail if you can't import it"?
I've not seen an option for that in DAL's constructor.
Could I just remove the folder gluon/contrib/pg8000/ to be sure my
application will use only psycopg2 and fail if it can't find it?
Thank you very much in advance.
Regards,
Lisandro.
El miércoles, 23 de enero de 2019, 10:56:15 (UTC-3), Massimiliano escribió:
>
> When psycopg is available web2py will use it when is not it use pg8000
> that was included in web2py
>
> Il giorno mer 23 gen 2019 alle 14:05 Lisandro <rostagno...@gmail.com
> <javascript:>> ha scritto:
>
>> Thank you all for that notes.
>>
>> When I run web2py at my server, I see this available drivers: sqlite3,
>> imaplib, pymysql, pg8000
>> I don't see psycopg2, so I presume it will be available if I uninstall
>> those two versions and install the psycopg2-binary version.
>>
>> One additional question: which driver is using my app then?
>> I mean, right now my application is connecting directly to PostgreSQL
>> without problems. Would this mean it is using the pg8000 driver? Would
>> psycopg2 be available to web2py once I reinstall it (restarting uwsgi)?
>>
>>
>>
>> El miércoles, 23 de enero de 2019, 7:54:37 (UTC-3), Massimiliano escribió:
>>>
>>> Try to uninstall psycopg2-* and reinstall only psycopg2-binary
>>>
>>> On Wed, Jan 23, 2019 at 11:52 AM Massimiliano <mbel...@gmail.com> wrote:
>>>
>>>> Could be.
>>>>
>>>> When you strart web2py it show database driver available:
>>>> Mine:
>>>> Database drivers available: psycopg2, pymysql, imaplib, sqlite3,
>>>> pg8000, pyodbc, pymongo
>>>>
>>>>
>>>>
>>>>
>>>> On Wed, Jan 23, 2019 at 10:59 AM Lisandro <rostagno...@gmail.com>
>>>> wrote:
>>>>
>>>>> Thanks Massimiliano.
>>>>>
>>>>> Apparently psycopg2 is already installed (of course it was already
>>>>> installed, maybe something broke during the packages upgrade).
>>>>> Something weird is that I see psycopg2 installed twice, is this
>>>>> correct?
>>>>>
>>>>> ~$ pip freeze | grep psycopg2
>>>>> psycopg2==2.7.5
>>>>> psycopg2-binary==2.7.5
>>>>>
>>>>> Could this be the source of the problem?
>>>>> I don't see how. For what I understand, using or not using pgBouncer
>>>>> in the middle is transparent to the web2py application: it always
>>>>> connects
>>>>> in the same way, the application doesn't know if its connecting to
>>>>> PostgreSQL or pgBouncer. I think that's the whole idea of pgBouncer, to
>>>>> act
>>>>> as a middle man, pooling connections, behaving as if the application was
>>>>> connected directly to PostgreSQL.
>>>>>
>>>>> Any comment or suggestion will be much appreciated.
>>>>>
>>>>> El miércoles, 23 de enero de 2019, 6:51:06 (UTC-3), Massimiliano
>>>>> escribió:
>>>>>>
>>>>>> Have you tried to install psycopg2? Is the standard de facto
>>>>>> postgresql driver.
>>>>>> The pip package should be psycopg2-binary
>>>>>>
>>>>>> On Wed, Jan 23, 2019 at 10:39 AM Lisandro <rostagno...@gmail.com>
>>>>>> wrote:
>>>>>>
>>>>>>> Hi there! Yesterday I had a MAJOR downtime and I would need your
>>>>>>> help to understand what happened.
>>>>>>>
>>>>>>> The team that is in charge of upgrading security packages at my
>>>>>>> server (CentOS 7 at Linode) did an update that involved an upgrade to
>>>>>>> pgBouncer. Accordingly to what they said, they noticed pgBouncer was
>>>>>>> throwing errors after the upgrade, so they downgraded to the previous
>>>>>>> version that was installed. But sadly the problem remained. After this
>>>>>>> upgrade/downgrade of pgBouncer, all the attempts of connecting from my
>>>>>>> web2py app to pgBouncer fail.
>>>>>>>
>>>>>>> Inside of postgresql.log I can see lot of this:
>>>>>>> 2019-01-22 14:39:37 -03 ERROR: prepared statement
>>>>>>> «pg8000_statement_0» already exists
>>>>>>> 2019-01-22 14:39:37 -03 SENTENCIA: begin transaction
>>>>>>> 2019-01-22 14:39:38 -03 ERROR: prepared statement
>>>>>>> «pg8000_statement_0» already exists
>>>>>>> 2019-01-22 14:39:38 -03 SENTENCIA: begin transaction
>>>>>>>
>>>>>>> I've noticed that "pg8000_statement_0" is referenced at line 1894 in
>>>>>>> gluon/contrib/pg8000/core.py, but I can't realise if there is something
>>>>>>> I
>>>>>>> could do to avoid the error.
>>>>>>> I'm using web2py Version
>>>>>>> 2.16.1-stable+timestamp.2017.11.14.05.54.25, and I've noticed that
>>>>>>> gluon/contrib/pg8000/core.py isn't anymore in version 2.17.1.
>>>>>>>
>>>>>>> Of course I've tried restarting al the involved services, but
>>>>>>> nothing worked. Every time my web2py application tries to connect to
>>>>>>> the
>>>>>>> database, if pgBouncer is at the middle, the 5 attempts fail and those
>>>>>>> lines are printed to the postgresql.log. Right now I've bypassed
>>>>>>> pgbouncer
>>>>>>> and my application is connecting directly to postgresql.
>>>>>>>
>>>>>>> Could you put some lights into this? What can I do to avoid that
>>>>>>> error and still connect to pgBouncer with web2py 2.16.1?
>>>>>>>
>>>>>>> Thank you very much in advance.
>>>>>>> Regards, Lisandro.
>>>>>>>
>>>>>>> --
>>>>>>> Resources:
>>>>>>> - http://web2py.com
>>>>>>> - http://web2py.com/book (Documentation)
>>>>>>> - http://github.com/web2py/web2py (Source code)
>>>>>>> - https://code.google.com/p/web2py/issues/list (Report Issues)
>>>>>>> ---
>>>>>>> You received this message because you are subscribed to the Google
>>>>>>> Groups "web2py-users" group.
>>>>>>> To unsubscribe from this group and stop receiving emails from it,
>>>>>>> send an email to web2py+un...@googlegroups.com.
>>>>>>> For more options, visit https://groups.google.com/d/optout.
>>>>>>>
>>>>>>
>>>>>>
>>>>>> --
>>>>>> Massimiliano
>>>>>>
>>>>> --
>>>>> Resources:
>>>>> - http://web2py.com
>>>>> - http://web2py.com/book (Documentation)
>>>>> - http://github.com/web2py/web2py (Source code)
>>>>> - https://code.google.com/p/web2py/issues/list (Report Issues)
>>>>> ---
>>>>> You received this message because you are subscribed to the Google
>>>>> Groups "web2py-users" group.
>>>>> To unsubscribe from this group and stop receiving emails from it, send
>>>>> an email to web2py+un...@googlegroups.com.
>>>>> For more options, visit https://groups.google.com/d/optout.
>>>>>
>>>>
>>>>
>>>> --
>>>> Massimiliano
>>>>
>>>
>>>
>>> --
>>> Massimiliano
>>>
>> --
>> Resources:
>> - http://web2py.com
>> - http://web2py.com/book (Documentation)
>> - http://github.com/web2py/web2py (Source code)
>> - https://code.google.com/p/web2py/issues/list (Report Issues)
>> ---
>> You received this message because you are subscribed to the Google Groups
>> "web2py-users" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to web2py+un...@googlegroups.com <javascript:>.
>> For more options, visit https://groups.google.com/d/optout.
>>
> --
> Massimiliano
>
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
