First try app packaging : librechat - issue with postgresql extention (pgvector)

[Valexico]

Response to myself : i just realized that I should install the extension in the host machine and not in the container
I'll try later
Still wondering what the documentation means by "whitelisted"

[Valexico]

I have a partial answer : the host has pgvector for postgresql version 16

$ apt list | grep pgvector
postgresql-16-pgvector/noble 0.6.0-1 amd64

However cloudron postgresql addon provide version 14. Am I missing something ?

[Valexico]

I need help : to install pgvector for postgresql 14 I need to add postgres APT repos, which I don't really want (I try not to mess up my Cloudron server with installing external dependancies).

Is it possible to provide a potgresql-16 DB with cloudron addon (postgresql-16 seem available on the server)

[nebulon]

Hi, thanks for sharing your findings here. Your initial approach was correct, apps use the postgresql service, not the host postgresql. In fact Cloudron does not run any host postgresql in the first place.

Cloudron does have a vector extension, but since immich requires pgvecto.rs we went with that one https://github.com/tensorchord/pgvecto.rs

In this case the sql statement thus is CREATE EXTENSION vectors;

Further please basically never install anything manually on the host system as this may break your Cloudron in the future. All app related things should be happening only within the app container.

[Valexico]

Thanks for the response @nebulon

I could fix my start.sh however, the vector extension is then expected by the app code

