AsyncConnectionPool Connection Timeout with Semantic Kernel PostgresSettings

[selfishark]

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!

[moonbox3]

Tagging @eavanvalkenburg.

[selfishark]

Hello everyone, is there any updates on this issue, please! And thanks in advance.
@moonbox3 @eavanvalkenburg

[moonbox3]

We're tracking updates in #12732. (Would be nice if GitHub automatically showed an issue was created from this and tags it.)

[moonbox3]

Closing as we're tracking the attached issue.