VectorStore doesn't work with custom schema and custom table

[saranshbansal]

I am trying to implementing RAG using pgvector/Postgres and stuck on a strange problem where RAG search fails when running programmatically. The raw query works fine on PostgresDB though.

We have two different issues:

1. When using the standard textbook implementation we get:

    processing failed: org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT *, embedding <=> ? AS distance FROM DEV_GENAI_DATA_OWNER.temp_rag_tbl WHERE embedding <=> ? < ?  ORDER BY distance LIMIT ? ]] with root cause
   
   org.postgresql.util.PSQLException: ERROR: operator does not exist: public.vector <=> public.vector
     Hint: No operator matches the given name and argument types. You might need to add explicit type casts.
     Position: 21
   

2. When running the same RAG search query as a native query via Spring JPA, we get this error:

   .springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT *, embedding <=> ?::vector AS distance FROM temp_rag_tbl ORDER BY embedding <=> ?::vector LIMIT ?]] with root cause
   org.postgresql.util.PSQLException: ERROR: type "vector" does not exist
     Position: 29
   

Clearly, the vector extension does exist:
SELECT * FROM pg_extension WHERE extname = 'vector'; -> shows result

The user has access to relevant accesses.

Here is the complete pgvector configurations:

# Pgvector configs
spring.ai.vectorstore.pgvector.index-type=HNSW
spring.ai.vectorstore.pgvector.distance-type=COSINE_DISTANCE
spring.ai.vectorstore.pgvector.table-name=<my-embedding-table>
spring.ai.vectorstore.pgvector.schema-name=<my-schema>
spring.ai.vectorstore.pgvector.dimensions=1536
spring.ai.vectorstore.pgvector.batching-strategy=TOKEN_COUNT

Spring AI version: M5 (Milestone 5)

Any clue what could be wrong?

UPDATE:
I managed to narrow it down to the fact that it needs explicite "public" schema in my jdbc connection string in order to execute vector queries which is a bug. I don't know if it's just the way it is designed.

Also, multiple tables can act as vector tables but I don't see how i can configure that.

[dev-jonghoonpark]

Have you initialized the schema?

According to the Spring AI pgvector documentation

The vector store implementation can initialize the required schema for you, but you must opt-in by specifying the initializeSchema boolean in the appropriate constructor or by setting …​initialize-schema=true in the application.properties file.

[saranshbansal]

Have you initialized the schema?

According to the Spring AI pgvector documentation

The vector store implementation can initialize the required schema for you, but you must opt-in by specifying the initializeSchema boolean in the appropriate constructor or by setting …​initialize-schema=true in the application.properties file.

I did, but it did not do anything. Also, my schema could have multiple tables which needs to store embeddings. How can I configure that given the vector store only takes a single table as a parameter.

[saranshbansal]

The problem seems to be related to using the custom pgvector store dependency.

<dependency>
    <groupId>org.springframework.ai</groupId>
    <artifactId>spring-ai-pgvector-store</artifactId>
</dependency>

As opposed to using PgVectorStore boot starter dependency:

<dependency>
    <groupId>org.springframework.ai</groupId>
    <artifactId>spring-ai-starter-vector-store-pgvector</artifactId>
</dependency>

Switching to starter dependency made it work flawlessly.