Psycopg2: (DatabaseError) error with no message from the libpq

[barrachri]

Hi guys, I get randomly this error:
The stack is flask with sqlalchemy and postgres.

Traceback (most recent call last): File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/engin/base.py", line 951, in _execute_context context) File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/engin/default.py", 
line 436, in do_execute cursor.execute(statement, parameters)
psycopg2.DatabaseError: error with no message from the libpq The above exception was the direct cause of the following exception: Traceback (most recent call last): File "/usr/local/lib/python3.4/dist-packages/flask/app.py", 
line 1817, in wsgi_app response = self.full_dispatch_request() File "/usr/local/lib/python3.4/dist-packages/flask/app.py", 
line 1477, in full_dispatch_request rv = self.handle_user_exception(e) File "/usr/local/lib/python3.4/dist-packages/flask/app.py", 
line 1381, in handle_user_exception reraise(exc_type, exc_value, tb) File "/usr/local/lib/python3.4/dist-packages/flask/_compat.py", 
line 33, in reraise raise value File "/usr/local/lib/python3.4/dist-packages/flask/app.py", 
line 1475, in full_dispatch_request rv = self.dispatch_request() File "/usr/local/lib/python3.4/dist-packages/flask/app.py", 
line 1461, in dispatch_request return self.view_functionsrule.endpoint File "./main/views.py", 
line 63, in auth_login user = User.query.filter_by(username=username).first() File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/orm/query.py", 
line 2367, in first ret = list(self[0:1]) File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/orm/query.py", 
line 2228, in getitem return list(res) File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/orm/query.py", 
line 2438, in iter return self._execute_and_instances(context) File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/orm/query.py", 
line 2453, in _execute_and_instances result = conn.execute(querycontext.statement, self._params) File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/engine/base.py", 
line 729, in execute return meth(self, multiparams, params) File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/sql/elements.py", 
line 322, in _execute_on_connection return connection._execute_clauseelement(self, multiparams, params) File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/engine/base.py", 
line 826, in _execute_clauseelement compiled_sql, distilled_params File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/engine/base.py", 
line 958, in _execute_context context) File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/engine/base.py", 
line 1159, in _handle_dbapi_exception exc_info File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/util/compat.py", line 188, in raise_from_cause reraise(type(exception), exception, tb=exc_tb, cause=exc_value) File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/util/compat.py", 
line 181, in reraise raise value.with_traceback(tb) File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/engine/base.py", 
line 951, in _execute_context context) File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/engine/default.py", 
line 436, in do_execute cursor.execute(statement, parameters) sqlalchemy.exc.DatabaseError (DatabaseError) error with no message from the libpq 'SELECT users.id AS users_id, users.username AS users_username, users.first_name AS users_first_name, users.last_name AS users_last_name, users.password AS users_password, users.status AS users_status, users.is_staff AS users_is_staff, users.user_plan AS users_user_plan, users.last_login AS users_last_login, users.date_joined AS users_date_joined, users.reset_password_token AS users_reset_password_token \nFROM users \nWHERE users.username = %(username_1)s \n LIMIT %(param_1)s' {'username_1': '[email protected]', 'param_1': 1}

[dvarrazzo]

What version of psycopg and libpq are these? Are you talking with straight Postgres or to some derived database (AWS) or to some middleware? Is your network reliable?

I can take a look better to what happens only if I see psycopg debug log, unfortunately a sqlalchemy is of no use. Please check the documentation to see how to compile psycopg in debug mode.

[barrachri]

Hi Daniele,
psycopg2==2.5.4, for libpq I need to check.
I have 2 vm (digital ocean), in one there's my docker container with a python app and in the other vm there's a container with postgres.
Is my network reliable? I hope so, they are using the private network.

I'll try to have some logs.

[dvarrazzo]

I think psycopg is doing the right thing here in handling an unexpected status from the libpq. If you find ways to improve what is going on feel free to reopen.

[hanbaga]

I also hit the issue. how to fix it finally.

[ajoyoommen]

Same issue here.

[ajoyoommen]

CherryPy server talking to PostgresSQL (9.2.7)

