Re: Question on trigger

[veem v]

Thank you Adrian.

So it seems the heavy DML tables will see an impact if having triggers
(mainly for each row trigger) created on them.

And also the bulk DML/array based insert (which inserts multiple rows in
one short or one batch) , in those cases it seems the trigger will not make
that happen as it will force it to make it happen row by row, as the
trigger is row based. Will test anyway though.

On Thu, 11 Apr 2024 at 22:00, Adrian Klaver 
wrote:

> On 4/11/24 07:31, veem v wrote:
> > Hi, We used to use Oracle database in which we had audit
> > triggers(something as below) mandated for all tables by the control
> > team. Now we are going to use the postgresql 15.4 database for one of
> > our applications. So,wanted to understand if there exists any downside
> > of such audit trigger setup for all the tables? Will it impact the bulk
> > data insert/update/delete OR slowdown of any of the DML operations
> > significantly (and thus will not be advisable to use for all tables but
> > selected ones)?
>
> Triggers are overhead in Postgres as they where in Oracle. If they
> didn't cause an issue in Oracle I would suspect that would also be the
> case in Postgres. To confirm you would need to create a test setup and
> run some common operations and see what the overhead is.
>
> Some potential performance improvements:
>
> https://www.postgresql.org/docs/current/sql-createtrigger.html
>
> "...a trigger that is marked FOR EACH STATEMENT only executes once for
> any given operation, regardless of how many rows it modifies (in
> particular, an operation that modifies zero rows will still result in
> the execution of any applicable FOR EACH STATEMENT triggers)."
>
> <...>
>
> "The REFERENCING option enables collection of transition relations,
> which are row sets that include all of the rows inserted, deleted, or
> modified by the current SQL statement. This feature lets the trigger see
> a global view of what the statement did, not just one row at a time.
> This option is only allowed for an AFTER trigger that is not a
> constraint trigger; also, if the trigger is an UPDATE trigger, it must
> not specify a column_name list. OLD TABLE may only be specified once,
> and only for a trigger that can fire on UPDATE or DELETE; it creates a
> transition relation containing the before-images of all rows updated or
> deleted by the statement. Similarly, NEW TABLE may only be specified
> once, and only for a trigger that can fire on UPDATE or INSERT; it
> creates a transition relation containing the after-images of all rows
> updated or inserted by the statement."
>
>
> As example:
>
> https://www.postgresql.org/docs/current/plpgsql-trigger.html
>
> Example 43.7. Auditing with Transition Tables
>
> >
> > CREATE OR REPLACE TRIGGER TAB_AUD_TRG
> >BEFORE DELETE OR INSERT OR UPDATE
> >ON tab
> >FOR EACH ROW
> > BEGIN
> >IF inserting THEN
> >  :NEW.create_timestamp := systimestamp;
> >  :NEW.create_userid  := sys_context('USERENV','SESSION_USER');
> >  :NEW.update_timestamp := systimestamp;
> >  :NEW.update_userid := sys_context('USERENV','SESSION_USER');
> >ELSIF updating THEN
> >  IF  updating('create_userid') OR updating('create_timestamp')
> THEN
> >  :new.create_userid   := :old.create_userid;
> >  :new.create_timestamp  := :old.create_timestamp;
> >  END IF;
> >  :NEW.update_timestamp := systimestamp;
> >  :NEW.update_userid := sys_context('USERENV','SESSION_USER');
> >END IF;
> >END;
> > /
> >
> > Regards
> > Veem
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>

Re: Question on trigger

[Adrian Klaver]

On 4/13/24 00:03, veem v wrote:

Thank you Adrian.

So it seems the heavy DML tables will see an impact if having triggers 
(mainly for each row trigger) created on them.


And also the bulk DML/array based insert (which inserts multiple rows in 
one short or one batch) , in those cases it seems the trigger will not 
make that happen as it will force it to make it happen row by row, as 
the trigger is row based. Will test anyway though.


You said you have triggers in the Oracle database and I assumed they 
worked and where not a show stopping issue there. What makes you think 
that would be different in Postgres?


