pg_stat_activity中SQL请求记录何时消失？AUTOCOMMIT连接状态疑问

代码示例 #

import asyncio

from sqlalchemy import select
from sqlalchemy.ext.asyncio import create_async_engine, async_sessionmaker

url = "postgresql+asyncpg://postgres:postgres@localhost:5433/postgres"
engine = create_async_engine(
    url,
    echo=True,
    connect_args={"server_settings": {"application_name": "NON-AUTOCOMMIT"}}
)
autocommit_engine = create_async_engine(
    url,
    echo=True,
    isolation_level="AUTOCOMMIT",
    connect_args={"server_settings": {"application_name": "AUTOCOMMIT"}}
)
Session = async_sessionmaker(engine, expire_on_commit=False)
AutocommitSession = async_sessionmaker(autocommit_engine, expire_on_commit=False)


def print_pool_sized():
    print("\tnon-autocommit session:", engine.pool.status())
    print("\tautocommit session:", autocommit_engine.pool.status())
    print()


async def main():
    print("Pool before all:")
    print_pool_sized()
    async with Session() as session:
        async with AutocommitSession() as ac_session:
            await ac_session.scalar(select(1))
        print("\nPool after AutocommitSession worked:")
        print_pool_sized()
        await session.scalar(select(1))
        print("\nPool after Session performed first query:")
        print_pool_sized()
    print("\nPool after all:")
    print_pool_sized()


asyncio.run(main())

运行日志 #

Pool before all:
    non-autocommit session: Pool size: 5  Connections in pool: 0 Current Overflow: -5 Current Checked out connections: 0
    autocommit session: Pool size: 5  Connections in pool: 0 Current Overflow: -5 Current Checked out connections: 0

2025-04-07 22:40:39,201 INFO sqlalchemy.engine.Engine select pg_catalog.version()
2025-04-07 22:40:39,201 INFO sqlalchemy.engine.Engine [raw sql] ()
2025-04-07 22:40:39,203 INFO sqlalchemy.engine.Engine select current_schema()
2025-04-07 22:40:39,203 INFO sqlalchemy.engine.Engine [raw sql] ()
2025-04-07 22:40:39,205 INFO sqlalchemy.engine.Engine show standard_conforming_strings
2025-04-07 22:40:39,205 INFO sqlalchemy.engine.Engine [raw sql] ()
2025-04-07 22:40:39,206 INFO sqlalchemy.engine.Engine BEGIN (implicit; DBAPI should not BEGIN due to autocommit mode)
2025-04-07 22:40:39,207 INFO sqlalchemy.engine.Engine SELECT 1
2025-04-07 22:40:39,207 INFO sqlalchemy.engine.Engine [generated in 0.00006s] ()
2025-04-07 22:40:39,208 INFO sqlalchemy.engine.Engine ROLLBACK using DBAPI connection.rollback(), DBAPI should ignore due to autocommit mode

Pool after AutocommitSession worked:
    non-autocommit session: Pool size: 5  Connections in pool: 0 Current Overflow: -5 Current Checked out connections: 0
    autocommit session: Pool size: 5  Connections in pool: 1 Current Overflow: -4 Current Checked out connections: 0

2025-04-07 22:40:39,230 INFO sqlalchemy.engine.Engine select pg_catalog.version()
2025-04-07 22:40:39,230 INFO sqlalchemy.engine.Engine [raw sql] ()
2025-04-07 22:40:39,232 INFO sqlalchemy.engine.Engine select current_schema()
2025-04-07 22:40:39,232 INFO sqlalchemy.engine.Engine [raw sql] ()
2025-04-07 22:40:39,233 INFO sqlalchemy.engine.Engine show standard_conforming_strings
2025-04-07 22:40:39,234 INFO sqlalchemy.engine.Engine [raw sql] ()
2025-04-07 22:40:39,235 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2025-04-07 22:40:39,235 INFO sqlalchemy.engine.Engine SELECT 1
2025-04-07 22:40:39,235 INFO sqlalchemy.engine.Engine [generated in 0.00005s] ()

Pool after Session performed first query:
    non-autocommit session: Pool size: 5  Connections in pool: 0 Current Overflow: -4 Current Checked out connections: 1
    autocommit session: Pool size: 5  Connections in pool: 1 Current Overflow: -4 Current Checked out connections: 0

2025-04-07 22:40:39,236 INFO sqlalchemy.engine.Engine ROLLBACK

Pool after all:
    non-autocommit session: Pool size: 5  Connections in pool: 1 Current Overflow: -4 Current Checked out connections: 0
    autocommit session: Pool size: 5  Connections in pool: 1 Current Overflow: -4 Current Checked out connections: 0

现象 #

AutocommitSession执行完成并归还连接池后，在pg_stat_activity中仍能看到应用名为AUTOCOMMIT的连接记录。

疑问解答 #

1. 连接记录为何仍存在于pg_stat_activity？ #

SQLAlchemy连接池的核心逻辑是复用物理连接，当会话关闭时，并不会立即断开与PostgreSQL的底层连接，而是将连接放回池中，等待后续新会话复用。只要物理连接没被关闭，PostgreSQL就会在pg_stat_activity中保留这条连接的记录。

2. 归还池后的连接状态是什么？ #

此时连接处于PostgreSQL的**idle（空闲）**状态，没有正在执行的SQL请求，但连接本身保持打开状态，随时可以被SQLAlchemy分配给新的会话使用。从日志里的autocommit_engine.pool.status()输出也能确认，连接已经回到池中（Connections in pool: 1），属于可用状态。

3. pg_stat_activity中的SQL记录何时消失？ #

• 正在执行的SQL完成后，query字段会被清空，但连接记录不会消失，只是状态变为idle。
• 只有当连接被连接池主动关闭（比如达到连接超时时间、池大小收缩、应用进程退出），或者PostgreSQL端主动断开连接时，这条连接记录才会从pg_stat_activity中移除。

内容的提问来源于stack exchange，提问作者Альберт Александров