Re: subtransactions and FETCH behaviour (was Re: PREPARE

Alvaro Herrera wrote:

> Well, the proposal of implementing it like holdable cursors means using
> a Materialize node which, if I understand correctly, means taking the
> whole result set and storing it on memory (or disk).  So the same
> question arises: why bother implementing that at all?  Of course the
> answer is that the server definitely _has_ to provide the functionality.

It seems more reasonable to implement this on the server side -- it 
already has the data to hand (not on the other side of a network 
connection) and is much more likely to have memory/disk available.

> Now, the cursor problem is beyond me ATM -- it needs deep understanding
> of the executor code that I do not have and won't be able to develop in
> two weeks ... if there's no reasonable solution in sight maybe the best
> we can do is revert the whole nested xacts patch (or at least disable
> the funcionality) so we have more time to solve this particular problem.
> 
> Sadly, AFAICS this is the only major problem with the functionality, so
> it would be a pity to throw away all work only for this.

Is there an approach that means we can do *something* sane with cursors 
and keep nested transactions? Something like "close all non-hold cursors 
on transaction start"? I think the JDBC driver can pass this restriction 
on to the application if we document it -- "creating a savepoint or 
starting a new subtransaction invalidates all open resultsets" -- as a 
necessary limitation of the current backend's implementation. I do think 
that this will make savepoints useless in many cases, but it's better 
than not having subtransactions at all.

Then maybe better cursor support can be done for 7.6?

-O