sql: triggers do expensive system table lookups #144211
Description
Triggers currently use cat.Catalog.FullyQualifiedName to get the schema name for the target table. However, this function does not go through any cache and always results in potentially expensive KV lookups. Here's an example that demonstrates the issue:
CREATE DATABASE multi_region_test_db PRIMARY REGION "europe-west1" REGIONS "us-west1", "us-east1";
USE multi_region_test_db;
CREATE TABLE t (x INT, y INT) LOCALITY REGIONAL BY ROW;
CREATE OR REPLACE FUNCTION trigger_fn() RETURNS TRIGGER AS $$ BEGIN RETURN NEW; END $$ LANGUAGE PLpgSQL;
CREATE TRIGGER tr BEFORE INSERT OR UPDATE ON t FOR EACH ROW EXECUTE FUNCTION trigger_fn();
EXPLAIN ANALYZE (DEBUG) INSERT INTO t VALUES (1, 2);
EXPLAIN ANALYZE (DEBUG) INSERT INTO t VALUES (1, 2);
EXPLAIN ANALYZE (DEBUG) INSERT INTO t VALUES (1, 2);
The following diff that removes the call produces a trace without the planning-time overhead:
diff --git a/pkg/sql/opt/optbuilder/trigger.go b/pkg/sql/opt/optbuilder/trigger.go
index d43eca8f54b..19809c67ccf 100644
--- a/pkg/sql/opt/optbuilder/trigger.go
+++ b/pkg/sql/opt/optbuilder/trigger.go
@@ -220,15 +220,15 @@ func (mb *mutationBuilder) buildTriggerFunctionArgs(
tgOp := tree.NewDString(eventType.String())
tgRelID := tree.NewDOid(oid.Oid(mb.tab.ID()))
tgTableName := tree.NewDString(string(mb.tab.Name()))
- fqName, err := mb.b.catalog.FullyQualifiedName(mb.b.ctx, mb.tab)
- if err != nil {
- panic(err)
- }
- tgTableSchema := tree.NewDString(fqName.Schema())
+ //fqName, err := mb.b.catalog.FullyQualifiedName(mb.b.ctx, mb.tab)
+ //if err != nil {
+ // panic(err)
+ //}
+ tgTableSchema := tree.NewDString("foo")
tgNumArgs := tree.NewDInt(tree.DInt(len(trigger.FuncArgs())))
tgArgV := tree.NewDArray(types.String)
for _, arg := range trigger.FuncArgs() {
- err = tgArgV.Append(arg)
+ err := tgArgV.Append(arg)
if err != nil {
panic(err)
}Jira issue: CRDB-49272
Metadata
Metadata
Assignees
Labels
Type
Projects
Status