On Tue, Aug 21, 2012 at 09:39:20AM +0800, Craig Ringer wrote: > On 08/21/2012 03:06 AM, Martijn van Oosterhout wrote: > >I'm not sure I have an opinion on pushing ORM features to the database > >layer, SQLAlchemy is doing a pretty good job for me already. > > There are some things ORMs could really use help from the database > with, though. Particularly when fetching larger object graphs with > deep relationships. The classic ORM chained-left-join pattern leads > to *awful* explosions of join sizes, and huge amounts of duplicate > data being sent. The n+1 selects alternative is even worse. Well, Postgres in principle supports arrays of records, so I've wondered if a relationship join could stuff all the objects in a single field of the response using an aggregate. I think what's always prevented this from working is that client would have to parse the resulting output text output, which is practically impossible in the face of custom types. What seems more useful to me is working on returning multiple resultsets, which could be interleaved by the server, so you could do things like (syntax invented on the spot, essentially WITHs without an actual query): WITH order_result AS (SELECT * FROM orders WHERE interesting) WITH widget_result AS (SELECT * FROM widgets WHERE widgets.order=order_result.id); Here the server could perform the join and return both sides of the join in seperate result sets. But named, so they can refer to eachother. I suppose for backward compatability you'd have a master result set with named children, otherwise the memory management gets hairy. And I have no idea if the BE/FE protocol can handle it, but it would be useful, and I think easy for ORMs to use, since they can stuff the user query in the first bit, and tack their relationship joins on the end. If the bare WITHs look like it might be ambiguous, you could make the actual query something like: ... RETURNS order_result, widget_result; which might be better since it allows the original query to use WITHs without interfering with the result. Have a nice day, -- Martijn van Oosterhout <kleptog@xxxxxxxxx> http://svana.org/kleptog/ > He who writes carelessly confesses thereby at the very outset that he does > not attach much importance to his own thoughts. -- Arthur Schopenhauer
Attachment:
signature.asc
Description: Digital signature
