[racket] Possible bug with DB virtual-connections and connection-pools

On 06/11/2014 11:40 AM, Curtis Dutton wrote:
> I ran into a problem while using virtual-connections with
> connection-pooling in my webserver.
>
> With a postgres database. If the database itself gets restarted after a
> connection is formed from the webserver, then I cannot re-establish a
> connection afterword.
>
> In production the database and webserver are on different hosts, but my
> development box those the same symptoms.
>
>
> So here is what I see....
>
> (require db)
>
> (define conn #f)
>
> (define (reset)
>    (set! conn (virtual-connection (connection-pool (λ ()
>                                                      (postgresql-connect
> #:server "localhost"
> #:user "postgres"
> #:password "postgres"
> #:database "test"))
> #:max-idle-connections 5))))
>
> (define (go)
>    (query conn "SELECT * FROM test"))
>
> (reset)
>
> (go)
> ;get results
>
> ;now restart my postgres server
>
> (go)
> . . tcp-write: error writing
>    system error: Unknown error; errno=10054
>
> ;ok what now?
> (disconnect conn)
> (go)
> . . tcp-write: error writing
>    system error: Unknown error; errno=10054
>
> ;now what
> (reset)
> (go)
> ;I get my results again
>
>
> I think what I expect is that the connection would be renegotiated for
> me. Or maybe at least renegotiated if I run disconnect afterword. If
> that isn't the case, that is fine... How should I handle this case?

I've just pushed a partial fix for this issue. The main problem was that 
the internal client connection didn't realize that the server had 
disconnected after a fatal error.

After the change, you will see an interaction like this instead:

(go)
;; => results
;; Restart the server
(go)
;; => query: terminating connection due to administrator command
;;      SQLSTATE: 57P01
(go)
;; => results

That is, the virtual connection will raise a "fatal" error, but then 
when you use it again, it will create a new internal connection and work 
fine.

(At least, that's what I see on postgresql 9.1.13; I was never able to 
reproduce the precise interaction you included in your email.)

Unfortunately, getting rid of that error in the middle seems difficult. 
The problem is that the server takes the time to say goodbye. Since 
there's input pending, the internal connection is still considered 
connected, so the virtual connection commits to using that internal 
connection rather than creating a new one.

Thanks for reporting this problem!

Ryan