Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.7k views
in Technique[技术] by (71.8m points)

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

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...