Trigger firings should show up in EXPLAIN output

[mw5h]

Is your feature request related to a problem? Please describe.

While QAing Triggers, it was difficult to determine whether triggers would fire during a query. Triggers should be visible in explain output, the same as any other constraint.

Take the following output from investigating #134745:

demo@127.0.0.1:26257/demoapp/defaultdb> EXPLAIN DELETE FROM my_parent WHERE true;
                                                    info
------------------------------------------------------------------------------------------------------------
  distribution: local
  vectorized: true

  • root
  │
  ├── • delete range
  │     from: my_parent
  │     spans: FULL SCAN
  │
  └── • fk-cascade
      │ fk: my_fk
      │
      └── • delete
          │ from: my_table
          │
          └── • filter
              │ estimated row count: 2
              │ filter: change_name IS DISTINCT FROM NULL
              │
              └── • render
                  │
                  └── • render
                      │
                      └── • render
                          │
                          └── • scan
                                estimated row count: 2 (100% of the table; stats collected 38 minutes ago)
                                table: my_table@my_table_pkey
                                spans: FULL SCAN
(29 rows)

Time: 13ms total (execution 12ms / network 1ms)

You would never know that a trigger would fire as part of this query!

Describe the solution you'd like Triggers should show up in explain output, preferably with information about timing, etc.

Jira issue: CRDB-44212

[DrewKimball]

BEFORE triggers do actually show up in the EXPLAIN output, but only with the VERBOSE option:

query T
EXPLAIN (VERBOSE) DELETE FROM my_parent WHERE true;
----
distribution: local
vectorized: true
·
• root
│ columns: ()
│
├── • delete range
│     columns: ()
│     estimated row count: 0 (missing stats)
│     from: my_parent
│     spans: FULL SCAN
│
└── • fk-cascade
    │ fk: my_fk
    │
    └── • delete
        │ columns: ()
        │ estimated row count: 0 (missing stats)
        │ from: my_table
        │
        └── • project
            │ columns: (id)
            │
            └── • filter
                │ columns: ("check-rows", id, name, is_locked, old, prevent_delete_locked)
                │ estimated row count: 1,000 (missing stats)
                │ filter: prevent_delete_locked IS DISTINCT FROM NULL
                │
                └── • render
                    │ columns: ("check-rows", id, name, is_locked, old, prevent_delete_locked)
                    │ render check-rows: CASE WHEN prevent_delete_locked IS DISTINCT FROM old THEN crdb_internal.plpgsql_raise('ERROR', 'trigger prevent_locked_delete attempted to modify or filter a row in a cascade operation: ' || old::STRING, e'changing the rows updated or deleted by a foreign-key cascade\n can cause constraint violations, and therefore is not allowed', e'to enable this behavior (with risk of constraint violation), set\nthe session variable \'unsafe_allow_triggers_modifying_cascades\' to true', '27000') ELSE CAST(NULL AS INT8) END
                    │ render id: id
                    │ render name: name
                    │ render is_locked: is_locked
                    │ render old: old
                    │ render prevent_delete_locked: prevent_delete_locked
                    │
                    └── • render
                        │ columns: (prevent_delete_locked, id, name, is_locked, old)
                        │ render prevent_delete_locked: prevent_delete_locked(NULL, old, 'prevent_locked_delete', 'BEFORE', 'ROW', 'DELETE', 106, 'my_table', 'my_table', 'public', 0, ARRAY[])
                        │ render id: id
                        │ render name: name
                        │ render is_locked: is_locked
                        │ render old: old
                        │
                        └── • render
                            │ columns: (old, id, name, is_locked)
                            │ render old: ((id, name, is_locked) AS id, name, is_locked)
                            │ render id: id
                            │ render name: name
                            │ render is_locked: is_locked
                            │
                            └── • scan
                                  columns: (id, name, is_locked)
                                  estimated row count: 1,000 (missing stats)
                                  table: my_table@my_table_pkey
                                  spans: FULL SCAN

We could potentially add some logic to show the trigger function invocation even for non-verbose EXPLAIN.

[mw5h]

I feel like triggers are at least as important as foreign key constraints and should have similar visibility.

[DrewKimball]

Maybe we could do something similar to this for BEFORE triggers:

 ├── insert xy
 │    ├── columns: <none>
 │    ├── insert-mapping:
 │    │    ├── column1:5 => x:1
 │    │    └── column2:6 => y:2
 │    ├── input binding: &1
 │    ├── after-triggers
 │    │    ├── tr
 │    │    └── tr2
 │    └── values
 │         ├── columns: column1:5 column2:6
 │         └── (1, 2)

Show the names in non-verbose mode, and also show the function invocations in verbose mode (like we do now).

[mw5h]

That seems reasonable to me!