How to reach postgres maximum connection with sqlalchemy?

Question

This is related to sqlalchemy and pg8000.

I have read everywhere that I should close the ResultProxy object so that connection could be returned to the pool.

The local test database allows a maximum of 100 connections:

$ psql -h 127.0.0.1 -U postgres
Password for user postgres: 
psql (9.5.5, server 9.6.0)
WARNING: psql major version 9.5, server major version 9.6.
         Some psql features might not work.
Type "help" for help.

postgres=# show max_connections;
 max_connections 
-----------------
 100
(1 row)

The following test script creates an engine in every loop and does not read nor close the ResultProxy object. It really is as bad as it can get.

The weird thing is, it also does not generate a too many connections kind of error. This is really confusing to me. Does sqlalchemy performs some magic? Or maybe postgres is actually magic?

#!/usr/bin/env python2.7
from __future__ import print_function
import sqlalchemy

def handle():
    url = 'postgresql+pg8000://{}:{}@{}:{}/{}'
    url = url.format("postgres", "pass", "127.0.0.1", "5432", "usercity")
    conn = sqlalchemy.create_engine(url, client_encoding='utf8')
    meta = sqlalchemy.MetaData(bind=conn, reflect=True)
    table = meta.tables['events']
    clause = table.select()
    result = conn.execute(clause)


if __name__=='__main__':
    for i in range(2000):
        print(i)
        handle()

cco · Accepted Answer · 2017-01-07 05:13:50Z

3

No magic, just garbage collection. Since handle() doesn't return anything (or modify global data), there is no way for a reference to the connection or cursor it creates to live beyond the scope of handle(). When they go out of scope, their reference counts drop to 0, and they get deleted (there is no hard guarantee about when this happens, but in practice, in CPython this happens immediately).

answered Jan 7, 2017 at 5:13

cco

6,3712 gold badges20 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

lingxiao Over a year ago

Thank you, that makes total sense. Tested and verified.

Collectives™ on Stack Overflow

How to reach postgres maximum connection with sqlalchemy?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related