Rather than executing a whole query at once, it is possible to set up a cursor that encapsulates the query, and then read the query result a few rows at a time. See:

http://www.postgresql.org/docs/8.1/static/plpgsql-cursors.html

This is a simple test program to save and read a bytestring to/from a database. It works fine with sqlite, but not with postgresql. There are 2 problems:

• It raises an exception on insert SqlError {seState = "22021", seNativeError = 7, seErrorMsg = "execute: PGRES_FATAL_ERROR: ERROR: invalid byte sequence for encoding \"UTF8\": 0x80\n"}
• If I reduce testData to some shorter value, the readBack won't compare equal.

import Data.ByteString

import Database.HDBC
--import Database.HDBC.Sqlite3 (connectSqlite3)
import Database.HDBC.PostgreSQL (connectPostgreSQL)

testData :: ByteString
testData = pack [0..255]

main :: IO ()
main = do
    --conn <- connectSqlite3 "test1.db"
    conn <- connectPostgreSQL "host=localhost dbname=test user=test"

    _ <- run conn "CREATE TABLE test (value BYTEA)" []
    commit conn

    _ <- run conn "INSERT INTO test VALUES (?)" [toSql testData]
    commit conn

    readBack <- do
        rv <- quickQuery' conn "SELECT * from test" []
        return $ fromSql $ Prelude.head $ Prelude.head rv

    print $ testData == readBack

    disconnect conn

It looks like a database expects UTF8. But I just want to use raw ByteString. There might as well be a problem in a testprogram. If this is the case, please advice what would be a correct way to do it.

HDBC-postgresql uses ProgramSearchPath, not available in older versions of Cabal, but doesn't include an appropriate version constraint. We had Cabal 1.16 installed, which didn't work, but HDBC-postgresql's version constraint allows that version.

