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

On 08/21/2012 03:01 PM, Martijn van Oosterhout wrote:
> 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.

That's where the new JSON support is interesting; it provides a much
more commonly understood and easier to parse structured form for
results, so trees (but not more general graphs) can be returned.

> 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

That'd certainly be a nice option, but there's a big difference between
it and the other form: With multiple result sets, the client still has
to effectively join everything client side to work out the relationships
and build a graph or tree (usually an object graph).

On the upside, multiple result sets can be transformed into graphs,
where JSON can only represent simple trees without introducing the need
for cross reference resolution.

I like your notion of chaining common table expressions so you can
return intermediate CTs as result sets. That feels clean.

Currently many ORM systems (those that don't do horrible giant chained
left joins or n+1 selects) do follow-up queries that repeat much of the
work the 1st query did, eg:

SELECT a.*
FROM a INNER JOIN b ON (...) INNER JOIN c ON (...)
WHERE expensive_clause;

SELECT b.* FROM b WHERE b IN (
SELECT a.b_id
FROM a INNER JOIN b ON (...) INNER JOIN c ON (...)
WHERE expensive_clause;
);

... and that's one of the *nicer* ways they execute queries.

Multiple result set support would be pretty handy for stored procs, too;
it's something people grumble about occasionally, though I've never
needed it and would just use refcursors if I did.

How do other DBs handle multiple result sets? Do they only support them
from stored procs?

> 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.

I suspect the BE/FE protocol would be a bit of an issue. That's part of
the reason I was thinking about the utility of the JSON support for
this, because with a few aggregate operators etc it'd be a fairly low
impact solution.

--
Craig Ringer