[SQL: BEGIN;SELECT pg_advisory_xact_lock(1573678846307946496);CREATE EXTENSION IF NOT EXISTS vector;COMMIT;]
Nov 27 11:31:05 (Background on this error at: https://sqlalche.me/e/20/e3q8)
Nov 27 11:31:05 2024-11-27T10:31:05Z
Nov 27 11:31:05 The above exception was the direct cause of the following exception:
Nov 27 11:31:05 2024-11-27T10:31:05Z
Nov 27 11:31:05 Traceback (most recent call last):
Nov 27 11:31:05 File "/app/code/rag_api/main.py", line 46, in <module>
Nov 27 11:31:05 from psql import PSQLDatabase, ensure_custom_id_index_on_embedding, pg_health_check
Nov 27 11:31:05 File "/app/code/rag_api/psql.py", line 3, in <module>
Nov 27 11:31:05 from config import DSN, logger
Nov 27 11:31:05 File "/app/code/rag_api/config.py", line 232, in <module>
Nov 27 11:31:05 vector_store = get_vector_store(
Nov 27 11:31:05 File "/app/code/rag_api/store_factory.py", line 20, in get_vector_store
Nov 27 11:31:05 return AsyncPgVector(
Nov 27 11:31:05 File "/usr/local/lib/python3.10/dist-packages/langchain_core/_api/deprecation.py", line 183, in warn_if_direct_instance
Nov 27 11:31:05 return wrapped(self, *args, **kwargs)
Nov 27 11:31:05 File "/usr/local/lib/python3.10/dist-packages/langchain_community/vectorstores/pgvector.py", line 341, in __init__
Nov 27 11:31:05 self.__post_init__()
Nov 27 11:31:05 File "/usr/local/lib/python3.10/dist-packages/langchain_community/vectorstores/pgvector.py", line 348, in __post_init__
Nov 27 11:31:05 self.create_vector_extension()
Nov 27 11:31:05 File "/usr/local/lib/python3.10/dist-packages/langchain_community/vectorstores/pgvector.py", line 386, in create_vector_extension
Nov 27 11:31:05 raise Exception(f"Failed to create vector extension: {e}") from e
Nov 27 11:31:05 Exception: Failed to create vector extension: (psycopg2.errors.UndefinedFile) could not open extension control file "/usr/share/postgresql/14/extension/vector.control": No such file or directory

And this seems to come directly from Langchain code. I didn't know this interesting verto.rs alternative, however the normal "pgvector" seems more broadly used. Could we have both alternative on Cloudron ? Or maybe a symlink if pgvecto.rs is compatible with classic pgvector ?

[nebulon]

Last time we checked, both could not be loaded in parallel. I am not sure if this is still the case after a quick research. But pgvectorS is supposed to be compatible with the other at least. Do you know more about this maybe or can you try with a patch to replace pgvector with pgvectors? Maybe this only a few seds

[Valexico]

Hmm I tried a sed and could create a vectors extension instead of vector

But then it cascaded to more errors. And with the different abstraction layers (langchain, sqlalchemy...) I couln't solve the issue.

I think current implementation tries to ceate a table with a VECTOR type anv vecto.rs expects VECTOR(dimension) (or is maybe not loaded properly)

The above exception was the direct cause of the following exception:
Nov 27 16:17:11 2024-11-27T15:17:11Z
Nov 27 16:17:11 Traceback (most recent call last):
Nov 27 16:17:11 File "/app/code/rag_api/main.py", line 46, in <module>
Nov 27 16:17:11 from psql import PSQLDatabase, ensure_custom_id_index_on_embedding, pg_health_check
Nov 27 16:17:11 File "/app/code/rag_api/psql.py", line 3, in <module>
Nov 27 16:17:11 from config import DSN, logger
Nov 27 16:17:11 File "/app/code/rag_api/config.py", line 232, in <module>
Nov 27 16:17:11 vector_store = get_vector_store(
Nov 27 16:17:11 File "/app/code/rag_api/store_factory.py", line 20, in get_vector_store
Nov 27 16:17:11 return AsyncPgVector(
Nov 27 16:17:11 File "/usr/local/lib/python3.10/dist-packages/langchain_core/_api/deprecation.py", line 183, in warn_if_direct_instance
Nov 27 16:17:11 return wrapped(self, *args, **kwargs)
Nov 27 16:17:11 File "/usr/local/lib/python3.10/dist-packages/langchain_community/vectorstores/pgvector.py", line 341, in __init__
Nov 27 16:17:11 self.__post_init__()
Nov 27 16:17:11 File "/usr/local/lib/python3.10/dist-packages/langchain_community/vectorstores/pgvector.py", line 355, in __post_init__
Nov 27 16:17:11 self.create_tables_if_not_exists()
Nov 27 16:17:11 File "/usr/local/lib/python3.10/dist-packages/langchain_community/vectorstores/pgvector.py", line 390, in create_tables_if_not_exists
Nov 27 16:17:11 Base.metadata.create_all(session.get_bind())
Nov 27 16:17:11 File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/sql/schema.py", line 5825, in create_all
Nov 27 16:17:11 bind._run_ddl_visitor(
Nov 27 16:17:11 File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/engine/base.py", line 3254, in _run_ddl_visitor
Nov 27 16:17:11 conn._run_ddl_visitor(visitorcallable, element, **kwargs)
Nov 27 16:17:11 File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/engine/base.py", line 2460, in _run_ddl_visitor
Nov 27 16:17:11 visitorcallable(self.dialect, self, **kwargs).traverse_single(element)
Nov 27 16:17:11 File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/sql/visitors.py", line 664, in traverse_single
Nov 27 16:17:11 return meth(obj, **kw)
Nov 27 16:17:11 File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/sql/ddl.py", line 918, in visit_metadata
Nov 27 16:17:11 self.traverse_single(
Nov 27 16:17:11 File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/sql/visitors.py", line 664, in traverse_single
Nov 27 16:17:11 return meth(obj, **kw)
Nov 27 16:17:11 File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/sql/ddl.py", line 956, in visit_table
Nov 27 16:17:11 )._invoke_with(self.connection)
Nov 27 16:17:11 File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/sql/ddl.py", line 314, in _invoke_with
Nov 27 16:17:11 return bind.execute(self)
Nov 27 16:17:11 File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/engine/base.py", line 1421, in execute
Nov 27 16:17:11 return meth(
Nov 27 16:17:11 File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/sql/ddl.py", line 180, in _execute_on_connection
Nov 27 16:17:11 return connection._execute_ddl(
Nov 27 16:17:11 File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/engine/base.py", line 1532, in _execute_ddl
Nov 27 16:17:11 ret = self._execute_context(
Nov 27 16:17:11 File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/engine/base.py", line 1849, in _execute_context
Nov 27 16:17:11 return self._exec_single_context(
Nov 27 16:17:11 File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/engine/base.py", line 1989, in _exec_single_context
Nov 27 16:17:11 self._handle_dbapi_exception(
Nov 27 16:17:11 File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/engine/base.py", line 2356, in _handle_dbapi_exception
Nov 27 16:17:11 raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
Nov 27 16:17:11 File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/engine/base.py", line 1970, in _exec_single_context
Nov 27 16:17:11 self.dialect.do_execute(
Nov 27 16:17:11 File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/engine/default.py", line 924, in do_execute
Nov 27 16:17:11 cursor.execute(statement, parameters)
Nov 27 16:17:11 sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedObject) type "vector" does not exist
Nov 27 16:17:11 LINE 4: embedding VECTOR,
Nov 27 16:17:11 ^
Nov 27 16:17:11 2024-11-27T15:17:11Z
Nov 27 16:17:11 [SQL:
Nov 27 16:17:11 CREATE TABLE langchain_pg_embedding (
Nov 27 16:17:11 collection_id UUID,
Nov 27 16:17:11 embedding VECTOR,
Nov 27 16:17:11 document VARCHAR,
Nov 27 16:17:11 cmetadata JSON,
Nov 27 16:17:11 custom_id VARCHAR,
Nov 27 16:17:11 uuid UUID NOT NULL,
Nov 27 16:17:11 PRIMARY KEY (uuid),
Nov 27 16:17:11 FOREIGN KEY(collection_id) REFERENCES langchain_pg_collection (uuid) ON DELETE CASCADE
Nov 27 16:17:11 )

[nebulon]

So looks like the application code needs to be adjusted further. I guess we have to see if both extensions can be made to work in parallel. So lets keep this as a feature request.

[nebulon]

We have managed to add the pgvector extension next to pgvectors. So far things are looking good, but we have to do more testing so we don't break other apps using postgres at the moment. But I am hopeful we can get this done with the next Cloudron release.

[Valexico]

Wow thanks @nebulon . I think it would have been necessary one day or another with the current AI hype.

By the way I'd be glad to share my "cloudron packaging prompt engineering" techniques some when. I'll make a second try with another app, I am thinking appsmith