[PEAK] entityDM, Postgres/psycopg and cursors blocking each
p.mayers at imperial.ac.uk
Sat Sep 11 14:06:20 EDT 2004
Phillip J. Eby wrote:
> At 03:22 PM 9/11/04 +0100, Phil Mayers wrote:
>> Doing a proof-of-concept in PEAK at the moment - very nice system.
>> I'm running into some behaviour specific to the psycopg Postgres DA,
>> and it's connection pool. The problem is coming about because I'm
>> using 3/4 DMs that are apparently creating independent cursors, and at
>> transaction commit time, the inserts/updates for one DM block on the
>> previous selects of another DM (e.g. in the case of foreign keys).
> Multiple cursors shouldn't be blocking each other. Are you sure you're
> not using multiple DB connections? Your samples didn't show where the
> DM's get their connections from.
I *thought* that was the psycopg-specific thing - each cursor
corresponding to a different SQL connection; as the README puts it:
psycopg is different from the other database adapter because it was
designed for heavily multi-threaded applications that create and destroy
lots of cursors and make a conspicuous number of concurrent INSERTs or
Every open Python connection keeps a pool of real (UNIX or TCP/IP)
connections to the database. Every time a new cursor is created, a new
connection does not need to be opened; instead one of the unused
connections from the pool is
Having said that, I could be wrong (proably am - see below) which is
embarassing, considering how much I've used it - I really *ought* to
know how it works... :o/ Certainly upon re-reading, it's not entirely
clear to me.
>> ..however, when the transaction commits, the addition of the new "bar"
>> fails, since the SQL INSERT waits on the "select * from foo where
>> where fooid=xxx" that was done previously by the foo DM *in another
>> What's the appropriate way to deal with many interacting DMs that
>> share a connection but have differening cursors (and can potentially
>> block each other)?
> Make sure they're using the same connection object. They should be
> using an 'Obtain(configurationKey)' binding rather than a 'Make()'
> binding, and the 'configurationKey' should not be a database URL. It
> should either be an interface for which you've configured a factory in
> the application's .ini file, or it should be a property name that's
> listed under '[Named Services]' in your application .ini.
> If you've directly used a database URL, or used a property name that
> isn't a named service, each DM will have its own database connection,
> which would result in the behavior you describe.
Yes, sorry the code was contrived due to the very local-specificity of
the app. I am using Obtain to get the connection (created by a Make on
the app object), but it *is* using a URL in the app.ini (code stolen
from the Wiki example, since my tarball didn't seem to have bulletins
example in it?)
Foos = binding.Make(
Bars = binding.Make(...)
# The SQL connection
db = binding.Obtain(PropertyName('hdb.db'))
# Loads more common code
app = importString('example.app:DB')
db = naming.LinkRef('psycopg://user:@localhost/db')
I had thought this was right, but obviously not - thanks, changing to a
Named Service did solve the problem, so I was actually spawning multiple
Hmm - back to the psycopg docs to figure out what I've misunderstood
about its connection pool.
>> Also, as you can see above, there's a lot of manual code which I'm
>> sure some hypothetical SQL reflector could do automatically? Has this
>> been done, or should I write it (I need it, and it's not too hard)? If
>> I write it, would people be interested in having it added to PEAK?
>> What's the licensing / code style / etc.
> A lot of people have written such reflector services; see e.g.:
Thanks - that code looks very very similar to what I've written, I
thought that others would have cracked it. I have yet to figure out how
to handle joins, but there we go.
> The plan for PEAK is to replace the DM paradigm altogether with a
> "workspace" paradigm coupled with a query language. The resulting API
> will be somewhat closer to e.g. Hibernate in the Java world.
Hibernate I've not seen, since I gave up on Java about ten minutes after
seeing the python interactive interpreter but SQLObject I'm familiar
with - aside from it's onerous schema limitations, it's the "ideal"
solution for me.
I'll go look at some Java docs (ack!)
Thanks for the help
More information about the PEAK