sql: BEFORE triggers can cause spurious constraint violation errors

[DrewKimball]

Consider a diamond cascade pattern, where an update to a parent table cascades to two child tables, which in turn cascade to the same grandchild table. In the time between when the first child cascades to the grandchild, and the second child does the same, attempting to check the FK relation from the grandchild to the second child would find a constraint violation. Here's an example logic test:

statement ok
CREATE TABLE parent (k INT PRIMARY KEY);

statement ok
CREATE TABLE child (k INT PRIMARY KEY, v INT UNIQUE NOT NULL REFERENCES parent(k) ON UPDATE CASCADE ON DELETE CASCADE);
CREATE TABLE child2 (k INT PRIMARY KEY, v INT UNIQUE NOT NULL REFERENCES parent(k) ON UPDATE CASCADE ON DELETE CASCADE);

statement ok
CREATE TABLE grandchild (
  k INT PRIMARY KEY,
  v INT REFERENCES child(v) ON UPDATE CASCADE ON DELETE CASCADE,
  v2 INT REFERENCES child2(v) ON UPDATE CASCADE ON DELETE CASCADE
);

statement ok
CREATE FUNCTION g() RETURNS TRIGGER LANGUAGE PLpgSQL AS $$
  BEGIN
    RAISE NOTICE '% % ON %: % -> %', TG_WHEN, TG_OP, TG_TABLE_NAME, OLD, NEW;
    RETURN COALESCE(NEW, OLD);
  END
$$;

statement ok
CREATE TRIGGER foo BEFORE INSERT OR UPDATE OR DELETE ON grandchild FOR EACH ROW EXECUTE FUNCTION g();

statement ok
INSERT INTO parent VALUES (1), (2), (3);
INSERT INTO child VALUES (1, 1), (2, 2), (3, 3);
INSERT INTO child2 VALUES (1, 1), (2, 2), (3, 3);
INSERT INTO grandchild VALUES (1, 1, 1), (2, 2, 2), (3, 2, 2), (4, 3, 3);

# Update the parent table, which should cascade to the children and grandchild.
# Note that both child tables cascade to the grandchild.
statement ok
UPDATE parent SET k = k + 10 WHERE k < 3;

The problem is that the trigger could in principle change how the rows are modified during the cascade to the grandchild. This results in a check being queued for both child tables after the cascade for the first one completes. This results in a FK violation error, when simply waiting for the second cascade to complete would avoid the error.

We actually disallow BEFORE triggers that modify rows in cascades by default (determined by unsafe_allow_triggers_modifying_cascades). As long as this is the case, we could avoid building the extra checks, since we verify at runtime that the cascading mutation isn't modified.

Jira issue: CRDB-43749

[blathers-crl]

Hi @DrewKimball, please add branch-* labels to identify which branch(es) this C-bug affects.

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

[blathers-crl]

Based on the specified backports for linked PR #133220, I applied the following new label(s) to this issue: branch-release-24.3. Please adjust the labels as needed to match the branches actually affected by this issue, including adding any known older branches.

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