Pgvector not working on Self-Hosted Retool on AWS ECS Fargate

[Fernando_Garcia]

Hi there!

I got Retool deployed on AWS ECS Fargate using the cloudformation template retool-workflows.fargate.yaml

So I have the temporal cluster, the Retool servers and the dedicated RDS hosted on my AWS account.

I need to enable Retool-managed Vectors so I followed the tutorials for Setting up a Retool Database on a Self-hosted deployment and Enable Retool-managed Vectors on self-hosted deployments

So I created a new Postgres RDS, with only one user connected it to Retool as "Retool Database" in which I'm able to query from Retool
Then I successfully installed pgvector on the same RDS using psql from my local with the same credentials as Retool and created y queried a table with vector data type.

Nevertheless when I try to create a table with vector data type from Retool using:

CREATE TABLE sample_vector_table (id serial PRIMARY KEY, embedding vector(1536));

As stated in the documentation, I got a 'type "vector" does not exist' error

Additionally from my local in psql I'm unable to see tables created on retool, the same way Retool is unable to se the tables I've created from psql, albeit both are in public scheme

What's going on? It looks like retool made some kind of logical separation, so it didn't see neither the extensions nor the tables I create into the same database using psql

[Darren]

Hi @Fernando_Garcia! Welcome to the community.

I haven't been able to fully test this just yet, but I think I might know what's going on here. Which credentials are you using in order to connect to your RDS cluster via the command line? I ask because the credentials included in the YAML configuration files are not the same credentials that the Retool instance uses!

When connected via PSQL, you can see the additional roles (\du) and databases (\l) that Retool has created:

This is also noticeable when looking at the connection details in Retool:

All this to say - it should hopefully be enough to create the vector extension in each of those other databases in order to get everything working as expected! Let me know if you run into any additional issues.