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/