Multidimensional array flattened when using CopyFrom instead of Exec+Insert

[rrb3942]

Describe the bug
When using CopyFrom to insert a string value of a postgres formatted multidimensional array, the array is getting flattened. If inserted via Exec the dimensions are properly persevered.

My specific scenario is bulk loading from a csv with fields containing postgres formatted arrays into a text[][] column.

To Reproduce
Steps to reproduce the behavior:

In postgres:

 create table array_test ( array_col text[][] );

package main

import (
        "context"
        "fmt"
        "os"

        "github.com/jackc/pgx/v5"
)

func main() {
        conn, err := pgx.Connect(context.Background(), os.Getenv("DATABASE_URL"))

        if err != nil {
                fmt.Fprintf(os.Stderr, "Unable to connect to database: %v\n", err)
                os.Exit(1)
        }

        defer conn.Close(context.Background())

        multi := "{{one,two,three},{four,five,six}}"
        rows := [][]any{
                        {multi},
                }

        //will insert as {{one,two,three},{four,five,six}}
        _, err = conn.Exec(context.Background(), "insert into array_test (array_col) values ($1)", multi)

        if err != nil {
                fmt.Println(err)
                os.Exit(1)
        }

        //will insert as {one,two,three,four,five,six}
        _, err = conn.CopyFrom(context.Background(), pgx.Identifier{"array_test"}, []string{"array_col"}, pgx.CopyFromRows(rows))

        if err != nil {
                fmt.Println(err)
                os.Exit(1)
        }

        fmt.Println("insert ok")
}

Expected behavior
Array dimensions to be preserved and identical behavior to Exec+Insert.

Actual behavior
Array dimensions flattened.

Version

• Go: create table array_test ( array_col text[][] );
• PostgreSQL: PostgreSQL 17.6 (Debian 17.6-1.pgdg12+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14+deb12u1) 12.2.0, 64-bit
• pgx: v5.7.6

Additional context
psql with COPY or \COPY from the csv works as expected.

[jackc]

I have determined what the problem is, but a full solution is eluding me.

Here's what is happening. pgx's CopyFrom always uses the binary format. However, you are passing it a string. Normally, that will work. pgx determines the type that PostgreSQL needs and parses the text into that type, then encodes that in binary into the copy stream. It's a bit inefficient, but it normally works.

Unfortunately, the PostgreSQL type system does not distinguish between the dimensions of arrays. That is, PostgreSQL has a text[] type, but doesn't actually have a text[][] type. In fact, an array column can have values of varying dimensions.

Typically, when parsing a value, pgx is scanning the value into a value provided to it. e.g. a []string. However, in this path it doesn't have a value provided so it has to choose what to scan into before it looks at the value. In ArrayCodec.DecodeValue it ultimately chooses []any as the value to scan into. And pgx when scanning a multi-dimensional array into a Go slice, pgx will flatten the array rather than fail.

Potentially, the behavior of ArrayCodec.DecodeValue could be changed, but that might have unintended side effects on other uses.

---

However, for your use case in particular, there may be a much simpler solution. If you go down a layer to PgConn.CopyFrom you can directly stream your CSV directly to PostgreSQL. That should avoid the problem and have better performance as well.

[rrb3942]

I was able to do an implementation using PgConn.CopyFrom directly, thanks for the advice!

Yeah, I noticed postgresql was a little loose with array dimensions. When I created a multidim array for a column type, \d on the table only showed a single dimension. I guessed this might have been related, but wasn't sure.

Feel free to close this issue unless you want it left open to track investigating ArrayCodec.DecodeValue changes.

[jackc]

I'll leave it open for now. It is a bug. Even if one I'm not sure can be fixed.

[abrightwell]

I was poking at this one a little. It wasn't quite clear to me either where the proper location(s) might be for a fix. However, what I discovered is perhaps a step in that direction? Specifically, it seems that in CopyFrom all rows ultimately flow through encodeCopyValue which has a 'fallback' when direct binary encoding of the value fails. It is in this fallback tryScanStringCopyValueThenEncode that we can potentially mitigate this behavior (and perhaps others if they arise?).

Regardless, here is the diff of what I've been playing with:

abrightwell/pgx@master...abrightwell:pgx:abrightwell-fix-multi-dim-array-copy-from

The one piece that is challenging, perhaps, is that apparently CRDB doesn't support multi-dimensional arrays? So, this causes one of the existing tests TestConnCopyFromAutomaticStringConversionArray to fail, so I just skipped it for expedience with the intent to revisit if this seems like a reasonable solution. 🤷

[jackc]

That approach seems promising. Though the whole scan then encode path is unfortunate. I've been tempted from time to time to implement the copy protocol in text format and fallback to that if not all values could be directly encoded in binary. But I've never spent the time.

[jackc]

Just for kicks I asked Claude to add auto fallback to the text protocol for CopyFrom... and it seems to work. Not sure I'm willing to commit it, but it is an interesting proof of concept.

#2521