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/ -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
