search

haskellari / postgresql-simple

Mid-level client library for accessing PostgreSQL from Haskell
Other
88 stars 46 forks source link

Using postgresql-simple to connect to Clickhouse #112

Open jonathangenlambda opened 1 year ago

jonathangenlambda commented 1 year ago

We are trying to connect to Clickhouse via its "postgres emulation port" 9005 (see).

Unfortunately sending a simple SELECT 1 query fails with SqlError {sqlState = "", sqlExecStatus = FatalError, sqlErrorMsg = "", sqlErrorDetail = "", sqlErrorHint = ""}

When looking at the error log of Clickhouse we see:

PostgreSQLHandler: DB::Exception: Syntax error: failed at position 15 ('TO'): TO ISO;SET client_encoding TO UTF8;SET standard_conforming_strings TO on. Expected one of: token, Dot, Equals

We checked the code of postgresql-simple and found the corresponding location in Database.PostgreSQL.Simple.Internal which executes "SET datestyle TO ISO;SET client_encoding TO UTF8;SET standard_conforming_strings TO on" eventually.

I assume currently there is no way to prevent this from getting executed? We will fork the repo and remove this execution and see what happens - maybe this could be made optional?

jonathangenlambda commented 1 year ago

We have removed the corresponding executiong of "SET datestyle TO ISO;SET client_encoding TO UTF8;SET standard_conforming_strings TO on" and are now able to successfully query Clickhouse via postgresql-simple.

Please consider introducing a configuration/argument that allows for disabling these "SET..." commands.