Using with psycopg 3 pools throws thread error about pgvector-python

[ankane]

If it's not persisting, then yes. I'd recommend connecting to your database with psql to check:

SELECT extversion FROM pg_extension WHERE extname = 'vector';

[dmvieira]

I did here a workaround that solved errors and keep a single pool. Start pool in __new__ once for solving second one:

   def __new__(cls):
        # Adding Singleton for only one connection and database configuration
        with cls._lock:
            if cls._instance is None:
                cls._pool = ConnectionPool(
                    kwargs=pgvectorstore_config(),
                    max_size=20,
                    check=ConnectionPool.check_connection,
                    configure=cls.register_pgvector,
                    open=True
                )
                cls._instance = super().__new__(cls)
        return cls._instance

Add lock inside register_vector, solving multi worker configuration (first error):

def register_pgvector(cls, conn: Connection):
    with cls._lock():
        stmt = 'CREATE EXTENSION IF NOT EXISTS vector'
        with conn.cursor() as cur:
            cur.execute(stmt)
        register_vector(conn)

[ankane]

Hi @dmvieira, I'd recommend running CREATE EXTENSION with your schema / migration code rather than on every connection.

[dmvieira]

Hey @ankane , for some reason it requires every new connection too. It throws an exception:

error connecting in 'pool-1': vector type not found in the database

[ankane]

Make sure you're committing the transaction.

https://www.psycopg.org/psycopg3/docs/basic/transactions.html

[dmvieira]

It's already configured with autocommit 🤔 ... it's really necessary?