CODEWITHSUNDEEP
pythonpostgresqlsslpsycopg2pgrouting
Phil Donovan
Phil Donovan

Reputation: 1265

Postgres SSL SYSCALL error: EOF detected with python and psycopg

Using psycopg2 package with python 2.7 I keep getting the titled error: psycopg2.DatabaseError: SSL SYSCALL error: EOF detected

It only occurs when I add a WHERE column LIKE ''%X%'' clause to my pgrouting query. An example:

SELECT id1 as node, cost FROM PGR_Driving_Distance(
  'SELECT id, source, target, cost 
     FROM edge_table
     WHERE cost IS NOT NULL and column LIKE ''%x%'' ',
  1, 10, false, false)

Threads on the internet suggest it is an issue with SSL intuitively, but whenever I comment out the pattern matching side of things the query and connection to the database works fine.

This is on a local database running Xubuntu 13.10.

After further investigation: It looks like this may be cause by the pgrouting extension crashing the database because it is a bad query and their are not links which have this pattern.

Will post an answer soon ...

Upvotes: 84

Views: 215106

Answers (9)

Arabinda Ghosh
Arabinda Ghosh

Reputation: 41

Fixed for me after increasing CPU cores of the flask server.

Upvotes: 0

Ali Tlekbai
Ali Tlekbai

Reputation: 475

I encountered the same error. By CPU, RAM usage everything was ok, solution by @antonagestam didn't work for me.

Basically, the issue was at the step of engine creation. pool_pre_ping=True solved the problem:

engine = sqlalchemy.create_engine(connection_string, pool_pre_ping=True)

What it does, is that each time when the connection is being used, it sends SELECT 1 query to check the connection. If it is failed, then the connection is recycled and checked again. Upon success, the query is then executed.

sqlalchemy docs on pool_pre_ping

In my case, I had the same error in python logs. I checked the log file in /var/log/postgresql/, and there were a lot of error messages could not receive data from client: Connection reset by peer and unexpected EOF on client connection with an open transaction. This can happen due to network issues.

Upvotes: 26

Jurgen Strydom
Jurgen Strydom

Reputation: 3930

The error: psycopg2.operationalerror: SSL SYSCALL error: EOF detected

The setup: Airflow + Redshift + psycopg2

When: Queries take a long time to execute (more than 300 seconds).

A socket timeout occurs in this instance. What solves this specific variant of the error is adding keepalive arguments to the connection string.

keepalive_kwargs = {
    "keepalives": 1,
    "keepalives_idle": 30,
    "keepalives_interval": 5,
    "keepalives_count": 5,
}

conection = psycopg2.connect(connection_string, **keepalive_kwargs)

Redshift requires a keepalives_idle of less than 300. A value of 30 worked for me, your mileage may vary. It is also possible that the keepalives_idle argument is the only one you need to set - but ensure keepalives is set to 1.

Link to docs on postgres keepalives.

Link to airflow doc advising on 300 timeout.

Upvotes: 52

papko26
papko26

Reputation: 91

In my case that was OOM killer (query is too heavy)

Check dmesg:

dmesg | grep -A2 Kill

In my case:

Out of memory: Kill process 28715 (postgres) score 150 or sacrifice child

Upvotes: 8

Charles F
Charles F

Reputation: 559

Very similar answer to what @FoxMulder900 did, except I could not get his first select to work. This works, though:

WITH long_running AS (
    SELECT pid, now() - pg_stat_activity.query_start AS duration, query, state
    FROM pg_stat_activity
    WHERE (now() - pg_stat_activity.query_start) > interval '1 minutes'
      and state = 'active'
)
SELECT * from long_running;

If you want to kill the processes from long_running just comment out the last line and insert SELECT pg_cancel_backend(long_running.pid) from long_running ;

Upvotes: 10

FoxMulder900
FoxMulder900

Reputation: 1291

This issue occurred for me when I had some rogue queries running causing tables to be locked indefinitely. I was able to see the queries by running:

SELECT * from STV_RECENTS where status='Running' order by starttime desc;

then kill them with:

SELECT pg_terminate_backend(<pid>);

Upvotes: 10

Fiskabollen
Fiskabollen

Reputation: 81

I got this error running a large UPDATE statement on a 3 million row table. In my case it turned out the disk was full. Once I had added more space the UPDATE worked fine.

Upvotes: 3

antonagestam
antonagestam

Reputation: 4789

I ran into this problem when running a slow query in a Droplet on a Digital Ocean instance. All other SQL would run fine and it worked on my laptop. After scaling up to a 1 GB RAM instance instead of 512 MB it works fine so it seems that this error could occur if the process is running out of memory.

Upvotes: 20

piro
piro

Reputation: 13931

You may need to express % as %% because % is the placeholder marker. http://initd.org/psycopg/docs/usage.html#passing-parameters-to-sql-queries

Upvotes: 2

Related Questions