PostgreSQL Record Involved Table Activities

Question

Recently I came to a new challenge to understand a new ERP system and the tables involved when different business functions are taking place. I need to find the tables involved by each function. For example, if we have a vehicle purchase, we need to identify table that this function has inserted/updated data.

In SQL Server we had the SQL Server profiler, so we could start recording, perform the action and then stop the recording. All activities were listed and with some simple or complex filters we could find out those tables.

*Note than I am not interested into SQL Execution plan where tables shown are involved into a single query, but instead to a group of functions/queries/tables.

Is there any similar tool in PostgreSQL?

Answer 1

So I did a quick research and I developed a quick solution using PostgreSQL internal tables. Maybe is not has graphical representation with table visualizer and start or stop capabilities but, it does the job.

First create a new table, to store some metadata:

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;

Then execute a function of operation to stamp access statistics before and after the business workflow:

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$;

Then execute a function of operation to track the statistic changes between iterations of start and stop (before and after) workflow.

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$;

To call the operate and track:

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

The outcome of the last functions if there are inserts, updates or deletes: