Mike Beaton <mjsbeaton@xxxxxxxxx> writes: > [ generally accurate information ] > **WARNINGS** > It would *never* make sense to do `FETCH ALL FROM cursor` for > astronomically large data, if your client side code (including your data > access layer) has any bottleneck at all at which means that all the data > from a command is fetched before any processing can be done. Many data > access layers (and especially data access wrappers) are like this. So > beware. But it is also true that not all client side code is made this way. It would probably be good to point out that most client-side libraries will do it that way, including libpq, because then they can make success or failure of the query look atomic to the application. If you use an API that lets you see rows as they come off the wire, it's up to you to recover properly from a query failure that occurs after some/many rows have already been returned. > Returning huge data using a `TABLE` or `SETOF` return type from within a > PostgeSQL function will *always* be broken (i.e. will create a huge buffer > and take a very long time to start). This will be so whether the function > is called from SQL to SQL or called over the wire. I believe this is false in general. I think it's probably true for all the standard PL languages, because they don't want to bother with suspending/resuming execution, so they make "RETURN NEXT" add the row to a tuplestore not return it immediately. But it's definitely possible to write a C function that returns a row at a time, and depending on what the calling SQL statement looks like, that could get streamed back to the client live rather than being buffered first. As a trivial example, if you do select generate_series(1,100000000); in psql and watch what's happening with "top", you'll see psql's memory usage going through the roof (because libpq tries to buffer the result) but the connected backend's memory usage is steady as a rock --- nor does it dump the data into a temporary file. On the other hand, select * from generate_series(1,100000000); does dump the data into a temp file, something we ought to work on improving. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
