Hi David,
On 3/13/2017 12:49 PM, David Storrs wrote:
I've got a centralized database connector:
(define dbh
(virtual-connection
(connection-pool
(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:
:
do_query_1
:
do_query_2
:
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))
a-handle))
(define sth (prepare dbh "select foo from bar where baz = $1"))
...stuff...
)
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.
George
--
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.
