sql: support IMPORT PGDUMP into table with partial indexes

[mgartner]

Description

CockroachDB does not currently support IMPORTing into a table with partial indexes.

Workaround for IMPORTing into a table with partial indexes

1. Drop any partial indexes defined on the table.
2. Perform the IMPORT.
3. Recreate the partial indexes.

Workaround for IMPORTing a PGDUMP with partial indexes

1. Drop the partial indexes on the Postgres server.
2. Recreate the PGDUMP.
3. IMPORT the PGDUMP.
4. Add partial indexes on the CockroachDB server.

Notes

In order to support this, the DatumRowConverter will need to evaluate partial index predicate expressions with the variables replaced with each row's values. I've left a TODO here.

Jira issue: CRDB-4140
Epic CRDB-8353

[mgartner]

Currently, there is no paradigm for evaluating expressions, like partial index predicates, during an IMPORT. IMPORT doesn't currently support computed columns. It also doesn't validate the rows satisfy CHECK constraints:

root@localhost:26257/defaultdb> create table t (a int, b int, check (a > 2));
CREATE TABLE

root@localhost:26257/defaultdb> show create table t;
  table_name |              create_statement
+------------+---------------------------------------------+
  t          | CREATE TABLE t (
             |     a INT8 NULL,
             |     b INT8 NULL,
             |     FAMILY "primary" (a, b, rowid),
             |     CONSTRAINT check_a CHECK (a > 2:::INT8)
             | )
(1 row)

root@localhost:26257/defaultdb> import into t (a, b) csv data ('nodelocal://1/test.c
sv');
        job_id       |  status   | fraction_completed | rows | index_entries | bytes
+--------------------+-----------+--------------------+------+---------------+-------+
  574429359494791169 | succeeded |                  1 |    5 |             0 |   105
(1 row)

root@localhost:26257/defaultdb> select * from t;
  a  | b
+----+----+
   1 |  1
   2 |  2
   3 |  3
  11 | 11
  40 | 40
(5 rows)

Therefore, I think a suitable first step for partial indexes is to err when trying to IMPORT into a table with partial indexes. The work around would be to drop the partial index, issue the IMPORT, then re-add the partial index.

[avivimgn]

Another workaround that has worked for us is to use pg_dump --section=pre-data --section=data, this will dump the db without all indices.

[vy-ton]

fyi @rafiss another SQL incompatibility mentioned for IMPORT

[ecwall]

Workaround for IMPORTing into a table with partial indexes

This has been tested and merged in #85244, but PGDUMP has not yet been tested.

[ajwerner]

@ecwall or @rafiss is it worth making a new issue to test pgdump and close this one?

[rafiss]

we won't schedule this work, as per #93660