PGVector vectorstore hangs in database transaction

[hanguofeng]

Issue description:

I have encountered an issue while using the PGVector vectorstore in long-running applications like Celery. with a non-existent table. Currently, the application hangs indefinitely in a database transaction, which affects visibility of created tables (e.g., langchain_pg_embedding) when inspecting the database directly. Additionally, this behavior blocks other processes from accessing the database.

Expected behavior:

When the specified table does not exist, I expect the application to automatically create the table and make it accessible immediately, without locking the process in a transaction. This would allow users to work seamlessly with the vectorstore and prevent unintended blocking of other processes.

Actual behavior:

Instead of creating the table automatically and making it accessible, the application hangs indefinitely in a database transaction, which prevents the created tables from being visible when inspecting the database directly. Additionally, this behavior blocks other processes from accessing the database, causing issues in long-running applications like Celery.

Steps to reproduce:

In celery worker

• Set up a PostgreSQL connection string pointing to a non-existent database.
• Initialize the PGVector vectorstore with the connection string.
• Attempt to perform any operation, such as adding or querying vectors.

In another process , e.g fastapi application or even another celery worker

• While the application is running, try to access the database using a different process.
• See it hangs when accessing database

Environment:

• Python version: 3.10
• PostgreSQL version: PostgreSQL 15.2 (Debian 15.2-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
• langchain version: 0.0.152
• SQLAlchemy version: 2.0.11
• psycopg2-binary version: 2.9.6

I hope this issue can be addressed soon, as it would significantly improve the user experience when working with non-existent databases and prevent unintended blocking of other processes. Thank you for your time and efforts in maintaining this project.