psycopg2.OperationalError: could not create socket: Too many open files

Question

I am running a flask application on default development server. I have defined a post method which push data from an IoT device every few seconds to postgres database. I am making sure all the DB connections opened are also closed successfully. The only issue I am having is that I am getting error - too many open files.

Here is my code:

@app.route('/aqi/add', methods=['POST'])
def addAQI():

   ## get a connection from the pool
   conn = get_db_connection()
   cur = conn.cursor()

   if request.method == 'POST':

       data = request.form

       co_ugm3 = data.get('co', default=0)
       no2_ugm3 = data.get('no2', default=0)
       so2_ugm3 = data.get('so2', default=0)
       o3_ugm3 = data.get('o3', default=0)
       co2_ugm3 = data.get('co2', default=0)
       tvoc = data.get('tvoc', default=0)
       pm1_ugm3 = data.get('pm1', default=0)
       pm25_ugm3 = data.get('pm25', default=0)
       pm4_ugm3 = data.get('pm4', default=0)
       pm10_ugm3 = data.get('pm10', default=0)
       noise_dba = data.get('noise', default=0)
       datetime = data.get('datetime')
       h2s_ugm3 = data.get('h2s', default=0)
       station_name = data.get('station')
       battery_voltage_dc = data.get('battery', default=0)
       temperature_celsius = data.get('tmp', default=0)
       relative_humidity_percent = data.get('hr', default=0)
       barometric_pressure_hpa = data.get('hpa', default=0)
       station_id = data.get('station_id')

       # if device_id != None and coordenadas != None and registrada != None:
       try: 
          cur.execute("INSERT INTO nifx_devices_data(co_ugm3, no2_ugm3, so2_ugm3, o3_ugm3,   co2_ugm3, tvoc, pm1_ugm3, pm25_ugm3, pm4_ugm3, pm10_ugm3, noise_dba, datetime, h2s_ugm3, station_name, battery_voltage_dc, temperature_celsius, relative_humidity_percent, barometric_pressure_hpa,station_id) VALUES('"+str(co_ugm3)+"','"+str(no2_ugm3)+"','"+str(so2_ugm3)+"','"+str(o3_ugm3)+"','"+str(co2_ugm3)+"','"+str(tvoc)+"','"+str(pm1_ugm3)+"','"+str(pm25_ugm3)+"','"+str(pm4_ugm3)+"','"+str(pm10_ugm3)+"','"+str(noise_dba)+"','"+str(datetime)+"','"+str(h2s_ugm3)+"','"+str(station_name)+"','"+str(battery_voltage_dc)+"','"+str(temperature_celsius)+"','"+str(relative_humidity_percent)+"','"+str(barometric_pressure_hpa)+"','"+str(station_id)+"')")

          conn.commit()
          data = {'Status': 1}
          response = Response(response=json.dumps(data), mimetype='application/json')
          response.status_code = 200
       except Exception as e:
          cur.execute('rollback')
          print(e)
          data = {'Status': 0}
          response = Response(response=json.dumps(data), mimetype='application/json')
          response.status_code = 400
            
    cur.close()
    conn.close()

    return response

I have also configured pgbouncer for connection pooling.

Here is the complete error msg:

def get_db_connection():
    conn = psycopg2.connect(dbname="database",
                            port="port",
                            host="ipaddress",
                            user="username",
                            password="password",
                            cursor_factory= psycopg2.extras.RealDictCursor)
    return conn

1) Do not use images for textual information copy and paste as text. 2) The issue seems to be coming from `get_db_connection`. Add that code to your question. — Adrian Klaver, Jan 09 '23 at 17:52
this error comes after every few days, so i just took a snapshot. i will copy complete text when it shows again. — Shahzad Bacha, Jan 09 '23 at 18:23
btw when i restart flask service, the error disappears for a few days — Shahzad Bacha, Jan 09 '23 at 18:23
I am not seeing where you are using the `psycopg2` [pool](https://www.psycopg.org/docs/pool.html?highlight=pool#module-psycopg2.pool). — Adrian Klaver, Jan 09 '23 at 18:28
My advice at this point is to monitor [pg_stat_activity](https://www.postgresql.org/docs/15/monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW), the Postgres log and `ps ax` on the Flask machine. Something is not cleaning up after itself and it is going to take more digging to figure out what. — Adrian Klaver, Jan 09 '23 at 19:12
If it works a few days and then breaks after that time, it means that *somewhere in your code*, you are leaking database connections or any other *file like* object. — Serge Ballesta, Jan 11 '23 at 07:04
@AdrianKlaver i have monitored pg_stat_activity but the number of sessions doesn't exceeds 9 or 10.. which means connection pooling is working. Also what do i look for in ps ax? — Shahzad Bacha, Jan 11 '23 at 17:48
Besides postgres, the problem could be also from pgbouncer, did you check the logs from it? — HoangNguyen, Jan 17 '23 at 14:22

score 0 · Answer 1 · answered Jun 04 '23 at 19:25

On a Unix system, a connection is a "file like" object. You could be hitting the terminal's file limit. This limit can be verified using ulimit -n. (usually defaults to 1024).

It can be increased by using ulimit -n 4096. This increases the limit of that terminal session to 4096 and does not affect other terminal sessions.

Based on one of your comments "btw when i restart flask service, the error disappears for a few days", this could be the solution.

score -1 · Answer 2 · answered Jan 11 '23 at 06:50

-1

Use with statements instead of open or close. As the error message states too many open files. Please refer this question on how to use with statement.

What is the python keyword "with" used for?

answered Jan 11 '23 at 06:50

pandian

1
1

i am creating database connections so i am not sure how does this answers my question. – Shahzad Bacha Jan 11 '23 at 17:42
Did you try using the with statement? The error method clears states there are too many connection opened and is not able to create a new. – pandian Jan 14 '23 at 15:38
This does not answer the question nor does it explain why the error occurs in the first place. The OP clearly closes the connection in their code. – analytical_prat Jun 04 '23 at 19:14

psycopg2.OperationalError: could not create socket: Too many open files

2 Answers2