ORM integration?

Hi folks

I know many people here loathe ORM systems. I'm one of them, but I still
use them when they appear to be appropriate, despite their problems.

In the process I've come to realize that ORMs in general have a couple
of issues that could be avoided with some help from the database.
Specifically:

- They need to fetch graphs of records that keep records associated
with peers in relationships. Essentially they want nested sets.
Ideally they need to be able to do this with a WHERE or
LIMIT/OFFSET on the "root" relation, so they can do batch
fetching of blocks of records in contexts where holding a
transaction open (permitting cursor use) isn't appropriate.

Currently ORMs do this by doing multiple LEFT OUTER JOINs and
post-processing the results to eliminate duplication of data.
Needless to say this is incredibly inefficient. It also makes
using LIMIT/OFFSET nigh impossible, so they often fetch the whole
data set into local memory (!!) even if the app only asks for a
small fragment of it.

A native way to fetch a query's results as a nested set, as
(say) XML or JSON, would potentially be a huge bonus if ORM
systems could be convinced to use it. It's potentially possible
already with use of nested subqueries and array_agg.

I've even been idly playing with the idea of using
PL/Java to build a Java object graph in memory and send that
to the client!

I'm wondering if anyone's been down the path of building a
more ORM-friendly relation graph representation database-side
and sending it to the client.

- ORMs tend to lack cache coherence. They generally maintain a cache
of records fetched from the database - partly because their fetching
is so expensive (as noted above) and partly just as a general
performance optimisation. The problem is that the database has no
way to invalidate the ORM's cache of a particular record when changes
are made to it in the database, so the ORM's cache tends to get out
of sync with the database.

In my code I can work around that by turning the blasted thing off.
I don't need the kind of performance that the cache is intended for.
Others do need it, and I'm curious about whether anyone's looked into
approaches to help integrate the caches in ORMs like Hibernate with
the backend database to keep the cache consistent.

I'm currently thinking that the upgraded LISTEN/NOTIFY mechanism
in 9.0 might be a good channel for sending cache invalidation
messages with. Thoughts? Ideas?

--
Craig Ringer

Tech-related writing: http://soapyfrogs.blogspot.com/