Re: BUG #5982: recursive type crashes postgres

On Fri, Apr 15, 2011 at 4:27 PM, Kevin Grittner
<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
>
>> There are lots of use cases for this.  I use composite types to
>> marshal data to the client all the time, and recursive structures
>> are fairly common in many classic problems.  Recursive composites
>> fit the bill perfectly.
>
> I'm trying to get my head around why SQL composite types are a good
> way to marshal complex object graphs with recursion.  I can see
> where it could be done, I'm still not convinced that it's better
> than SQL passing out data in tabular form with relationship
> established by matching values.  In other words, when is it a good
> idea to do the relation to object mapping in the database engine?

You can certainly do that. however that requires the client to put
take the data and immediately put it in relational-ish data structures
so you can browse the data properly. That's a popular approach,
albeit expensive and error prone. If the database sends it back to
you constructed, you can just stupldly iterate over the returned set
and process it as you go (you could in fact stream the data directly
off the protocol of the client library supports it).

arrays, composites, etc. are just ways of setting up ad hoc structures
for passing. they serve the same purpose (at least, they way I use
them) as xml or json documents getting ripped directly in and out of
the database. maybe it's a good idea to nest data that way going and
out, and maybe it isn't, but it's fast and effective. xml and json
solve the problem but introduce a completely difference set of
headaches because they are not tightly coupled to the database...and
slow.

Consider we also have to send data to the database. I can recursively
wrap up data in the client using libpqtypes, fire it to a receiving
function which unnests it and processes it. This is a couple of
orders of magnitude faster than streaming it in over multiple queries.
I could for example, send in a entire bill of materials assembly in a
single query. I don't consider that hacky, in fact I think a lot of
classic SQL approaches are in fact hacks around not being able to do
things that way.

merlin