Re: On the performance of views - Mailing list pgsql-performance

Josh Berkus wrote:
> Shridhar, Bill,
>
>>>Is MSSQL allows to mix rows of two types in single function invocation,
>>>I am sure that would be a hell lot of porting trouble..
>
> There's also the question of whether or not PG would every want to do this.
> Frankly, as a once-upon-a-time SQL Server application developer, I found the
> ability to return multiple rowsets from a single SQL Server procedure pretty
> useless, and a source of endless debugging if we tried to implement it.

Well, I would have agreed with the uselessness, until this project.  The
"source of endless debugging" frightens me!

>>1) Returning a homogenious set of rows, but the composition of those rows
>>    will not be known until run time, as a different set of logic will be
>>    done depending on the values of some parameters.
>
> This can be done with Set Returning Functions.   The issue is that the call to
> the function requires special syntax, and the program calling the function
> must know what columns are going to be returned at the time of the call.
> Hmmm, is that clear or confusing?

Clear as mud.  In my case, my application simply doesn't care what row of
what kind are returned.  See, I'm writing the server end, and all said and
done, it's really just glue (frighteningly thick glue, but glue nonetheless)

Basically, all I do is call each query in turn until I've collected all the
results, then marshall the results in to a SOAP XML response (using gsoap,
if anyone's curious) and give them back to the client application.  It's
the client app's job to figure out what to do with them, not mine.  I
never would have written it this way on my own, but the client app is
already written, so as I migrate it to the client-server model, the
programmers who wrote the client app are specifying what they expect me
to provide them.

The only advantage I see is that combining a number of result sets into a
single response reduces the number of round trips between the client and
server.

If Postgres supported combined recordsets, it would simplify my C code
somewhat, and possibly speed up things a bit by making less calls between
the soap server and Postgrees ... overall, I don't see a huge advantage
to it.

>>#2 has to be solved at the application level.  My solution so far has
>>been to create multiple Postgres functions, call each one in turn, join
>>the results in C, and return them as a structure via SOAP to the client.
>>May not be the easiest way to get it working, but it's working so far.
>>(although I'm always open to suggestions if someone knows of a better
>>way)
>
> See my comment above.   I frankly don't understand what the use of a
> non-homogenous recordset is.   Can you explain?

I hope what I already mentioned explains enough.  If I understand the
application enough (and it's amazing how little I understand about it,
considering I'm writing the server end!) what they're doing with these
combined recordsets is driving their forms.  When a form instantiates,
it makes a single soap call that causes me to return one of these
non-homogenious recordsets.  One row may have data on how to display
the form, while another has data on what buttons are available, and
another has the actual data for the header of the form, while the
remaing rows might have data to fill in the lower (grid) portion of
the form.

If I had designed this, I would just have done the same thing with
a homogenious recordset that had values set to null where they weren't
apropriate.  This would have bloated the data being transfered, but
otherwise would have worked in the same way.

Now that I'm aware of MSSQL's combined recordset capability, I'm not
sure if I would do it differently or not (were I developing on a system
that had that capability)  I probably won't have a concept of whether
or not I think this is a good idea until this project is further along.

--
Bill Moran
Potential Technologies
http://www.potentialtech.com