Horizontal scaling deployment causes PGVector violates unique key constraint

[cdxvy30]

Current Behavior

When trying to initialize data manager with following stack (PostgreSQL as scalar storage, PGVector as vector storage and Redis as eviction manager cause' I want to set GPTCache in multi-node K8s environment.)

data_manager = manager_factory(
            manager="postgresql,pgvector",
            eviction_manager="redis",
            scalar_params={
                "sql_url": cache_store_url,
            },
            vector_params={
                "url": vector_store_url,
                "collection_name": "qa_cache",
                "dimension": albert.dimension,
            },
            eviction_params={
                "url": redis_url,
                "maxmemory": f"{CACHE_MAX_SIZE}mb",
                "policy": CACHE_EVICTION,
            }
        )

I encountered:

ERROR: Failed to initialize cache: (psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "pg_extension_name_index"
DETAIL:  Key (extname)=(vector) already exists.

[SQL: CREATE EXTENSION IF NOT EXISTS vector;]
(Background on this error at: https://sqlalche.me/e/20/gkpj)

Expected Behavior

Connect with Vector storage/Scalar storage and create table, index without reporting errors.

Steps To Reproduce

1. In a multi-node environment (like K8s), set both Vector storage and Scalar storage with manager_factory, and deploy the application with GPTCache on multi-node.

[cdxvy30]

I am not sure if there is way to avoid this kind of error in GPTCache setting, like using get_data_manager or some other ways. Look into gptcache/manager/vector_data/pgvector.py, I thought this error was caused by race condition from CREATE EXTENSION IF NOT EXISTS vector; when two K8s Pod are trying to create connection and create index with PGVector at almost same time.

[SimFG]

1f4b70f
@a9raag If it's convenient, could you please help confirm it?

[a9raag]

Hey @SimFG , I totally missed this message.
I spend some time trying to reproduce your issue but I couldn't. But I understand how it can be occurring.
@cdxvy30 You are right, it is because of a race condition where multiple pods are trying to execute CREATE EXTENSION IF NOT EXISTS vector
To avoid this, a good idea would be to put it in a try-catch block and skip if it already exists.
In this case, pass UniqueViolation exception.

PS: Best way to deal it with would be precreate the index

[a9raag]

Took a look at this on how the error is raised by (Background on this error at: https://sqlalche.me/e/20/gkpj)
I have created a PR that tackles the issue at SQLAlchemy level and looks for UniqueVioltation allows the exception to pass.
Otherwise re-raise the exception.
@cdxvy30 @SimFG