import: Issue tracking PGDUMP improvements

[adityamaru]

High Priority

• Support user defined schema / Schema manipulation statements
  importccl: IMPORT PGDUMP should ignore schema manipulation statements #29471
  Fix: importccl,sql: support importing non-public schemas from pgdump #57183
• PARTIAL INDEXES
  sql: support IMPORT PGDUMP into table with partial indexes #50225
• CREATE SEQUENCE AS integer: failed since we do not support AS
  importccl: IMPORT PGDUMP fails with sequences #29046
  Fix: sql: Added CREATE SEQUENCE ... AS <typename> option. #57339
• Array data
  importccl: cannot load array data with IMPORT PGDUMP #29043
  Fix: importccl: Fixed COPY FROM STDIN compatibility for IMPORT pgdump array data #58244
• Views
  importccl: IMPORT PGDUMP does not import views #29039

Medium Priority

• DROP TABLE [IF EXISTS] importccl: support DROP TABLE [IF EXISTS] for PGDUMP #53112
  Fix: importccl: Add DROP TABLE [IF EXISTS] support for import pgdump. #56920
• CREATE and ALTER SEQUENCE
  import: PGDUMP imports do not suport ALTER SEQUENCE ... OWNED BY ... #53958
  importccl: CREATE and ALTER SEQUENCE commands not support for IMPORT PGDUMP #55469
  import: postgres-compat ALTER SEQUENCE ... OWNED BY #56767

Low Priority

• Citext columns

To Triage

• DROP DATABASE [IF EXISTS]. No issue created yet. TLDR: pgdump --clean and --create flags allow users to prepend DROP DATABASE IF EXISTS and CREATE DATABASE to the dump file.

Not solving - other workaround

• Stored procedures
• Auto Vacuum import: pgdump fails on unimplemented storage parameter autovacuum_analyze_scale_factor #56364
• GRANT ALL ON SCHEMA
  importccl: IMPORT PGDUMP fails with syntax error on GRANT ALL ON SCHEMA #30647
  import: failure on handling grant #56369
• Deferred FK
• ALTER DEFAULT PRIVILEGES

Jira issue: CRDB-2917

[blathers-crl]

Hi @adityamaru, I've guessed the C-ategory of your issue and suitably labeled it. Please re-label if inaccurate.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is otan.}

[mokaixu]

NB: Work for CREATE VIEW #29039 has been put on on hold due to design limitations. CREATE VIEW needs to be able to support arbitrary SQL queries and therefore the code at import should be able to understand all of SQL (this logic exists in the sql optbuilder).

Can circle back on this when discussions related to separating sql logic for ddls and import logic for the ingestion of data is addressed.

[mwang1026]

Any thoughts on varchar_pattern_ops? From what I can tell it allows creation of indexes that allow LIKE or POSIX string comparisons. Is this something where we would just skip the creation of that index?

[adityamaru]

nextval support added in - #56473

[adityamaru]

Work is also being done to improve the ignore semantics of unsupported statements during an IMPORT PGDUMP.
The current approach is to introduce an option ignore_unsupported which will ignore all unparseable and unsupported SQL statements seen during an IMPORT PGDUMP. We also plan to expose a ignored_stmt_log_destination (name subject to change) which will allow the user to point to where they would like us to write the ignored constructs.

#57827

[adityamaru]

TODO: go through all our example customer pgdump files and add ignore rules for unsupported statements.

[adityamaru]

IMPORT has moved over to @cockroachdb/sql-foundations, considering most items on this list were completed I'm inclined to close this. Future improvements can be tracked more granularly.