Determine the source of a trigger in Postgresql

Question

Do you think it is possible to determine the source of a trigger execution in PostgreSQL? Let's assume I have two tables as following:

CREATE TABLE tbl1 (id bigserial NOT NULL PRIMARY KEY,
    name text NOT NULL);
CREATE TABLE tbl2 (id bigserial NOT NULL PRIMARY KEY,
    owner bigint NOT NULL REFERENCES tbl1(id) ON DELETE CASCADE,
    prop text NOT NULL);

Where tbl2 references tbl1 with a "ON DELETE CASCADE".

Furthermore, let's define a trigger on tbl2 which is executed after a ROW is deleted:

CREATE FUNCTION test_fn() RETURNS trigger AS $$
BEGIN
    RAISE NOTICE 'test_fn()';
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER test_delete AFTER DELETE ON tbl2
    FOR EACH ROW EXECUTE PROCEDURE test_fn();

The trigger is always executed after a ROW in tbl2 is deleted, independently if the rows are removed directly or through on cascade. For example both statements below eventually fire the trigger:

DELETE FROM tbl1 WHERE id = 1;
DELETE FROM tbl2 WHERE id = 1;

Inside the test_fn(), is it possible to distinguish the two cases? I.e. figure out why the row is removed? I tried to determine the reason using the stack (i.e. with GET DIAGNOSTICS stack = PG_CONTEXT) but nothing came out.

Can anybody help me here? Thanks a lot in advance

Answer 1

The context of the operation (i.e. is it cascaded delete or simple delete) should be stored somewhere. You can use a custom parameter for this purpose. When the cascaded delete (from table tbl1) is performed, triggers are fired in the following order:

trigger before delete on tbl1
trigger before delete on tbl2
trigger after delete on tbl1
trigger after delete on tbl2

You need therefore two triggers (before and after delete) on table tbl1 and a trigger before delete on table tbl2.

Create two triggers on tbl1. Set custom parameter to on in the before trigger function and to off in the after trigger function:

create or replace function tbl1_trigger_before_delete()
returns trigger language plpgsql as $$
begin
    set tbl1.cascade to on;
    return old;
end $$;

create or replace function tbl1_trigger_after_delete()
returns trigger language plpgsql as $$
begin
    set tbl1.cascade to off;
    return null;
end $$;

create trigger tbl1_trigger_before_delete
before delete on tbl1
for each row execute procedure tbl1_trigger_before_delete();

create trigger tbl1_trigger_after_delete
after delete on tbl1
for each row execute procedure tbl1_trigger_after_delete();

In tbl2 trigger function check the current value of the parameter. Exception block is necessary in case the parameter has not been set yet:

create or replace function tbl2_trigger_before_delete()
returns trigger language plpgsql as $$
begin
    begin
        if current_setting('tbl1.cascade') = 'on' then
            raise notice 'cascaded';
        else
            raise exception '';
        end if;
    exception when others then
        raise notice 'not cascaded';
    end;
    return old;
end $$;

create trigger tbl2_trigger_before_delete
before delete on tbl2
for each row execute procedure tbl2_trigger_before_delete();

Test:

insert into tbl1 values
(1, '1'),
(2, '2');

insert into tbl2 values
(1, 1, '1'),
(2, 1, '2'),
(3, 2, '3'),
(4, 2, '4');

delete from tbl1 where id = 1;
NOTICE:  cascaded
NOTICE:  cascaded
DELETE 1

delete from tbl2 where owner = 2;
NOTICE:  not cascaded
NOTICE:  not cascaded
DELETE 2    

---

Alternative solution.

When the trigger before delete on table tbl2 is executed in context of cascaded delete, the diagnostic value PG_EXCEPTION_CONTEXT is set to some string and it is empty when the delete is not cascaded:

create or replace function tbl2_trigger_before_delete()
returns trigger language plpgsql as $$
declare
    context text;
begin
    begin
        raise exception '';
    exception when others then
        GET STACKED DIAGNOSTICS context := PG_EXCEPTION_CONTEXT;
    end;
    if context = '' then
        raise notice 'not cascaded';
    else
        raise notice 'cascaded';
    end if;
    return old;
end $$;

create trigger tbl2_trigger_before_delete
before delete on tbl2
for each row execute procedure tbl2_trigger_before_delete();

This solution may be questionable in this sense that it results only from tests, this behavior is not documented anywhere.

Answer 2

Have you tried using EXPLAIN ANALYZE on the query to see what comes out the PostgreSQL query analyzer. According to the PostgreSQL v9.4 documentation, Postgres will show

"The Execution time shown by EXPLAIN ANALYZE includes executor start-up and shut-down time, as well as the time to run any triggers that are fired, but it does not include parsing, rewriting, or planning time. Time spent executing BEFORE triggers, if any, is included in the time for the related Insert, Update, or Delete node; but time spent executing AFTER triggers is not counted there because AFTER triggers are fired after completion of the whole plan. The total time spent in each trigger (either BEFORE or AFTER) is also shown separately. Note that deferred constraint triggers will not be executed until end of transaction and are thus not considered at all by EXPLAIN ANALYZE."

(source URL: http://www.postgresql.org/docs/9.4/static/using-explain.html)

If you're using PostgreSQL v9.1, however, you need to use EXPLAIN ANALYZE with VERBOSE to get the same functionality (http://www.postgresql.org/docs/9.1/static/sql-explain.html)