sql: COPY CSV doesn't support hex encoding for bytea

[steven-sheehy]

Describe the problem

Following up on #68804, CockroachDB does not support hex encoded byte array data when attempting to COPY FROM STDIN WITH CSV. As part of #68804, octal/escape encoded byte arrays were fixed which provides a valid workaround, but escape encoding is incredibly inefficient in the amount of data sent over the wire. This can greatly reduce ingest performance if the frequency or size of the bytea columns are large.

There was some discussion about how hex encoding isn't supported due to the SQL layer not supporting the PostgreSQL byte array literals syntax due to #26128. I would like to point out that IMPORT INTO CSV DATA already does support the \x hex encoded format for CSVs. Also, since COPY doesn't support the x'abc' or the \xabc syntax it doesn't have the difficult problem having to support both or not breaking compatibility like the SQL layer.

The fact that other areas of the system already support \x syntax suggests that COPY could be also be enhanced to do the necessary translation from \x to the format that the SQL layer supports.

To Reproduce

cat t.csv
5,"\x6869"
6,\x6869

> create table t(a int primary key, b bytea);
> set bytea_output = 'escape';
> COPY t FROM STDIN WITH CSV;
> 1,X'6869'
> 2,x'6869'
> 3,"\x6869"
> 4,\x6869
> \.
> IMPORT INTO t (a, b) CSV DATA ('http://localhost:3000/t.csv');
> select * from t where b = decode(encode('hi', 'hex'), 'hex');
  a | b
----+-----
  5 | hi
  6 | hi
(2 rows)
> select * from t;
 a |     b
---+------------
 1 | X'6869'
 2 | x'6869'
 3 | \\x6869
 4 | \\x6869
 5 | hi
 6 | hi
(6 rows)

Only the rows that came from IMPORT are inserted with the correct bytea data.

Expected behavior

Hex encoded bytea data supported via COPY CSV.

Additional data / screenshots

If applicable, add screenshots to help explain your problem.

Environment:

• CockroachDB version 21.1.8
• Server OS: MacOS
• Client app: psql

Additional context

Unable to use existing dumps that have hex encoding. Reduced ingest performance due to less efficient octal encoding.

Jira issue: CRDB-9700

[blathers-crl]

Hello, I am Blathers. I am here to help you get the issue triaged.

Hoot - a bug! Though bugs are the bane of my existence, rest assured the wretched thing will get the best of care here.

I have CC'd a few people who may be able to assist you:

• @rafiss (assigned to COPY CSV doesn't support bytea column #68804)
• @cockroachdb/bulkio (found keywords: IMPORT)
• @knz (author of sql: CockroachDB's extended literal formats are not compatible with postgres #26128)
• @bdarnell (commented on sql: CockroachDB's extended literal formats are not compatible with postgres #26128)
• @nvanbenschoten (commented on sql: CockroachDB's extended literal formats are not compatible with postgres #26128)
• @jordanlewis (commented on sql: CockroachDB's extended literal formats are not compatible with postgres #26128)

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

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

[rafiss]

Thanks for typing up this issue @steven-sheehy! I'll post back here once we decide how to handle this