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

Thank you for taking a look.

For my scenario, your fix is great for me. It is a rare occurrence when
this problem occurs, and the end result is that a user of my websites has
to hit the refresh button once.


On Thu, Jun 12, 2014 at 7:02 PM, Ryan Culpepper <ryanc at ccs.neu.edu> wrote:

> 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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.racket-lang.org/users/archive/attachments/20140616/6ba488db/attachment.html>