Postgresql session isolation is playing tricks on you.
go to psql and do the query from your commandline, and you will see all
connections listed.
in your program, you can get it to "see" connections that were made after the
scope of the current-checked-out-connection started like this:
from sqlalchemy import create_engine
engine = create_engine("postgresql://scott:tiger@localhost/test")
print("create engine")
print(engine.pool.status())
conn1 = engine.connect()
print("create conn1")
print(conn1.execute("rollback; select state from pg_stat_activity where
usename='scott'").fetchall())
print(engine.pool.status())
conn2 = engine.connect()
print("create conn2")
print(conn2.execute("rollback; select state from pg_stat_activity where
usename='scott'").fetchall())
print(engine.pool.status())
conn2.close()
print("close conn2")
print(conn1.execute("rollback; select state from pg_stat_activity where
usename='scott'").fetchall())
print(engine.pool.status())
then you will see all the connections:
$ python test3.py
create engine
Pool size: 5 Connections in pool: 0 Current Overflow: -5 Current Checked out
connections: 0
create conn1
[('active',)]
Pool size: 5 Connections in pool: 0 Current Overflow: -4 Current Checked out
connections: 1
create conn2
[('idle',), ('active',)]
Pool size: 5 Connections in pool: 0 Current Overflow: -3 Current Checked out
connections: 2
close conn2
[('active',), ('idle',)]
Pool size: 5 Connections in pool: 1 Current Overflow: -3 Current Checked out
connections: 1
On Sat, May 8, 2021, at 12:09 PM, Soumaya Mauthoor wrote:
> Hello Mike
>
> Thanks for quick response as always :-) So I understand setting AUTOCOMMIT
> has no impact on the number of 'IDLE' connections and will look for different
> culprit.
>
> About the single row in pg_stat_activity, I was actually running the query
> below, but sent you the shortened version because I didn't think it would
> make a difference.
>
> engine = create_engine("postgresql://*@localhost:5432/postgres")
> print("create engine")
> print(engine.pool.status())
>
> conn1 = engine.connect()
> print("create conn1")
> print(conn1.execute("select state from pg_stat_activity where
> usename='souma'").fetchall())
> print(engine.pool.status())
>
> conn2 = engine.connect()
> print("create conn2")
> print(conn2.execute("select state from pg_stat_activity where
> usename='souma'").fetchall())
> print(engine.pool.status())
>
> conn2.close()
> print("close conn2")
> print(conn1.execute("select state from pg_stat_activity where
> usename='souma'").fetchall())
> print(engine.pool.status())
>
> I have run this query multiple times and get a single row every time in the
> last query against pg_stat_activity. However, if I close conn1 instead of
> conn2, I do get two rows [('idle in transaction',), ('active',)] in the last
> query against pg_stat_activity. I also noticed that if I only use conn1 to
> query pg_stat_activity then I get at most one row:
>
> engine = create_engine("postgresql://souma:ppp@localhost:5432/postgres")
> print("create engine")
> print(engine.pool.status())
>
> conn1 = engine.connect()
> print("create conn1")
> print(conn1.execute("select state from pg_stat_activity where
> usename='souma'").fetchall())
> print(engine.pool.status())
>
> conn2 = engine.connect()
> print("create conn2")
> print(conn1.execute("select state from pg_stat_activity where
> usename='souma'").fetchall())
> print(engine.pool.status())
>
> conn2.close()
> print("close conn2")
> print(conn1.execute("select state from pg_stat_activity where
> usename='souma'").fetchall())
> print(engine.pool.status())
>
> *create engine
> Pool size: 5 Connections in pool: 0 Current Overflow: -5 Current Checked out
> connections: 0
> create conn1
> [('active',)]
> Pool size: 5 Connections in pool: 0 Current Overflow: -4 Current Checked out
> connections: 1
> create conn2
> [('active',)]
> Pool size: 5 Connections in pool: 0 Current Overflow: -3 Current Checked out
> connections: 2
> close conn2
> [('active',)]
> Pool size: 5 Connections in pool: 1 Current Overflow: -3 Current Checked out
> connections: 1*
>
> I'm guessing this is due to some difference about how "open" TCP/IP
> connections are treated by postgres/psycopg2?
>
> Regards
> Soumaya
>
> Le sam. 8 mai 2021 à 16:05, Mike Bayer <mike...@zzzcomputing.com> a écrit :
>> __
>>
>>
>> On Sat, May 8, 2021, at 10:34 AM, sumau wrote:
>>> Hello
>>>
>>> Our postgreSQL database is suffering from too many IDLE connections and I'm
>>> wondering if this has to do with isolation level which we usually set to
>>> AUTOCOMMIT.
>>
>> that's not really possible as AUTOCOMMIT "isolation" merely means that the
>> psycopg2 driver will not emit BEGIN TRANSACTION on a connection before SQL
>> is emitted. an idle connection typically means the pool has this connection
>> ready for use; when AUTOCOMMIT is on in-play connections, they likely will
>> also appear as IDLE if they are not invoking a statement at that moment.
>> so the number of IDLE connections should never exceed pool_size +
>> max_overflow for a single process (and engine); multiply that by the number
>> of processes you have (and by engines, if you have more than one engine per
>> process).
>>
>>>
>>> When I use the default connection settings then checked-in connections do
>>> not appear as IDLE in postgres:
>>>
>>> engine = create_engine("postgresql://*@localhost:5432/postgres")
>>> conn1 = engine.connect()
>>> conn2 = engine.connect()
>>> conn2.close()
>>> print(conn1.execute("select state from pg_stat_activity where
>>> usename='souma'").fetchall())
>>> print(engine.pool.status())
>>>
>>> *[('active',)]*
>>> *Pool size: 5 Connections in pool: 1 Current Overflow: -3 Current Checked
>>> out connections: 1*
>>
>> that response does not make any sense because you have two connections in
>> play, there should be two rows in pg_stat_activity, so make sure you are
>> selecting the correct criteria from pg_stat_activity and that you are not
>> using something like DISTINCT.
>>
>> if I run your program as given the output is what's expected:
>>
>> [('active',), ('idle',)]
>> Pool size: 5 Connections in pool: 1 Current Overflow: -3 Current Checked
>> out connections: 1
>>
>>
>>
>>>
>>> However if I set the Isolation level to AUTOCOMMIT then the checked-in
>>> connection appears as IDLE in postgres:
>>>
>>> engine = create_engine("postgresql://*@localhost:5432/postgres")
>>> conn1 = engine.connect()
>>> conn2 = engine.connect()
>>> conn2.close()
>>> print(conn1.execute("select state from pg_stat_activity where
>>> usename='souma'").fetchall())
>>> print(engine.pool.status())
>>>
>>> *[('active','idle')]*
>>> *Pool size: 5 Connections in pool: 1 Current Overflow: -3 Current Checked
>>> out connections: 1*
>>
>> That's the result we'd expect in your earlier case as well.
>>
>>> **
>>> I was wondering if:
>>> 1. There was a reason for this difference in behaviour? Should a
>>> checked-in connection show up as 'IDLE'?
>>
>> I would check your methodology of measurement because that's not the
>> expected result and it does not reproduce here.
>>
>>> 1. It is recommended to disable the pool when using autocommit?
>>
>> absolutely not.
>>
>>
>>
>>> Thanks !
>>> Soumaya
>>>
>>> --
>>> SQLAlchemy -
>>> The Python SQL Toolkit and Object Relational Mapper
>>>
>>> http://www.sqlalchemy.org/
>>>
>>> To post example code, please provide an MCVE: Minimal, Complete, and
>>> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
>>> description.
>>> ---
>>> You received this message because you are subscribed to the Google Groups
>>> "sqlalchemy" group.
>>> To unsubscribe from this group and stop receiving emails from it, send an
>>> email to sqlalchemy+unsubscr...@googlegroups.com.
>>> To view this discussion on the web visit
>>> https://groups.google.com/d/msgid/sqlalchemy/1f6b6767-4d38-4de9-903f-28e49e92a8b8n%40googlegroups.com
>>>
>>> <https://groups.google.com/d/msgid/sqlalchemy/1f6b6767-4d38-4de9-903f-28e49e92a8b8n%40googlegroups.com?utm_medium=email&utm_source=footer>.
>>
>>
>> --
>> SQLAlchemy -
>> The Python SQL Toolkit and Object Relational Mapper
>>
>> http://www.sqlalchemy.org/
>>
>> To post example code, please provide an MCVE: Minimal, Complete, and
>> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
>> description.
>> ---
>> You received this message because you are subscribed to the Google Groups
>> "sqlalchemy" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to sqlalchemy+unsubscr...@googlegroups.com.
>> To view this discussion on the web visit
>> https://groups.google.com/d/msgid/sqlalchemy/f47be475-b873-4dcb-959d-4b4f8d9a04e5%40www.fastmail.com
>>
>> <https://groups.google.com/d/msgid/sqlalchemy/f47be475-b873-4dcb-959d-4b4f8d9a04e5%40www.fastmail.com?utm_medium=email&utm_source=footer>.
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/CAN14jWT02rwRS5nPDe45RYXv4BBWZHLczXD0DsHJ0u8V9xSm5Q%40mail.gmail.com
>
> <https://groups.google.com/d/msgid/sqlalchemy/CAN14jWT02rwRS5nPDe45RYXv4BBWZHLczXD0DsHJ0u8V9xSm5Q%40mail.gmail.com?utm_medium=email&utm_source=footer>.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/a901d1d3-2ac0-41b9-a72b-c3fc130d6975%40www.fastmail.com.
