How do I see trigger functions in the log files

I have a table charass on this table I have a trigger

CREATE TRIGGER charassheattrigger
    BEFORE INSERT OR UPDATE 
    ON public.charass
    FOR EACH ROW
    EXECUTE PROCEDURE public._charassheattrigger();

The function executed by this trigger looks like

CREATE OR REPLACE FUNCTION public._charassheattrigger()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE
AS $BODY$

BEGIN

  IF (TG_OP = 'INSERT' OR (TG_OP = 'UPDATE' AND OLD.charass_value <> NEW.charass_value)) AND NEW.charass_char_id = 34 THEN
    IF NOT EXISTS(SELECT true FROM heat WHERE heat_number = NEW.charass_value) THEN
      INSERT INTO heat (heat_number,heat_created,heat_vend_id) 
        SELECT NEW.charass_value,NOW(), order_vend_id
        FROM ls
        JOIN detail ON (ls_id = detail_ls_id)
        JOIN hist ON (detail_hist_id = hist_id)
        JOIN order ON (hist_order_id = order_id)
        WHERE hist_ordtype = 'PO' AND hist_transtype = 'RP' AND ls_id = NEW.charass_target_id;
    END IF;
  END IF;  

  RETURN NEW;
END;
$BODY$;

When I look at the log file. I can see the statement executed

statement: INSERT INTO charass (charass_value, charass_target_type, charass_target_id, charass_char_id) VALUES ('123456789', 'LS', 1234, 34)

I check the heat table expecting to see a record with the heat_number as ‘123456789’ but nothing is in the heat table.

So i then open up pg admin and copy the above statement and execute it manually. Now there is a record in the heat table.

What is the difference between the manual insert and the one done by the application. Is there a way to tell? the username behind the statement is the same so it cant be a permission thing. I was wondering if there was some way to send information from the trigger function to the log file so I can review it

Answer

Add auto_explain to shared_preload_libraries in postgresql.conf as well as these additional parameters:

auto_explain.log_nested_statements = on
auto_explain.log_min_duration = 0

Then restart PostgreSQL.

Now you will get the statements inside the function logged, along with their execution plan.

Advertisement

Answer