Error: prepared statement «pg8000_statement_0» already exists

[Lisandro]

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.

[Massimiliano]

Have you tried to install psycopg2? Is the standard de facto postgresql driver.

The pip package should be psycopg2-binary

--
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

[Lisandro]

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.

[Lisandro]

Another weird stuff I noticed: in my server, if I open a terminal, run python and try to import psycopg2, I receive 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

Does it mean that psycopg2 is broken? If that's the case, how can my application still be running? I'm pretty lost. 

[Massimiliano]

Could be.

When you strart web2py it show database driver available:

Mine:

Database drivers available: psycopg2, pymysql, imaplib, sqlite3, pg8000, pyodbc, pymongo

[黄祥]

perhaps this conversation can give a hint:

https://github.com/DataDog/dd-agent/issues/2755

did you backup the server before upgrade?

best regards,

stifan

[Massimiliano]

Try to uninstall psycopg2-* and reinstall only psycopg2-binary

--

Massimiliano

[Lisandro]

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)?

[Massimiliano]

When psycopg is available web2py will use it when is not it use pg8000 that was included in web2py

[Lisandro]

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.