cli/dump: cockroach dump produces unrestorable output for CREATE TABLE statements with interleaved indices

[rolandcrosby]

Interleaved indices can't be added as part of a CREATE TABLE statement; this is a known limitation tracked by #9148. However, cockroach dump is unaware of this limitation: if you dump a table with an interleaved index, the index is emitted inline in the CREATE TABLE. This produces dump files that can't be restored without manual editing.

In the past we've made changes to ShowCreateTable and the crdb_internal table to produce usable CREATE TABLE statements in dumps (see 65c0bfb). To solve this issue we either need to fix the root cause (#9148) or make another change to the dump logic to add interleaved indices after table creation instead of in the CREATE TABLE.

Relates to #28948 (master issue of cockroach dump inadequacies).

The manual edit you have to make: change

CREATE TABLE t (
  somecolumndef,
  somecolumndef,
  somecolumndef,
  INDEX i (somecolumndef, somecolumndef) INTERLEAVE IN PARENT t (somecolumndef)
);

into

CREATE TABLE t (
  somecolumndef,
  somecolumndef,
  somecolumndef
);
CREATE INDEX i (somecolumndef, somecolumndef) INTERLEAVE IN PARENT t (somecolumndef);

Full repro on a 19.1 build:

Repro steps

root@:26257/test> create table t1 (a int, b int, primary key (a));
CREATE TABLE

Time: 17.7833ms

root@:26257/test> create index b_idx on t1(a, b) interleave in parent t1 (a);
CREATE INDEX

Time: 191.1104ms

root@:26257/test> show create table t1;
  table_name |                      create_statement
+------------+-------------------------------------------------------------+
  t1         | CREATE TABLE t1 (
             |     a INT8 NOT NULL,
             |     b INT8 NULL,
             |     CONSTRAINT "primary" PRIMARY KEY (a ASC),
             |     INDEX b_idx (a ASC, b ASC) INTERLEAVE IN PARENT t1 (a),
             |     FAMILY "primary" (a, b)
             | )
(1 row)

Time: 4.2069ms

root@:26257/test> \q
PS C:\Users\roland\Desktop> cockroach-20190211.exe dump --insecure test
CREATE TABLE t1 (
        a INT8 NOT NULL,
        b INT8 NULL,
        CONSTRAINT "primary" PRIMARY KEY (a ASC),
        INDEX b_idx (a ASC, b ASC) INTERLEAVE IN PARENT t1 (a),
        FAMILY "primary" (a, b)
);
PS C:\Users\roland\Desktop> cockroach-20190211.exe sql --insecure
# Welcome to the cockroach SQL interface.
# All statements must be terminated by a semicolon.
# To exit: CTRL + D.
#
# Server version: CockroachDB CCL v2.2.0-alpha.20190211 (x86_64-w64-mingw32, built 2019/02/07 23:54:03, go1.11.4) (same version as client)
# Cluster ID: cf81eacb-45cd-4cfe-8113-6b1ea1f2f744
#
# Enter \? for a brief introduction.
#
root@:26257/defaultdb> create database test2; use test2;
SET

Time: 16.5387ms

root@:26257/test2> CREATE TABLE t1 (
                ->         a INT8 NOT NULL,
                ->         b INT8 NULL,
                ->         CONSTRAINT "primary" PRIMARY KEY (a ASC),
                ->         INDEX b_idx (a ASC, b ASC) INTERLEAVE IN PARENT t1 (a),
                ->         FAMILY "primary" (a, b)
                -> );
pq: unimplemented: use CREATE INDEX to make interleaved indexes
HINT: See: https://github.com/cockroachdb/cockroach/issues/9148
root@:26257/test2>

[tim-o]

Zendesk ticket #3068 has been linked to this issue.

[jdoupe]

This issue is referenced from the documentation as a workaround for fixing interleaved tables. However, this only applies to interleaved indices.

Is there a workaround or alternate solution for interleaved tables?

(I should clarify that the problem exists with import, not necessarily with cockroach sql < dump.sql)

[kenliu]

@dt would this be a good one for @tyler314 to pick up next?

[dt]

I think @jordanlewis is already working on this in #38317 ?

[jordanlewis]

I was, but I don't have bandwidth to finish that work right now. It needs more testing still. If @tyler314 wants to pick this up, I'm completely okay with that.

[dt]

@tyleroberts I think we can move all the index creation statements to the end like @mjibson suggested -- for IMPORT I think it won't matter and for regular insert, I think it might be faster than making every INSERT do index writes.