Returning `executemany()`

[dvarrazzo]

In #163 we brushed on the idea of having executemany() returning the resulting results.

Let's explore this idea.

One possible interface already available could be Cursor.nextset(). The results might be retrieved only if the queries return TUPLES_OK: for instance INSERT... RETURNING or SELECT function() with a side effect, discarded for COMMAND_OK results.

Note that the user might accidentally consume a lot of memory if they are not aware of the feature. However it seems an unlikely occurrence. We might add a returning: bool argument to control it, but I'd be comfortable enough to default it to True.

[dvarrazzo]

Like in #145, this feature might take advantage of the pipeline/batch mode for performance. Probably the pq-level functions are enough for it.

[eheu]

sorry for nitpicking - just occured to me, maybe the default returning value should be False so there wont be a performance hit just from upgrading to 3.1? admittedly only however slight the cost of having the result set returned is, but still...

[dvarrazzo]

Hello @eheu thank you.

We are still in time to change it. However the memory would be only used in case executemany runs a query returning a result, i.e. people use a SELECT with side effect or an INSERT... RETURNING. There is not extra penalty when, for instance, a query with just an INSERT is executed.

So I think the application affected would be relatively few, ones executing massive INSERT... RETURNING or SELECT in an executemany and discarding the result anyway, because there wasn't a way to access it previously. The downside for these apps is that the memory of these results is held for the time between the executemany and the next time the cursor is used again, or it is closed. There is no performance impact because, even previously, the results were transmitted to the client anyway - just to be discarded immediately.

So all in all it seems a low risk change for the benefit of a more convenient interface. I don't see returning=False very used, unless people know that the executemany is returning massive but unneeded results. In that case, by the way, it would make much more sense to reformulate the query in order to not return the result at all, because regardless of the returning value, and both in psycopg 2 and 3, the result values are transferred back to the client.

[dvarrazzo]

@eheu I have changed the default to be False, based on the fact that if the user didn't ask us for something we shouldn't do extra work :) We are improving executemany performance by using libpq batch mode (#260) and the extra fetch might also imply slowing the pipeline, so we will return the results only if users really want it.

Thank you again for your input!