首页 » PostgreSQL/GaussDB » Script: 在PostgreSQL查找包含”VOLATILE” Function 的Views

Script: 在PostgreSQL查找包含”VOLATILE” Function 的Views

前两篇在《VIEW dependencies in Oracle、MySQL、PostGreSQL(数据库比较系列十一)》Alert : PostgreSQL inline Subquery or View 包含volatile functions 阻止谓词推进(Predicate PushDown)》记录了因为view 包含Volatile Function导致的性能问题,通常,PostgreSQL 函数可以按如下方式标记:VOLATILE、STABLE、IMMUTABLE , 而这也是算最被广泛忽视的性能问题点,这篇文档整理个SQL列出所有包含”VOLATILE” Function 的View, 判断是否可以修改稳定性属性。

直接调用

select v.oid , relname , relnamespace::regnamespace::name ,    ref_fun.proname,provolatile,prokind
from pg_class as v
join pg_rewrite rwr on v.oid=rwr.ev_class
join pg_depend dep on rwr.oid=dep.objid
join pg_proc ref_fun on dep.refobjid = ref_fun.oid
where v.relkind='v' 
--and relname='v1'
and dep.deptype = 'n'
and  ref_fun.provolatile='v'
;
highgo-# ;
  oid  |    relname     | relnamespace |       proname        | provolatile | prokind
-------+----------------+--------------+----------------------+-------------+---------
 13902 | v_lic_baseinfo | public       | get_lic_baseinfo     | v           | f
 13906 | v_lic_funcinfo | public       | get_lic_funcinfo     | v           | f
 16639 | db_pipes       | dbms_pipe    | __list_pipes         | v           | f
 17370 | pg_buffercache | public       | pg_buffercache_pages | v           | f
 26561 | v1             | public       | f2                   | v           | f
(5 rows)


间接调用

WITH RECURSIVE views AS (
select v.oid::regclass AS view , v.oid ,dep.refobjid::regclass as refname, relnamespace::regnamespace as namespacename,  ref_fun.oid foid,  ref_fun.proname,ref_fun.provolatile,ref_fun.prokind, 1 level
from pg_class as v
join pg_rewrite rwr on v.oid=rwr.ev_class
join pg_depend dep on rwr.oid=dep.objid
left join pg_proc ref_fun on dep.refobjid = ref_fun.oid 
where v.relkind='v' 
and dep.deptype = 'n'
and  ref_fun.provolatile='v' 
UNION
   -- add the views that depend on these
   SELECT v.oid::regclass AS view,v.oid ,dep.refobjid::regclass as refname, relnamespace::regnamespace namespacename , ref_fun.oid ,   ref_fun.proname,ref_fun.provolatile,ref_fun.prokind, views.level + 1
   FROM views
      JOIN pg_depend dep
         ON dep.refobjid = views.view
		join pg_rewrite rwr  on rwr.oid=dep.objid
		join pg_class as v on v.oid=rwr.ev_class
		left join pg_proc ref_fun on views.foid = ref_fun.oid
		where 
  v.oid <> views.view 
)
SELECT view,oid,namespacename,refname,proname,level
FROM views;

        view        |  oid  | namespacename | refname |       proname        | level
--------------------+-------+---------------+---------+----------------------+-------
 v_lic_baseinfo     | 13902 | public        | 13900   | get_lic_baseinfo     |     1
 v_lic_funcinfo     | 13906 | public        | 13901   | get_lic_funcinfo     |     1
 dbms_pipe.db_pipes | 16639 | dbms_pipe     | 16638   | __list_pipes         |     1
 pg_buffercache     | 17370 | public        | 17369   | pg_buffercache_pages |     1
 v1                 | 26561 | public        | 26550   | f2                   |     1
 v2                 | 26583 | public        | v1      | f2                   |     2
(6 rows)


如果您正在编写自己的过程 – 请不要忘记适当标记您的函数。否则,函数可能会被过于频繁地调用,这对性能不利。正确定义你的函数是一个非常重要且简单任务。

 

打赏

,

目前这篇文章还没有评论(Rss)

我要评论