Hi David,

On 3/13/2017 12:49 PM, David Storrs wrote:
I've got a centralized database connector:

    (define dbh
                 (thunk (postgresl-connect ...)))))

This gets passed around from handle to handle and into various temporary or ongoing worker threads. Thinking about it, I'd like to check that I've understood it properly:

- The virtual connection (VC) itself is very lightweight and it's fine for it to persist throughout the run of the program.

Yes. However, virtual connections are multiplexed over real connections. The underlying real connection can be stolen (given to another virtual connection) any time it is idle - such as when it is between queries. If you have a thread that performs a sequence of queries:


then it's possible for the real connection to be snatched away between the queries. Of course, when the thread goes to use the VC again, it will get *some* real connection, but there's no guarantee that it will be the *same* one each time.

This is why you can't use prepared queries with virtual connections - because there is no guarantee that the connection that prepared the query will be the same one that tries to execute it.

- The connection pool (CP) inside the VC will maintain a (small?) collection of persistent connections to hand out upon request, so I'm not paying the connection-setup-time penalty every time I use it.

Yes and no. The pool will try to keep idle connections, but the DBMS eventually will close them (subject to the keep-alive settings in postgresql.conf).

- It's also fine to pass the VC into other threads. It will be shared state between the threads, but the CP will keep their connections isolated and when the threads terminate it won't interfere. (Ignore pathological cases -- obviously if I give it to enough threads and they all use it at once then we might exceed the DB limits on number of handles, speed, bandwidth, etc.)

No. A VC is not tied to any particular real connection, so it's not possible to guarantee that 2 threads sharing a VC do not share an RC. It's actually quite likely in a lightly loaded system.

If you need connection isolation, you have to use real connections.

- The CP will create more connections as needed, so there's no need to worry about running out (barring pathological cases).

Subject to limitations. You can't open more real connections than the DBMS permits. However, you can have many more virtual connections than real connections.

- db connections will get garbage collected normally, at the marked points:

I have to defer this to someone who knows for sure. Real connections will persist until closed. Virtual connections can come and go like dreams.

Assuming I've understood all that correctly, my last question would be how to get around the 'can't do prepare with a virtual connection' issue for situations where I've been passed a connection (perhaps from third party code) and it might or might not be virtual. First, to dispose of some quibbles:

- One answer is "well, don't do that." Write a contract on the function that mandates the connection being non-virtual.

- Another is "well, don't do that." Test if the connection is virtual and, if so, don't use prepare.

- Another is "well, don't do that." Pass around a dsn instead of a VC and generate connections as needed.

None of these is terribly satisfying. The first violates the principle of "be generous in what you accept and strict in what you emit", the second gives up a lot of speed if we were in a situation where we wanted to use 'prepare' in the first place, and the third isn't feasible since I won't always have control over what a third-party library emits.

My ideal solution would be something like this:

(define (myfunc a-handle)
    (define dbh
        (if (virtual-connection? a-handle)
(my-function-to-do-connect-with-dsn (get-dsn-from a-handle))
    (define sth (prepare dbh "select foo from bar where baz = $1"))

In other words, check if the connection is virtual and, if so, extract the dsn from it and use that to create a non-virtual connection.

Is there a way to do that? I've been through the db module docs and Google but not found a way. Did I miss something?

Even if you could discover the DSN, it would not help if the DBMS connection limit is reached, or equivalently some user limit imposed by a proxy server that you don't know about.

There is no way in the current implementation to get at the real connection underlying the virtual one. Because the system multiplexes connections, I think it probably would break badly if you were able to somehow get hold of the underlying real connection.

I know this isn't what you want to hear.

You received this message because you are subscribed to the Google Groups "Racket 
Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to racket-users+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Reply via email to