haskell postgresql-simple incompatible type _int8 and Int64 (and Integer)

The erroneous function below is part of a program called subdivide working with Postgis geospatial intersections on the server side and processing the returned array of Int64 on the client side.

It is built and run under Stack, resolving to Nightly 2016-08-02 and explicitly specifying architecture x86_64.

I get the following runtime error executing the Postgres query defined as "intersectionsSql" (see the comment RUNTIME ERROR HERE):

"Created table:  server : [Only {fromOnly = \"PostgreSQL 9.6beta2 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit\"}] quadrant: BOX3D(-180.0 90.0, -90.0 45.0)"
subdivide: Incompatible {errSQLType = "_int8", errSQLTableOid = Nothing, errSQLField = "object_ids", errHaskellType = "Int64", errMessage = "types incompatible"}

I have tried Integer, Int64 and Int, all with the same result, which is counter-intuitive as those Haskell types should all be compatible with _int8 according to the PostgreSQL-simple instance documentation:

https://hackage.haskell.org/package/postgresql-simple-0.5.0.0/candidate/docs/Database-PostgreSQL-Simple-FromField.html

The SQL query should return a single row of postgres bigint[], which I have confirmed via PGAdmin.

Any ideas?

Also any comments around how I have written the code - its over a decade since last I worked with GHC and times have changed.

Thanks for your consideration.

Mike Thomas

accumulateIntersections :: Identifier -> Identifier -> ConnectInfo -> ((Double,Double),(Double,Double)) -> IO ()
accumulateIntersections sourceTable accumulationTable connectionInfo q =
let
    theBox = makeBox3D (fst (fst q)) (snd (fst q)) (fst (snd q)) (snd (snd q))
    theValue = (Only theBox)
    dropTable = [sql| DROP TABLE IF EXISTS ? CASCADE |]
    createTable = [sql| CREATE TABLE ? ( quadrant_id BIGSERIAL, area_metres_squared FLOAT8, shape GEOMETRY, object_ids BIGINT[] ) |]
    aggregateSql = [sql| DROP AGGREGATE IF EXISTS _array_agg (anyarray);
                         CREATE AGGREGATE _array_agg(anyarray) (SFUNC = array_cat, STYPE = anyarray);
                        |]
    intersectionsSql = [sql| SELECT _array_agg (object_ids) object_ids
                             FROM ?
                             WHERE ST_Intersects(ST_SetSRID ( ?::box3d, 4326 ), shape)
                          |]
    insertIntersections = [sql| INSERT INTO ? (shape, object_ids)
                                VALUES ( ST_SetSRID ( ?::box3d, 4326 )
                                        , ? ) |]
in
do
  connection <- connect connectionInfo
  execute_ connection aggregateSql
  postgresVersion <- (query_ connection "SELECT version()" :: IO [Only String])
  i0 <- execute connection dropTable (Only accumulationTable)
  i1 <- execute connection createTable (Only accumulationTable)
  print ("Created table:  server : " ++ (show postgresVersion) ++ " quadrant: " ++ theBox)
  is :: [Only Int64] <- query connection intersectionsSql (sourceTable, theBox) -- RUNTIME ERROR HERE
  print ("Intersections done.")
  ids::[Int64] <- forM is (\(Only id) -> return id)
  print ("Ids done.")
  close connection
  return ()

See the above comment relayed from LP Smith, who I contacted when no answers were forthcoming here. It resolves my issue.

The key was to recognize that _int8 represents an array of 8 byte integers, rather than thinking, as I had done, that it was an internal representation for a single 8 byte integer. Leon's suggested change was to substitute "[Only (Vector Int64)]" for "[Only Int64]" in the line marked above as the point of the runtime error.

Thank you Leon.