July 2013 - postmodern-devel - mailman3.common-lisp.net

[postmodern-devel] A question about connection pooling, and the many ways to connect.
by Jim Barrows 12 Nov '13

12 Nov '13

I'm a java developer, and I think I'm expecting something to happen that's not. In Java I setup a pooled connection, and just use it. The connections pool themselves. I had thought that by doing: (setf *database* (connect "PomBanDone" "pombandone" "pombandone" "localhost" :pooled-p T))) that I would get connection pooling. However now that I'm getting a better understanding of Lisp and I think Postmodern, that connections aren't getting pooled. I think I have to use (with-connection (spec &body body) in order to make the connection pooling work. Is that the case? If not, why would I want to use with-connection? Thanks! -- James A Barrows

3 2

cl-postgres convert nil to false
by Julien Danjou 29 Jul '13

29 Jul '13

Hi there, I'm dealing with a problem and I'd like some enlightenment. I've a table with text columns that can't be NULL. In my Lisp program, sometimes I'm assigning `nil' as value for this column, either via a direct query execution or via the DAO. What I would expect in this case, is to PostgreSQL to raise an error. But this does not happen. What happens is that (cl-postgres:to-sql-string nil) returns "false", and that the query is built to: INSERT INTO mytable (mytextfield) VALUES (false); PostgreSQL implicit type conversion kicks in and insert the string 'false' into the field, which does not trigger an error since this it not NULL. The obvious fix here is to patch all my code to convert nil to :null, but this is a pain. I don't want to do the type checking I'm doing with PostgreSQL one more time in my application. What I'd like is for cl-postgres to consider nil as being NULL. The boolean false value could be mapped as :false, which would also make sure no code is inserting false just because a value is actually nil. The change is pretty trivial and I'm running with it right now, which allowed me to debug a lot of implicit nil conversion to 'false' in text fields. I recognize this is something that would break compatibility, so that might be enabled via a defparameter or something. Does that sounds like a good idea, or do I miss an obvious problem? -- Julien Danjou ;; Free Software hacker ; freelance consultant ;; http://julien.danjou.info

2 2

Timezone handling
by Julien Danjou 25 Jul '13

25 Jul '13

Hi there, I'm struggling with timezone handling in Postmodern. I'm using timestamp with time zone column types, and I'm looking for the correct way to feed postmodern such information on insert. Basically, I don't see any class that would fit. I've tried to grep through the code but didn't find much about timestamp/timezone handling. Concretely, I'm having UTC timestamp that I want to insert into those timestamp-with-time-zone columns, but I don't know how to tell Postmodern that it should postfix them with something like +00 so PG understand it should not default them to its default timezone. On the same topic, I thought that one easy solution would be to set the session timezone to UTC. It's easy enough to come with a macro doing a SET TIMEZONE='UTC' before each request, but honestly, I'd like to avoid that and do it only at connection time. Now, I've read through cl-postgres code, and I was hoping being able to plug my code at connection time, but it seems everything is defined as function and not as method, which seems weird to me, and not really pluggable in the end. Any hint appreciated! Cheers, -- Julien Danjou ;; Free Software hacker ; freelance consultant ;; http://julien.danjou.info

3 6

nyef's postmodern gripes
by Zach Beane 25 Jul '13

25 Jul '13

Postmodern fits my needs very well, but whenever it comes up in discussion, nyef has dropped vague disparagement. I pestered him to write down some specifics, so here they are: http://paste.lisp.org/display/138060 Zach

3 2

nyef's postmodern gripes
by Sabra Crolleton 20 Jul '13

20 Jul '13

Is there a better way to write "common table expressions " (with and with recursive) than: (def-sql-op :with (&rest args) (let ((x (butlast args)) (y (last args))) `("WITH " ,@(sql-expand-list x) ,@(sql-expand (car y))))) (def-sql-op :with-recursive (&rest args) (let ((x (butlast args)) (y (last args))) `("WITH RECURSIVE " ,@(sql-expand-list x) ,@(sql-expand (car y))))) They work for me, but I have the feeling there is a better way to do it. Actual use would be something like the following (using the example from the postgresql website): (defun test2 () (query (:with-recursive (:as 'regional-sales (:select 'region (:as (:sum 'amount) 'total-sales) :from 'orders :group-by 'region)) (:as 'top-regions (:select 'region :from 'regional-sales :where (:> 'total-sales (:select (:/ (:sum 'total-sales) 10) :from 'regional-sales)))) (:select 'region 'product (:as (:sum 'quantity) 'product-units) (:as (:sum 'amount) 'product-sales) :from 'orders :where (:in 'region (:select 'region :from 'top-regions)) :group-by 'region 'product)))) Sabra

1 0