Dropping a column can break a trigger WHEN clause
Nobody has claimed this yet.
Assessment
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Newbie friendliness
- 45/100
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
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:
psql16.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
- No Dockerfile or Docker Compose file
- No pull request template
- Read the contributing guide
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
More from cockroachdb/cockroach
-
C-bug O-community X-blathers-untriaged
Difficulty 2/5 1-3 hours Newbie friendliness 76/100
cockroachdb/cockroach#176245 · 1 comment ·
-
sql: pg_database.dattablespace is 0 but pg_tablespace.oid is 1663, so they no longer joinPossibly taken @1AbdullahButt claimed this 14 days ago. OpenC-bug O-community X-blathers-triaged X-move-internal
Difficulty 2/5 1-3 hours Newbie friendliness 68/100
cockroachdb/cockroach#176228 · 1 comment ·
-
PG compat: overflow in integer power calculation triggers incompatible SQLSTATEMay be free again @darshpatel1052 claimed this 18 days ago, and no pull request is open. OpenC-bug O-community X-blathers-untriaged
Difficulty 2/5 1-3 hours Newbie friendliness 62/100
cockroachdb/cockroach#176223 · 2 comments ·
-
util/log: bufferedSinkCloser.Close leaks a goroutine after timeoutPossibly taken @dajiaohuang claimed this 14 days ago. OpenC-bug O-community X-blathers-untriaged
Difficulty 1/5 Under an hour Newbie friendliness 84/100
cockroachdb/cockroach#176210 · 1 comment ·
-
C-bug O-community T-sql-queries X-blathers-triaged X-move-internal
Difficulty 2/5 1-3 hours Newbie friendliness 78/100
cockroachdb/cockroach#176150 · 1 comment ·
All issues in cockroachdb/cockroach
Similar issues
-
☢️ Bug v3
Difficulty 2/5 1-3 hours Newbie friendliness 82/100
Maintainers usually reply within 1 day
-
area/import-export area/projects concern/ux
Difficulty 2/5 1-3 hours Newbie friendliness 68/100
go-vikunja/vikunja#4455 · 1 comment ·
Maintainers usually reply within 2 days
-
Dependabot PRs no longer merge automaticallyPossibly taken @dwisiswant0 claimed this today. Open
Difficulty 2/5 1-3 hours Newbie friendliness 78/100
projectdiscovery/urlfinder#160 ·
Maintainers usually reply within 1 day
-
database
Difficulty 2/5 1-3 hours Newbie friendliness 72/100
chnm/legal-modernism#450 ·
Maintainers usually reply within 1 day
-
tech-debt
Difficulty 2/5 1-3 hours Newbie friendliness 76/100
Maintainers usually reply within 1 day