[PEAK] entityDM, Postgres/psycopg and cursors blocking each other

Phil Mayers 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:
> 
>> All,
>>
>> 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 
UPDATEs.
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
<snip>

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 
>> cursor*.
>>
>> 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?)

app.py:

class DB(commands.AbstractCommand):

     Foos = binding.Make(
             'app.storage.FooDM',
             offerAs=[storage.DMFor(Foo)],
             )
     Bars = binding.Make(...)

storage.py:

class baseDM(storage.EntityDM):
     # The SQL connection
     db = binding.Obtain(PropertyName('hdb.db'))
     # Loads more common code
class FooDM(baseDM):
     # stuff

app.ini:

[peak.running]
app = importString('example.app:DB')

[example]
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 
connections.

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.:
> 
> http://www.eby-sarna.com/pipermail/peak/2003-October/000772.html
> 

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 mailing list