SQL Error: Specified types or functions not supported on Redshift tables
Since Amazon Redshift architecture is different when compared to many other data platforms like SQL Server, SQL programmers developing database applications can experience interesting errors with their coding habits from previous databases. In this Amazon Redshift database SQL tutorial, I wanted to take developers' attention to execution of SQL queries on lead node and compute nodes of Amazon Redshift cluster. Redshift SQL has some functions enabled to run only on lead node on the other hand some are only enabled to execute on compute nodes.
I have a simple SQL SELECT statement where I use COUNT() Window function on pg_table_def table as seen in below SQL code.
select tablename,
count(*) over (partition by tablename) as counts
from pg_table_def;
The execution of above SQL SELECT command on Amazon Redshift database produces following SQL error
An error occurred when executing the SQL command:
select tablename,
count(*) over (partition by tablename) as counts
from pg_table_def
[Amazon](500310) Invalid operation: Specified types or functions (one per INFO message) not supported on Redshift tables.; [SQL State=0A000, DB Errorcode=500310]
Warnings:
Function "pg_table_is_visible(oid)" not supported.
1 statement failed.
This is an annoying SQL error since I know Redshift supports SQL Window functions like Row_Number(), Sum(), Count(), etc used with Partition By clause, etc.
I realized that I can execute similar SQL SELECT scripts on tables from for example public schema on the other hand I got an SQL exception when the same query is executed on tables of pg_catalog schema.
For example, below SQL query successfully executes
select id, country, city,
count(*) over (partition by country) as counts
from city;
Using Count() function as a SQL Window function is also explained in Amazon Redshift database guide.
The reason behind the above SQL error is Amazon Redshift has leader node and compute node differentiation.
Some of the Redshift SQL functions have a limitation like Compute Node–Only Functions and Leader Node–Only Functions
If a query references catalog tables with prefix PG or does not have a table reference, that query is executed on leader node
On the other hand, if SQL query references user tables or system tables (with prefix STL or STV) or additionally system views (with prefix SVL or SVV) then this query is executed on compute nodes.
In following diagram, I wanted to summarize these considerations in a diagram.
Below diagram shows Amazon Redshift SQL query execution on leader node and compute nodes according to referenced tables within the SQL statement.
Although Count() function is not classified either Compute Node–Only Function or Leader Node–Only Function, I assume it is not allowed to be executed on leader node when I query catalog table pg_table_def.
I see other window functions like ListAgg, Median etc but Count() function with its this type of use also fails on Leader Node SQL query execution for catalog table pg_table_def
For more information on Leader Node–Only Functions and on Compute Node–Only Functions please refer to referenced official documentation.