• Always on a particular url
• Same line every time cursor.execute(sale_records_sql, (sales_ids,))
• Vanishes after the server is restarted
• Emerges again after a indefinite time

File "/home/ec2-user/venv/lib/python3.4/site-packages/psycopg2/extras.py", line 288, in execute return super(NamedTupleCursor, self).execute(query, vars) psycopg2.DatabaseError: error with no message from the libpq

[dvarrazzo]

@ajoyoommen: good you can reproduce it but we need more info. Can you please compile psycopg in debug mode and post the output here? Also please report the psycopg version. Thank you very much.

[ajoyoommen]

@dvarrazzo I will most probably be able to work on this next week. I will post further details then.

But this error always occurs on one table (sales). I have another table on another url, but there is no error.

Could it be due to some specific data values? I don't know for sure, but I'll update this thread.

[ajoyoommen]

The SQL statement that raises this error fetches about 9000 sale ids. Individual queries for each id do not raise any errors.

AWS AMI

Psycopg: installed with pip. Version: 2.5.2

yum info postgresql-devel

Name        : postgresql-devel
Arch        : noarch
Version     : 9.2
Release     : 1.20.amzn1
Size        : 0.0  
Repo        : installed
From repo   : amzn-main

Ubuntu 14.04

I cloned the same database on my Ubuntu 14.04. I can't reproduce the error, so far.

Psycopg: installed with pip. Version: (same) 2.5.2

libpq-dev:
  Installed: 9.3.9-0ubuntu0.14.04

[supadhyaya]

Did anyone find the fix for this problem. In my case I am having the problem while trying to connect to heroku postgres and dumping them into csv. I am using psycopg2 to dump many tables in parallel and the error is generated only for certain tables which are very big. I am working with ubuntu machine. One thing I suspect is that the libpq installed on ubuntu and libpq in heroku might be of different version ( although both of them are of version greater than 9.x.x and hence compatibility?). I am not sure how to check the version of the libpq in heroku. Best.

[dayusor]

Same here when I do the following actions several times repeatedly:

• set the search_path with "db.session.execute('SET search_path TO )"
• then running a query
  the error is non-deterministic, it occurs sometimes when stressing manually the db.

sqlalchemy.exc.DatabaseError: (psycopg2.DatabaseError) error with status PGRES_TUPLES_OK and no message from the libpq [SQL: 'SET search_path TO schemaname']

[dvarrazzo]

@dayusor "Same here" do you mean against Heroku, AWS, etc. or against a vanilla Postgres server? What psycopg version are you using?

[supadhyaya]

thank you guys, i have gotten to the root of the problem. So the cause was actually not the psycopg2. It was a network error between a client program and a postgres it was trying to connect to in the Heroku. Somehow instead of throwing a connection error, psycopg2 will only throw error which are built in by default. In this case the error was ".. no message from libpq". This error was misleading as it indicated there was something with the psycopg2 happening in the background. Way i debugged is >> I used the psycopg2.errorcodes ( please look into the documentation, its pretty well explained there). In my case i found that the exception throwed a NULL object which under normal try catch would throw the misleading error. Since none of the objects in the psycopg2.errorcodes matched NONE object, it defaulted to (DatabaseError) error with no message from the libpq. I have verified it by shifting the application form local server to the AWS where it works perfectly fine. I hope the finding might be useful in other scenarios as well.

[dvarrazzo]

Thank you @supadhyaya. Yes, when the database gives a polite error it communicates back the error code you saw. Even when there is some connectivity error the libpq tells that in a better way (it's often an error in the underlying socket). Even killing -9 the database (don't do this at home) results in a less cryptic message. "No message from the libpq" seems being a synonym of "communication problem with AWS/Heroku". While I don't think we can do much better it would be great to report a better error message here.

[mayureshnw]

Interestingly I am facing the same issue however, my script does multiple uploads.
The database inserts before using the copy command work fine.

I am sure there is a well established connection between my app and the database.

Observation:
I have been facing this problem only after I tried to use multiple process to simultaneously use copy command to upload to the same table in parallel.
(using multiprocessing module from python)

[dvarrazzo]

@Mnw2212 you know that you cannot use a connection in a separate process, right? You should create instead a new connection in the working process.

[dinesh-procore]

