[PEAK] entityDM, Postgres/psycopg and cursors blocking
Phillip J. Eby
pje at telecommunity.com
Sat Sep 11 12:50:35 EDT 2004
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.
>..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.:
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.
More information about the PEAK