What type of triggers where there in Oracle, per row, per statement or a 
mix?





On Thu, 11 Apr 2024 at 22:00, Adrian Klaver > wrote:


On 4/11/24 07:31, veem v wrote:
 > Hi, We used to use Oracle database in which we had audit
 > triggers(something as below) mandated for all tables by the control
 > team. Now we are going to use the postgresql 15.4 database for
one of
 > our applications. So,wanted to understand if there exists any
downside
 > of such audit trigger setup for all the tables? Will it impact
the bulk
 > data insert/update/delete OR slowdown of any of the DML operations
 > significantly (and thus will not be advisable to use for all
tables but
 > selected ones)?

Triggers are overhead in Postgres as they where in Oracle. If they
didn't cause an issue in Oracle I would suspect that would also be the
case in Postgres. To confirm you would need to create a test setup and
run some common operations and see what the overhead is.

Some potential performance improvements:

https://www.postgresql.org/docs/current/sql-createtrigger.html


"...a trigger that is marked FOR EACH STATEMENT only executes once for
any given operation, regardless of how many rows it modifies (in
particular, an operation that modifies zero rows will still result in
the execution of any applicable FOR EACH STATEMENT triggers)."

<...>

"The REFERENCING option enables collection of transition relations,
which are row sets that include all of the rows inserted, deleted, or
modified by the current SQL statement. This feature lets the trigger
see
a global view of what the statement did, not just one row at a time.
This option is only allowed for an AFTER trigger that is not a
constraint trigger; also, if the trigger is an UPDATE trigger, it must
not specify a column_name list. OLD TABLE may only be specified once,
and only for a trigger that can fire on UPDATE or DELETE; it creates a
transition relation containing the before-images of all rows updated or
deleted by the statement. Similarly, NEW TABLE may only be specified
once, and only for a trigger that can fire on UPDATE or INSERT; it
creates a transition relation containing the after-images of all rows
updated or inserted by the statement."


As example:

https://www.postgresql.org/docs/current/plpgsql-trigger.html


Example 43.7. Auditing with Transition Tables

 >
 > CREATE OR REPLACE TRIGGER TAB_AUD_TRG
 >    BEFORE DELETE OR INSERT OR UPDATE
 >    ON tab
 >    FOR EACH ROW
 > BEGIN
 >        IF inserting THEN
 >          :NEW.create_timestamp := systimestamp;
 >          :NEW.create_userid  :=
sys_context('USERENV','SESSION_USER');
 >          :NEW.update_timestamp := systimestamp;
 >          :NEW.update_userid := sys_context('USERENV','SESSION_USER');
 >        ELSIF updating THEN
 >          IF  updating('create_userid') OR
updating('create_timestamp') THEN
 >              :new.create_userid   := :old.create_userid;
 >              :new.create_timestamp  := :old.create_timestamp;
 >          END IF;
 >          :NEW.update_timestamp := systimestamp;
 >          :NEW.update_userid := sys_context('USERENV','SESSION_USER');
 >        END IF;
 >    END;
 > /
 >
 > Regards
 > Veem

-- 
Adrian Klaver

adrian.kla...@aklaver.com 



--
Adrian Klaver
adrian.kla...@aklaver.com

Recursively trace all Foreign Key "referenced by" tables?

[Ron Johnson]

The attached PG function dba.get_fk_referenced_by() has been tested on PG
9.6 and 14.

A recursive bash function (also attached) calls the PG function, and
displays the whole tree of tables that the table in question depends on.
Output also attached.

Is there a better way to do this?  (I'm limited to scripting languages and
what can be installed from RPM files.)