/tmp/HDBC-postgresql-2.3.2.4-233794/HDBC-postgresql-2.3.2.4/dist/dist-sandbox-fc3e54a/setup/setup.hs:40:53:
    Not in scope: type constructor or class `ProgramSearchPath'

All of PostgreSQL's types dealing with time support -infinity and infinity, which is before (or after) all other times. You can read about it in Section 8.4.1.5 of PostgreSQL 9.0 manual.

HDBC will throw an exception when getting back a time value of this type. It appears probable that fixing this will involve modifying the API of HDBC itself, so I'm not sure the best solution here.

One possibility without changing the API would be to represent -infinity and infinity as the minimum and maximum time representable by the associated Haskell type. Of course, when passing time as a parameter to a prepared statement, it would be necessary to translate these special values back into the appropriate -infinity and infinity, so that most values can round-trip without problem.

Comparisons should then work identically in Haskell and in Postgres, but time manipulations wouldn't, as Haskell wouldn't know to treat these values specially. However, if time manipulations aren't identical anyway, then worrying about this issue would be putting the cart before the horse.

The newest version uploaded to hackage is 2.3.2.4 ( http://hackage.haskell.org/package/HDBC-postgresql-2.3.2.4 ), but head of this repository is 2.3.2.3.

The HDBC API supports "prepared" statements, however the PostgreSQL currently doesn't do anything with this. Instead, the query string will be sent to the database repeatedly, which means that the database will have to repeatedly re-parse the query and re-create a query plan.

This script reliably fails, even though it is doing nothing wrong. It can be run with cabal run ./Test.hs.

{- cabal:
   build-depends: base, HDBC, HDBC-postgresql, time, convertible
-}
module Main where
import Data.Time
import Database.HDBC
import Database.HDBC.PostgreSQL
import Data.Convertible
main = do
  c <- connectPostgreSQL "host=/var/run/postgresql user=janus password=lol"
  runRaw c "set timezone to 'America/Mexico_City';" -- UH OH
  select <- prepare c "SELECT ? :: timestamptz;"
  execute select [toSql $ (convert (UTCTime (fromGregorian 1920 12 25) 0) :: SqlValue)]
  result <- fetchAllRows select
  putStrLn $ show result
  commit c

The issue seems to be that if the timezone offset that comes back has sufficient granularity, it cannot be parsed by the Convertible instance:

 % cabal run ./Test.hs
Resolving dependencies...
Build profile: -w ghc-9.0.1 -O1
In order, the following will be built (use -v for more details):
 - fake-package-0 (exe:script) (configuration changed)
Configuring executable 'script' for fake-package-0..
Preprocessing executable 'script' for fake-package-0..
Building executable 'script' for fake-package-0..
[1 of 1] Compiling Main             ( Main.hs, /home/janus/flipstone/hdbc-postgresql/dist-newstyle/build/x86_64-linux/ghc-9.0.1/fake-package-0/x/script/build/script/script-tmp/Main.o )
Linking /home/janus/flipstone/hdbc-postgresql/dist-newstyle/build/x86_64-linux/ghc-9.0.1/fake-package-0/x/script/build/script/script ...
script: Convertible: error converting source data SqlString "1920-12-24 17:23:24-06:36:36" of type SqlValue to type ZonedTime: Cannot parse using default format string "%Y-%m-%d %T%Q %z"
CallStack (from HasCallStack):
  error, called at ./Data/Convertible/Base.hs:66:17 in convertible-1.1.1.0-612d414cafd18c253ab58ea430e579d9acf3584c43d4fcee9b6e216dac83fa55:Data.Convertible.Base

It seems like HDBC-postgresql doesn't set a session timezone, instead it relies on whatever the session ends up using. Since my system timezone is America/Mexico_City, the error is triggered for me even without manually setting the timezone in the connection. And if I set the timezone to UTC in the script, there is no problem either.

If you change to 1930 instead of 1920, there is no crash. It is probably related to the date at which Mexico adopted time zones.

My preferred solution would be that HDBC-postgresql parses all timestamps returned by PostgreSQL, even if they have weird offsets. But one workaround could be that HDBC-postgresql sets the timezone to UTC on opening the connection. I dunno if that would be considered confusing. I actually thought it was more confusing that the system timezone is used at all.

I am using PostgreSQL 12 from Ubuntu 20.04, and the latest HDBC-postgresql from Hackage for GHC 9.0.1.

If you'd like me to attempt at submitting a PR that fixes the problem, I can take a stab at it. But please tell me if you'd like the workaround or not.

The ConstOrId hack is going to need to be updated because the type of programFindLocation changed in Cabal-1.24. It now adds a list of FilePaths to the type.

See haskell/cabal#1467. The type of programFindLocation was changed in haskell/cabal#1415, which broke hdbc-postgresql's custom setup script. Unfortunately, it's not possible to make this change backwards-compatible, but it should be possible to rewrite the setup script to make it work both with new and old versions of Cabal.

I noticed that parsing values of type abstime will cause an error, at least with Postgres 9.0.

import Control.Exception
import Database.HDBC
import Database.HDBC.PostgreSQL

connstr = "dbname=postgres"

withPostgreSQL connstr handler
  = bracket (connectPostgreSQL connstr) (disconnect) handler

main = withPostgreSQL connstr $ \conn -> do
  print =<< quickQuery' conn "select '2011/1/11 1:11'::abstime" []

Double shouldn't be used when converting from intervals.

I want to write this query for PostgreSQL in HDBC:

SELECT * FROM my_table WHERE (key_value ? 'name');

Here my_table is a table that contains a PostgreSQL HStore column: key_value.
The thing is that the '?' is a membership operator and should not be substituted for an argument.

When written in HDBC like this:

quickQuery' conn "SELECT * FROM my_table WHERE (key_value ? 'name')" []

HDBC complains that it can't substitute '?' for an argument when there are none. There should be some way to escape substitution. Would it be possible to escape substitution if a '?' is prepended by a ''.
That would make the query look like this:

quickQuery' conn "SELECT * FROM my_table WHERE (key_value \\? 'name')" []

About HStore:
In a HStore column you can store Data.Map String String.
The '?' operator is a membership operator for the HStore column. It checks if a given key exists in the store.

We're seeing a crash using HDBC-postgresql together with resource-pool 0.2.2.0. I originally filed this as bos/pool#18 but there it seemed the problem was in HDBC-postgresql and its handling of disconnect in finalizers. See that ticket for details, but in short it seems that if external code calls disconnect from a finalizer, HDBC-postgresql will try to disconnect again from its own finalizer.

Hi,

It seems that the BYTEA/ByteString column type doesn't quote "special" characters properly, which results in silent data truncation at the first NULL character.

Sample table:

create table test (name bytea);

Sample program:

import Database.HDBC.PostgreSQL
import Database.HDBC
import Data.ByteString

main = do
  db <- connectPostgreSQL "dbname=debug"
  stmt <- prepare db "INSERT INTO test (name) VALUES($1)"
  execute stmt [toSql $ pack [0]]
  execute stmt [toSql $ pack [65, 0, 66]]
  commit db

The statements as received by the postgres server are:

2011-01-07 16:56:04 CET LOG:  execute <unnamed>: INSERT INTO test (name) VALUES($1)
2011-01-07 16:56:04 CET DETAIL:  parameters: $1 = ''
2011-01-07 16:56:04 CET LOG:  execute <unnamed>: INSERT INTO test (name) VALUES($1)
2011-01-07 16:56:04 CET DETAIL:  parameters: $1 = 'A'

As you can see, the bindings do not correctly escape the NULL bytes and this results in silent truncation. The proper escaping rules for BYTEA columns are documented here: http://www.postgresql.org/docs/8.4/static/datatype-binary.html

thanks,
iustin

When I run stack repl, I get a build error in hdbc-postgresql/Database/HDBC/PostgreSQL/Connection.hsc line 60. The error message:

Database/HDBC/PostgreSQL/Connection.hsc:60:130: error:
lexical error in string/character literal at character 's'

That line is a statement similar to:
60: do "some error message"
61: return whateverExpression

The weird part is that the letter 's' is in the middle of "some error message".

I deleted the entire error message expression, so the resulting file is:

60: do -- rest of line is blank
61: return whateverExpression

$ stack clean
$ stack build --ghc-options=-fforce-recomp

and the package builds and I can do things like this:

λ> conn <- connectPostgreSQL "host=localhost dbname=whateverName user=username"

and it appears to work okay, but obviously deleting error messages is not a real fix.

Does anybody know what this is about?

Please make buildable with GHC 8.2

Table of Contents

1. system info
2. reproduce with cabal and master of HDBC-postgresql
3. install haskell-platform 8.6.3
   1. show version
4. clone HDBC-postgresql
5. in HDBC-postgresql install directory
   1. show git commit of hdbc-postgresql
   2. try to build HDBC-postgresql

system info

system_profiler SPSoftwareDataType | grep -i System | head -n 2

System Software Overview:
  System Version: macOS 10.14.2 (18C54)

reproduce with cabal and master of HDBC-postgresql

DONE install haskell-platform 8.6.3

show version

ghc --version

DONE clone HDBC-postgresql

git clone https://github.com/hdbc/hdbc-postgresql.git

Cloning into 'hdbc-postgresql'...

in HDBC-postgresql install directory

show git commit of hdbc-postgresql

git log -n1 --oneline

f3923c3 Bump.

DONE try to build HDBC-postgresql

cabal install

Resolving dependencies...
Configuring HDBC-postgresql-2.3.2.5...
Building HDBC-postgresql-2.3.2.5...
Failed to install HDBC-postgresql-2.3.2.5
Build log ( /Users/codygman/.cabal/logs/ghc-8.6.3/HDBC-postgresql-2.3.2.5-COXC9aMUYABJ42VfzD82tr.log ):
cabal: Entering directory '.'
Configuring HDBC-postgresql-2.3.2.5...
Preprocessing library for HDBC-postgresql-2.3.2.5..
PTypeConv.hsc:53:16: error: use of undeclared identifier 'PG_TYPE_CHAR'
    hsc_const (PG_TYPE_CHAR);
               ^
PTypeConv.hsc:53:16: error: use of undeclared identifier 'PG_TYPE_CHAR'
PTypeConv.hsc:53:16: error: use of undeclared identifier 'PG_TYPE_CHAR'
PTypeConv.hsc:54:16: error: use of undeclared identifier 'PG_TYPE_CHAR2'
    hsc_const (PG_TYPE_CHAR2);
               ^
PTypeConv.hsc:54:16: error: use of undeclared identifier 'PG_TYPE_CHAR2'
PTypeConv.hsc:54:16: error: use of undeclared identifier 'PG_TYPE_CHAR2'
PTypeConv.hsc:55:16: error: use of undeclared identifier 'PG_TYPE_CHAR4'
    hsc_const (PG_TYPE_CHAR4);
               ^
PTypeConv.hsc:55:16: error: use of undeclared identifier 'PG_TYPE_CHAR4'
PTypeConv.hsc:55:16: error: use of undeclared identifier 'PG_TYPE_CHAR4'
PTypeConv.hsc:56:16: error: use of undeclared identifier 'PG_TYPE_CHAR8'
    hsc_const (PG_TYPE_CHAR8);
               ^
PTypeConv.hsc:56:16: error: use of undeclared identifier 'PG_TYPE_CHAR8'
PTypeConv.hsc:56:16: error: use of undeclared identifier 'PG_TYPE_CHAR8'
PTypeConv.hsc:57:16: error: use of undeclared identifier 'PG_TYPE_NAME'
    hsc_const (PG_TYPE_NAME);
               ^
PTypeConv.hsc:57:16: error: use of undeclared identifier 'PG_TYPE_NAME'
PTypeConv.hsc:57:16: error: use of undeclared identifier 'PG_TYPE_NAME'
PTypeConv.hsc:58:16: error: use of undeclared identifier 'PG_TYPE_BPCHAR'
    hsc_const (PG_TYPE_BPCHAR);
               ^
PTypeConv.hsc:58:16: error: use of undeclared identifier 'PG_TYPE_BPCHAR'
PTypeConv.hsc:58:16: error: use of undeclared identifier 'PG_TYPE_BPCHAR'
PTypeConv.hsc:59:16: error: use of undeclared identifier 'PG_TYPE_VARCHAR'
    hsc_const (PG_TYPE_VARCHAR);
               ^
fatal error: too many errors emitted, stopping now [-ferror-limit=]
20 errors generated.
compiling dist/build/Database/HDBC/PostgreSQL/PTypeConv_hsc_make.c failed (exit code 1)
command was: /usr/bin/gcc -c dist/build/Database/HDBC/PostgreSQL/PTypeConv_hsc_make.c -o dist/build/Database/HDBC/PostgreSQL/PTypeConv_hsc_make.o -fno-stack-protector -fno-stack-protector -D__GLASGOW_HASKELL__=806 -Ddarwin_BUILD_OS=1 -Dx86_64_BUILD_ARCH=1 -Ddarwin_HOST_OS=1 -Dx86_64_HOST_ARCH=1 -I/usr/local/Cellar/postgresql/11.1/include -I. -I/usr/local/Cellar/postgresql/11.1/include -Idist/build/. -DMIN_TIME_15 -Idist/build/autogen -Idist/build/global-autogen -include dist/build/autogen/cabal_macros.h -I/Library/Frameworks/GHC.framework/Versions/8.6.3-x86_64/usr/lib/ghc-8.6.3/time-1.8.0.2/include -I/Users/codygman/.cabal/lib/x86_64-osx-ghc-8.6.3/old-time-1.1.0.3-2XkcGgLYS3G4Bt8PCBG9iL/include -I/Library/Frameworks/GHC.framework/Versions/8.6.3-x86_64/usr/lib/ghc-8.6.3/bytestring-0.10.8.2/include -I/Library/Frameworks/GHC.framework/Versions/8.6.3-x86_64/usr/lib/ghc-8.6.3/base-4.12.0.0/include -I/Library/Frameworks/GHC.framework/Versions/8.6.3-x86_64/usr/lib/ghc-8.6.3/integer-gmp-1.0.2.0/include -I/Library/Frameworks/GHC.framework/Versions/8.6.3-x86_64/usr/lib/ghc-8.6.3/include -I/Library/Frameworks/GHC.framework/Versions/8.6.3-x86_64/usr/lib/ghc-8.6.3/include/
cabal: Leaving directory '.'
cabal: Error: some packages failed to install:
HDBC-postgresql-2.3.2.5-COXC9aMUYABJ42VfzD82tr failed during the building
phase. The exception was:
ExitFailure 1

There seems to be a problem with executing INSERT statements in parallel. An example (test table was created using the following statement: "CREATE TABLE test (id BIGINT, a BIGINT, PRIMARY KEY (id))" and is empty):

module Main where

import Control.Concurrent
import Database.HDBC
import Database.HDBC.PostgreSQL

main :: IO ()
main = withPostgreSQL "user='user' password='password' dbname='test'" $ \conn -> do
_ <- forkIO $ writedb 100000 conn
_ <- forkIO $ writedb 200000 conn
_ <- getChar
return ()

writedb :: Int -> Connection -> IO ()
writedb d conn = do
r <- withTransaction conn $ _ -> do
run conn "INSERT INTO test (id, a) VALUES (?, ?)" [toSql (1::Int), toSql d]
threadDelay d
putStrLn $ show d ++ ": " ++ show r

The output is:
NOTICE: there is no transaction in progress
WARNING: there is already a transaction in progress
: SqlError {seState = "23505", seNativeError = 7, seErrorMsg = "execute: PGRES_FATAL_ERROR: ERROR: duplicate key value violates unique constraint "test_pkey"\nDETAIL: Key (id)=(1) already exists.\n"}
100000: 1

And whereas error from second thread is expected, output "100000: 1" suggests that first thread successfully performed insertion, but the row doesn't actually get inserted into the table.

This is with hdbc-postgresql-2.2.3.3 and postgresql-server-9.0.4.

Hi,

Whenever the connection to Postgresql gets broken, there is no easy way to recover it. That's why the lowlevel PQreset function is provided in the libpq. Could we have a binding to it please ?

Thanks for this nice package.

connectPostgreSQL' and withPostgreSQL' are missing doc strings for understanding the difference between those functions and connectPostgreSQL and withPostgreSQL, respectively.

Josh reports at http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=596183 against version 2.2.3.1:

When HDBC-postgresql converts a SqlIntervalT to a SqlDiffTime in
makeSqlValue, it assumes hours:minutes:seconds, splits on ':', and tries
to use "read" to parse two Integer values and a Double. However,
postgresql stores intervals internally as months, days, and seconds; the
separate days field allows for 23-hour or 25-hour days (thank you
daylight savings time). For such intervals, postgresql outputs "N mons
N days N:N:N.N", with any of the three components omitted if zero. This
causes HDBC-postgresql to fail in "read".

I would expect an insert of the form

run conn "INSERT INTO documents VALUES (?)" [toSql $ data]

to insert the data in the bytestring as is into the table, but instead it does some processing to the data first (unescaping backspace octal sequences, and checking that the data only contains valid utf8 characters).

I have written more details about this at http://stackoverflow.com/questions/20506922/how-do-a-put-binary-data-into-postgres-through-hdbc/20598912 (see the response by jamshidh), including a conversion function that I used to fix the problem.

Its an edge case perhaps, binding an unparseable string to a numeric type doesn't cause an exception and continues, giving unexpected behavior which in this case SELECT with an empty result. The database shows the original value, so the transaction had been rolled back already by the time the SELECT came through.

-- $ cat Main.hs
module Main where

import qualified Database.HDBC as DB
import Database.HDBC.PostgreSQL (withPostgreSQL)
import Database.HDBC.SqlValue

-- Setup:
-- ghc-pkg list
--    ...
--    HDBC-2.3.1.2
--    HDBC-postgresql-2.3.2.1
--    ...
-- createdb testdb
-- CREATE TABLE test1 (a NUMERIC(10,2));
-- CREATE USER test1user PASSWORD 'test1pass';
-- GRANT ALL ON test1 TO test1user;
main :: IO ()
main = do
  withPostgreSQL "host=localhost port=5432 dbname=testdb user=test1user password=test1pass" $ \lnk -> do
    _ <- DB.quickQuery' lnk "INSERT INTO test1 (a) VALUES (100);" []
    res <- DB.quickQuery' lnk "SELECT a FROM test1;" []
    putStrLn $ show res
    DB.commit lnk
    DB.withTransaction lnk $
      \link -> do
        _ <- DB.quickQuery' link "UPDATE test1 SET a = $1;" [ SqlString "Not a number" ]
        res' <- DB.quickQuery' link "SELECT a FROM test1;" []
        putStrLn $ show res'

-- $ rm Main.o
-- $ rm Main.hi
-- $ rm Main
-- $ ghc --make Main.hs
-- [1 of 1] Compiling Main             ( Main.hs, Main.o )
-- Linking Main ...
-- $ ./Main
-- [[SqlRational (100 % 1)]]
-- []

Consider the following example. In one terminal, type

$ socat tcp:localhost:5432 tcp-listen:1234,fork

Then in a second terminal:

ghci>  conn <- connectPostgreSQL "host=localhost port=1234"
(0.01 secs, 526400 bytes)
ghci>  quickQuery' conn "select current_time" []
[[SqlZonedLocalTimeOfDay 21:55:08.55714 -0500]]
(0.00 secs, 1050128 bytes)

Then in the first terminal, ^C out of socat, and then try the query again back at the second:

ghci>  quickQuery' "select current_time" []
Segmentation Fault

Presumably, this would also happen if a network cable got unplugged, which makes HDBC rather unsuitable to writing reliable applications that must talk to a database across unreliable networks, because you are likely to segfault in situations that are out of control of your program.

Currently, executeMany is simply a call to replicateM execute. This could be optimised.