go-postgis
go-postgis copied to clipboard
cridenour
Does not seem to work with pgx driver
The insert point/ewkb query works fine with lib/pq but not the pgx which is the go to driver for postgresql nowadays.
The error comes from the decode function: interface conversion: interface {} is string, not []uint8 [recovered]
Most likely from this line ewkb, err := hex.DecodeString(string(value.([]byte)))
It seemed to me that the value is already a string and therefore failed the type assertion.
Hey thanks for the report. Honestly it's a miracle this code (almost 9 years old!) has survived this long. A strong testament to Go.
I haven't written Go in over 5 years - but I can't imagine why pgx would return a string if you're asking for geometry bytes. What query are you running?
Thank you for the reply. This code is still very solid. It is actually recommended by ChatGPT as one of the best Go library for handling Geometry from Postgis.
Go is getting very popular these days. Much more popular than 9 years ago I imagine. I didn't know the language until last year but now I use it as my main backend language. Go pioneer like you should come back to Go world sometime :)
Back to the topic, the query is just a simple "SELECT name, location FROM waypoint LIMIT 1". The location is a point geometry. I was merely testing if pgx works for me or not.
I pulled the code and tested it. Here is the original decode function:
// Since Postgres by default returns hex encoded strings we need to first get bytes back, only used from Scan
func decode(value interface{}) (io.Reader, error) {
ewkb, err := hex.DecodeString(string(value.([]byte)))
if err != nil {
return nil, err
}
return bytes.NewReader(ewkb), nil
}
The code works perfectly with lib/pq. However, the assumption is broken with pgx. The value is no longer a hex encoded string, []byte, but a string so the type assertion value.([]byte) will fail.
I wanted to know why there is such a difference so I asked ChatGPT, I got back:
When you query for a geometry field from a PostGIS-enabled PostgreSQL database, the database server returns a binary representation of the geometry data.
lib/pq returns this data as a []byte slice (which is equivalent to []uint8 in Go), because it does not provide a custom decoder for geometry types. This is the raw, undecoded value directly from the database.
On the other hand, pgx handles these types differently. It includes built-in support for a wide variety of PostgreSQL types, including PostGIS types. For geometry types, pgx automatically decodes the binary data from the database into its textual EWKB (Extended Well-Known Binary) representation, which is a string.
Not sure the truthiness of the story but it makes sense.
Eventually, I come up a fix that works for both pgx and lib/pq:
func decode(value interface{}) (io.Reader, error) {
var ewkb []byte
var err error
switch v := value.(type) {
case string:
// For pgx, decode the hex-encoded string into bytes
ewkb, err = hex.DecodeString(v)
if err != nil {
return nil, err
}
case []byte:
// For lib/pq, cast it to string and decode the hex-encoded string into bytes
ewkb, err = hex.DecodeString(string(v))
if err != nil {
return nil, err
}
default:
return nil, fmt.Errorf("unsupported type: %T", value)
}
return bytes.NewReader(ewkb), nil
}
I have just created a pull request with the fix. Would you like to review it? @cridenour
Had almost the same problem, pgx returned "connection busy" error. After many hours of debugging I realized that the problem was in scanning PointS type from row. Applied the fix of the decode function, now everything just works.
This should be fixed now that #9 was merged. Thanks for confirming the fix @CyberTea0X and huge thanks to @mylaluna for the issue and PR.