Running :copyfrom with a uuid causes ERROR: incorrect binary data format (SQLSTATE 22P03) with PGX

[austincollinpena]

Version

1.12.0

What happened?

A bug happened!

Relevant log output

ERROR: incorrect binary data format (SQLSTATE 22P03)

Database schema

CREATE TABLE IF NOT EXISTS ad_check_results
(
    ad_check_results_id   uuid      DEFAULT gen_random_uuid(),
    query                 varchar(150) NOT NULL CHECK (query != ''),
    executed_at           TIMESTAMP DEFAULT now(),
    location              varchar(150) NOT NULL CHECK (location != ''),
    location_type         varchar(150),
    is_top                boolean      NOT NULL,
    position              int          NOT NULL,
    number_of_top_results int,
    total_number_of_ads   int,
    burst_run_number      int,
    headline              varchar(200) NOT NULL,
    description           varchar(300) NOT NULL,
    url                   TEXT,
    PRIMARY KEY (ad_check_results_id),
    ad_check_parent_id    uuid,
    CONSTRAINT fk_ad_check FOREIGN KEY (ad_check_parent_id) REFERENCES ad_check (ad_check_id) ON DELETE SET NULL
)

SQL queries

-- name: BulkInsertAdResults :copyfrom
INSERT INTO ad_check_results (query, location, location_type, is_top, position,
                              number_of_top_results, total_number_of_ads, headline, description, url,
                              ad_check_parent_id)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11);

Configuration

version: 1
packages:
  - name: db_access
    path: "ad_intelligence_project/db_access"
    queries: ["./ad_intelligence_project/biz_logic/organization/org-sql", "./ad_intelligence_project/biz_logic/ad_intelligence/sql"]
    schema: "./go_common/db/a_db/migrations"
    sql_package: pgx/v4
    emit_json_tags: true
overrides:
  - column: "ad_check.country_to_check"
    go_type: "database/sql.NullString"
  - column: "ad_check.utc_times_of_checks"
    go_type: "github.com/lib/pq.StringArray"

Playground URL

https://play.sqlc.dev/p/2d26c7887908446358ff23c10defa67e2a2d3a7c4f34e2b1a0e686560420659e

What operating system are you using?

Windows

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

[austincollinpena]

Updating my code from:

func (r iteratorForBulkInsertAdResults) Values() ([]interface{}, error) {
	return []interface{}{
		r.rows[0].Query,
		r.rows[0].Location,
		r.rows[0].LocationType,
		r.rows[0].IsTop,
		r.rows[0].Position,
		r.rows[0].NumberOfTopResults,
		r.rows[0].TotalNumberOfAds,
		r.rows[0].Headline,
		r.rows[0].Description,
		r.rows[0].Url,
		r.rows[0].AdCheckParentID,
	}, nil
}

to:

func (r iteratorForBulkInsertAdResults) Values() ([]interface{}, error) {
	asBinary, err := r.rows[0].AdCheckParentID.MarshalBinary()
	if err != nil{
		return nil, err
	}
	return []interface{}{
		r.rows[0].Query,
		r.rows[0].Location,
		r.rows[0].LocationType,
		r.rows[0].IsTop,
		r.rows[0].Position,
		r.rows[0].NumberOfTopResults,
		r.rows[0].TotalNumberOfAds,
		r.rows[0].Headline,
		r.rows[0].Description,
		r.rows[0].Url,
		asBinary,
	}, nil
}

Worked :)

[CNLHC]

I have encountered this problem too. I do not think this is a bug. But it is nice to write some documents to warn people who want to COPY UUID type.

The reason for this problem is neither google/uuid nor gofrs/uuid implements the BinaryEncoder interface (link) which needs by the pgx to encode your data during COPY.

A quick solution is to add overrides settings to sqlc.yaml to force using UUID type from github.com/jackc/pgtype

For example

version: "1"
project:
    id: ""
packages: 
  - path: "***"
    name: "document"
    engine: "postgresql"
    schema: "***"
    sql_package: "pgx/v4"
    queries: "***"
    overrides:
    - go_type: "github.com/jackc/pgtype.UUID"
      db_type: "uuid"
    - go_type: "github.com/jackc/pgtype.UUID"
      db_type: "uuid"
      nullable: true

[sudotliu]

I'm running into this too with the following settings:

• go version go1.19.4 darwin/amd64
• sqlc version v1.17.0
• pgx v4
• query type :copyfrom
• UUID type "github.com/google/uuid"

I noticed an extra detail here which is that the failure only happens for me when a NullUUID is present in the copyfrom query as one of the data rows to insert. Normal UUID behaves fine.

This is consistent with the OP's case it seems since ad_check_parent_id is also nullable there based on the posted schema and that's why the manual intervention hack of adding MarshalBinary() explicitly works.

Is there a better workaround here than overriding with the pgtype as suggested? We have far too many locations where we've been using Google's UUID to make this an easy swap by doing so and that just feels way too heavy-handed as a workaround. We can't afford to wait for pgx v5 to settle either (not to mention the cost of switching over to that).

Is the next best option to change the copyfrom query to a transacted loop of single inserts? I spent some time trying to get a custom type to work but it seemed to suffer the same problem which makes sense since it still doesn't really affect how the copyfrom code is being generated or used.

[kyleconroy]

I believe this has been fixed in pgx/v5. Could you upgrade to v5 and let me know if you're still seeing the issue?

[sudotliu]

@kyleconroy , got some time to work on the upgrade today and this does appear to be fixed now in v5, thank you very much sir!

For anyone else migrating from v4, these were super helpful resources:

• https://brandur.org/fragments/pgx-v5-sqlc-upgrade
• Migrate logger fron v4 to v5 jackc/pgx#1381