Conversion fails with "Failed reading: satisfyWith" for arrays with lower bound < 1

[sophie-h]

PostgreSQL arrays can have a lower bound different from 1 http://www.postgresql.org/docs/9.4/static/arrays.html. Such arrays are also returned by some fields in the pg system catalogs.

Example to produce such an array:

SELECT '[0:1]={"a","b"}'::varchar[]

The conversion fails with

ConversionFailed {
 errSQLType = "_varchar",
 errSQLTableOid = Nothing,
 errSQLField = "varchar",
 errHaskellType = "PGArray [Char]",
 errMessage = "Failed reading: satisfyWith"
}

The problem can be worked around in resetting the index in the select statement with something like

SELECT ARRAY(SELECT UNNEST('[0:1]={"a","b"}'::varchar[]))

Would be nice to have this bug fixed anyways.

[lpsmith]

The conversion doesn't handle ranges outside the default range at all, whether it's less than one or greater than one. This was briefly mentioned, though not really discussed, in the original pull request for arrays, #33.

So yes, this is something that would be nice to fix, but I myself don't use arrays much, and I haven't used non-default index ranges at all. I guess, the main question is how to handle them. If we want to just ignore the index range altogether, that's pretty easy... but I don't think we want to do that. Unfortunately, dealing with an index range is a little bit problematic, because the existing types associated with pg arrays (namely Vector and PGArray) don't have any way of representing the index range.

[sophie-h]

I am not sure what a good solution would be since I don't know what people use this (insane) lower bound feature for. Some PostgreSQL people seem to regret that this non-standard feature has been introduced, but it will not go away. For me, it sounds like a reasonable default to throw away the PostgreSQL lower bounds. Then, PGArray a would be the type to convert to a usual list type. Who ever needs it, might call for a PGArrayWithLowerBound a. Personally, I am also happy to get a reasonable error message (like "unsupported") to know what I have to work around instead of "Failed reading: satisfyWith".

However, not supporting other lower bounds as default might bite people at runtime. Maybe out there is a library or a developer that likes inserting arrays with 0 lower bound into array columns and postgresql-simple clients suddenly will start to fail retrieving data from this database.

[lpsmith]

I'm not sure whether or not the feature is insane; after all Data.Array has the same feature. Probably the thing to do is to add a new datatype that reflects the index range, such as perhaps data PGArray' ix a = PGArray' ix ix [a], then parse the index range into that. Probably the thing to do for the existing instances is to ignore the index range altogether.

As for indexing, there's already a mismatch between Haskell and PG, as lists and vectors start with 0, and the only kind of pg array we support starts with 1.