c/driver/postgresql: bind arrow.uuid parameters as PostgreSQL uuid

[cofin]

What happened?

adbc_driver_postgresql binds a canonical Arrow UUID parameter as PostgreSQL
bytea instead of uuid.

PyArrow 25.0.0 infers a Python uuid.UUID value as
extension<arrow.uuid> with fixed_size_binary[16] storage. The PostgreSQL
driver's PostgresType::FromSchema() recognizes arrow.json, but does not
recognize arrow.uuid; it falls through to the storage-type switch, where all
FixedSizeBinary values resolve to PostgreSQL bytea.

As a result, using the parameter against a PostgreSQL uuid column fails while
the statement is prepared:

Stack Trace

INVALID_ARGUMENT: [libpq] Failed to prepare query: ERROR:
operator does not exist: uuid = bytea
HINT: No operator matches the given name and argument types.

Expected behavior: a field carrying the canonical arrow.uuid extension name
and valid FixedSizeBinary(16) storage should bind as PostgreSQL uuid.
Unannotated FixedSizeBinary(16) should continue to bind as bytea.

How can we reproduce the bug?

import os
import uuid

import pyarrow as pa
from adbc_driver_postgresql import dbapi

value = uuid.UUID("12345678-1234-5678-1234-567812345678")
array = pa.array([value])

assert str(array.type) == "extension<arrow.uuid>"
assert str(array.storage.type) == "fixed_size_binary[16]"

with dbapi.connect(os.environ["ADBC_POSTGRESQL_TEST_URI"]) as connection:
    with connection.cursor() as cursor:
        cursor.execute("CREATE TEMP TABLE adbc_uuid_repro (id uuid)")
        cursor.execute(
            """
            INSERT INTO adbc_uuid_repro
            VALUES ('12345678-1234-5678-1234-567812345678')
            """
        )

        # Fails because $1 is prepared as bytea.
        cursor.execute(
            "SELECT count(*) FROM adbc_uuid_repro WHERE id = $1",
            (value,),
        )
        assert cursor.fetchone()[0] == 1

This reproduction uses ADBC directly; SQLSpec is not involved.

Relevant source:

arrow-adbc/c/driver/postgresql/postgres_type.h

Lines 588 to 638 in 84a56fe

	ArrowSchema* schema, PostgresType* out,
	ArrowError* error) {
	ArrowSchemaView schema_view;
	NANOARROW_RETURN_NOT_OK(ArrowSchemaViewInit(&schema_view, schema, error));
	if (schema_view.extension_name.data != nullptr &&
	std::string_view(schema_view.extension_name.data,
	schema_view.extension_name.size_bytes)
	.compare("arrow.json") == 0) {
	switch (schema_view.type) {
	case NANOARROW_TYPE_STRING:
	case NANOARROW_TYPE_LARGE_STRING:
	case NANOARROW_TYPE_STRING_VIEW:
	return resolver.Find(resolver.GetOID(PostgresTypeId::kJson), out, error);
	default:
	break;
	}
	ArrowErrorSet(
	error, "Field '%s' is of type arrow.json but storage type is not a string type",
	schema_view.schema->name);
	return EINVAL;
	}
	switch (schema_view.type) {
	case NANOARROW_TYPE_BOOL:
	return resolver.Find(resolver.GetOID(PostgresTypeId::kBool), out, error);
	case NANOARROW_TYPE_INT8:
	case NANOARROW_TYPE_UINT8:
	case NANOARROW_TYPE_INT16:
	return resolver.Find(resolver.GetOID(PostgresTypeId::kInt2), out, error);
	case NANOARROW_TYPE_UINT16:
	case NANOARROW_TYPE_INT32:
	return resolver.Find(resolver.GetOID(PostgresTypeId::kInt4), out, error);
	case NANOARROW_TYPE_UINT32:
	case NANOARROW_TYPE_INT64:
	case NANOARROW_TYPE_UINT64:
	return resolver.Find(resolver.GetOID(PostgresTypeId::kInt8), out, error);
	case NANOARROW_TYPE_HALF_FLOAT:
	case NANOARROW_TYPE_FLOAT:
	return resolver.Find(resolver.GetOID(PostgresTypeId::kFloat4), out, error);
	case NANOARROW_TYPE_DOUBLE:
	return resolver.Find(resolver.GetOID(PostgresTypeId::kFloat8), out, error);
	case NANOARROW_TYPE_STRING:
	case NANOARROW_TYPE_LARGE_STRING:
	case NANOARROW_TYPE_STRING_VIEW:
	return resolver.Find(resolver.GetOID(PostgresTypeId::kText), out, error);
	case NANOARROW_TYPE_BINARY:
	case NANOARROW_TYPE_LARGE_BINARY:
	case NANOARROW_TYPE_FIXED_SIZE_BINARY:
	case NANOARROW_TYPE_BINARY_VIEW:
	return resolver.Find(resolver.GetOID(PostgresTypeId::kBytea), out, error);

Arrow UUID specification:
https://arrow.apache.org/docs/format/CanonicalExtensions.html#uuid

Environment/Setup

• Python 3.12
• PostgreSQL 16
• adbc-driver-postgresql==1.11.0
• adbc-driver-manager==1.11.0
• pyarrow==25.0.0
• PyPI wheels

[lidavidm]

@cofin was there a reason to close this? I think it would be reasonable for the driver to recognize arrow.uuid and translate it to the uuid type instead of bytea

[cofin]

@lidavidm I felt like I needed to do more due diligence on this one since it was my first issue on this project. I agree that it would be reasonable for the driver to fix this. I'll re-open it.

[lidavidm]

CC @Mandukhai-Alimaa

[Mandukhai-Alimaa]

take