fk8 ()
{
localTbl=$1
localPadding=$2
local -i Level=$3
localTblFKFld
localt
localChldFKFld
localChldIdx
local -i Cnt=0
localsql="SELECT this_column, REPLACE(downstream_table, '\"', ''), 
downstream_column, downstream_index 
  FROM dba.get_fk_referenced_by('$Tbl');"
if [ $Level = 0 ]; then Level=1; fi
if [ -z $Padding ]; then Padding="+++"; fi
while IFS=$'\t' read -r -a R
do
Cnt=$((Cnt+1))
TblFKFld=${R[0]}
t=${R[1]}
ChldFKFld=${R[2]}
ChldIdx="${R[3]}"
printf "%10d%02d %s %-35s %-30s %-30s\n" $Level $Cnt $Padding $t 
$ChldFKFld "$ChldIdx"
fk8 $t "${Padding}" $((Level*100+Cnt)) 
done < <(psql CDSLBXW -XtAF$'\t' -c "$sql")
if [[ $Level = 1 ]]
then
printf "  %10d %s\n" $Level $Tbl
fi
}

/*
_p_table_name must be schema.table_name (Example: tms.document)
*/
CREATE OR REPLACE FUNCTION dba.get_fk_referenced_by(_p_table_name TEXT) 
RETURNS TABLE (this_column TEXT
  , downstream_table TEXT
  , downstream_column TEXT
  , downstream_index TEXT)
LANGUAGE plpgsql 
AS 
$$
DECLARE
_v_name TEXT; 
_v_downstream_index_name NAME;
_c_downstream CURSOR FOR
select att2.attname::text AS this_column
, 
cla2.relnamespace::regnamespace::text||'.'||co.conrelid::regclass::text AS 
downstream_table
, co.conrelid AS downstream_oid
, att1.attname::text AS downstream_column
from pg_constraint co
inner join pg_class cla1
on co.confrelid = cla1.oid
inner join pg_class cla2
on co.conrelid = cla2.oid
inner join pg_attribute att1
on co.conrelid = att1.attrelid
inner join pg_attribute att2
on co.confrelid = att2.attrelid
where co.contype = 'f'
and cla1.relnamespace::regnamespace::text||'.'||cla1.relname = 
_p_table_name
and att1.attnum = co.conkey[1]
and att2.attnum = co.confkey[1]
order by 2;
BEGIN
_v_name := TRIM(BOTH FROM _p_table_name);
PERFORM relname
FROM pg_class
WHERE relnamespace::regnamespace::text||'.'||relname = _v_name
  AND relkind = 'r';
IF NOT FOUND THEN 
downstream_table := _v_name;
downstream_column := 'does not exist';
downstream_index := NULL;
RETURN NEXT;
RETURN;
END IF;
FOR i IN _c_downstream
LOOP
this_column := i.this_column;
downstream_table := i.downstream_table;
downstream_column := i.downstream_column;
SELECT indexrelid::regclass::text
into downstream_index
FROM pg_index
INNER JOIN pg_attribute ON indexrelid = attrelid
WHERE indrelid = i.downstream_oid
  AND attname = i.downstream_column;
IF downstream_index IS NULL THEN
downstream_index := 'ALERT!!! MISSING INDEX';
END IF;
RETURN NEXT;
END LOOP;
END;
$$;

==

$ . fk.sh && fk8 tms.batch
 101 +++ cds.rel_x937bundle_batchbatch_id   
idx_cds_rel_x937bundle_batch  
 102 +++ tms.action  batch_id   
idx_action_batch_id   
 103 +++ tms.alerted_watchlist   batch_id   
ALERT!!! MISSING INDEX
 104 +++ tms.batch_association   batch_id   
idx_batch_association_batch_id
 105 +++ tms.batch_clearing_auditbatch_id   
idx_batch_clearing_audit_batch_id
 106 +++ tms.batch_formatbatch_id   
idx_batch_format_batch_id 
   10601 +++ tms.batch_format_item   batch_format_id
idx_batch_format_item_batch_format_id
 107 +++ tms.batch_item_mapping  batch_id   
idx_batch_item_mapping_batch_id
 108 +++ tms.batch_trigger   batch_id   
idx_batch_trigger_batch_id
 109 +++ tms.operatorbatch_id   
idx_operator_batch_id 
 110 +++ tms.sub_batch   batch_id   
idx_sub_batch_batch_id
   11001 +++ cds.cdssubba