We have a strange issue which has appeared in the last couple of days.

We have two Amazon Redshift clusters. One cluster has our production workload, and the other cluster has our non-production workload. There are 4 copies of the same database. 1 production copy, and 3 copies in non-production.

In the last few days, data extracts began failing in our non-production environment. Upon further investigation, we see that the error is:

ERROR: schema "dbo" does not exist

So, upon closer inspection. Sure enough, the query is using dbo as the schema and there is no dbo schema in the database. All of the tables exist in the public schema. This would make sense if this didn't work for months prior to this event, and is actually still working in production. In addition, the query did not start failing at the same time on the non-production cluster. One query to one copy of the database started failing, and then the next day, the same query to another copy of the database started failing.

The original (simplified) query looks like this:

select top 1 * from [dbo].[my_table];

In this case, dbo does not exist in any environment. my_table exists in the public schema. However, the above query works fine still on our production cluster, while it now fails, with the invalid schema error on our non-production cluster.

I also tried changing the query to:

select top 1 * from "dbo"."my_table";

I get the same results. Works fine in production. Does not work in non-production. In fact, if I try select top 1 * from "public"."my_table"; I get the same results (as dbo) in production, and the query works as expected in non-production.

I also checked the Redshift user's search path, in case that had something to do with it. In each environment the search patch is default: $user,public

I confirmed on all the databases that the dbo schema does NOT exist, and that all the tables are in public. I also checked the cluster version and update history. There are no updates or version changes that correspond with when this problem started occurring.

I can't find any reference to Redshift translating dbo to public or any other related feature that might do that.

Why does the query select top 1 * from "dbo"."my_table"; work at all when the actual table is "public"."my_table"?

Why did it stop working all the sudden at different times and on different databases and clusters?