amazon-redshift - Redshift pg_user 表在 JOIN 上抛出无效操作错误

Question

当我运行以下查询时，

select * from stl_query q
join pg_user u on q.userid = u.usesysid
where u.usename = 'admin';

我收到以下错误:

SQL Error [500310] [0A000]: [Amazon](500310) Invalid operation: Specified types or functions (one per INFO message) not supported on Redshift tables.;

查询在领导节点上运行。我做错了什么？

Answer 1

pg_user 是 Leader Node-Only 函数，不能与不是 Leader Node-Only 的函数混合。

来自文档:

Some Amazon Redshift SQL functions are supported only on the leader node and are not supported on the compute nodes. A query that uses a leader-node function must execute exclusively on the leader node, not on the compute nodes, or it will return an error.

The documentation for each leader-node only function includes a note stating that the function will return an error if it references user-defined tables or Amazon Redshift system tables.

来源:http://docs.aws.amazon.com/redshift/latest/dg/c_SQL_functions_leader_node_only.html

作为您方案中的变通方法，您可以使用所需的 pg_user 数据子集生成一个临时表，然后加入该临时表。

select usesysid 
into temp table tmp_user
from pg_user
where usename = 'admin';

select * from stl_query q
inner join tmp_user u on q.userid = u.usesysid;