Dropping a column can break a trigger WHEN clause

Open
#176,177 1 comment 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Assessment

Difficulty
4/5
Estimated time
3-5 days
Newbie friendliness
45/100
Issue type
Bug
Clarity
Mostly clear
Activity status
Active
Tech stack
go, postgresql, sql
Domain
backend, databases

Research direction

Start by tracing how CockroachDB records dependencies for trigger WHEN clauses and how ALTER TABLE ... DROP COLUMN checks or removes dependent objects. The issue provides a SQL reproduction; use it to verify that dropping the referenced column is rejected or CASCADE removes the trigger, so subsequent inserts do not fail.

Written by the indexing model from the issue text.

Description

C-bug O-community T-sql-foundations X-blathers-triaged X-move-internal

Describe the problem

A column referenced only by a trigger WHEN clause is not tracked as a
dependency. ALTER TABLE ... DROP COLUMN succeeds and leaves the trigger in
place, after which every insert into the table fails because the trigger
references a column that no longer exists.

To Reproduce

DROP TABLE IF EXISTS trigger_when_dependency;
DROP FUNCTION IF EXISTS trigger_when_dependency_fn();

CREATE TABLE trigger_when_dependency (
    id INT PRIMARY KEY,
    a INT,
    b INT
);

CREATE FUNCTION trigger_when_dependency_fn() RETURNS TRIGGER AS $$
BEGIN
    RETURN NEW;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_when_dependency_trg
BEFORE INSERT ON trigger_when_dependency
FOR EACH ROW
WHEN ((new).a > 0)
EXECUTE FUNCTION trigger_when_dependency_fn();

ALTER TABLE trigger_when_dependency DROP COLUMN a;
-- ALTER TABLE

INSERT INTO trigger_when_dependency (id, b) VALUES (1, 1);
-- ERROR: could not identify column "a" in tuple{int AS id, int AS b}

Expected behavior

The dependency should prevent dropping a, or DROP COLUMN ... CASCADE
should remove the dependent trigger. The table must not be left in a state
where ordinary inserts fail. The final INSERT should either succeed, or the
earlier ALTER should be rejected.

Environment:

  • CockroachDB version: v26.3.0-alpha.00000000-dev-13cb3eb27674b4a981e5c526d04c2387e81efd0b
  • Server OS: Linux amd64
  • Client app: psql 16.15

Jira issue: CRDB-70505

Dominant language
Go
Stars
32.6k
Forks
4.2k
PR merge metrics
No merged PRs in 30d

Getting set up

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from cockroachdb/cockroach

All issues in cockroachdb/cockroach

Similar issues

More Go issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.