Python: AsyncConnectionPool Connection Timeout with Semantic Kernel PostgresSettings

[markwallace-microsoft]

Discussed in #12655

^{Originally posted by selfishark July 2, 2025}

Problem Description

Hello Everyone,
I'm experiencing connection timeout issues when trying to initialise an async PostgreSQL connection pool using Python Semantic Kernel's PostgresSettings.create_connection_pool() method, from Postrgres Connector The sync version works perfectly, but the async version consistently fails with timeout errors.

Environment

• Python: 3.10
• psycopg: 3.2.9
• psycopg-pool: 3.2.6
• Semantic Kernel: 1.34.0
• PostgreSQL: Running in Docker container (image: pgvector/pgvector:pg16)
• OS: Windows

Error Messages

ERROR:__main__:Database initialization failed: couldn't get a connection after 30.00 sec
Database initialization failed: couldn't get a connection after 30.00 sec

Code Setup

Working Sync Version (psycopg2)

# This works perfectly
class PostgreSQLConnectionManager:
    def __init__(self):
        self.connection_string = (
            f"postgresql://{user}:{password}@{host}:{port}/{dbname}"
            "?client_encoding=utf8&connect_timeout=30"
        )
    
    def initialize_pool(self) -> bool:
        self.pool = psycopg2.pool.ThreadedConnectionPool(
            minconn=1,
            maxconn=20,
            host=host,
            port=port,
            database=dbname,
            user=user,
            password=password,
            options='-c client_encoding=UTF8'
        )
        return True  # Works fine

Failing Async Version (Semantic Kernel)

from semantic_kernel.connectors.postgres import PostgresStore, PostgresSettings
# POSTGRES_CONNECTION_STRING=postgresql://root:root@localhost:5432/postgres    # dummy
# connection_string=SecretStr(POSTGRES_CONNECTION_STRING) if POSTGRES_CONNECTION_STRING is not None else None,  # 

class MemoryManager:
    def __init__(self):
        self.postgres_settings = PostgresSettings(
            # connection_string=SecretStr(POSTGRES_CONNECTION_STRING) if POSTGRES_CONNECTION_STRING else None,
             connection_string=f"host={host} port={port} user={user} password={password} dbname={dbname}",
            host=host,
            port=int(port) if port else 5432,
            dbname=dbname,
            user=user,
            password=SecretStr(password),
        )
        self.connection_pool: Optional[AsyncConnectionPool] = None

    async def initialize_postgres_database(self) -> bool:


       try:
            self.connection_pool = await self.postgres_settings.create_connection_pool()
            self.pool = self.connection_pool
            await self.pool._check_connection(self.connection_pool)

            # This line fails with timeout
            await self._test_connection()

            if not self.connection_pool:
                return False

            tables_to_check = [
                    'users', 'threads', 'steps', 'elements', 'feedbacks',   # chainlit core tables
                    'error_vectors', 'script_metadata', 'error_patterns',    # vectorisation tables
                ]
            # Create PostgresStore
            self.postgres_store = PostgresStore(
                embedding_generator=self.embedding_service,
                connection_pool=self.connection_pool,
                tables=tables_to_check
            )
           return True
        except Exception as e:
            print(f"Database initialization failed: {e}")
            return False

    # example of test
    async def _test_connection(self):
        """Test the connection and verify pgvector extension"""
        # async with self.get_connection() as conn:
        async with self.pool.connection() as conn:
            async with conn.cursor() as cursor:
                # Test basic connection
                await cursor.execute("SELECT version();")
                version = await cursor.fetchone()
                self.logger.info(f"Connected to PostgreSQL: {version[0]}")

Connection String Testing Results

I've tested different connection string formats:

• postgresql+asyncpg://user:pass@host:port/db → "Error creating connection pool"
• asyncpg://user:pass@host:port/db → "Error creating connection pool"
• postgresql://user:pass@host:port/db → "couldn't get a connection after 30.00 sec"

PostgreSQL Configuration Verified

✅ PostgreSQL is running and accessible:

• Sync psycopg2 connections work perfectly
• Direct psql command line access works
• Docker container is healthy and listening on port 5432
• User credentials are correct

✅ PostgreSQL config appears correct:

• listen_addresses = '*' in postgresql.conf
• Appropriate entries in pg_hba.conf
• Port 5432 is accessible via telnet

Hypothesis

Based on psycopg issue #370, I suspect this might be related to the AsyncConnectionPool's reconnection timeout behaviour. The pool may be:

