Unable to create a vector, but probably an issue with retooldb


I am unable to create a vector resource with the error "Error Creating Vector Resource", and I do see other posts similar but my problem appears to be different.

-I am running a new version of self hosted using the latest version from hub.docker tryretool/backend
-I am running postgres15 cloudsql in GCP
-I have vector extension installed on my database that I called retooldb in my postgres instance
-My user has access to create role and create db

I first created the new retool database at mydomain/resources?setupRetoolDB=1
The connection was successful and I now see the retooldb as a resource to use

In my postgres instance, Since adding this new resource, I now see additional databases in my postgres instance created. One example has a name of 0bde2cb5056d4833b482ec96f35b3df0.

Using the query library in the retool app, if I run

SELECT current_database();

I get back 0bde2cb5056d4833b482ec96f35b3df0

I was expecting retoolbd as a returned result. I do not have access to create extension vector on this database. I am not even sure how to access this database that has been created. So I am pretty sure this is why I cannot create a vector database. I am able to create tables and add rows in my retooldb through the retool app.

Any help would be greatly appreciated.


Running into this same issue! I have the vector extension installed and I can even create a table with a vector type in it, but am not able to create a vector via the UI:

It should be noted that I can successfully create a vector table from the query library directly in the Retool Database resource with the query CREATE TABLE sample_vector_table (id serial PRIMARY KEY, embedding vector(1536)); but can't in the UI.

I was able to connect to the database created by retool using the connection string that can be accessed with the down arrow on the retool database screen, see screenshot. There maybe some permissions that you need to figure out but you then need to create the vector extension on the database that the Retool Database is actually using not the one you think it's using.

This is working, but it would be good to have an answer from Retool to understand why it's not using the database that is specified in the connection set up as this causes other issues downstream.

Thanks @_Kingsley_Blatter you rock. This worked like a charm. Agreed on looking to understand why we are spinning up new Retool resources when I specifically want it to be working with the database that I have provisioned for it.

this looked like a hash of something and, after checking, it is indeed a SHA-256 hash of "retooldb".

that's what I used to check... or you could ask the Bing AI to do it, I also checked that out of curiosity and it gets the right answer also =)

I'm not sure why you're getting it, but normally this is used in like checking user entered passwords and stuff. someone enters a password, you retrieve the stored 'salt' from a db for the user, then hash the password with the salt and compare what you get to what you have (or that 0bde2.... string you have).

sorry it's not a solution, but maybe it'll help you figure out what part of the connection process is messing up

Thanks @bobthebear this is super interesting. Yes, would love to understand the why behind this.

Hey folks - Just to loop back from the Retool DB side of things, the expected behavior is that Retool connects once with the provided credentials on connection setup and then creates 1 Postgres database and user (matching names) per Retool environment. After that initialization, that user/database associated with a given environment is used, so that they only have permissions for the data they're managing via Retool DB.

As you saw, these are some hashes at the moment, but we are working on releasing a more user-friendly naming convention that includes the associated environment here soon!


Thank you.