Truncate fails with foreign key

General
#1

Let me start off by saying I like the direction cockroach is headed. Doing some tests with a generic test db to see how well it works for our use case in an upcoming project…

Dropping table and recreating table works well enough, but this should too:
root@10.0.4.91:26257/employees> TRUNCATE TABLE employees;
pq: “employees” is referenced by foreign key from table “dept_manager”

Above sounds reasonable, had to disable cascade on delete as that isn’t supported yet AFAIK, except…

root@10.0.4.91:26257/employees> select * from dept_manager
-> ;
±--------±-------±----------±--------+
| dept_no | emp_no | from_date | to_date |
±--------±-------±----------±--------+
±--------±-------±----------±--------+
(0 rows)

so there are no rows to complain about…

Somewhat related note, would be nice if delete supported a LIMIT option. Deleting large number of rows is very slow and ends up with pq: transaction deadline exceeded…

#2

Hi @jlauro,

Although this behavior might seem strange since the employees table is empty, it’s actually identical to Postgres’ behavior, which we generally try to stick to as a guiding principle. Adding CASCADE to your query will have the desired effect:

root@:26257/test> CREATE TABLE employees (id INT PRIMARY KEY);
CREATE TABLE
root@:26257/test> CREATE TABLE dept_manager (id INT PRIMARY KEY, emp_id INT REFERENCES employees(id));
CREATE TABLE
root@:26257/test> TRUNCATE TABLE employees;
pq: "employees" is referenced by foreign key from table "dept_manager"
root@:26257/test> TRUNCATE TABLE employees CASCADE;
TRUNCATE

BTW, you can see Postgres does the same thing - check out this log from psql against Postgres 9.6:

jordan=# create table employees (id int primary key);
CREATE TABLE
jordan=# create table dept_manager (id int primary key, emp_id int references employees(id));
CREATE TABLE
jordan=# truncate table employees;
ERROR:  cannot truncate a table referenced in a foreign key constraint
DETAIL:  Table "dept_manager" references "employees".
HINT:  Truncate table "dept_manager" at the same time, or use TRUNCATE ... CASCADE.
jordan=# truncate table employees cascade;
NOTICE:  truncate cascades to table "dept_manager"
TRUNCATE TABLE
#3

Interesting. Will have to try that with postgres sometime. I am more used to mysql and it’s variants.

However, I still seem to have a problem with cascade on the TRUNCATE option:
root@10.0.4.91:26257/employees> TRUNCATE employees cascade;
(after a bit of time)
pq: does not exist

#4

Retried the truncate with cascade command. (repeat,first gave “pq: does not exist” after a short time.)
Second has been sitting there for over an hour now.
Tried a
SELECT * from employees limit 2;
and it’s hung too.

The node the truncate was issued to is stuck at 100% cpu on one of the cores.

Looks like using cascade with truncate has some problems, or at least is not optimized any better than delete. Dropping and recreating tables provides acceptable performance.

Is there a way to tell if it’s doing anything and will likely finish, or if it’s completely hung?

#5

TRUNCATE parent CASCADE is essentially just shorthand for TRUNCATE child, parent – it just adds all dependent tables to the set to be truncated (same as pg). CASCADE itself doesn’t really have any performance considerations, just those of TRUNCATE in general.

TRUNCATE on an interleaved table is forced to do row-by-row deletions (e.g. same as DELETE FROM ...), since other tables’ rows may exist in between the rows being deleted, while a non-interleaved table can be truncated a little faster – a single truncation is sent to the storage layer for the whole table.

At the storage layer though, to maintain our MVCC records such that reads in the past can still use the table, both DELETE and TRUNCATE still need to individually update each record in the table to mark it deleted.

I don’t think there’s a way to inspect progress while the txn is running.

#6

We do have some performance problems with DROP and TRUNCATE that we’re going to address
through https://github.com/cockroachdb/cockroach/issues/2003 . This is going to be in our 1.1 release. Thanks

#7

DROP doesn’t appear to have the performance problems (in my limited testing so far), so I think that portion may have already been merged in. The above issue has pointers to other areas marked closed, along with some merges. Is good to know there are plans to improve TRUNCATE in the future, but I think they might be done with DROP (or at least it’s partially complete).

Generally speaking, I think DROP and TRUNCATE are both allowed to terminate other transactions accessing the table (return a deadlock error for example), assuming they don’t have a table level lock.

#8

Yeah DROP is certainly faster, but behind the scenes is doing more work to GC the table after the command runs. I agree that for TRUNCATE we will hold a lock on the table before running the operation.
Thanks!