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.
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. And, if I try select top 1 * from "asdf"."my_table" it fails universally.
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. I identified that the non-production cluster is running version 1.0.44126, and the production cluster is running version 1.0.43931. The extracts did start failing after this update. I cannot find any reference to version 1.0.44126 online and have no idea what has changed here. (C'mon AWS, update your documentation!)
I can't find any reference to Redshift translating dbo to public or any other related feature that might do that.
I am able to reproduce the issue by issuing the following two statements:
create table public.test_table ( test integer );
select * from dbo.test_table;
I can also do: drop table dbo.test_table;
It will work fine on my 1.0.43931 cluster, and fail on the 1.0.44126 cluster. Clearly there is a mechanism here which is treating these two schemas names the same.
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 in version 1.0.44126 (if that is the case)?
