-- Create test tables CREATE TABLE Employee ( EmployeeId INT NOT NULL, LastName VARCHAR(20) NOT NULL, CONSTRAINT PK_Employee PRIMARY KEY (EmployeeId)); CREATE TABLE Employee_Audit ( EmployeeId INT NOT NULL, LastName VARCHAR(20) NOT NULL, EmpAdditionTime VARCHAR(20) NOT NULL); -- Create trigger functions CREATE OR REPLACE FUNCTION employee_insert_trigger_fnc() RETURNS trigger AS $$ BEGIN INSERT INTO Employee_Audit (EmployeeId, LastName, EmpAdditionTime) VALUES(NEW.EmployeeId,NEW.LastName,current_date); RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION before_trigger_fnc() RETURNS trigger AS $$ BEGIN PERFORM 'SELECT 1'; RETURN NEW; END; $$ LANGUAGE plpgsql; -- Hook the insert trigger twice after each row CREATE TRIGGER employee_insert_trigger AFTER INSERT ON Employee FOR EACH ROW EXECUTE PROCEDURE employee_insert_trigger_fnc(); CREATE TRIGGER employee_insert_trigger_2 AFTER INSERT ON Employee FOR EACH ROW EXECUTE PROCEDURE employee_insert_trigger_fnc(); -- Call insert /*dddbs='postgres.db',traceparent='00-00000000000000000000000000000001-0000000000000001-01'*/ INSERT INTO Employee VALUES(10,'Adams'); -- -- +--------------------------------------------------------------------------------------------------------+ -- | B: INSERT INTO Employee | -- ++----------+-+--------------+---------------------------------------------------------------------------+ -- |C: Planner| |D: ExecutorRun| G: ExecutorFinish | -- +----------+ +--------------+--+-----------------------------------+-+--------------------------------+ -- |I: INSERT INTO Employee_Audit... | |N: INSERT INTO Employee_Audit...| -- +-+----------++--------------------++ +--------------------------------+ -- |J: Planner||K: ExecutorRun | |O: ExecutorRun | -- +----------++--------------------+ +---------------+ -- Gather span_id, span start and span end of call with triggers SELECT span_id AS span_b_id, get_epoch(span_start) as span_b_start, get_epoch(span_end) as span_b_end from pg_tracing_peek_spans where parent_id='0000000000000001' AND span_type='Insert query' \gset SELECT span_id AS span_c_id, get_epoch(span_start) as span_c_start, get_epoch(span_end) as span_c_end from pg_tracing_peek_spans where parent_id=:'span_b_id' and span_type='Planner' \gset SELECT span_id AS span_d_id, get_epoch(span_start) as span_d_start, get_epoch(span_end) as span_d_end from pg_tracing_peek_spans where parent_id=:'span_b_id' and span_operation='ExecutorRun' \gset -- Executor Finish, first trigger SELECT span_id AS span_g_id, get_epoch(span_start) as span_g_start, get_epoch(span_end) as span_g_end from pg_tracing_peek_spans where parent_id=:'span_b_id' and span_operation='ExecutorFinish' \gset SELECT span_id AS span_i_id, get_epoch(span_start) as span_i_start, get_epoch(span_end) as span_i_end from pg_tracing_peek_spans where parent_id=:'span_g_id' and span_type='Insert query' limit 1 \gset SELECT span_id AS span_j_id, get_epoch(span_start) as span_j_start, get_epoch(span_end) as span_j_end from pg_tracing_peek_spans where parent_id=:'span_i_id' and span_type='Planner' \gset SELECT span_id AS span_k_id, get_epoch(span_start) as span_k_start, get_epoch(span_end) as span_k_end from pg_tracing_peek_spans where parent_id=:'span_i_id' and span_operation='ExecutorRun' \gset -- Executor Finish, second trigger SELECT span_id AS span_n_id, get_epoch(span_start) as span_n_start, get_epoch(span_end) as span_n_end from pg_tracing_peek_spans where parent_id=:'span_g_id' and span_type='Insert query' limit 1 offset 1 \gset SELECT span_id AS span_o_id, get_epoch(span_start) as span_o_start, get_epoch(span_end) as span_o_end from pg_tracing_peek_spans where parent_id=:'span_n_id' and span_operation='ExecutorRun' \gset -- Check that spans' start and end are within expection -- Planner SELECT :span_c_start >= :span_b_start as planner_starts_after_root_span; planner_starts_after_root_span -------------------------------- t (1 row) -- ExecutorRun SELECT :span_d_start >= :span_c_end as executor_run_starts_after_planner, :span_d_end <= :span_g_start as executor_run_ends_before_finish; executor_run_starts_after_planner | executor_run_ends_before_finish -----------------------------------+--------------------------------- t | t (1 row) -- ExecutorFinish SELECT :span_g_start <= :span_i_start as executor_finish_starts_before_child, :span_g_end >= :span_o_end as executor_finish_ends_after_child; executor_finish_starts_before_child | executor_finish_ends_after_child -------------------------------------+---------------------------------- t | t (1 row) -- First trigger SELECT :span_i_end >= GREATEST(:span_j_end, :span_k_end) as first_trigger_ends_after_children; first_trigger_ends_after_children ----------------------------------- t (1 row) -- Second trigger SELECT :span_o_start >= :span_i_end as second_trigger_starts_after_first, :span_o_end >= :span_o_end as second_trigger_ends_after_child, :span_o_end <= :span_g_end as second_trigger_ends_before_executorfinish_end, :span_n_end <= :span_g_end as second_trigger_ends_before_root_executorfinish_end; second_trigger_starts_after_first | second_trigger_ends_after_child | second_trigger_ends_before_executorfinish_end | second_trigger_ends_before_root_executorfinish_end -----------------------------------+---------------------------------+-----------------------------------------------+---------------------------------------------------- t | t | t | t (1 row) -- Check span_operation SELECT span_type, span_operation, lvl from peek_ordered_spans where trace_id='00000000000000000000000000000001'; span_type | span_operation | lvl -------------------+---------------------------------------------------------------------------------------------------------------------+----- Insert query | INSERT INTO Employee VALUES($1,$2) | 0 Planner | Planner | 1 ExecutorRun | ExecutorRun | 1 Insert | Insert on employee | 2 Result | Result | 3 ExecutorFinish | ExecutorFinish | 1 Insert query | INSERT INTO Employee_Audit (EmployeeId, LastName, EmpAdditionTime) VALUES(NEW.EmployeeId,NEW.LastName,current_date) | 2 Planner | Planner | 3 ExecutorRun | ExecutorRun | 3 Insert | Insert on employee_audit | 4 Result | Result | 5 Insert query | INSERT INTO Employee_Audit (EmployeeId, LastName, EmpAdditionTime) VALUES(NEW.EmployeeId,NEW.LastName,current_date) | 2 Planner | Planner | 3 ExecutorRun | ExecutorRun | 3 Insert | Insert on employee_audit | 4 Result | Result | 5 TransactionCommit | TransactionCommit | 0 (17 rows) -- Check count of query_id SELECT count(distinct query_id) from pg_tracing_consume_spans where trace_id='00000000000000000000000000000001'; count ------- 2 (1 row) -- Test table to test before trigger CREATE TABLE before_trigger_table (a int); -- Hook the before trigger CREATE TRIGGER employee_insert_trigger BEFORE INSERT ON before_trigger_table FOR EACH ROW EXECUTE PROCEDURE before_trigger_fnc(); CREATE TRIGGER employee_update_trigger BEFORE UPDATE ON before_trigger_table FOR EACH ROW EXECUTE PROCEDURE before_trigger_fnc(); CREATE TRIGGER employee_delete_trigger BEFORE DELETE ON before_trigger_table FOR EACH ROW EXECUTE PROCEDURE before_trigger_fnc(); -- Call before trigger insert /*dddbs='postgres.db',traceparent='00-00000000000000000000000000000001-0000000000000001-01'*/ INSERT INTO before_trigger_table SELECT generate_series(1,2); SELECT trace_id, span_type, span_operation, lvl from peek_ordered_spans where trace_id='00000000000000000000000000000001'; trace_id | span_type | span_operation | lvl ----------------------------------+-------------------+----------------------------------------------------------------+----- 00000000000000000000000000000001 | Insert query | INSERT INTO before_trigger_table SELECT generate_series($1,$2) | 0 00000000000000000000000000000001 | Planner | Planner | 1 00000000000000000000000000000001 | ExecutorRun | ExecutorRun | 1 00000000000000000000000000000001 | Insert | Insert on before_trigger_table | 2 00000000000000000000000000000001 | ProjectSet | ProjectSet | 3 00000000000000000000000000000001 | Result | Result | 4 00000000000000000000000000000001 | Select query | SELECT $1 | 2 00000000000000000000000000000001 | Planner | Planner | 3 00000000000000000000000000000001 | ExecutorRun | ExecutorRun | 3 00000000000000000000000000000001 | Result | Result | 4 00000000000000000000000000000001 | Select query | SELECT $1 | 2 00000000000000000000000000000001 | ExecutorRun | ExecutorRun | 3 00000000000000000000000000000001 | Result | Result | 4 00000000000000000000000000000001 | TransactionCommit | TransactionCommit | 0 (14 rows) -- Call before trigger update /*dddbs='postgres.db',traceparent='00-00000000000000000000000000000002-0000000000000002-01'*/ UPDATE before_trigger_table set a=1; SELECT trace_id, span_type, span_operation, lvl from peek_ordered_spans where trace_id='00000000000000000000000000000002'; trace_id | span_type | span_operation | lvl ----------------------------------+-------------------+--------------------------------------+----- 00000000000000000000000000000002 | Update query | UPDATE before_trigger_table set a=$1 | 0 00000000000000000000000000000002 | Planner | Planner | 1 00000000000000000000000000000002 | ExecutorRun | ExecutorRun | 1 00000000000000000000000000000002 | Update | Update on before_trigger_table | 2 00000000000000000000000000000002 | SeqScan | SeqScan on before_trigger_table | 3 00000000000000000000000000000002 | Select query | SELECT $1 | 2 00000000000000000000000000000002 | Planner | Planner | 3 00000000000000000000000000000002 | ExecutorRun | ExecutorRun | 3 00000000000000000000000000000002 | Result | Result | 4 00000000000000000000000000000002 | Select query | SELECT $1 | 2 00000000000000000000000000000002 | ExecutorRun | ExecutorRun | 3 00000000000000000000000000000002 | Result | Result | 4 00000000000000000000000000000002 | TransactionCommit | TransactionCommit | 0 (13 rows) -- Call before trigger delete /*dddbs='postgres.db',traceparent='00-00000000000000000000000000000003-0000000000000003-01'*/ DELETE FROM before_trigger_table where a=1; SELECT trace_id, span_type, span_operation, lvl from peek_ordered_spans where trace_id='00000000000000000000000000000003'; trace_id | span_type | span_operation | lvl ----------------------------------+-------------------+---------------------------------------------+----- 00000000000000000000000000000003 | Delete query | DELETE FROM before_trigger_table where a=$1 | 0 00000000000000000000000000000003 | Planner | Planner | 1 00000000000000000000000000000003 | ExecutorRun | ExecutorRun | 1 00000000000000000000000000000003 | Delete | Delete on before_trigger_table | 2 00000000000000000000000000000003 | SeqScan | SeqScan on before_trigger_table | 3 00000000000000000000000000000003 | Select query | SELECT $1 | 2 00000000000000000000000000000003 | Planner | Planner | 3 00000000000000000000000000000003 | ExecutorRun | ExecutorRun | 3 00000000000000000000000000000003 | Result | Result | 4 00000000000000000000000000000003 | Select query | SELECT $1 | 2 00000000000000000000000000000003 | ExecutorRun | ExecutorRun | 3 00000000000000000000000000000003 | Result | Result | 4 00000000000000000000000000000003 | TransactionCommit | TransactionCommit | 0 (13 rows) -- Check count of query_id SELECT count(distinct query_id) from pg_tracing_consume_spans; count ------- 4 (1 row) -- Test foreign key failure CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple'); CREATE TABLE enumtest_parent (id rainbow PRIMARY KEY); CREATE TABLE enumtest_child (parent rainbow REFERENCES enumtest_parent); INSERT INTO enumtest_parent VALUES ('red'); /*traceparent='00-00000000000000000000000000000004-0000000000000004-01'*/ INSERT INTO enumtest_child VALUES ('blue'); ERROR: insert or update on table "enumtest_child" violates foreign key constraint "enumtest_child_parent_fkey" DETAIL: Key (parent)=(blue) is not present in table "enumtest_parent". SELECT trace_id, span_type, span_operation, lvl from peek_ordered_spans where trace_id='00000000000000000000000000000004'; trace_id | span_type | span_operation | lvl ----------------------------------+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------+----- 00000000000000000000000000000004 | Insert query | INSERT INTO enumtest_child VALUES ($1) | 0 00000000000000000000000000000004 | Planner | Planner | 1 00000000000000000000000000000004 | ExecutorRun | ExecutorRun | 1 00000000000000000000000000000004 | Insert | Insert on enumtest_child | 2 00000000000000000000000000000004 | Result | Result | 3 00000000000000000000000000000004 | ExecutorFinish | ExecutorFinish | 1 00000000000000000000000000000004 | Select query | SELECT $2 FROM ONLY "public"."enumtest_parent" x WHERE "id"::pg_catalog.anyenum OPERATOR(pg_catalog.=) $1::pg_catalog.anyenum FOR KEY SHARE OF x | 2 00000000000000000000000000000004 | Planner | Planner | 3 00000000000000000000000000000004 | ExecutorRun | ExecutorRun | 3 00000000000000000000000000000004 | LockRows | LockRows | 4 00000000000000000000000000000004 | IndexScan | IndexScan using enumtest_parent_pkey on enumtest_parent x | 5 (11 rows) -- Test before trigger with copy dml create table copydml_test (id serial, t text); create function qqq_trig() returns trigger as $$ begin if tg_op in ('INSERT', 'UPDATE') then return new; end if; end $$ language plpgsql; create trigger qqqbef before insert or update or delete on copydml_test for each row execute procedure qqq_trig(); /*traceparent='00-00000000000000000000000000000005-0000000000000005-01'*/ copy (insert into copydml_test (t) values ('f') returning id) to stdout; 1 SELECT trace_id, span_type, span_operation, lvl from peek_ordered_spans where trace_id='00000000000000000000000000000005'; trace_id | span_type | span_operation | lvl ----------------------------------+-------------------+--------------------------------------------------------------------------+----- 00000000000000000000000000000005 | Utility query | copy (insert into copydml_test (t) values ('f') returning id) to stdout; | 0 00000000000000000000000000000005 | ProcessUtility | ProcessUtility | 1 00000000000000000000000000000005 | Insert query | copy (insert into copydml_test (t) values ($1) returning id) to stdout | 2 00000000000000000000000000000005 | Planner | Planner | 3 00000000000000000000000000000005 | ExecutorRun | ExecutorRun | 3 00000000000000000000000000000005 | Insert | Insert on copydml_test | 4 00000000000000000000000000000005 | Result | Result | 5 00000000000000000000000000000005 | Select query | tg_op in ($7, $8) | 4 00000000000000000000000000000005 | Planner | Planner | 5 00000000000000000000000000000005 | TransactionCommit | TransactionCommit | 0 (10 rows) -- Test explain on a before trigger -- This will go through ExecutorEnd without any parent executor run /*dddbs='postgres.db',traceparent='00-fed00000000000000000000000000006-0000000000000003-01'*/ explain INSERT INTO before_trigger_table VALUES(10); QUERY PLAN ------------------------------------------------------------------ Insert on before_trigger_table (cost=0.00..0.01 rows=0 width=0) -> Result (cost=0.00..0.01 rows=1 width=4) (2 rows) SELECT trace_id, span_type, span_operation, lvl from peek_ordered_spans where trace_id='fed00000000000000000000000000006'; trace_id | span_type | span_operation | lvl ----------------------------------+----------------+------------------------------------------------------+----- fed00000000000000000000000000006 | Utility query | explain INSERT INTO before_trigger_table VALUES(10); | 0 fed00000000000000000000000000006 | ProcessUtility | ProcessUtility | 1 fed00000000000000000000000000006 | Insert query | explain INSERT INTO before_trigger_table VALUES($1); | 2 fed00000000000000000000000000006 | Planner | Planner | 3 (4 rows) -- Cleanup CALL reset_settings(); CALL clean_spans();