Postgres COPY with ltree column error: "unsupported ltree version"

[jameshochadel]

Version

1.30.0

What happened?

I have a table with an ltree column named path. When trying to use :copyfrom to copy data into the table, I get error:

unsupported ltree version number 108

The ltree send and receive [0] functions in postgres expect the first byte to be a version number, and the only valid version number is 1. In my case, the first letter of my path is l (lowercase L), which is ASCII 108. So, the first byte of my ltree column is not being set to 1 on the wire, but rather is being set to the first byte of the contents.

[0] https://doxygen.postgresql.org/ltree__io_8c.html#a734db337a5f59ca8ea10022715958c50

Relevant log output

Database schema

CREATE TABLE organization (
	id UUID PRIMARY KEY,
	kind UUID REFERENCES org_kind(id),
	path LTREE UNIQUE NOT NULL
);

SQL queries

-- name: CopyOrgs :copyfrom
INSERT INTO organization(id, kind, path)
VALUES($1, $2, $3);

Configuration

version: "2"
servers:
  - engine: postgresql
    uri: "postgres://postgres:postgres@localhost:5432/postgres?sslmode=disable"
sql:
  - engine: "postgresql"
    queries: "sql/queries"
    schema: "sql/migrations"
    gen:
      go:
        emit_interface: true
        package: "db"
        sql_package: "pgx/v5"
        out: "internal/db"
    database:
      managed: true
    rules:
      - sqlc/db-prepare

Playground URL

No response

What operating system are you using?

macOS

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

[jameshochadel]

Quick update, configuring pgx as follows fixed the issue:

pgxconf, err := pgxpool.ParseConfig("")
if err != nil {
	return err
}
pgxconf.AfterConnect = func(ctx context.Context, conn *pgx.Conn) error {
	// Look up the OID for ltree
	var oid uint32
	if err := conn.QueryRow(ctx,
		`SELECT t.oid
FROM pg_type t
WHERE t.typname = 'ltree'`,
	).Scan(&oid); err != nil {
		return err
	}

	// Register type name -> OID
	conn.TypeMap().RegisterType(&pgtype.Type{
		Name:  "ltree",
		OID:   oid,
		Codec: pgtype.LtreeCodec{},
	})

	return nil
}
conn, err := pgxpool.NewWithConfig(ctx, pgxconf)
if err != nil {
	return err
}

I am not familiar enough with pgx to understand if this is the expected behavior or not, so I'll leave the issue open for now.