Postgres DB "used to be of type SqlQueryUnified has changed types, and is no longer valid!"

When attempting to do a simple "select * from schema.table" using an existing postgres DB resource I am getting the following error:

The datasource the query is connected to used to be of type SqlQueryUnified has changed types, and is no longer valid!

I have existing apps that continue to access this resource normally. The query seems to collapse when using schema name in the query i.e. select * from schema.table. If I do select * from table it works ok. Any thoughts on why schema would cause this?

Hey @illumination!

This error can surface when there's a difference in permissions (schema.table might be requiring write permissions). I know you mentioned this works in other apps, though. Do you perhaps have a staging environment enabled in your resource?

For example, your staging config could be “write” whereas your production config is “read” and schema.table only works with write access, so only in apps with staging enabled.

Let me know if any of this gets us closer!

I am logged in as a super user so there is no permissions difference. I have full authority over all objects.

After fiddling with it a bit it seems to go away if I remove the Twilio SMS query from the sheet. It seems to be that having twilio on the app causes some sort of conflict. If I just have postgres queries it works great, with or without schema names. If I have a twilio query on the app it causes this error. The twilio query works fine.

That is so strange! There must be some conflict with schema being used in both resources, though I wouldn't expect this to happen. I'm going to set up an app with Twilio + Postgres queries to see if I can reproduce!

It looks like it's working for me! To confirm, deleting the Twilio query in your app allows you to run schema.table?

Now add another postgres query, query4, and see if it can query anything. It happens every time I add a query after I've added the twilio query.

Still working for me! :thinking: Would you mind sharing a screenshot of your query that isn't working?

This shows my 3 queries. I can replicate this issue with two identical queries, one that works and one that does not. The strange part is when the Twilio query is deleted the failing query no longer fails.

That’s definitely strange...and do you see any errors in your browser console? And just out of curiosity, is there any particular reason you’d ideally like to use the schema in your query?

Found the problem. The call to the API tool to change the resource type (back and forth between postgres and twilio) was being blocked by the AWS firewall. The key was checking the browser console, good thinking there. There was a 403 error 'Denied' hidden in the various API calls. Modified the firewall rule and now it works normally.

This was triggered by the introduction of a second resource. There was no indication to the end user that an API call had failed and the resource wasn't changed on the server. The browser showed that it was successfully changed. This resulted in a resource-mismatch on a specific query that would then always fail when triggered.