Re: [database-devel] Arrays and postgresql-simple
I have integrated Bas's type cache modifications with a more robust parser and pretty printer, which handle quoted strings and nested arrays. https://github.com/solidsnack/postgresql-simple/tree/arrays In an earlier email, Leon P. Smith raised some concerns about the types of arrays of arrays. 2012-07-30 21:00:34 UTC, leon.p.smith@gmail.com:
Now, presumably, postgresql does actually support arrays of arrays, but I'm guessing that you need to create the array of array of string type before you can actually run this query...
The reason I ask is that Bas van Dijk has done some work on adding support for arrays to postgresql-simple. And he's modified the type cache from TypeOID -> IO TypeName to become a TypeOID -> IO TypeInfo, where the TypeInfo type is defined as follows:
data NamedOid = NamedOid { typoid :: !PQ.Oid , typname :: !ByteString } deriving Show
data TypeInfo = TypeInfo { typ :: !NamedOid , typelem :: !(Maybe NamedOid) } deriving Show
I think this is a perfectly reasonable first attempt, but I don't think it's correct, because I'm pretty sure that PostgreSQL does actually support arrays of arrays if you know what you are doing. So I think that TypeInfo needs to look something more like
data TypeInfo = Plain { typ :: !NamedOid } | Array { typ :: !NamedOid , typelem :: !TypeInfo } deriving Show
The real issue here is one dealing with purity and effects: In some sense, Bas's first attempt carries the same information, but you might need to do some IO in order to retrieve it. (Though this assumes that one can query the type cache directly, which is something I should probably add anyway...) But the fromField method, which is is the most likely consumer of this information, isn't allowed to do IO. So I think we really want to change this type.
Because a Postgres array is multi-dimensional, it would stand to reason that the Haskell types [Int4], [[Int4]] and [[[Int4]]] would all map to _int4 in Postgres. The array parsing code linked to above relies on that assumption. -- Jason Dusek pgp // solidsnack // C1EBC57DC55144F35460C8DF1FD4C6C1FED18A2B
Ok, I really want to get the TypeInfo structure more or less "correct"
before we release this work.
After reviewing the documentation regarding the pg_type table, I think it
would be a mistake to "mandate" an interpretation of the table in the new
TypeInfo structure, as my suggested TypeInfo would do. Rather, I think
it would be better to represent (part of) the pg_type table faithfully,
closer to what Bas originally did. However, I do think it should be a
properly recursive type.
So my new suggestion for the TypeInfo structure is this:
data TypeInfo = TypeInfo
{ typoid :: {-# UNPACK #-} !PQ.Oid
, typcategory :: {-# UNPACK #-} !Char
, typdelim :: {-# UNPACK #-} !Char
, typname :: !ByteString
, typelem :: Maybe TypeInfo
}
Best,
Leon
On Thu, Aug 16, 2012 at 9:22 AM, Jason Dusek
I have integrated Bas's type cache modifications with a more robust parser and pretty printer, which handle quoted strings and nested arrays.
https://github.com/solidsnack/postgresql-simple/tree/arrays
In an earlier email, Leon P. Smith raised some concerns about the types of arrays of arrays.
2012-07-30 21:00:34 UTC, leon.p.smith@gmail.com:
Now, presumably, postgresql does actually support arrays of arrays, but I'm guessing that you need to create the array of array of string type before you can actually run this query...
The reason I ask is that Bas van Dijk has done some work on adding support for arrays to postgresql-simple. And he's modified the type cache from TypeOID -> IO TypeName to become a TypeOID -> IO TypeInfo, where the TypeInfo type is defined as follows:
data NamedOid = NamedOid { typoid :: !PQ.Oid , typname :: !ByteString } deriving Show
data TypeInfo = TypeInfo { typ :: !NamedOid , typelem :: !(Maybe NamedOid) } deriving Show
I think this is a perfectly reasonable first attempt, but I don't think it's correct, because I'm pretty sure that PostgreSQL does actually support arrays of arrays if you know what you are doing. So I think that TypeInfo needs to look something more like
data TypeInfo = Plain { typ :: !NamedOid } | Array { typ :: !NamedOid , typelem :: !TypeInfo } deriving Show
The real issue here is one dealing with purity and effects: In some sense, Bas's first attempt carries the same information, but you might need to do some IO in order to retrieve it. (Though this assumes that one can query the type cache directly, which is something I should probably add anyway...) But the fromField method, which is is the most likely consumer of this information, isn't allowed to do IO. So I think we really want to change this type.
Because a Postgres array is multi-dimensional, it would stand to reason that the Haskell types [Int4], [[Int4]] and [[[Int4]]] would all map to _int4 in Postgres. The array parsing code linked to above relies on that assumption.
-- Jason Dusek pgp // solidsnack // C1EBC57DC55144F35460C8DF1FD4C6C1FED18A2B
_______________________________________________ database-devel mailing list database-devel@haskell.org http://www.haskell.org/mailman/listinfo/database-devel
On Thu, 2012-08-16 at 19:18 -0400, Leon Smith wrote:
Ok, I really want to get the TypeInfo structure more or less "correct" before we release this work.
After reviewing the documentation regarding the pg_type table, I think it would be a mistake to "mandate" an interpretation of the table in the new TypeInfo structure, as my suggested TypeInfo would do. Rather, I think it would be better to represent (part of) the pg_type table faithfully, closer to what Bas originally did. However, I do think it should be a properly recursive type.
It doesn't look like TypeInfo is returned to users, so I don't see a major problem with using a more convenient representation if we find one. What am I missing?
So my new suggestion for the TypeInfo structure is this:
data TypeInfo = TypeInfo { typoid :: {-# UNPACK #-} !PQ.Oid , typcategory :: {-# UNPACK #-} !Char , typdelim :: {-# UNPACK #-} !Char , typname :: !ByteString , typelem :: Maybe TypeInfo }
We'll be omitting some information there, notably the subtypes for range types and the subtypes for composite types. I assume that you intend to add those organically as the need arises (e.g. keep adding in more parts of the PG catalogs)? Regards, Jeff Davis
participants (3)
-
Jason Dusek -
Jeff Davis -
Leon Smith