SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software Amazon Web Services AWS Tutorials and Guides
Development resources, articles, tutorials, code samples and tools and downloads for ASP.Net, SQL Server, R Script, Windows, Windows Phone, AWS, SAP HANA and ABAP, like SAP UI5, Screen Personas, etc.




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

SQL Error - Invalid operation: Specified types or functions (one per INFO message) not supported on Redshift tables

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.

Amazon Redshift SQL query execution on leader node and compute nodes according to referenced tables

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.





AWS Tutorials

AWS Tutorials

AWS








Copyright © 2004 - 2019 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems