sql: can't perform mutiple deletes when there are self-referencing foreign keys

[chessman]

BUG REPORT

I180522 14:00:20.450904 1 util/log/clog.go:1134  [config] file created at: 2018/05/22 14:00:20
I180522 14:00:20.450904 1 util/log/clog.go:1134  [config] running on machine: 9d314f7029a7
I180522 14:00:20.450904 1 util/log/clog.go:1134  [config] binary: CockroachDB CCL v2.0.2 (x86_64-unknown-linux-gnu, built 2018/05/21 14:55:20, go1.10)
I180522 14:00:20.450904 1 util/log/clog.go:1134  [config] arguments: [/cockroach/cockroach start --insecure]
I180522 14:00:30.619025 50 cli/start.go:595  [config] clusterID: 54ebe203-1eb9-493e-8fcf-77d5ee9cc647

2. Please describe the issue you observed:

• What did you do?

CREATE TABLE test (
  id INT PRIMARY KEY,
  parent INT REFERENCES test(id)
);
INSERT INTO test (id, parent) VALUES (1, NULL);
INSERT INTO test (id, parent) VALUES (50, 1);
INSERT INTO test (id, parent) VALUES (75, 1);
DELETE FROM test;

https://www.db-fiddle.com/f/aK4HEbXXLyWw6k8oDf294U/0

• What did you expect to see?

No errors.

• What did you see instead?

pq: foreign key violation: values [1] in columns [id] referenced in table "test"

[BramGruneir]

Sadly, this is a known limitation right now, but one we do want to fix.

We don't do a good job of batching all the foreign keys checks until the end of the statement, which would be required here. So each delete can be seen to happen one at a time.

I did check and this works well in postgres.

If you do want to remove everything from a table, I suggest using TRUNCATE instead, which will always be faster.

root@:26257/test> DELETE FROM test;
pq: foreign key violation: values [1] in columns [id] referenced in table "test"

root@:26257/test> TRUNCATE test;
TRUNCATE

Time: 41.352579ms

@rmloveland, this might need some more documentation.

[rmloveland]

Thanks for the heads up @BramGruneir - created cockroachdb/docs#3186 to update the docs.

[sploiselle]

@BramGruneir Can I get a quick blurb describing this known limitation w/r/t the impact to user experience? Ideally, we need it by Friday 10/26 for the 2.1 Known Limitations page. Posting it on this issue and/or pinging me would be great.

[sploiselle]

@BramGruneir Just wanted to ping you real quick to see if you could get this description to me by first thing Monday?

[BramGruneir]

@sploiselle

One of the current limitations of our FK checking is that we evaluate every foreign key relationship on a per row basis. So if a statement mutates more than one row, we don't wait to check if the FK constraints are met until the end of the statement, but eagerly check them after each single mutation.

Postgres does wait until the end of a statement and you can even defer some of the checks until the end of a transaction. See https://begriffs.com/posts/2017-08-27-deferrable-sql-constraints.html for a really good explanation on why one would want to defer a check.

Specifically, our limitation is that we don't wait until the end of a statement to evaluate FK constraints and instead we eagerly evaluate them on a per row basis. This also slows down our FK checking further adding to our slowness in #15157.

To bring this back to the example above, since there is a self-referential FK constraint, when evaluating the DELETE, it deletes a single row, and as long as the deletes are not performed in the exact correct order (the reverse of the order in which they were added), it will cause an FK constraint violation.

[asubiotto]

@RaduBerinde is this fixed by opt-driven foreign keys?

[RaduBerinde]

Yeah, this works now.

[jseldess]

@RaduBerinde, can I remove/resolve this known limitation in the 20.1 docs? https://www.cockroachlabs.com/docs/dev/known-limitations.html#cannot-delete-multiple-rows-with-self-referencing-fks

[RaduBerinde]

Yeah I think we can remove that.