context¶
In this section, i will create a new rule (B009).
Let say we want to fix issue #19, about odd triggers.
here is a trigger definition i saw few time ago
CREATE OR REPLACE FUNCTION odd.trigger_odd_revision_event()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
DECLARE
event_payload JSONB;
odd_ids varchar;
id_odd int8;
BEGIN
odd_ids := NULL;
IF (UPPER(TG_TABLE_NAME) = 'T1') THEN
select STRING_AGG(NEW.id::TEXT, ',' ) into odd_ids;
END IF;
IF (UPPER(TG_TABLE_NAME) = 'T2') THEN
select STRING_AGG(distinct id::TEXT, ',' ) into odd_ids
from (
select distinct b.id
from t1 b
inner join t4 lpm on lpm.id_surface = b.surface_id
where lpm.id_item_concept = NEW.id
union
select distinct b.id
from t1 b
inner join t2 pm on pm.parent_item_conception_id = b.item_id
where pm.id=NEW.id and b.surface_id is null
) as all_odds;
END IF;
IF (UPPER(TG_TABLE_NAME) = 'T3') THEN
select STRING_AGG(distinct id::TEXT, ',' ) into odd_ids
from (
select distinct b.id
from t1 b
inner join t4 lpm on lpm.id_surface = b.surface_id
inner join t2 pm on pm.id = lpm.id_item_concept
inner join t3 pa on (pa.id = NEW.id and pm.id = pa.parent_item_concept_id)
union
select distinct b.id
from t1 b
inner join t2 pm on pm.parent_item_conception_id = b.item_id
inner join t3 pa on (pa.id = NEW.id and pa.parent_item_concept_id = pm.id)
where b.surface_id is null
) as all_odds;
END IF;
IF (UPPER(TG_TABLE_NAME) = 'T4') THEN
select STRING_AGG(distinct b.id::TEXT, ',' ) into odd_ids
from t1 b
where b.surface_id= COALESCE(NEW.id_surface,OLD.id_surface);
END IF;
IF (odd_ids is not null) THEN
FOREACH id_odd IN array string_to_array(odd_ids::TEXT, ',')
LOOP
with concepts_agg_from_surface as (select b.id as odd_id,
json_agg(json_build_object(
'code', pm.item_code,
'items', (select COALESCE(json_agg(json_build_object('code', pa.item_code)), '[]'::json) as items
from t3 pa
where pa.parent_item_concept_id = pm.id
group by pa.parent_item_concept_id))) as concepts
from t1 b
inner join t4 lpm on lpm.id_surface = b.surface_id
inner join t2 pm on (pm.id = lpm.id_item_concept and pm.parent_item_conception_id = b.item_id)
where b.id = id_odd
group by b.id),
concepts_agg_from_item as (select b.id as odd_id,
json_agg(json_build_object(
'code', pm.item_code,
'items', (select COALESCE(json_agg(json_build_object('code', pa.item_code)),'[]'::json) as items
from t3 pa
where pa.parent_item_concept_id = pm.id
group by pa.parent_item_concept_id))) as concepts
from t1 b
inner join t2 pm on pm.parent_item_conception_id = b.item_id
where b.id = id_odd
group by b.id)
-- objet final
select jsonb_build_object(
'id', b.id,
'number', NULL,
'status', bs.code,
'concepts', (case
when b.surface_id is not null then
(select COALESCE(m.concepts, '[]'::json) from concepts_agg_from_surface m where b.id = m.odd_id)
else
(select COALESCE(m.concepts, '[]'::json) from concepts_agg_from_item m where b.id = m.odd_id)
end
),
'creation_date', b.creation_date,
'update_date', b.update_date,
'approval_date', NULL,
'comment', NULL,
'is_deleted', b.deleted_flag,
'creation_user', jsonb_build_object(
'id', b.creation_user_id ,
'name', creation_user.name
),
'update_user', jsonb_build_object(
'id', b.update_user_id ,
'name', update_user.name
),
'approval_user', null
)
INTO event_payload
from t1 b
inner join odd_status bs on bs.id = b.odd_status_id
left join fwkuser creation_user on b.creation_user_id = creation_user.userid
left join fwkuser update_user on b.update_user_id = update_user.userid
where b.id = id_odd;
INSERT INTO relay_odd_revision_event
( creation_date, creator_id, event_status, sent_date, error_id, payload, odd_id, triggered_by)
VALUES( now(), 'ODD', 'TO_SEND', NULL, NULL, event_payload,id_odd, TG_NAME);
END LOOP;
END IF;
RETURN NULL;
END;
$function$
;
According to me this is not a good approach because:
- Versions are complicated to manage (for example v1 is for table T1, if you want to add a new table T2 you must create a version 2 that has no sense with T1.)
- You will have to deal with conflict resolution, each dev will work on the same file.
- Hard to read.
Create the rule B009¶
The rule number will be 9, so we will create B009 (for base checking).
B009¶
This base checking will be use later to create a global indicator of the database health (not implemented for the moment).
find the queries¶
Each rule requires a q4 query stored in the q4 column of the pglinter.rules table. It is executed at runtime by get_violations_for_rule(). The query must return three columns: classid (OID of the system catalog class), objid (OID of the violating object), and objsubid (sub-object identifier, 0 for table-level violations).
For B009 we want to identify every trigger that shares its trigger function with triggers on other tables:
q4 should be
-- Returns classid, objid, objsubid for tables using the same trigger function (B009)
WITH SharedFunctions AS (
SELECT
SUBSTRING(t.action_statement FROM 'EXECUTE FUNCTION ([^()]+)') AS trigger_function_name
FROM information_schema.triggers t
WHERE t.trigger_schema NOT IN (
'pg_toast', 'pg_catalog', 'information_schema', 'pglinter', '_timescaledb', 'timescaledb'
)
GROUP BY 1
HAVING COUNT(DISTINCT t.event_object_table) > 1
)
SELECT
'pg_trigger'::regclass::oid AS classid,
tg.oid AS objid,
0 AS objsubid
FROM pg_trigger tg
JOIN pg_class c ON c.oid = tg.tgrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN information_schema.triggers t
ON t.trigger_name = tg.tgname
AND t.event_object_table = c.relname
AND t.trigger_schema = n.nspname
JOIN SharedFunctions s ON s.trigger_function_name = SUBSTRING(t.action_statement FROM 'EXECUTE FUNCTION ([^()]+)')
WHERE n.nspname NOT IN (
'pg_toast', 'pg_catalog', 'information_schema', 'pglinter', '_timescaledb', 'timescaledb'
)
update rules.sql for B009¶
Add the rule metadata and the q4 query together as a new row in the INSERT block in sql/rules.sql:
(
'HowManyTableSharingSameTrigger', 'B009', 'BASE',
'Count number of table that use the same trigger vs nb table with their own triggers.',
'{0}/{1} table(s) using the same trigger function. Object list:\n{4}',
ARRAY[
'For more readability and other considerations use one trigger function per table.',
'Sharing the same trigger function add more complexity.'
],
$q$-- Returns classid, objid, objsubid for tables using the same trigger function (B009)
WITH SharedFunctions AS (
SELECT
SUBSTRING(t.action_statement FROM 'EXECUTE FUNCTION ([^()]+)') AS trigger_function_name
FROM information_schema.triggers t
WHERE t.trigger_schema NOT IN (
'pg_toast', 'pg_catalog', 'information_schema', 'pglinter', '_timescaledb', 'timescaledb'
)
GROUP BY 1
HAVING COUNT(DISTINCT t.event_object_table) > 1
)
SELECT
'pg_trigger'::regclass::oid AS classid,
tg.oid AS objid,
0 AS objsubid
FROM pg_trigger tg
JOIN pg_class c ON c.oid = tg.tgrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN information_schema.triggers t
ON t.trigger_name = tg.tgname
AND t.event_object_table = c.relname
AND t.trigger_schema = n.nspname
JOIN SharedFunctions s ON s.trigger_function_name = SUBSTRING(t.action_statement FROM 'EXECUTE FUNCTION ([^()]+)')
WHERE n.nspname NOT IN (
'pg_toast', 'pg_catalog', 'information_schema', 'pglinter', '_timescaledb', 'timescaledb'
)$q$
),
Note: The q4 query is stored directly in the
q4column of thepglinter.rulestable (using a dollar-quoted string literal). There is no longer a separatesrc/rule_queries.rsfile — the database is the single source of truth for all rule queries.
regression test¶
Now create some regress files, where you can raise the rule B009 message, for example
tests/sql/b009_trigger_sharing.sql
- create 10 tables
- 5 of them with their own trigger.
- 3 of them with the same trigger function.
- 2 without any trigger.
edit the Makefile, to add the test:
(...)
REGRESS_TESTS+= b009_trigger_sharing
(...)
run only this test
make installcheck REGRESS=b009_trigger_sharing
copy the result file
cp results/b009_trigger_sharing.out tests/expected
test all the regress test using
make install
make installcheck