1. Failing during initial connection attempts
2. Hitting the reconnect_timeout (default 300s, but timing out at 30s)
3. Giving up on reconnection attempts entirely

Questions

1. What's the correct connection string format for Semantic Kernel's PostgresSettings?
2. Does PostgresSettings.create_connection_pool() have specific requirements or limitations?
3. Are there additional async drivers or dependencies needed beyond psycopg[pool]?
4. How can I debug what's happening inside the create_connection_pool() method?
5. Are there configuration options to handle the reconnection timeout behaviour?

Attempted Solutions

• ✅ Verified PostgreSQL accessibility (sync works)
• ✅ Tested multiple connection string formats
• ✅ Confirmed credentials and network connectivity
• ❌ Still unable to establish async connection pool

Request for Help

I'd appreciate guidance on:

• Proper configuration for Semantic Kernel's AsyncConnectionPool
• Debugging techniques for connection pool initialisation
• Alternative approaches if this is a known limitation
• Any specific requirements or gotchas for async PostgreSQL connections with Semantic Kernel

Additional Context

The fact that the synchronous version works perfectly suggests this isn't a PostgreSQL server configuration issue, but rather something specific to the async connection pool implementation or configuration.

Any insights or suggestions would be greatly appreciated!

[eavanvalkenburg]

@lossyrob can you take a look as well?

[lossyrob]

Root Cause Analysis

1. Missing Timeout Configuration (Primary Issue)

The core issue is that AsyncConnectionPool from psycopg_pool has timeout parameters that are not exposed in
semantic-kernel's PostgresSettings class.

Evidence:

• AsyncConnectionPool.init has timeout: float = 30.0 (matches your "30.00 sec" error)
• Also has reconnect_timeout: float = 300.0, max_lifetime: float = 3600.0, max_idle: float = 600.0
• None of these are configurable through PostgresSettings in
  https://github.com/microsoft/semantic-kernel/blob/main/python/semantic_kernel/connectors/postgres.py#L269-L299

2. Connection String Format Confusion

The connection string formats you tried that failed:

• ❌ postgresql+asyncpg://user:pass@host:port/db → ProgrammingError: missing "=" after...
• ❌ asyncpg://user:pass@host:port/db → ProgrammingError: missing "=" after...

Valid formats (both work):

• ✅ libpq format: host=localhost port=5432 dbname=postgres user=postgres password=example
• ✅ PostgreSQL URI: postgresql://postgres:example@localhost:5432/postgres

SQLAlchemy-style schemes (postgresql+asyncpg://) are not supported by psycopg.

Recommended Solution

1. Expose Timeout Configuration in PostgresSettings

Add timeout parameters to the PostgresSettings class:

class PostgresSettings(KernelBaseSettings):
# Existing fields...
min_pool: int = 1
max_pool: int = 5

  # Add these timeout configurations:
  timeout: float = 30.0                    # Connection timeout (your issue)
  reconnect_timeout: float = 300.0         # Reconnection timeout
  max_lifetime: float = 3600.0             # Max connection lifetime
  max_idle: float = 600.0                  # Max idle time

  async def create_connection_pool(self, **kwargs):
      pool = AsyncConnectionPool(
          min_size=self.min_pool,
          max_size=self.max_pool,
          timeout=self.timeout,                    # Add this
          reconnect_timeout=self.reconnect_timeout, # Add this
          max_lifetime=self.max_lifetime,          # Add this
          max_idle=self.max_idle,                  # Add this
          open=False,
          kwargs={**self.get_connection_args(), **kwargs},
          **extra_args,
      )

2. Improve Documentation

• Document supported connection string formats clearly
• Add examples for both libpq and PostgreSQL URI formats
• Explicitly state that SQLAlchemy-style schemes are not supported
• Provide timeout configuration examples

3. Environment Variable Support

Allow users to configure via environment variables:

POSTGRES_TIMEOUT=60.0
POSTGRES_RECONNECT_TIMEOUT=600.0
POSTGRES_MAX_LIFETIME=7200.0
POSTGRES_MAX_IDLE=1200.0

Scope

This is a Python-only issue. The .NET implementation uses NpgsqlDataSourceBuilder which properly exposes timeout
configuration.

Immediate Workaround

For users experiencing this issue now, try:

1. Use proper connection string format: postgresql://user:pass@host:port/db
2. Check network connectivity and database accessibility
3. Consider using the synchronous version if async isn't required

[lossyrob]

Note: the documentation in MicrosoftDocs/semantic-kernel-docs#204 will help users understand how to create connection strings for the connector.

[lossyrob]

After further investigation, I've believe that the 30-second timeout is a symptom, not the root
cause. The real issue is related to AsyncConnectionPool's asyncio object lifecycle, as documented in
psycopg/psycopg#219.

The Real Problem

The error "couldn't get a connection after 30.00 sec" occurs when:

1. AsyncConnectionPool is created in one asyncio event loop context
2. Then used in a different asyncio event loop context

This causes the pool's internal asyncio objects (queues, schedulers, worker tasks) to be bound to the wrong event
loop, making them unable to communicate properly.

Common User Scenarios That Trigger This

1. Global pool creation:

# Created at module level (no event loop running)
settings = PostgresSettings(...)

async def my_function():
    # Later used in different async context
    pool = await settings.create_connection_pool()  # ❌ Can fail

2. Jupyter notebook usage:

• Each cell execution might run in slightly different async context
• Pool created in one cell, used in another

3. Web framework patterns:

• Pool created during app startup
• Used in request handlers with different event loop timing

4. Thread-based async execution:

• Pool created in background thread
• Used in main application thread

Reproduction Script

I've created a script that reproduces the exact issue:

#!/usr/bin/env python3
"""
Simple diagnosis of the AsyncConnectionPool event loop issue.
This demonstrates the actual root cause of GitHub issue #12732.
"""

import asyncio
import sys
import threading
from concurrent.futures import ThreadPoolExecutor

from semantic_kernel.connectors.postgres import PostgresSettings

def demonstrate_root_cause():
    """Demonstrate the actual root cause of the timeout issue."""

    def create_pool_wrong_way():
        """Creates pool in a separate thread/event loop context."""

        # Create a new event loop in this thread
        loop = asyncio.new_event_loop()
        asyncio.set_event_loop(loop)

        async def create():
            settings = PostgresSettings(
                host="localhost",
                port=5432,
                dbname="postgres",
                user="postgres",
                password="example"
            )
            return await settings.create_connection_pool()

        try:
            pool = loop.run_until_complete(create())
            return pool
        finally:
            loop.close()

    async def use_pool_in_main_loop(pool):
        """Try to use the pool in the main event loop."""
        try:
            # This will timeout with the 30-second error
            async with pool.connection() as conn:
                async with conn.cursor() as cur:
                    await cur.execute("SELECT 'This should fail'")
                    result = await cur.fetchone()

        except Exception as e:
            if "couldn't get a connection after 30.00 sec" in str(e):
                print("🎯 THIS IS THE EXACT ERROR FROM THE GITHUB ISSUE!")
                print("   The issue is NOT about timeout values being too low!")
                print("   It's about asyncio object lifecycle!")
            return False

        return True

    async def main():
        # Step 1: Create pool in wrong context (separate thread)
        with ThreadPoolExecutor() as executor:
            future = executor.submit(create_pool_wrong_way)
            pool = future.result()

        # Step 2: Try to use pool in different context (main thread)
        if pool:
            await use_pool_in_main_loop(pool)  # This will fail with 30s timeout

        # Step 3: Demonstrate correct usage
        settings = PostgresSettings(
            host="localhost",
            port=5432,
            dbname="postgres",
            user="postgres",
            password="example"
        )

        # Create and use in same async context - this works perfectly
        pool = await settings.create_connection_pool()
        async with pool.connection() as conn:
            async with conn.cursor() as cur:
                await cur.execute("SELECT 'This works perfectly'")
                result = await cur.fetchone()
                print(f"✅ Correct usage works: {result}")

        await pool.close()

    return main

if __name__ == "__main__":
    demo = demonstrate_root_cause()
    asyncio.run(demo())

Usage Guidelines (Real Solution)

✅ Correct Patterns:

# Pattern 1: Create and use in same async function
async def my_database_operation():
    settings = PostgresSettings(...)
    async with PostgresCollection(..., settings=settings) as collection:
        # Use collection here
        pass

# Pattern 2: Connection factory pattern
class DatabaseManager:
    def __init__(self):
        self.settings = PostgresSettings(...)

    async def get_collection(self):
        # Always create fresh pool in current async context
        return PostgresCollection(..., settings=self.settings)

❌ Problematic Patterns to Avoid:

# Don't do this - global pool creation
settings = PostgresSettings(...)  # Created at module level
pool = None  # Global pool variable

async def init_app():
    global pool
    pool = await settings.create_connection_pool()  # ❌ Event loop binding issue

async def handle_request():
    # Using global pool from different async context
    async with pool.connection() as conn:  # ❌ Can timeout
        pass