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

[Phil Mayers]

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

class baseDM(storage.EntityDM):
    # Lots of interesting common code here
    # Is there an existing SQL reflector?

class Foo(model.Element):
    class fooid(model.Attribute):
	referencedType = model.String
    class foodescr(model.Attribute):
	referencedType = model.String

class FooDM(baseDM):
    # I'm sure this can be automated...
    defaultClass = Foo
    tablename = 'foo'
    primarykey = 'fooid'

class Bar(model.Element):
    class barid(model.Attribute):
	referencedType = model.String
    class fooid(model.Attribute):
	# Really, this should be:
	#   referencedType = Foo
	# ...but see below
	referencedType = model.String
    class installdate(model.Attribute):
	referencedType = model.String

class BarDM(baseDM):
    defaultClass = Bar
    tablename = 'bar'
    primarykey = 'barid'


...more code, then in the app:

for line in csv.reader(open(filename)):
    f = self.foodm.get(line['fooid'])
    if not f:
	f = self.foodm.newItem()
	f.fooid = line['fooid']
	f.description = line['description']
    else:
	if f.description!=line['description']:
	    # Is model.Element smart enough to ignore no-ops?
	    # Don't want redundant SQL
	    f.description=line['description']
    for b in line['bars'].split(':'):
	eb = self.bardm.get(b)
	if eb:
	    if eb.fooid!=f.fooid:
		print "Bar %s already assigned to %s" % (b, f.fooid)
	else:
	    eb = self.bardm.newItem()
	    eb.barid = b
	    eb.fooid = f.fooid

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

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.

-- 

Regards,
Phil

+------------------------------------------+
| Phil Mayers                              |
| Network & Infrastructure Group           |
| Information & Communication Technologies |
| Imperial College                         |
+------------------------------------------+