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

[Phillip J. Eby]

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.


>..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.


>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

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.