Amazon Redshift Error - ERROR: 0A000: Specified types or functions (one per INFO message) not supported on Redshift tables

Question

When running the below query:

WITH sublevels AS (
    SELECT 1 UNION ALL 
    SELECT 1 UNION ALL
    SELECT 1
), FIELDA AS (
   SELECT (ROW_NUMBER() OVER ())::INT sublevel 
   FROM sublevels sl1, sublevels sl2, sublevels sl3
)
SELECT TOP 10 
FIELDB, 
sublevel, 
REPLACE(REGEXP_REPLACE(REGEXP_SUBSTR(UPPER(FIELDC), 'FROM \\S+', 1, sublevel), 'FROM ', ''),')','') ALIASA
FROM TABLEA
JOIN FIELDA ON sublevel <= REGEXP_COUNT(UPPER(FIELDC), 'FROM ')
WHERE ALIASA != 'ABC'
    AND lower(split_part(ALIASA, '.', 2)) IN (
        SELECT DISTINCT lower(t.table_name)
        FROM information_schema.tables t
        INNER JOIN information_schema.columns c on c.table_name = t.table_name AND c.table_schema = t.table_schema
        WHERE lower(column_name) similar TO '%(aaa|bbb|ccc)%')

I am getting the following error:

ERROR: 0A000: Specified types or functions (one per INFO message) not supported on Redshift tables.

I have no idea why, if I run the queries individually they work fine:

Query1

WITH sublevels AS (
    SELECT 1 UNION ALL 
    SELECT 1 UNION ALL
    SELECT 1
), FIELDA AS (
    SELECT (ROW_NUMBER() OVER ())::INT sublevel 
    FROM sublevels sl1, sublevels sl2, sublevels sl3
)
SELECT TOP 10 
FIELDB, 
sublevel, REPLACE(REGEXP_REPLACE(REGEXP_SUBSTR(UPPER(FIELDC), 'FROM \\S+', 1, sublevel), 'FROM ', ''),')','') ALIASA
FROM TABLEA
JOIN FIELDA ON sublevel <= REGEXP_COUNT(UPPER(FIELDC), 'FROM ')
WHERE ALIASA != 'ABC'

Query2

SELECT DISTINCT lower(t.table_name)
FROM information_schema.tables t
INNER JOIN information_schema.columns c on c.table_name = t.table_name AND c.table_schema = t.table_schema
WHERE lower(column_name) similar TO '%(aaa|bbb|ccc)%'

Hyperbaric

@a_horse_with_no_name Still the same error – Hyperbaric

Answer 1

In Redshift:

1. queries against information_schema run on the leader node only
2. queries against any "normal" tables run on compute nodes only

You cannot mix and match 1. and 2.

Danseuse

Is there a way to get round this? Could I put the information_schema query into a view then populate a new table with the results, then call that from the main query? – Hyperbaric

no way within Redshift. you could use a e.g. python or bash script to extract the data from your table and construct a hard-coded dynamic query against information_schema – Danseuse

SELECT tbl_rows INTO row_count_user_variable from SVV_TABLE_INFO where schema = current_schema() and "table" = 'test_table'; In my case when running the above query, i got the same error. Turns out that the current_schema() is a db level function and cannot be executed in a leader_node, where as the rest of the query runs in leader node. Thanks !! – Mountainous

Selecting from svv_columns for one table is fast. Inserting that result into a physical table doesn't throw an error. However it appears to process your entire databases views to get their column types, which for me is mad mad slow cause i have a lot of udfs running against spectrum columns. Wierd bug. My setup is too complicated :) – Albanian

Answer 2

The only workaround I have found to the "leader node only" error is to wrap the select in a loop as shown below. It is terrible for performance but acceptable when working with small(ish) schema related tables (e.g. objects in information_schema, pg_views, etc...).

create or replace procedure jupiter.tmp_proc()
as
$$
declare
  row record;
begin
drop table if exists tmp_ViewDef;
create table tmp_ViewDef(schemaname varchar,viewname varchar, schema_view varchar, "DDL" varchar(max));
for row in 
      select 
           cast(schemaname as varchar) as "schemaname"
          ,cast(viewname as varchar) as "viewname"
          ,cast(schemaname as varchar) || '.' || cast(viewname as varchar) as "schema_view"             
          ,cast(definition as varchar(max)) as "DDL"
      from pg_views
  loop
  insert into tmp_viewdef(schemaname,viewname,schema_view,ddl) values (row.schemaname,row.viewname,row.schema_view,row.ddl);
end loop
;
end;
$$ 
language plpgsql
;
call jupiter.tmp_proc()
;
select top 100 * from tmp_ViewDef;

Nahshunn

Will test this tomorrow and report back but seems a solid solution! – Hyperbaric

I can confirm technique works; I've used it before. – Millinery

This worked for me - it's a bit frustrating that AWS haven't provided a better way around this. There is some AWS documentation (see link below), but it's not a lot of use unless you're familiar with the problem already. docs.aws.amazon.com/redshift/latest/dg/… – Pelson

You are my hero, i've used this a couple times so far – Albanian