On Fri, Sep 17, 2010 at 3:59 PM, Sam Mason <sam@samason.me.uk> wrote:
> On Fri, Sep 17, 2010 at 04:51:46PM +0200, Willy-Bas Loos wrote:
>> i have a function that produces a result in xml.
>> that is one row, one value even, but it grows pretty large.
>> how is that handled?
>
> Rows are sent back in the entireity, so the PG instance would need
> enough memory to work with that row. When you're running a 32bit
> version of PG, values whose size is beyond ~100MB are a bit touch and go
> whether it will work.
well, the entire result has to be stored in memory on the client side,
not just the row. This is why certain operations are so much more
convenient on the server (like insert into foo select * from bar).
After working with pg for a while you just get used to paging through
results on the client (I try to keep the result size under 10mb or so)
using any one of a number of methods. This is probably 'good sql
style' anyways. One group of people that bump into this are xbase
refugees who could browse massive results without fear due to the
serverless architecture.
There is a probably a good case to be made for more flexible result
architecture in libpq that supports streaming, but I'd personally much
rather see features like wCTE and stored procedures that remove cases
where you have to send lots of data through the protocol to the
client.
merlin