Re: clsql problems with postgresql

On Sat, 3 Oct 2009, Dr. Michael Dowling wrote:

> Date: Sat, 3 Oct 2009 17:17:10 +0200
> From: Dr. Michael Dowling <[email protected]>
> Reply-To: [email protected]
> To: [email protected]
> Subject: [CLSQL] clsql problems with postgresql
> 
> Hello!
>
> before I begin, I should say that I cannot answer email from Monday
> until Thursday as I work in another city without Internet access on
> these days.
>
>
> I am new to this list and to CLSQL, so I hope that I am on topic.
>
> I'm using Arch Linux x86_64 with sbcl-1.0.31.
>
> I have two problems.  After getting CLSQL to connect to my postgresql
> database under Arch Linux, there came an upgrade for postgresql to
> version 8.4.1, whereupon CLSQL failed to connect.  So, can it be that
> CLSQL requires upgrading for this version, and, if so, can anybody point
> me to a patch?
>
> The second problem I think concerns character coding.  At least I could
> get a response to my SQL queries _provided_ there were no German
> national characters involved in the output.  This remains true if I use
> an SQLite database instead of a PostgreSQL one.  At least, that seems to
> me to be the common cause.  I use iso-5589-15 character encoding both
> for my Linux environment and for the postgreSQL database.  Could it be
> that CLSQL insists on UTF8 encoding?  Can this be altered?
>
> Finally, both these problems have one aspect in common.  I get no error
> message; I am not thrown into the lisp debugger; the REPL is simply
> killed without comment!  That strikes me as being extreme!
>
> I would be very grateful for any help on these topics.
>
> Thanks in advance,
> Mike Dowling

The connection problem: which clsql backend are you using?  There is a
choice of clsql-postgresql and clsql-postgresql-socket.  I always use
clsql-postgresql-socket myself.  And it still works for me after
upgrading to postgresql 8.4.1.  The thing I like about
clsql-postgresql-socket is that it requires no C driver.  This
eliminates a lot of potential problems.  For instance, I have been
battling with getting clsql to work with clsql-oracle on RedHat.  It
worked fine for a while, but then stopped one day after a glibc
upgrade.  Less API layers is better, and less foreign function calls
is better too.

You should examine the file pg_hba.conf within your $PGDATA directory.
The listening interfaces are defined at the bottom of that file.  Make
sure postgresql is listening on the interface which you are trying to
use!

If you are having difficulty connecting via the local UNIX socket
connection, perhaps try to change it to a network socket.  In other
words: instead of connecting to localhost, connect to the machine's
public hostname.  You'll need to add an appropriate line to
pg_hba.conf.  Here's an example:


# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
# "local" is for Unix domain socket connections only
local   all         all                               md5
# IPv4 local connections:
host    all         all         127.0.0.1/32          md5
host    all         all         192.168.1.0/24        md5
# IPv6 local connections:
host    all         all         ::1/128               md5


Perhaps it would be worth testing your postgresql connection using
some other client.  Maybe a simple perl or PHP test script would do.
This way you could be certain that the connection works.  This would
eliminate it from the troubleshooting equation.

The character enciding problem: I have been fighting that one too.  My
fight has been with clsql-oracle.  I have posted my questions to this
list in the past.  I received one good reply from a ppostgresql user.
Though their solution didn't help me, it may very well help you.  Here
is the reply in its entirety:

--

I also have had to deal with databases with encoded characters and
clsql. My solution was to overwrite two implementation functions as
follows (in my case in package :postgresql-socket package - and using
sbcls unicode implementation). I would assume a similar approach would
work for other backends - and that flexi-streams could be used for CL
implementations without native utf8 external format (or if you are using
some other encoding on the database). If there is a better more general
approach available I too would like to know of it.

(defun read-socket-value-string (socket)
    (declare (type stream socket))
    #-sb-unicode
    (with-output-to-string (out)
      (loop for code = (read-byte socket)
            until (zerop code)
            do (write-char (code-char code) out)))
    #+sb-unicode
    (let ((bytes (make-array 64
                             :element-type '(unsigned-byte 8)
                             :adjustable t
                             :fill-pointer 0)))
      (loop for code = (read-byte socket)
            until (zerop code)
            do (vector-push-extend code bytes))
      (sb-ext:octets-to-string bytes :external-format :utf8)))

(defun send-socket-value-string (socket value)
    (declare (type stream socket)
            (type string value))
    #-sb-unicode
    (loop for char across value
          for code = (char-code char)
          do (write-byte code socket)
          finally (write-byte 0 socket))
    #+sb-unicode
    (write-sequence (sb-ext:string-to-octets value  :external-format :utf8
                                             :null-terminate t) socket)
    nil)

--

I have recently changed my locale from en_US to de_DE.utf8.  My
upgraded postgresql database is using this locale as well.  I can use
extended characters like öäüߤ, even without applying the code fix
given above.  If I were you, I would try to use utf8 if possible.  My
oracle character encoding issues are a result of the database using
the windows-1252 codepage.  If it were utf8, I think my problems would
disappear.

My system locale:

LANG=german
LC_CTYPE=de_DE.utf8
LC_NUMERIC="german"
LC_TIME="german"
LC_COLLATE="german"
LC_MONETARY="german"
LC_MESSAGES=de_DE.utf8
LC_PAPER=de_DE.utf8
LC_NAME="german"
LC_ADDRESS="german"
LC_TELEPHONE="german"
LC_MEASUREMENT="german"
LC_IDENTIFICATION="german"
LC_ALL=


My database locale:

                                  Liste der Datenbanken
    Name    | Eigentümer | Kodierung | Sortierfolge | Zeichentyp |    Zugriffsrechte 
-----------+------------+-----------+--------------+------------+-----------------------
  pippics   | pippics    | UTF8      | de_DE.utf8   | de_DE.utf8 |
  postgres  | postgres   | UTF8      | de_DE.utf8   | de_DE.utf8 |
  svbronze  | svbronze   | UTF8      | de_DE.utf8   | de_DE.utf8 |
  template0 | postgres   | UTF8      | de_DE.utf8   | de_DE.utf8 | =c/postgres
                                                                 : postgres=CTc/postgres
  template1 | postgres   | UTF8      | de_DE.utf8   | de_DE.utf8 | =c/postgres
                                                                 : postgres=CTc/postgres
(5 Zeilen)


My platform is Gentoo.  I have never used Arch Linux.  I have no
information regarding Arch Linux's effect on your situation.

Vielen Glück,
Carlos

_______________________________________________
CLSQL mailing list
[email protected]
http://lists.b9.com/cgi-bin/mailman/listinfo/clsql