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

[Phil Mayers]

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