Re: How hard would a "path" operator be to implement in PostgreSQL

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(at)svana(dot)org> 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