release-24.3: sql: display triggers in EXPLAIN output

DrewKimball commented 1 day ago

Backport 4/4 commits from #135556.

/cc @cockroachdb/release

sql: refactor functions for handling cat.Trigger

This commit performs some refactoring for the functions that check for the presence of and collect the list of triggers that apply to a given mutation. The functions are now exported to be used in following commits.

Informs #134747 Informs #135155

Release note: None

opt: display BEFORE-trigger names in opt-tester output

This commit adds a before-triggers field to the opt-tester output for mutations. Similar to the after-triggers field, it displays the names of the triggers that will fire for that mutation.

Informs #134747

Release note: None

sql: display BEFORE trigger names for EXPLAIN

This commit adds the names of fired BEFORE triggers to the EXPLAIN output for a mutation. This is visible in both verbose and non-verbose variants, as well as EXPLAIN ANALYZE. Note that the verbose EXPLAIN already shows the trigger-function invocations; the benefit of this change is that it makes clear even in non-verbose EXPLAIN which triggers fired.

Fixes #134747

Release note (sql change): The names of BEFORE triggers fired by a mutation now show up in the EXPLAIN output. The trigger-function invocations are visible in the output of verbose EXPLAIN.

sql: display AFTER triggers for EXPLAIN

This commit adds AFTER triggers to the EXPLAIN and EXPLAIN ANALYZE outputs. The output displays the names of the fired triggers, and then the expressions used to implement them, including the buffer scan that supplies the mutated rows.

There is a subtlety in displaying the plans for triggers for the ANALYZE variants that actually execute the query. This is because the transaction may have auto-committed by the time the plan is being explained, and so attempts to resolve objects while building the trigger plan will fail.

To avoid this problem, we check if the transaction is still open before attempting to build the plan for AFTER triggers, or a cascade with BEFORE triggers. As a result, if the statement auto-commits, only the trigger plans that were actually involved in the execution will be displayed. Note that this will only affect the EXPLAIN output for mutations that involve triggers.

Fixes #135155

Release note (sql change): AFTER triggers will now show up in the output of vanilla EXPLAIN, as well as EXPLAIN ANALYZE.

Release justification: observability improvement for new functionality in 24.3

blathers-crl[bot] commented 1 day ago

Thanks for opening a backport.

Please check the backport criteria before merging:

[ ] Backports should only be created for serious issues or test-only changes.
[ ] Backports should not break backwards-compatibility.
[ ] Backports should change as little code as possible.
[ ] Backports should not change on-disk formats or node communication protocols.
[ ] Backports should not add new functionality (except as defined here).
[ ] Backports must not add, edit, or otherwise modify cluster versions; or add version gates.
[ ] All backports must be reviewed by the owning areas TL. For more information as to how that review should be conducted, please consult the backport policy.

If your backport adds new functionality, please ensure that the following additional criteria are satisfied:

- [ ] There is a high priority need for the functionality that cannot wait until the next release and is difficult to address in another way. - [ ] The new functionality is additive-only and only runs for clusters which have specifically “opted in” to it (e.g. by a cluster setting). - [ ] New code is protected by a conditional check that is trivial to verify and ensures that it only runs for opt-in clusters. State changes must be further protected such that nodes running old binaries will not be negatively impacted by the new state (with a mixed version test added). - [ ] The PM and TL on the team that owns the changed code have signed off that the change obeys the above rules. - [ ] Your backport must be accompanied by a post to the appropriate Slack channel (#db-backports-point-releases or #db-backports-XX-X-release) for awareness and discussion.

Also, please add a brief release justification to the body of your PR to justify this backport.

blathers-crl[bot] commented 1 day ago

Your pull request contains more than 1000 changes. It is strongly encouraged to split big PRs into smaller chunks.

_{:owl: Hoot! I am a Blathers, a bot for CockroachDB. My owner is dev-inf.}

cockroach-teamcity commented 1 day ago

This change is

cockroachdb / cockroach