I am running into the same issue. I use smart_open with copy_expert inside and it fails with this error

INFO - uploading part #1, 52428941 bytes (total 0.049GB)
[2018-11-28 01:24:28,613] {models.py:1595} ERROR - error with status PGRES_COPY_OUT and no message from the libpq

smart_open tries to upload a large file in multiple parts using the multi_part upload, but then after the 1st part the process fails.

Any help on this will be much appreciated.

Dinesh

[daefresh]

We are consistently running into the same issue, on red hat, in python, and it's non-deterministic, we have this problem in Mac OS, Red Hat, running on a server, in AWS, or on bare metal. Do you have any recommendations for what we can do?

Libraries

• psycopg2-binary==2.7.4
• psycopg2==2.7.4
• sqlalchemy==1.2.6

Python
Python 3.6.5 :: Anaconda, Inc.

Linux
Linux 3.10.0-693.5.2.el7.x86_64 #1 SMP Fri Oct 13 10:46:25 EDT 2017 x86_64 x86_64 x86_64 GNU/Linux

[supadhyaya]

Don't remember exactly which package I installed but i resolved this. I had
to install some package in python which in turn installed libpq on its own
/ or other helper library. But for sure the error message is misleading as
it does not show what package is missing, rather it complains about library
used by that package. If there is something I can find, I will share that
asap.

On Wed, Jan 2, 2019, 9:14 PM daefresh <[email protected] wrote:

We are consistently running into the same issue, on red hat, in python,
and it's non-deterministic, we have this problem in Mac OS, Red Hat,
running on a server, in AWS, or on bare metal. Do you have any
recommendations for what we can do?

Libraries

• psycopg2-binary==2.7.4
• psycopg2==2.7.4
• sqlalchemy==1.2.6

Python
Python 3.6.5 :: Anaconda, Inc.

Linux
Linux 3.10.0-693.5.2.el7.x86_64 #1
https://github.com/psycopg/psycopg2/pull/1 SMP Fri Oct 13 10:46:25 EDT
2017 x86_64 x86_64 x86_64 GNU/Linux

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/psycopg/psycopg2/issues/281#issuecomment-450972003,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AERY_CEQOBIP28ABHdlBol_jQ89ijIyCks5u_RMigaJpZM4DUhR-
.

[daefresh]

Thanks for responding so quickly! Do you know if it was to install libpq?

[supadhyaya]

I am not sure. Need to look at my logs. But in the mean time u can try
installing libpq as well. If I find what I did then will surely share with
u. But rest assured, it's fixable

On Wed, Jan 2, 2019, 9:23 PM daefresh <[email protected] wrote:

Thanks for responding so quickly! Do you know if it was to install libpq?

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/psycopg/psycopg2/issues/281#issuecomment-450974166,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AERY_B7Nz4GD0hcpHtg3pCEPBvLCl-5kks5u_RU3gaJpZM4DUhR-
.

[daefresh]

I did try to install python library libpq.
I do, inconsistently, get error with status PGRES_TUPLES_OK and no message from the libpq...

[daefresh]

This hasn't vanished yet. Any other ideas?

[analytik]

Try either VACUUM FULL ANALYSE; or restarting the DB server.

[virajpadte]

I see it appear inconsistently I am using flask and peewee to talk to AWS RDS Postgres. Does anyone know a fix for this?

[dibrovsd]

its hapend with multiprocess mode. When use fork() for create many process.
DB connections copy to child process, but realy connection in new process not exists

Dettach from damaged SQLA pull (use engine.dispose()). After first request connect will be restored

https://docs.sqlalchemy.org/en/13/core/connections.html#engine-disposal
https://stackoverflow.com/questions/30241911/psycopg2-error-databaseerror-error-with-no-message-from-the-libpq

example (file wsgi.py)

`
from sphere import server as application, db

try:
    con = db.session.connection()
    con.engine.dispose()
except Exception as exc:
    application.logger.warning('Cant init db connection: %s' % exc)


if __name__ == "__main__":
    application.run()

`

[funk1d]

@daefresh
Seems to be the issue with sqlalchemy's connection pooling lost after you restarted the database host.
Disable pooling may be a workaround.