PostgreSQL集返回函数调用优化

Question

我在PostgreSQL 9.3中遇到了以下问题。

有一个视图封装了对某些资源（例如文档）的非琐碎查询。让我们像一样简单地说明它

CREATE VIEW vw_resources AS
  SELECT * FROM documents; -- there are several joined tables in fact...

客户端应用程序使用的视图通常在几个字段上具有一些WHERE条件，还可能使用结果的分页，因此也可以应用OFFSET和LIMIT。

现在，在vw_resources计算的实际资源列表之上，我只想显示允许当前用户使用的资源。关于特权，有一组相当复杂的规则（它们取决于有问题的资源的几个属性、显式ACL、基于用户角色或与其他用户的关系的隐式规则…），所以我想将所有这些规则封装在一个函数中。为了防止对每个资源重复进行代价高昂的查询，该函数获取一个资源ID列表，一次评估所有资源的权限，并返回一组请求的资源ID以及相应的权限（读/写是有区别的）。大致如下：

CREATE FUNCTION list_privileges(resource_ids BIGINT[])
  RETURNS TABLE (resource_id BIGINT, privilege TEXT)
AS $function$
BEGIN
  -- the function lists privileges for a user that would get passed in an argument - omitting that for simplicity
  RAISE NOTICE 'list_privileges called'; -- for diagnostic purposes
  -- for illustration, let's simply grant write privileges for any odd resource:
  RETURN QUERY SELECT id, (CASE WHEN id % 2 = 1 THEN 'write' ELSE 'none' END)
               FROM unnest(resource_ids) id;
END;
$function$ LANGUAGE plpgsql STABLE;

问题是如何在vw_resources视图中集成这样的函数，使其只提供用户有特权的资源（即，具有'read'或'write'特权）。

一个琐碎的解决方案将使用CTE:

CREATE VIEW vw_resources AS
  WITH base_data AS (
    SELECT * FROM documents
  )
  SELECT base_data.*, priv.privilege
  FROM base_data
       JOIN list_privileges((SELECT array_agg(resource_id) FROM base_data)) AS priv USING (resource_id)
  WHERE privilege IN ('read', 'write');

问题是视图本身给出的行太多——一些WHERE条件和OFFSET/LIMIT子句仅应用于视图本身，如SELECT * FROM vw_resources WHERE id IN (1,2,3) LIMIT 10（客户端应用程序可能会请求任何复杂的筛选）。由于PostgreSQL无法将条件推送到CTE，list_privileges(BIGINT[])函数最终会评估数据库中所有资源的权限，这实际上会降低性能。

因此，我尝试使用一个窗口函数，该函数将从整个结果集中收集资源ID，并在外部查询中加入list_privileges(BIGINT[])函数，如下所示，但list_privileges(BIGINT[])函数最终会对每一行重复调用（正如"list_privileges called"notices所证明的那样），这有点破坏了之前的工作：

CREATE VIEW vw_resources AS
  SELECT d.*, priv.privilege
  FROM (
    SELECT *, array_agg(resource_id) OVER () AS collected
    FROM documents
  ) AS d
  JOIN list_privileges(d.collected) AS priv USING (resource_id)
  WHERE privilege IN ('read', 'write');

我会强制客户端提供两个单独的查询，第一个在没有应用特权的情况下获取vw_resources，第二个调用list_privileges(BIGINT[])函数，将第一个查询获取的资源ID列表传递给它，并在客户端过滤不允许的资源。然而，这对客户端来说相当笨拙，例如，获得前20个允许的资源实际上是不可能的，因为限制第一个查询根本无法获得它——如果一些资源由于权限而被过滤掉，那么我们在总体结果中根本没有20行。。。

欢迎任何帮助！

附言：为了完整起见，我附上了一个示例documents表：

CREATE TABLE documents (resource_id BIGINT, content TEXT);
INSERT INTO documents VALUES (1,'a'),(2,'b'),(3,'c');

Answer 1

如果必须使用plpgsql，则创建不带参数的函数

create function list_privileges()
  returns table (resource_id bigint, privilege text)
as $function$
begin
  raise notice 'list_privileges called'; -- for diagnostic purposes
  return query select 1, case when 1 % 2 = 1 then 'write' else 'none' end
  ;
end;
$function$ language plpgsql stable;

并将其与其他复杂查询连接以形成vw_resources视图

create view vw_resources as
select *
from
    documents d
    inner join
    list_privileges() using(resource_id)

过滤条件将在查询时添加

select *
from vw_resources
where
    id in (1,2,3)
    and
    privilege in ('read', 'write')

让计划器发挥其优化魔法，在任何"过早优化"之前检查explain输出。

这只是一个猜测：该函数可能会使规划者更难或不可能进行优化。

如果plpgsql不是真正必要的，并且非常频繁，我只会在中创建一个视图，而不是函数

create view vw_list_privileges as
select
    1 as resource_id,
    case when 1 % 2 = 1 then 'write' else 'none' end as privilege

并以与复杂查询相同的方式将其加入

create view vw_resources as
select *
from
    documents d
    inner join
    vw_list_privileges using(resource_id)