lpsmith / postgresql-simple Goto Github PK

The LargeObjects, Notifications, and Ok modules need some documentation.

There are many postgres string types (I'm dealing with the cidr and uuid types right now) with no Result instance in postgresql-simple, and defining one is tedious.

One solution is to extend okText to include them, so that the user can just read them as String, ByteString, or Text.

Or, if you export the compat, mkCompats, and doConvert functions (and make them more user friendly), it would be a lot easier to define one's own instance of Result for types like cidr and uuid.

For projects with multiple queries mapping to the same types, having to get the columns in perfect order is not manageable. It'd be much better if I could do field "id" and field "name" and not care about the ordering.

After conversations with @basvandijk and @msieradzki I do believe some modifications to postgresql-simple are in order that would better support compound types such as arrays and the upcoming range types available in PostgreSQL 9.2.

Currently, postgresql-simple makes available all typnames, even if the type does not appear in the BuiltinTypes module. Every time postgresql-simple runs across a type oid that it hasn't seen before, it queries the metaschema for the typname and then stores that typname in a per-connection cache for later use. The code that does this is available here. This information is then made available to FromField instances via the Database.PostgreSQL.Simple.FromField.typename operator.

Now, approximately speaking, most PostgreSQL types have kind *, whereas arrays and ranges have kind * -> *. PostgreSQL does provide type Oids for different array instantiations of kind *, but it would be nice to modify this functionality to make more information about the type; namely, whether the type is a plain type, an array, or a range, and what the component type is in the case of array and range types.

Bas van Dijk proposed the following query to fetch all array types and their component types:

SELECT typname, (SELECT typname FROM pg_type WHERE oid = P.typelem) AS typelem 
  FROM pg_type AS P WHERE typelem <> 0 AND typlen = -1;

Certainly, the metaschema needs to be thoroughly investigated, especially for possible differences between PostgreSQL versions.

Currently I'm using a custom data type to extract the value of a query using the postgres COUNT function:

data ContactCount = ContactCount                                                                                                                                                                                    
    {count :: Integer} deriving (Show) 

instance FromRow ContactCount where                                                                                                                                                                                 
    fromRow = ContactCount <$> field  

countUnread :: Connection -> IO Integer                                                                                                                                                                             
countUnread conn = do                                                                                                                                                                                               
    matches <- query_ conn countUnreadQ :: IO [ContactCount]                                                                                                                                                        
    let (ContactCount {count = c}) = head matches                                                                                                                                                                   
    return c

countUnreadQ :: Query                                                                                                                                                                                               
countUnreadQ = "SELECT COUNT(id) FROM contacts WHERE read = FALSE"

I was wondering if there was a easier way to do this or maybe if that is something that is planned to be implemented?

(I'm new to Haskell and this library, so sorry for the ugly code)

With version 0.4.0.1, I get the following compilation error:

src/Database/PostgreSQL/Simple/FromField.hs:479:0:
     error: invalid preprocessing directive #elsif

I'm a bit stumped on what's going on in the CPP world, it seems like some processors accept both elif and elsif, and some don't.

hi,

this goes a way into query generation, so if this is out of scope for postgresql-simple, just tell me.

i have to call sql functions with a variable number of arguments, so i needed something along the lines of the following.

This usage

query conn "SELECT my_function(?);" (Only $ FunctionArguments [FunctionArgument True] [NamedFunctionArgument "key" 4])

will generate the following query.

SELECT my_function(True, "key" := 4);

the implementation is not pretty (ExistentialQuantification) but works:

data NamedFunctionArgument = forall a. ToField a => NamedFunctionArgument Identifier a
  deriving (Typeable)

instance ToField NamedFunctionArgument where
    toField (NamedFunctionArgument ident a) =
        Many [toField ident, Plain (fromByteString " := "), toField a]

data FunctionArgument = forall a. ToField a => FunctionArgument a
  deriving (Typeable)

instance ToField FunctionArgument where
    toField (FunctionArgument a) = toField a

data  FunctionArguments = FunctionArguments [FunctionArgument] [NamedFunctionArgument]
  deriving (Typeable)

instance ToField FunctionArguments where
    toField (FunctionArguments pos named) =
        Many . intersperse (Plain (fromByteString ", ")) $
            map toField pos ++ map toField named

I have a javascript wysiwyg editor which outputs HTML when styling text:

<p>Hello, <strong>how are</strong> you?</p>

And it seems HTML special characters are converted to HTML entities after being inserted into the database:

<p>Hello, <strong>how are</strong> you?</p>

Is there a way to be able unescape the data when I retrieve or save it without escaping it?

I have been looking around but have only found the function decodeHtml in Web.Encodings to be able to unescape it, but that package is obsolete now.

Maybe the only way is to roll my own function which uses lookupEntity in Text.HTML.TagSoup.Entity?

postgresql-simple uses attoparsec's "double" parser which prioritises speed over complete accuracy as documented:

http://hackage.haskell.org/package/attoparsec-0.11.2.1/docs/Data-Attoparsec-ByteString-Char8.html#v:double

...it'd be worth highlighting this in the postgresql-simple docs - or even better, switch to the safer attoparsec 'rational' parser and reserve the unsafe parser for, say, a PGFastDouble newtype wrapper or something.

The justification for this is that users may well expect:

forall x :: Double,   readFromPG( writeToPG (x) ) == x

... which doesn't hold because of the underlying parser.

In particular, include a "canonical" example of how to write a FromField instance.

Here's my table:

CREATE TABLE event (
      event_id uuid PRIMARY KEY NOT NULL
    , event_timestamp timestamp
    , event json
  );

At the moment I've got a type like this:

data Event e = Event {
     event_id        :: UUID
   , event_timestamp :: UTCTime -- This is the problem
   , event           :: e
   } deriving (Show)

instance FromField UUID where
  fromField f mdata =
    if typeOid f /= builtin2oid UUID
        then returnError Incompatible f ""
        else case fromString . C8.unpack <$> mdata of
               Nothing  -> returnError UnexpectedNull f ""
               Just dat -> case dat of
                    Nothing -> returnError ConversionFailed f "Couldn't convert to UUID"
                    Just x -> return x

instance FromJSON a => FromRow (Event a) where
  fromRow = Event <$> field --(fromJust . fromByteString <$> field)
                  <*> field
                  <*> (fromJust . decode <$> field)

instance ToJSON a => ToRow (Event a) where
  toRow Event {..} = [
                       toField (toString event_id)
                     , toField event_timestamp
                     , toField $ encode event
                     ]

I tried changing the UTCTime to a UTCTimestamp

I managed to get as far as inserting a row:

select * from event;
               event_id               |      event_timestamp       |     event      
--------------------------------------+----------------------------+----------------
 df2c7eb5-39d2-4dc3-b1c7-2ac9f8884330 | 2013-08-21 06:02:02.131355 | {"name":"Ben"}

However selecting with:

selectEvents :: Connection -> IO [Event NameEvent]
selectEvents con = query_ con "SELECT * FROM event"

gives:

 Exception: Incompatible {errSQLType = "timestamp", errSQLTableOid = Just (Oid 24594), errSQLField = "event_timestamp", errHaskellType = "UTCTime", errMessage = ""}

or when I use UTCTimestamp:

Exception: Incompatible {errSQLType = "timestamp", errSQLTableOid = Just (Oid 24594), errSQLField = "event_timestamp", errHaskellType = "UTCTimestamp", errMessage = ""}

Any idea what I might be doing wrong here?

BTW any insight into getting UUID to work with ByteStrings would also be appreciated - I'm sure a round trip through String can't be a good thing!

http://hackage.haskell.org/packages/archive/postgresql-simple/0.3.7.1/doc/html/Database-PostgreSQL-Simple-FromField.html

It fails for a few reasons, ConversionError seems to have been renamed to ConversionFailed, and it looks like it's missing a Word8->Char conversion (I wasn't confident enough in how this works to try to fix it myself).

A database cursor is probably a better way to implement the fold function than what is currently being used. Doing so would solve all the current issues hinted at in the commit notes for bce0d38. It is also the method that Takusen uses to implement it's database queries.

If the user-supplied function throws an exception, a straightforward cursor-based implementation of fold would avoid leaving libpq in an unusable state for that connection, although we should still close the cursor when this happens to free up resources on the database server.

Consider the following query:
table <- query "SELECT * FROM ?.?" (schema_name :: Maybe BS.ByteString, table_name :: Maybe BS.ByteString)

This will be rendered as (assuming we pass in something like public and orders):
SELECT * FROM 'public'.'orders'
which is not valid. However, SELECT * FROM "public"."orders" is valid. I'm not quite sure what to do about this, as a Query is purposefully difficult to construct out of string fragments. I've managed to work around it by changing the 'inQuotes' function in ToField.hs, but thats not really ideal. I'm still pretty new to Haskell and any help regarding this would be useful.

I think it would be a good idea to add FromField and ToField instances for Bas's scientific package.

Possibly related to #39, it appears as though query generation is quadratic when it involves escaping via libpq's escapeStringConn command. Here's a simple test case that reproduces the behavior:

{-# LANGUAGE OverloadedStrings, BangPatterns #-}

import Database.PostgreSQL.Simple
import System.Environment

main = do
   (nstr:_) <- getArgs
   let n = read nstr :: Int
   c <- connectPostgreSQL ""
   !q <- formatMany c "insert into foo values (?,?)" [ (i,show i) | i <- [1..n] ]
   close c

Note that this isn't a problem if you replace show i by say, i^2 or whatnot.

• pull in sopvop's enhancements to the SqlError type
• pull in jochu's enhancements to the ResultError type
• pull in the work regarding array support, courtesy of Jason Dusek, Bas van Dijk, and Joey Adams
• evaluate mightybyte's alternative interface
• add support for range types
• overhaul/deprecate the BuiltinTypes module

Am I missing anything?

Currently, I see no way of retrieving the names of the columns of the data that a query returns. This could be very useful at times. I would like something like this:

type Header = String
query :: (ToRow q, FromRow r) =>
    Connection -> Query -> q -> IO ([Header], [r])

Or am I missing something? Is there an easy way to accomplish this?

GHC 7.4.2 shipped with bytestring 0.9. Having a constraint of >= 0.10 excludes the possibility of easily/safely compiling postgresql-simple. Would it be possible to relax the lower bound?

A lot of times in basic webapps, you want to create a thing, then redirect to a page that displays the id of the created thing. Lots of database libraries (in other languages) return the id created from an insert function. Would this be possible to do in postgresql-simple?

I might just be missing an obvious way to do it to be fair.

an example:

createPost connection = do
  rawPost <- grabPost
  newId <- liftIO $ savePost connection -- which calls execute, which doesn't return the id created, so we can't do this as of the current API
  redirect_to "/posts/%s" `format` newId

Does postgresql-simple support working with ranges?

In particular, I am interested in using daterange columns but cannot figure out what the representing types should be. An example would be most helpful.

Thanks a lot in advance!

Do you think it would be a good idea to change ConnectInfo to work with Maybe values, and get rid of the special treatment of "" and negative numbers in postgreSQLConnectionString? I'm asking, because I'm not sure if I like the code for yesodweb/persistent#38.

Would it be possible to change fold and friends to accept a MonadIO instead of specifically IO?

Thank you

The timezones CDT and EST are both -5 offsets from UTC. When serializing ZonedTimes timezones as an offset, the original timezone is lost. It looks like this is what's being done in postgresql-simple:

timeZoneToBuilder :: TimeZone -> Builder

It looks like deserialization has the same problem.

This has some practical problems. For example:
I'm currently in CDT (daylight savings) so when I store a ZonedTime of the current time and then pull it back out of postgresql-simple, it's been shifted by an hour.

I'm also making the assumption that PGSQL actually stores the full timezone info (it looks like it does in the docs):
http://www.postgresql.org/docs/9.2/static/datatype-datetime.html

Our local postgres expert tells me that you cannot count on the mapping from oid to type to be consistent across different postgres databases, and that you should instead query the catalog to know what the mapping is.

I have observed on my machine that the "inet" type has oid 1015, but postgresql-simple expects it to be 869.

Though, it will break api and maybe some library clients, but I think it can be valuable addition. Maybe this can be done in next major version of postgresql-simple.

libgsql docs show what you can actually get different parts of error message from postgres, using PQresultErrorField function from libpq, instead of concatenated errors from connection object.
So, if SqlError gets additional fields like sqlErrorDetail, sqlErrorHint and then

sqlErrorMsg <- PG_DIAG_MESSAGE_PRIMARY
sqlErrorDetal <- PG_DIAG_MESSAGE_DETAIL
sqlErrorHint <- PG_DIAG_MESSAGE_HINT

For example HINT is often used to communicate error detail from database stored functions to client in some kind of parseable format, like with foreign key violation the default trigger sets hint to "user_id=(1337)". AFAIK it is not subject to localization.
Also user written functions may print something relevant there.

I'm willing to implement this :)

Edit: libpq bindings used by posgresql-simple proved all required functionality.
Edit2: And think you know that :)

ExecuteMany formatting has actually more limitations than documented, and diverges from what is possible within simple execute calls:

psql:
> create table test (x integer, y varchar(3));

ghci:
> execute c "insert into test (x,y) values (?,'abc')" (Only 1)
1
> executeMany c "insert into test (x,y) values (?,'abc')" [Only 1, Only 2]
*** Exception: FormatError {fmtMessage = "syntax error in multi-row template", fmtQuery = "insert into test (x,y) values (?,'abc')", fmtParams = []}

Originally, I came across this when trying to set timestamps ('now') within an executeMany, so I guess this might be a common use case.

The database queries in our application are not always static; the number and types of columns in the result of our select statements are often not known at compile time. So, sometimes I need to be able to check what the type of the result is, and in HDBC I would do that by pattern matching on a SqlValue.

So, it is useful for us to have a composite datatype like HDBC's SqlValue, and instance like this for it:

instance QueryResults [SqlValue]

We don't need an instance for Param or QueryParams because I can just use render to convert all my arguments to the Action type.

We wouldn't need the silly toSql and fromSql functions from HDBC, just the SqlValue and the QueryResults instance.

Does this seem reasonable? Would you accept a patch that provides this?

Though there is no clean way to get violated constraint name from postgres, but error messages haven't changed for quite some time, and usually are caught by parsing error messages like ' ERROR: insert or update on table "tbl_user" violates foreign key constraint "test2"'
Class 23 errors can be parsed with regexps and turned into something like

data ConstraintViolationError = NotNullViolation ByteString -- column name
                              | UniqueViolation ByteString
                              | ForeingKeyViolation ByteString ByteString -- table and column name
                              | CheckViolation ByteString

sqlErrorToViolation :: SqlError -> Maybe ConstraintViolationError
sqlErrorToViolation err =
  case sqlState err of
    "23503" -> extractForeign msg
               >>= Just . uncurry ForeignKeyViolation
    "23505" -> extractDuplicate msg
               >>= Just . UniqueViolation
    "23514" -> extractCheck msg
               >>= Just . uncurry CheckViolation
    _ -> Nothing
  where
    msg = sqlErrorMsg err

instance Exception ConstraintViolationError where

Or, instead of structured exceptions, define specialized catch like

catchConstraintViolation :: (SqlError -> ConstraintViolationError -> m a) -> m a -> m a
catchConstraontViolation f m = m `catch`
                     (\e -> maybe (throw e) (f e) $ sqlErrorToViolation e)

createUser :: User -> EitherT MyErrors IO ()
createUser u = catchViolation catcher $ execute insertQuery u
   where
      catcher _ (UniqueViolation "users_login_key") = left . LoginNotUnique $ userLogin u
      catcher e _ -> throw e

Possibly depends on system LC_MESSAGES, which is bad.
Also I have quilckly hacked ugly implementation of this for internal use. If good api is defined, I can implement it.

For some of postgresql's types, transferring them in a binary format would result in dramatically better performance. Particularly large improvements would likely be seen for timestamps, floating-point numbers, integers, and arrays of many different kinds of values.

This seems a particularly large issue, as evidenced by the fact that most postgresql clients only support binary formats for a limited number of datatypes. Many applications get around this by supporting a limited number of binary formats that are the most relevant for the performance of that application, and then casting the remaining fields to text. However, this does not interact well with the approach currently used by postgresql-simple, which uses those types for (dynamic) type checking purposes.

Also, it's unfortunate that retrieving results from PostgreSQL in a binary format is currently an all-or-nothing proposition. It seems to me that we need to find an incremental approach to this issue; otherwise I think any attempt to resolve this is likely to fail. One possibility that has crossed my mind is to have some way of having instances of FromField support a textual format and an optional binary format, and advertise whether or not binary formats are accepted advertised in the type. Then hopefully some compile-time metaprogramming could determine whether or not the query's results could be requested in binary instead of text.

This is less than ideal, because a FromField instance that does not support binary formats would then disable binary transfers for (often more profitable) columns. But it is incremental, in a way.

Query parameters are a bit easier, not least because one can transfer a few parameters in a binary format while the remainder can be transferred in a textual format. The one downside is that this requires the use of protocol-level parameters, and these types of queries have the rather arbitrary limitation that multiple statements cannot be transferred to PostgreSQL in one request, meaning that it can take more round trips between postgres and the client to accomplish a task. And it is a minor change to the interface that will break a few people.

I'd like to use executeMany to perform UPDATE queries in batch. The queries have roughly the form:

UPDATE table SET someField = ? WHERE someOtherField = ?;

As I understand this is not possible due to the restrictions executeMany poses on query templates.

• Is there a way to work around this limitation?
• I guess this limitation is not imposed by postgresql but rather introduced on the Haskell side. Is that true?

Sometimes our connections get screwed up. We like to check them and reset them if they get screwed up.

reset already exists in postgresql-libpq, but needs to be exposed through postgresql-simple. check needs to be added to both.

It'd be nice to modify the FromField instances for Integer and Rational to add support for pgmp.

http://pgmp.projects.pgfoundry.org/

Hi i'd like to be able to store Serializable objects to postgres, but I'm having a little bit of trouble making the types line up and I suspect that something to do with this:

bytea |  1 or 4 bytes plus the actual binary string  |  variable-length binary string

is killing me. Can anyone see what might be wrong here?

class (Typeable a, Serialize a) => StateRes a

newtype PGFromSerialize a = PGFromSerialize { unser :: a } deriving Typeable

instance (Serialize a) => ToField (PGFromSerialize a) where
  toField = toField . Pg.Binary . S.encode . unser

instance (StateRes a)  => FromField (PGFromSerialize a) where
  fromField f mdata = do
      typName <- typename f
      -- following case to verify that it's doing something odd with Binary vs Text
      case format f of
        Text-> returnError ConversionFailed f "It's text"
        FF.Binary   -> do -- returnError ConversionFailed f "It's not binary"
          if typName /= "bytea"
            then returnError Incompatible f "Not a serialize column"
            else case mdata of
              Nothing -> returnError ConversionFailed f "Couldnt' parse serializable"
              Just dat -> case S.decode dat of
                   Left err -> returnError ConversionFailed f ("Couldnt' parse binary: " ++ err)
                   Right a -> return $ PGFromSerialize a

selecting into IO [Only BtyeString] and then mapping the appropriate call to Serialize.decode seems to do the right thing.

Otherwise I get:

*** Exception: ConversionFailed {errSQLType = "bytea", errSQLTableOid = Just (Oid 32961), errSQLField = "aggregate_state", errHaskellType = "PGFromSerialize (Map [Char] Int)", errMessage = "Couldnt' parse binary: too few bytes\nFrom:\tdemandInput\n\n"}

See, for example, #4; we should add support for network addresses and universally unique identifiers among others.

Also, Haskell's UTCTime doesn't support infinity or -infinity, whereas PostgreSQL's timestamp type does.

With Postgresql it's possible to create your own types;

http://www.postgresql.org/docs/8.1/static/rowtypes.html

For example;

CREATE TYPE episode_id AS
(
    season_nr int,
    episode_nr int,
    show_id bigint
);

CREATE TABLE episodes
(
    id episode_id primary key,
    title varchar(255) not null,
    release_date timestamptz null
);

If you do multiple inserts like, you would write;

INSERT INTO episodes (id, title, release_date)
VALUES 
((1, 1, 58811), 'Pilot', null),
((1, 2, 58811), 'Vector', null);

Or;

INSERT INTO episodes
VALUES 
(ROW(1, 1, 58811), 'Pilot', null),
(ROW(1, 2, 58811), 'Vector', null);

However when I try do that in postgresql-simple it throws an FormatError

it would be great to connect to postgresql using other identification, i.e. ident.

There is a bug in the released verion of aeson's eitherDecodeStrictWith (indirectly called in the JSON.Value instance of FromField), which fails for basically all strict bytestrings. The current tip of aeson fixed this, but installing postgresql-simple from hackage at the moment (until aeson 0.7 is released) renders the JSON instances totally unusable.

If @bos releases 0.7 soon, this issue isn't very important. If not, I'm happy to submit a quick fix in the meantime, but it'll be hacky...

There are use cases, e.g. with writable CTEs, that the current interface cannot properly parameterize. For example:

CREATE TABLE things (
   id   SERIAL NOT NULL,
   name TEXT NOT NULL,
   PRIMARY KEY (id)
  );

CREATE TABLE attributes (
   id    INT NOT NULL REFERENCES things,
   key   TEXT NOT NULL,
   value TEXT NOT NULL
  );

Then one could populate a thing and its attributes in one query:

WITH thing AS
   ( INSERT INTO things (name) VALUES ('bob') RETURNING id )
INSERT INTO attributes (id,key,value) VALUES 
   ((SELECT id FROM thing), 'foo', 'bar'), 
   ((SELECT id FROM thing), 'bar', 'baz');

This sort of statement can't really be parameterized in the current incarnation of postgresql-simple; even ignoring the trick with the RETURNING statement, we can't parameterize both the name and all the key-values. Of course in this particular case, one could consider using an Hstore type, but there are tradeoffs, e.g. hstore does not support multiple key-value pairs with the same key, and only supports text to text mappings whereas it would be easy to add columns to the attribute table, and/or use different types for keys and values.

EDIT: There turns out to be a somewhat better way of writing the above CTE:

WITH thing AS ( 
    INSERT INTO things (name) VALUES ('bob') RETURNING id 
)   INSERT INTO attributes 
        SELECT thing.id, a.* 
        FROM thing JOIN (VALUES ('foo','bar'),('bar','baz')) a

1. what is needed to expose those as having their own types for the purposes of using them as query parameter types and result types?

2. I would be very happy to do the work, I just need someone to point me to what needs to be done to do the work

3. thanks!
   -Carter

Linking on OpenBSD 5.4-release fails with "unknown symbol `ERR_get_error'" message.

Resolving dependencies...
Configuring postgresql-simple-0.3.9.1...
Building postgresql-simple-0.3.9.1...
Preprocessing library postgresql-simple-0.3.9.1...
[ 1 of 32] Compiling Database.PostgreSQL.Simple.TypeInfo.Types ( src/Database/PostgreSQL/Simple/TypeInfo/Types.hs, dist/dist-sandbox-7b6ed3e/build/Database/PostgreSQL/Simple/TypeInfo/Types.o )
[ 2 of 32] Compiling Database.PostgreSQL.Simple.Time.Implementation ( src/Database/PostgreSQL/Simple/Time/Implementation.hs, dist/dist-sandbox-7b6ed3e/build/Database/PostgreSQL/Simple/Time/Implementation.o )
[ 3 of 32] Compiling Database.PostgreSQL.Simple.Time.Internal ( src/Database/PostgreSQL/Simple/Time/Internal.hs, dist/dist-sandbox-7b6ed3e/build/Database/PostgreSQL/Simple/Time/Internal.o )
[ 4 of 32] Compiling Database.PostgreSQL.Simple.SqlQQ ( src/Database/PostgreSQL/Simple/SqlQQ.hs, dist/dist-sandbox-7b6ed3e/build/Database/PostgreSQL/Simple/SqlQQ.o )
[ 5 of 32] Compiling Database.PostgreSQL.Simple.Time ( src/Database/PostgreSQL/Simple/Time.hs, dist/dist-sandbox-7b6ed3e/build/Database/PostgreSQL/Simple/Time.o )
[ 6 of 32] Compiling Database.PostgreSQL.Simple.TypeInfo.Static ( src/Database/PostgreSQL/Simple/TypeInfo/Static.hs, dist/dist-sandbox-7b6ed3e/build/Database/PostgreSQL/Simple/TypeInfo/Static.o )
[ 7 of 32] Compiling Database.PostgreSQL.Simple.BuiltinTypes ( src/Database/PostgreSQL/Simple/BuiltinTypes.hs, dist/dist-sandbox-7b6ed3e/build/Database/PostgreSQL/Simple/BuiltinTypes.o )
[ 8 of 32] Compiling Database.PostgreSQL.Simple.Arrays ( src/Database/PostgreSQL/Simple/Arrays.hs, dist/dist-sandbox-7b6ed3e/build/Database/PostgreSQL/Simple/Arrays.o )
[ 9 of 32] Compiling Database.PostgreSQL.Simple.Types ( src/Database/PostgreSQL/Simple/Types.hs, dist/dist-sandbox-7b6ed3e/build/Database/PostgreSQL/Simple/Types.o )
[10 of 32] Compiling Database.PostgreSQL.Simple.TypeInfo.Macro ( src/Database/PostgreSQL/Simple/TypeInfo/Macro.hs, dist/dist-sandbox-7b6ed3e/build/Database/PostgreSQL/Simple/TypeInfo/Macro.o )
[11 of 32] Compiling Database.PostgreSQL.Simple.ToField[boot] ( src/Database/PostgreSQL/Simple/ToField.hs-boot, dist/dist-sandbox-7b6ed3e/build/Database/PostgreSQL/Simple/ToField.o-boot )
[12 of 32] Compiling Database.PostgreSQL.Simple.ToRow[boot] ( src/Database/PostgreSQL/Simple/ToRow.hs-boot, dist/dist-sandbox-7b6ed3e/build/Database/PostgreSQL/Simple/ToRow.o-boot )
[13 of 32] Compiling Database.PostgreSQL.Simple.ToField ( src/Database/PostgreSQL/Simple/ToField.hs, dist/dist-sandbox-7b6ed3e/build/Database/PostgreSQL/Simple/ToField.o )
[14 of 32] Compiling Database.PostgreSQL.Simple.ToRow ( src/Database/PostgreSQL/Simple/ToRow.hs, dist/dist-sandbox-7b6ed3e/build/Database/PostgreSQL/Simple/ToRow.o )
[15 of 32] Compiling Database.PostgreSQL.Simple.Ok ( src/Database/PostgreSQL/Simple/Ok.hs, dist/dist-sandbox-7b6ed3e/build/Database/PostgreSQL/Simple/Ok.o )
[16 of 32] Compiling Database.PostgreSQL.Simple.Internal ( src/Database/PostgreSQL/Simple/Internal.hs, dist/dist-sandbox-7b6ed3e/build/Database/PostgreSQL/Simple/Internal.o )
[17 of 32] Compiling Database.PostgreSQL.Simple.LargeObjects ( src/Database/PostgreSQL/Simple/LargeObjects.hs, dist/dist-sandbox-7b6ed3e/build/Database/PostgreSQL/Simple/LargeObjects.o )
[18 of 32] Compiling Database.PostgreSQL.Simple.Notification ( src/Database/PostgreSQL/Simple/Notification.hs, dist/dist-sandbox-7b6ed3e/build/Database/PostgreSQL/Simple/Notification.o )
[19 of 32] Compiling Database.PostgreSQL.Simple.Errors ( src/Database/PostgreSQL/Simple/Errors.hs, dist/dist-sandbox-7b6ed3e/build/Database/PostgreSQL/Simple/Errors.o )
[20 of 32] Compiling Database.PostgreSQL.Simple.FromField[boot] ( src/Database/PostgreSQL/Simple/FromField.hs-boot, dist/dist-sandbox-7b6ed3e/build/Database/PostgreSQL/Simple/FromField.o-boot )
[21 of 32] Compiling Database.PostgreSQL.Simple.FromRow[boot] ( src/Database/PostgreSQL/Simple/FromRow.hs-boot, dist/dist-sandbox-7b6ed3e/build/Database/PostgreSQL/Simple/FromRow.o-boot )
[22 of 32] Compiling Database.PostgreSQL.Simple.Compat ( src/Database/PostgreSQL/Simple/Compat.hs, dist/dist-sandbox-7b6ed3e/build/Database/PostgreSQL/Simple/Compat.o )
[23 of 32] Compiling Database.PostgreSQL.Simple.Transaction ( src/Database/PostgreSQL/Simple/Transaction.hs, dist/dist-sandbox-7b6ed3e/build/Database/PostgreSQL/Simple/Transaction.o )
[24 of 32] Compiling Database.PostgreSQL.Simple[boot] ( src/Database/PostgreSQL/Simple.hs-boot, dist/dist-sandbox-7b6ed3e/build/Database/PostgreSQL/Simple.o-boot )
[25 of 32] Compiling Database.PostgreSQL.Simple.TypeInfo ( src/Database/PostgreSQL/Simple/TypeInfo.hs, dist/dist-sandbox-7b6ed3e/build/Database/PostgreSQL/Simple/TypeInfo.o )
[26 of 32] Compiling Database.PostgreSQL.Simple.FromField ( src/Database/PostgreSQL/Simple/FromField.hs, dist/dist-sandbox-7b6ed3e/build/Database/PostgreSQL/Simple/FromField.o )
Loading package ghc-prim ... linking ... done.
Loading package integer-gmp ... linking ... done.
Loading package base ... linking ... done.
Loading package array-0.4.0.1 ... linking ... done.
Loading package deepseq-1.3.0.1 ... linking ... done.
Loading package bytestring-0.10.0.2 ... linking ... done.
Loading package containers-0.5.0.0 ... linking ... done.
Loading package binary-0.5.1.1 ... linking ... done.
Loading package byteable-0.1.1 ... linking ... done.
Loading package cryptohash-0.11.1 ... linking ... done.
Loading package text-0.11.3.1 ... linking ... done.
Loading package hashable-1.1.2.5 ... linking ... done.
Loading package transformers-0.3.0.0 ... linking ... done.
Loading package mtl-2.1.2 ... linking ... done.
Loading package parsec-3.1.3 ... linking ... done.
Loading package old-locale-1.0.0.5 ... linking ... done.
Loading package time-1.4.0.1 ... linking ... done.
Loading package unix-2.6.0.1 ... linking ... done.
Loading package network-2.4.2.0 ... linking ... done.
Loading package network-info-0.2.0.3 ... linking ... done.
Loading package random-1.0.1.1 ... linking ... done.
Loading package uuid-1.3.3 ... linking ... done.
Loading package postgresql-libpq-0.8.2.4 ... linking ... ghc: /usr/local/lib/libpq.a: unknown symbol `ERR_get_error'
ghc: unable to load package `postgresql-libpq-0.8.2.4'
Failed to install postgresql-simple-0.3.9.1

Postgresql-simple can deal with joins in a reasonably nice way via the :. data type; e.g. if you have a website where users can post stories, you might have a schema that looks something like

CREATE TABLE users
   ( user_id   INT  NOT NULL
   , username  TEXT NOT NULL
   );

CREATE TABLE stories
   ( story_id  INT  NOT NULL
   , author    INT  NOT NULL REFERENCES users(user_id)
   , content   TEXT NOT NULL
   );

You could fetch all the (author, story) pairs via the following postgresql-simple code:

data Story = Story Int Int Text

instance FromRow Story where 
   fromRow = Story <$> field <*> field <*> field

data User = User Int Text

instance FromRow User where
   fromRow = User <$> field <*> field

getAuthors :: Connection -> IO [User :. Story]
getAuthors conn = do
    query_ conn [sql|  SELECT (u.*), (s.*) FROM 
                         users u JOIN stories s 
                           ON s.author = u.user_id |]

However, this will only return those users that have written at least one story. If you wanted to get all users and the stories they've written, regardless of whether or not that user has written a story, you could use a left outer join. The database will then fill the (normally non-nullable) story_id and content fields with nulls for that category of users.

Although left joins can be quite useful, they are an awkward construct to deal with in postgresql-simple. It would be nice if we could simply write:

getUsers :: Connection -> IO [User :. Maybe Story] 
getUsers conn = do
    query_ conn [sql| SELECT (u.*), (s.*) FROM
                         users u LEFT OUTER JOIN stories s 
                           ON s.author = u.user_id |]

However, this requires an instance FromRow a => FromRow (Maybe a) that would return Nothing if all the consumed fields are null. But it's impossible to write such an instance, as there's no way of knowing how many fields that a fromRow instance consumes. (And in fact it's possible to consume a variable number of fields, although the only practical example I know of is the existing instance FromField a => FromRow [a] that consumes all the remaining fields.)

One could implement a FromRow instance on specific Maybe types, e.g.

instance FromField (Maybe Story) where
    fromRow =  (null *> null *> null *> pure Nothing) <|> (Just <$> fromRow)
       where null = field :: RowParser Null

However, now you have an additional instance to keep in sync with the Story data type, not to mention that this requires the FlexibleInstances language extension, which isn't too problematic except that Haskell 98 instances support more polymorphism and therefore are preferable if they suffice.

So the question is, what can be done to make left outer joins easier to work with in postgresql-simple? How could we change and/or weaken the FromRow interface in order to support a generic, out-of-box instance FromRow a => FromRow (Maybe a)? Or is there another solution?

I'm a bit of a haskell noob, but there seems to be a problem with the precision of doubles. This issue is not present when the same query is run through plain HDBC.PostgreSQL.

Example:

{-# LANGUAGE OverloadedStrings #-}

import Control.Applicative
import Database.PostgreSQL.Simple
import Database.PostgreSQL.Simple.FromRow
import Database.PostgreSQL.Simple.FromField

data Test = Test { testX :: Double }

instance FromRow Test where
  fromRow = Test <$> field

main = do
  conn <- connect defaultConnectInfo
  x <- query_ conn "select cast(1.3355 as double precision)" :: IO [Test]
  close conn
  putStrLn $ show $ testX (head x)

$ runhaskell a.hs
1.3355000000000001

After a conversation with @joeyadams, I believe postgresql-simple ought to use asynchronous calls to libpq. We would then use threadWaitRead and threadWaitWrite to schedule asynchronous calls using GHC's IO manager, instead of using blocking C calls and having the OS kernel do the scheduling.

The performance impact of such a change is unclear, but the one clear advantage is that System.Timeout could then be used with connect, query, execute, etc. System.Timeout cannot be used with blocking C calls because GHC's runtime is not in control of the thread and cannot interrupt the thread until the call returns.

Currently, in ToField, the ByteString instance treats the argument as text format. For example:

> let bs = "\\123" :: ByteString
> bs
"\\123"
> query conn "SELECT ? :: TEXT" (Only bs) :: IO [Only ByteString]
[Only {fromOnly = "\\123"}]
> query conn "SELECT ? :: BYTEA" (Only bs) :: IO [Only ByteString]
[Only {fromOnly = "S"}] -- whoops
query conn "SELECT ? :: BYTEA" (Only (Binary bs)) :: IO [Only ByteString]
> [Only {fromOnly = "\\123"}]

If users represent BYTEA using ByteString, their code will silently be wrong, since ToField won't escape the data properly for BYTEA's input function (but it will for Binary, of course). I don't think FromField has this problem, as it checks the typeOid of the incoming data.

I propose that we remove the ToField instance for ByteString. Perhaps we could provide a newtype wrapper to access the same functionality, similar to the Binary wrapper:

newtype TextFormat a = TextFormat a

Actually, there might be a reasonable alternative: pass the parameter in binary format. I think this would handle both TEXT and BYTEA correctly. A downside is that, if we wanted to support rendering SQL queries for external use, or rendering COPY FROM data, we wouldn't be able to use ToField as-is, as it would lack the distinction between TEXT and BYTEA needed for rendering parameters in text format.

Thoughts?

The newest postgresql-simple requires hashable version 1.2.1 or higher. However, both the current and previous Haskell Platforms require a version in the 1.1 range, making this incompatible. Would it be possible to relax this restriction?

Hello.
I am trying to use postgresql-simple to store many same data in the database.
When my function looks like this

postSaveCandles :: Connection -> [Candle] -> IO ()
postSaveCandles c cndls = do
  executeMany c "insert into candles (open, close, minc, maxc, volume, timec, periodtype, periodsecs) values (?,?,?,?,?,?,?,?)" cndls
  return ()

My program leaks. I mean it constructs very many Candle consuming memory and then reduces them and executes query.

When i do this

postSaveCandles :: Connection -> [Candle] -> IO ()
postSaveCandles c cndls = do
  mapM_ (execute c "insert into candles (open, close, minc, maxc, volume, timec, periodtype, periodsecs) values (?,?,?,?,?,?,?,?)") cndls
  return ()

It works smothsly, inserts each candle one by one, but not very fast. I think bacause of many exec's.

Candle looks like this

data Period = FixedSecs !Int64 -- ^ Fixed time period defined by seconds count (i.e. hour is 3600 seconds)
            | Month           -- ^ Month time period
            | Year            -- ^ Year time period
            deriving (Eq, Show)

data Candle = Candle {candleOpenCost :: ! Double  -- ^ Opening cost of candle
                     ,candleCloseCost :: ! Double -- ^ Closing cost of candle
                     ,candleMinCost :: ! Double   -- ^ Minimal cost in the candle
                     ,candleMaxCost :: ! Double   -- ^ Maximal cost in the candle
                     ,candleVolume :: ! Double    -- ^ Volume of all deals in candle
                     ,candleTime :: !UTCTime       -- ^ Start time of the candle
                     ,candlePeriod :: !Period      -- ^ Candle time period
                     }
            deriving (Eq, Show)

And have instance ToRow

instance ToRow Candle where
  toRow (Candle {candleOpenCost = oc,
                 candleCloseCost = cc,
                 candleMinCost = minc,
                 candleMaxCost = maxc,
                 candleVolume = v,
                 candleTime = tm,
                 candlePeriod = per}) = [toField oc,
                                         toField cc,
                                         toField minc,
                                         toField maxc,
                                         toField v,
                                         toField tm] ++ (ptofield per)
    where
      ptofield (FixedSecs s) = [toField ("seconds" :: String),
                                toField s]
      ptofield Month = [toField ("month" :: String),
                        toField (0 :: Int64)]
      ptofield Year = [toField ("year" :: String),
                       toField (0 :: Int64)]

Candle generates just random with this instance

instance Random Candle where
  random g = (Candle {candleOpenCost = oc,
                      candleCloseCost = cc,
                      candleMinCost = mc,
                      candleMaxCost = mac,
                      candleVolume = v,
                      candleTime = t,
                      candlePeriod = p}, ng)
    where
      (oc, g1) = random g
      (cc, g2) = random g1
      (mc, g3) = random g2
      (mac, g4) = random g3
      (v, g5) = random g4
      (t, g6) = random g5
      (p, ng) = random g6
  randomR (Candle {candleOpenCost = oc1,
                   candleCloseCost = cc1,
.....

which is quite lazy.
here is all code
https://github.com/s9gf4ult/hadan/blob/master/post.hs

It seems that it's impossible, to drop a table, because to avoid SQL injection Strings are wrapped by quotes, but this causes a syntax error, because the generated SQL is not valid. Example code:

  execute dbConn "DROP TABLE IF EXISTS ? CASCADE" (Only schema)

This generates:

SqlError {sqlState = "42601", sqlExecStatus = FatalError, sqlErrorMsg = "syntax error at or near \"'news'\"", sqlErrorDetail
 = "", sqlErrorHint = ""}

And this is because the library generates this:

DROP TABLE 'news'

instead of

DROP TABLE news

And things are complicated by the way postgres handles quotes in table creation:

(cfr. http://stackoverflow.com/questions/6331504/omitting-the-double-quote-to-do-query-on-postgresql)

What am I missing?

If you have a multi-line query using PostgreSQL comments (--), the line break is not respected and thus the comment extends to the end of the query.