PostgreSQL记录涉及的表活动

pnwntuvh  于 2023-05-28  发布在  PostgreSQL
关注(0)|答案(2)|浏览(136)

最近,我遇到了一个新的挑战,要了解一个新的ERP系统和不同业务功能发生时所涉及的表。我需要找到每个函数所涉及的表。例如,如果我们有一个车辆购买,我们需要确定该函数已插入/更新数据的表。
在SQL Server中,我们有SQL Server分析器,所以我们可以开始记录,执行操作,然后停止记录。所有的活动都被列出,通过一些简单或复杂的过滤器,我们可以找到这些表。

  • 注意,我对SQL执行计划不感兴趣,其中所示的表涉及到单个查询,而是一组函数/查询/表。

PostgreSQL中有类似的工具吗?

cyvaqqii

cyvaqqii1#

所以我做了一个快速的研究,并开发了一个使用PostgreSQL内部表的快速解决方案。也许是没有图形表示与表可视化和启动或停止的能力,但它做的工作。
首先创建一个新表,以存储一些元数据:

CREATE TABLE IF NOT EXISTS public.table_monitoring
(
    iter_id int,
    iter_stmp timestamp with time zone,
    table_schm character varying COLLATE pg_catalog."default",
    table_name character varying COLLATE pg_catalog."default",
    inserts bigint,
    updates bigint,
    deletes bigint
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS public.table_monitoring
    OWNER to postgres;

然后执行一个操作的函数,对业务流程前后的访问统计进行盖章:

CREATE OR REPLACE FUNCTION public.operate_table_monitoring(
    )
    RETURNS void
    LANGUAGE 'sql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
INSERT INTO public.table_monitoring
(iter_id, iter_stmp, table_schm, table_name, inserts, updates, deletes)
SELECT
(SELECT COALESCE(MAX(iter_id) + 1,0) AS iter_id FROM public.table_monitoring),
NOW() AS iter_stmp,
schemaname AS table_schm, 
relname AS table_name, 
n_tup_ins AS inserts, 
n_tup_upd AS updates, 
n_tup_del AS deletes
FROM pg_stat_all_tables
$BODY$;

然后执行一个操作函数来跟踪开始和停止(之前和之后)工作流的迭代之间的统计变化。

CREATE OR REPLACE FUNCTION public.track_table_monitoring(
    )
    RETURNS TABLE(table_schm character varying, table_name character varying, inserts bool, updates bool, deletes bool) 
    LANGUAGE 'sql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000

AS $BODY$
SELECT
nxt.table_schm,
nxt.table_name,
CASE WHEN nxt.inserts > prv.inserts THEN True ELSE False END AS inserts,
CASE WHEN nxt.updates > prv.updates THEN True ELSE False END AS updates,
CASE WHEN nxt.deletes > prv.deletes THEN True ELSE False END AS deletes
FROM public.table_monitoring as nxt
INNER JOIN public.table_monitoring as prv
ON prv.table_schm = nxt.table_schm
AND prv.table_name = nxt.table_name
WHERE nxt.iter_id = (SELECT MAX(iter_id) FROM public.table_monitoring)
AND prv.iter_id = nxt.iter_id - 1
AND nxt.table_schm = 'public'
AND nxt.table_name <> 'table_monitoring'
AND
(
    nxt.inserts > prv.inserts
    OR
    nxt.updates > prv.updates
    OR
    nxt.deletes > prv.deletes
)
$BODY$;

要调用操作和跟踪,请执行以下操作:

SELECT public.operate_table_monitoring() //START AND STOP
SELECT public.track_table_monitoring() //GET CHANGES

如果有插入、更新或删除,则最后一个函数的结果:

yh2wf1be

yh2wf1be2#

要做到这一点并不容易。您可以创建一个测试数据库,并对每个函数执行以下操作:

  • 撤消所有表上的所有特权
  • 运行函数调用
  • 查看您得到的permission denied错误,并授予所需的权限
  • 重复此操作,直到函数按预期运行

相关问题